0

I have a strange (to me) performance issue. Based on the schema that runs the query I received very different performance. Here's my setup.

Oracle 12c DB 
    SAMPLE schema/user
       WIDGET table (300,000 rows/10 columns of varchars, nvarchars, dates, and numbers)
    USER1 schema/user
    USER2 schema/user
    DBA1 schema/user
--SELECT granted on WIDGET table to USER1 and USER2. 
--DBA1 already has access to all table accross all schemas and has many more privs. 

When I execute either of the following:

select * from SAMPLE.WIDGET;
select count(1) from SAMPLE.WIDGET;

It takes about 0.5 seconds in SAMPLE schema or DBA1 schema. It takes about 10 seconds in USER1 and USER2 schema.

So I'm looking for the following:

Under what condition(s) can query performance in Oracle differ based on the user you are logged in to when you execute a query?

2
  • Do you use resource manager or limits? – eckes Sep 20 '19 at 3:51
  • @eckes Not that I'm aware of – deepee1 Sep 23 '19 at 16:16
1

You can try to check what the current consumer group for the different sessions are. I.e. log in as SAMPLE and USER1 and run

SELECT SID,SERIAL#,USERNAME,RESOURCE_CONSUMER_GROUP FROM V$SESSION
    WHERE USERNAME IN ('SAMPLE','USER1','DBA1');

You can also check what the actual plans are assigned as DBA:

SELECT * FROM DBA_RSRC_CONSUMER_GROUP_PRIVS
    WHERE GRANTEE IN ('SAMPLE','USER1','DBA1');

This is designed to grant different resources for users (and admins) and might explain what you see. The plan should actually be shared across all executing users (it remembers the user which parsed it first). Just to be sure, auto trace the execution for the two different users.

1
  • the consumer group is the same for all. DBA_RSRC_CONSUMER_GROUP_PRIVS shows no rows for the grantees in question. – deepee1 Sep 26 '19 at 14:27

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.