Do AWR Reports Show the Whole Picture?

Posted in: Technical Track

AWR report is a great source of aggregated information on top activities happening in our databases. I use data collected in AWR quite often, and obviously the easiest way of getting the data out from the AWR is by running the AWR report. In most cases that’s not an issue, but there are certain scenarios when it hides the information one is looking for, just because of how it’s designed.

If I’m trying to collect information about top queries by physical reads, I would normally look at the “SQL ordered by Reads” section and this is what I’d see:

AWR DIsk readsI have the top SQLs by physical reads – just what I’ve been looking for (except the fact that AWR report covers only one of my RAC nodes).

But wait a second, what if there are queries that don’t use bind variables? This might be a problem as each query would have it’s own SQL_ID and probably they wouldn’t make it into the TOP10 just because each of them is treated separately. Nothing to worry about – AWR also collects FORCE_MATCHING_SIGNATURE values (read this blog post to understand why I know they would help) and we can use them to identify and group “similar” statements, we just need a custom script to do that.

Here I use my custom script to report TOP 20 SQL_IDs by physical reads in last 7 days (and I’m reporting data from both RAC nodes in the same list) – you can see the few TOP SQLs are the same as reported in AWR report, but because I’m reporting database-wide statistics instead of instance-wide as AWR does, I have other SQLs on the list too. I’ve also included 2 additional columns:

  • DIFF_PLANS – number of different PLAN_HASH_VALUE values reported for this SQL_ID, and if only one is found – it shows the actual PLAN_HASH_VALUE
  • DIFF_FMS – number of different FORCE_MATCHING_SIGNATURE values reported for this SQL_ID, and if only one is found – it shows the actual FORCE_MATCHING_SIGNATURE

Custom Script - sqlidNow, I can adjust the custom script to aggregate the data by FORCE_MATCHING_SIGNATURE, instead of SQL_ID. I’ll still keep the DIFF_PLANS column and will add a new one – DIFF_SQLID.

Custom Script - fmsThe situation is a little bit different now. Notice how the second row reports FORCE_MATCHING_SIGNATURE  = 0, this typically shows PL/SQL blocks that execute the SQL statements and aggregate statistics from them, so we’re not interested in them. Otherwise the original report by SQL_ID showed quite accurate data in this situation and my suspicions regarding the misuse of literal values where binds should be used, didn’t materialize. Could I be missing anything else? Yes — even the FORCE_MATCHING_SIGNATURE could be misleading in identification of TOP resource consumers, you can write two completely different SQLs (i.e. “select * from dual a” and “select * from dual b”) that will do the same thing and will use the same execution plan. Let’s query the top consumers by PLAN_HASH_VALUE to check this theory!

Custom Script - planI’ve highlighted the third row as the same PLAN_HASH_VALUE is reported for 20 different SQL_IDs, which allowed it to take the third place in the TOP list by physical reads (actually it’s the second place as PLAN_HASH_VALUE=0 is ignorable). The next query expands the third row:

Custom Script - sqlids for planAnd here are All the SQL statements:

All plan sqlsWhat I have here is 20 different  views generated by Oracle Discoverer that query the database by using exactly the same execution plan. Closer look revealed the views included hardcoded query parameters (date intervals for reporting), but in the end, this was the same query! It’s the TOP2 query by physical reads. in the database and if I tune it – all 20 discoverer views will benefit.

I think one of the drawbacks of AWR reports is that it is not able to identify such situations, it would be great if user could choose the column by which he aggregation is done. In the situation I described I was able to identify one of the top queries by physical reads only when I aggregated data by PLAN_HASH_VALUE.



Want to talk with an expert? Schedule a call with our team to get the conversation started.

About the Author

Maris Elsins is an experienced Oracle Applications DBA currently working as Lead Database Consultant at The Pythian Group. His main areas of expertise are troubleshooting and performance tuning of Oracle Database and e-Business Suite systems. He is a blogger and a frequent speaker at Oracle related conferences such as UKOUG, Collaborate, Oracle OpenWorld, HotSos, and others. Maris is an Oracle ACE, an Oracle Certified Master, and a co-author of “Practical Oracle Database Appliance” (Apress, 2014). He's also a member of the board at Latvian Oracle User Group.

6 Comments. Leave new

Could you post sql code for awr scripts?


Hi Vishal,
You can find the scripts here:


I had very similar thoughts quite some time ago.
I created an enhancement request at Oracle.
Some Oracle gurus liked the idea.

You read about it here:
You may upwote it if you like it.
It may become a feature one day.

And yet another missing info in AWR for me:


Maris Elsins
May 8, 2019 5:06 am

Hi Rob,

Thanks for posting the link to the ER.
Upvoting = done. I’m all in for including this in the AWR out of the box.



Hi Maris,
Thank you for this highly informative blog. I struggled with some performance issues and awr reports – not showing the complete picture. I did’t find this force_matching_signature information in awr.
In OEM, I often relied on top activity page and digging deeper from that angle, even for historic information. Recently I started using ASK analytics – which provided a view point of sqls with force matching signature-which gave higher visibility to the problematic sqls not using binds. Additional google searches-made me land on your page. Thanks again, this has been really helpful in my case.


Thanks Geeta, your feedback made my day!


Leave a Reply

Your email address will not be published. Required fields are marked *