Skip to Main Content
  • Questions
  • How to find why the same SQL is slower with a different user

Breadcrumb

May 4th

Question and Answer

Chris Saxon

Thanks for the question, Gyan Bahadur.

Asked: June 19, 2020 - 1:54 pm UTC

Last updated: June 22, 2020 - 10:49 am UTC

Version: 11g/12c

Viewed 1000+ times

You Asked

I have run the same SQL on production environment with System and other normal user. while checking the explain plan, showing same using both users. But while executing the SQL, system user return the result 50 times faster than normal user. I have set the user profile also same for both users. How can we trace the actual cause, the other than system user taking the more time to execute the same query on same environment??

and Chris said...

You need to get the EXECUTION plan for the statement. This includes runtime metrics such as:

- Number of rows processed
- Time taken
- Logical I/O done
- etc.

To get a plan including these metrics which you can share with others (such as on this site), run:

set serveroutput off
alter session set statistics_level = all;

select * from <your query>

select * 
from   table(dbms_xplan.display_cursor(null, null, 'ALLSTATS LAST'));


There are several other ways to do this, I discuss a few at:

https://blogs.oracle.com/sql/how-to-create-an-execution-plan

Once you've got this plan, I discuss what to look for in this video:


Rating

  (3 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Session's parameters?

David D., June 19, 2020 - 4:13 pm UTC

Hello,

What do you mean by "I have set the user profile also same for both users" : profile, parameters...?

Are you sure that the optimizers parameters have the same value in the session? For exemple, is optimizer_mode different? Is there an after logon trigger which set differently some parameters for your both users?

Chris Saxon
June 19, 2020 - 4:38 pm UTC

Good question. I'm also wondering if each execution reads the same tables, or if there's tables with the same names in different schemas.

A reader, June 20, 2020 - 2:28 am UTC

While checking the explain plan, showing same for both users

David D.., June 20, 2020 - 10:04 am UTC

Can you post both execution plans?

And if you use autotrace, can you post the result? I want to see if the number of read blocks is the same for both users.

Chris Saxon
June 22, 2020 - 10:49 am UTC

Yep, that's what I want to see too!

More to Explore

Administration

Need more information on Administration? Check out the Administrators guide for the Oracle Database