Oracle 12c query performance varies based on schema/user

oracleoracle-12c

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?

Best Answer

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.

Related Solutions

Oracle 12c create user

If you want to create a user that can admin any of the pluggable databases, prefix the username with C##.

For example:

create user c##name identified by password;

Testing Multiple Queries from Input File – Oracle, Java, Scripting, Python

It looks like you need to write dynamic SQL. Dynamic SQL is SQL the creates SQL that can get run. Here is a simple example of dynamic SQL.

SELECT 'ALTER ' ||
       CASE object_type 
       WHEN 'PACKAGE BODY' THEN 'PACKAGE '
       WHEN 'TYPE BODY'    THEN 'TYPE '
       ELSE object_type ||' '
       END || owner ||'.' || object_name ||' '||
       CASE object_type 
       WHEN 'PACKAGE BODY' THEN 'COMPILE BODY;'
       WHEN 'TYPE BODY'    THEN 'COMPILE BODY;'
       ELSE 'COMPILE;'
       END text
  FROM dba_objects
 WHERE status = 'INVALID'
 ORDER BY 1;

You should also have formatting in the SQL to keep the output of the SQL as clean as possible.

SET TERM      OFF
COLUMN text FORMAT A150
SET ECHO      OFF
SET TRIMSPOOL ON
SET TAB       OFF
SET FEEDBACK  OFF
SET PAGESIZE  0
SET LINESIZE  150
SET TIMING    OFF
SET TERM      ON

Then you just need to write the SQL that generates SQL, if you need to connect as a different user you can include a line like:

SELECT 'CONNECT user1/cdjkfghljsdg@sidname' text from dual;

But you need to figure out how to write the SQL, since you have not given many details. You will spool to a file, then turn on feedback, set pagesize 50 or so, probably set timing on etc, then run the output of the spool file. You may want to spool to a log file during the run phase. There are alot of examples, you just need to query for an example that matches what you want to do.

Best Answer

Related Solutions

Oracle 12c create user

Testing Multiple Queries from Input File – Oracle, Java, Scripting, Python

Related Question