Re: Extremely poor execute performance

Andy Baumhauer wrote:
Martin,

Thank you so much for the spot on reply. The work you saved with that reply is greatly appreciated.

Glad to hear it. resmg:wait quantum caught me some time ago when a complicated query was taking over 8s every now and again and yet running it outside of the application in sqlplus took less than .1s. It must now be one of the most optimised queries we have as we spent some time on it before we realised the remgr was adding an 8s wait every now and again because of the total work the application had done.

Martin
I checked the resource groups with this statement:

COL initial_rsrc_consumer_group FORMAT A24 HEADING 'Resource|Consumer|Group'
COL username FORMAT A12 HEADING 'User Name'
SELECT
initial_rsrc_consumer_group,
username
FROM dba_users
ORDER BY 1,2
;

The user was in the DEFAULT_CONSUMER_GROUP. I issued the following commands as the sysdba to move the user to the SYS_GROUP, and the problem went away (I'm the only user of this database, and it exists for conversion of data.

EXEC DBMS_RESOURCE_MANAGER_PRIVS.GRANT_SWITCH_CONSUMER_GROUP('SOMEORACLEUSER', 'SYS_GROUP', TRUE);
EXEC DBMS_RESOURCE_MANAGER.SET_INITIAL_CONSUMER_GROUP('SOMEORACLEUSER', 'SYS_GROUP');

I hope this helps someone else in the future.

Andy Baumhauer



.