About Me

I am Deepak Bhatnagar. An Oracle DBA with 11 years of experience in Oracle database technologies e.g Troubleshooting database performance issues, Troubleshooting ORA-4031 (unable to allocate x bytes of shared memory) errors, RAC, Dataguard, ASM, Goldengate etc..

Sunday, April 24, 2016

SQL Ordered by Elapsed Time Section of AWR Report Can Mislead your Analysis!!!


Whenever users complains that database is performing slow, most of DBAs immediately generate AWR reports for analysis. SQL Ordered by Elapsed Time is one of the major areas where most of DBAs look when analyzing AWR report.  Most of DBAs just assume that the topmost query in "SQL Ordered by Elapsed Time" section is the slowest query and they start looking for tuning opportunities.

But it is not necessary that Query Elapsed Time is always same what end user experiences. It is possible that Top most query in "SQL Ordered by Elapsed Time" section of AWR Report is NOT the slowest query and your analysis can go in wrong direction. Sometimes, SQL ordered by Elapsed Time section of AWR report can mislead your analysis.

Query Elapsed Time can be different from what end user experiences. To demonstrate this, I executed a simple query 4 times (4 tests). In each test, I increased the elapsed time to 2 times, 3 time and 4 times of actual time taken by the query and Elapsed Time is what is reported in AWR reports.

Test -1  - Elapsed Time of query is same what user experiences.





















Test - 2 - Elapsed Time of query is 2 times of what user experiences.


Test - 3 - Elapsed Time of query is 3 times of what user experiences.


































Test - 4 - Elapsed Time of query is 4 times of what user experiences.



Finally Interesting part is that, AWR report does not show the actual time taken by the query what user experiences. AWR Report shows Elapsed Time which is 95.2 seconds, not the user experienced time 24.41 seconds.



7 comments:

  1. This comment has been removed by the author.

    ReplyDelete
  2. This comment has been removed by the author.

    ReplyDelete
  3. This is intriguing!
    Why this behavior ? Awaiting further explanation on this behavior of system

    ReplyDelete
  4. Deepak sir...how this is happening,ideally it should not happen...because elapsed time which user will experience includes all internal queries and processing of the original one...

    ReplyDelete
  5. Exactly, but if you run the query using parallel threads, elapsed time will include time taken by all slave threads. This is how I performed by increasing parallelism in each test.

    ReplyDelete
  6. There are bug in the old 11.1.x.x database which reports wrong info in AWR report

    ReplyDelete