Forum Stats

  • 3,757,144 Users
  • 2,251,201 Discussions
  • 7,869,743 Comments

Discussions

AWR enhancement - new Unbound SQL Statistics section - making dark matter visible

RobK
RobK Member Posts: 99 Blue Ribbon
edited Mar 12, 2018 6:40AM in Database Ideas - Ideas

Hello!

SQLs that do not use BIND variables are often important resource consumers. Still in an AWR/statspack report we do not have direct information on the issue.

We have parsing related waits and latches, but that those waits and latches may be caused not only by unbound SQLs and more importantly they cannot be translated to problematic SQLs.

I would like to see in the AWR report information that can be used for root cause analysis.

So here is the idea:

I would like to see an "Unbound SQL Statistics" section in the AWR/Statspack report

It would be identical in structure to the current "SQL Statistics" section, but it would only contain SQLs where FORCE_MATCHING_SIGNATURE<>EXACT_MATCHING_SIGNATURE.

It would show the SQL texts with literals replaced by the usual :SYS_B...

The hyperlinks would show the FORCE_MATCHING_SIGNATURE as keys leading to the "Complete List of Unbound SQL Text" table.

All data in this section would be grouped by FORCE_MATCHING_SIGNATURE instead of SQLID

Hopefully a relatively cheap implementation is possible (both in terms of human and database effort).

A non approved ENHANCEMENT REQUEST exists already, but is has not moved ahead in the past 7 months. (As far as I see)

ENHANCEMENT REQUEST 26588854 - ER: ADD UNBOUND SQL SECTION IN AWR.

Please vote for it / implement it if you think it is a good idea.

Thanks,

Robert

nbayliss-Oracle3670402User_PEQH2havasizChris AntogniniFranck PachotzaheerfahgonzalezPatrick JolliffeRandolf GeistLothar FlatzSven W.Piotr Wrzosek1739956Martin Preissblessed DBADallas DeedsberxAndreas Huberuser7153084nokia4meuser12178958user829925Racer I.Rainer StenzelMaris ElsinsKnut HärtelBPeaslandDBAKayKUser_F054UBobby DurrettRobK
32 votes

Active · Last Updated

Comments

  • Lothar Flatz
    Lothar Flatz Member Posts: 681 Silver Badge

    Yep, that issue can be very serious if you just don't think about it.Thumbs up.

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

    I voted this up, however I think it cann't be done (easily).

    Problem is the AWR report can only work with the data it currently finds. And if you have 1000 very similar statements, that all have a different SQL_ID, then there is a good chance that those statements will not make it onto the AWR report/snapshot.

  • Lothar Flatz
    Lothar Flatz Member Posts: 681 Silver Badge

    I voted this up, however I think it cann't be done (easily).

    Problem is the AWR report can only work with the data it currently finds. And if you have 1000 very similar statements, that all have a different SQL_ID, then there is a good chance that those statements will not make it onto the AWR report/snapshot.

    Swen, you can summarize based on force_matching_signature or by plan_hash_value

  • blessed DBA
    blessed DBA Member Posts: 218

    Nice one this avoid repeat ion

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

    Swen, you can summarize based on force_matching_signature or by plan_hash_value

    Please correct me if I'm wrong.

    Are all the statements sampled? If AWR runs every hour, then there is a good chance that older cursors already did age out of the shared pool/library cache. This is usually less a problem when binds are used, since then the cursor is reused again and again and again. But if binds are missing, the statement might not be reused, a new cursor is created for all 1000 (similar) statements, but AWR will not find those statements anymore. 

    So if the AWR report shows a new statistics like this, then it might underestimate the real problem. In the worst case we could come to the conclusion that there is no such problem, although there is. However this might be more of a documentation problem.

  • RobK
    RobK Member Posts: 99 Blue Ribbon

    Please correct me if I'm wrong.

    Are all the statements sampled? If AWR runs every hour, then there is a good chance that older cursors already did age out of the shared pool/library cache. This is usually less a problem when binds are used, since then the cursor is reused again and again and again. But if binds are missing, the statement might not be reused, a new cursor is created for all 1000 (similar) statements, but AWR will not find those statements anymore. 

    So if the AWR report shows a new statistics like this, then it might underestimate the real problem. In the worst case we could come to the conclusion that there is no such problem, although there is. However this might be more of a documentation problem.

    Hi Sven,

    when I posted the enhancement request I had the same concerns.

    But when I think about it: I am not convinced the current AWR infrastructure only collects SQL related information at snapshot time. Maybe it samples library cache periodically. Does anybody have information about it?

    Also as you pointed out this might only be a documentation problem. The headings of the table could explain "The resource utilization values shown in this section should be regarded as minimum values".

    I agree with your comment.

    Also note that seeing 10-15 suspicious unbound SQLs in an AWR report is already a big step forward compared to seeing nothing about them.

    Cheers,

    RobK

  • RobK
    RobK Member Posts: 99 Blue Ribbon

    Thanks for the comments.

    R.