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


Dev Live Dev Intro

This month we are celebrating Developers at AskTOM. We welcome Developers of all levels of experience to join us at our FREE Developer Live events coming in August. Just click on the left to register today! If you are brand new to Database Technology, then we also have got you covered. Just click on the right for your comprehensive FREE training program to kick start your Oracle Database Development journey!

Question and Answer

Chris Saxon

Thanks for the question, Gyan Bahadur.

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

Answered by: Chris Saxon - Last updated: June 22, 2020 - 10:49 am UTC

Category: SQL - Version: 11g/12c

Viewed 100+ 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 we 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:

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

and you rated our response

  (3 ratings)


Session's parameters?

June 19, 2020 - 4:13 pm UTC

Reviewer: David D. from France


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.

June 20, 2020 - 2:28 am UTC

Reviewer: A reader

While checking the explain plan, showing same for both users

June 20, 2020 - 10:04 am UTC

Reviewer: David D.. from France

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


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