Most people are familiar with "SQL ordered by Elapsed Time", "SQL ordered by CPU Time", "SQL ordered by Gets" sections of the AWR report.
I recently learned that "Elapsed Time" in this context means "DB Time" and not "Wall Time"
This "Elapsed Time" naming is also used in SQL Monitor Report. However in SQL Monitor Report we have "Duration" information, which is very important information form the application point of view. The user of the application cares less about DB Time than about Duration of an operation.
So here I propose to have a new "SQL ordered by Duration" section in the AWR reports.
I would add the following columns
- Duration (s)
- Elapsed Time (s)
- Duration per Execution (s)
- Fetches per Execution (s)
- Data size / Fetch
- Rows per Fetch
- % Total
- % CPU
- % IO
- SQL module
- SQL text
What would be the benefits?
This section would immediately show us the following things:
- Which are the queries that are run in parallel (Duration<Elapsed Time)
- Which are the most important queries if we wanted to tune Application Response Time.
- We would be able to notice when the result fetching or the client activity for processing the fetched data or network roundtrip takes up significant time (Duration>Elapsed Time).
- We would be able to point out in the above scenarios that the database is not to be blamed for the perceived slowness.
Hope it makes sense.
ps: Check also and vote for my other AWR related idea: AWR enhancement - new Unbound SQL Statistics section - making dark matter visible