Skip navigation

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

score 270
You have not voted. Active

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

Comments

Vote history