Forum Stats

  • 3,837,208 Users
  • 2,262,236 Discussions
  • 7,900,222 Comments

Discussions

Add "SQL ordered by Duration" section to AWR reports

RobK
RobK Member Posts: 99 Bronze Badge
edited Jun 18, 2018 5:28PM in Database Ideas - Ideas

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:

RobKSven W.user12178958user829925User_TGB76User_F054U
7 votes

Active · Last Updated

Comments

  • Sven W.
    Sven W. Member Posts: 10,537 Gold Crown

    I have the feeling that this does not add a significant new information. Duration and elapsed time essentially are the same concepts, with duration needing a lttle more explaination and understanding. I do agree with the parallel execution example. But for the other tasks imho the current reports do give the wanted information. This is purely subjective to my experience with AWR however.

    AWR reports are long enough already. My feeling is that if a report is added to it, that it should add a more substantial contribution compared to what is there already. So a light downvote for the proposal from me.

  • RobK
    RobK Member Posts: 99 Bronze Badge

    I have the feeling that this does not add a significant new information. Duration and elapsed time essentially are the same concepts, with duration needing a lttle more explaination and understanding. I do agree with the parallel execution example. But for the other tasks imho the current reports do give the wanted information. This is purely subjective to my experience with AWR however.

    AWR reports are long enough already. My feeling is that if a report is added to it, that it should add a more substantial contribution compared to what is there already. So a light downvote for the proposal from me.

    Hi Sven!

    I think you underestimate the importance of the missing information. Probably I did not make myself clear. (I noted that nobody voted for it yet, so it is most probably not your fault...)

    Think about an application that retrieves much data from a java code. The default fetch size is 10 records. With the many fetches and roundtrips the processing is slow. They give you an AWR riport. All you are able to tell is that "The slow SQL is not bad from Oracle server side point of view. Consumes little CPU, makes little IO. The problem is somewhere else". With the current information the SQL may even not be present in the report.

    Still the SQL is important for the user. And the SQL is slow for the user.

    Think about how many application uses default fetch size for getting much data. Think about how many developers have no idea about setting fetch size appropriately, and how many DBAs don't have time for digging deeper and understanding the problem.

    The information I request would draw your attention to the problematic SQL it would give you the root cause: the fetch size. You'd be able to tell how much time the SQL spent inside and outside of the server.

    The SQL - slow form user perspective - would be almost surely present in the AWR report, which is not the case now.

    Or there could be another application that fetches data from the database but after each fetch cycle the data is processed by the client. You would be able immediately to suspect network or client issues. You would be able to tell which SQL has this kind of problem, which may not be evident from the application logs (not logging each individual SQL).

    And please consider also that this information is present in an SQL monitor report. There we have both Elapsed Time (a.k.a DB Time of the SQL) and Duration (a.k.a Wall time of the SQL). So why should not we have the very same information/approach in the AWR report?

    I am pretty sure, that this information would be used more frequently than "Latch Miss Sources" "Mutex Sleep Summary" (which I appreciate a lot nonetheless:)

    Thanks for your feedback and time,

    Cheers

    Robert

  • Sven W.
    Sven W. Member Posts: 10,537 Gold Crown

    Hi Sven!

    I think you underestimate the importance of the missing information. Probably I did not make myself clear. (I noted that nobody voted for it yet, so it is most probably not your fault...)

    Think about an application that retrieves much data from a java code. The default fetch size is 10 records. With the many fetches and roundtrips the processing is slow. They give you an AWR riport. All you are able to tell is that "The slow SQL is not bad from Oracle server side point of view. Consumes little CPU, makes little IO. The problem is somewhere else". With the current information the SQL may even not be present in the report.

    Still the SQL is important for the user. And the SQL is slow for the user.

    Think about how many application uses default fetch size for getting much data. Think about how many developers have no idea about setting fetch size appropriately, and how many DBAs don't have time for digging deeper and understanding the problem.

    The information I request would draw your attention to the problematic SQL it would give you the root cause: the fetch size. You'd be able to tell how much time the SQL spent inside and outside of the server.

    The SQL - slow form user perspective - would be almost surely present in the AWR report, which is not the case now.

    Or there could be another application that fetches data from the database but after each fetch cycle the data is processed by the client. You would be able immediately to suspect network or client issues. You would be able to tell which SQL has this kind of problem, which may not be evident from the application logs (not logging each individual SQL).

    And please consider also that this information is present in an SQL monitor report. There we have both Elapsed Time (a.k.a DB Time of the SQL) and Duration (a.k.a Wall time of the SQL). So why should not we have the very same information/approach in the AWR report?

    I am pretty sure, that this information would be used more frequently than "Latch Miss Sources" "Mutex Sleep Summary" (which I appreciate a lot nonetheless:)

    Thanks for your feedback and time,

    Cheers

    Robert

    Ok you convinced me. I upvoted now.

  • RobK
    RobK Member Posts: 99 Bronze Badge

    Ok you convinced me. I upvoted now.

    Thanks.

    Ro