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.