Oracle 12c query performance varies based on schema/user

Question

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?

• eckes: Do you use resource manager or limits?
• deepee1: @eckes Not that I'm aware of

Answer 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.

• deepee1: the consumer group is the same for all. DBA_RSRC_CONSUMER_GROUP_PRIVS shows no rows for the grantees in question.