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 - 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.
