Skip navigation
ANNOUNCEMENT: community.oracle.com is currently Read only due to planned upgrade until 29-Sep-2020 9:30 AM Pacific Time. Any changes made during Read only mode will be lost and will need to be re-entered when the application is back read/write.

Add "SQL ordered by Duration" section to AWR reports

score 40
You have not voted. Active

Hello!

 

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)

- Executions

- Duration per Execution (s)

- Fetches

- Fetches per Execution (s)

- Data size / Fetch

- Rows per Fetch

- % Total

- % CPU

- % IO

- SQL_ID

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

 

Cheers,

Robert

 

ps: Check also and vote for my other AWR related idea: AWR enhancement - new Unbound SQL Statistics section - making dark matter visible

Comments

Vote history