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.