#1 Issue you Should be Careful with AWR/STATSPACK Reported Numbers

Posted in: Technical Track

I have decided to share with you some of the real life examples of why sometimes V$ views may hide real resources utilization numbers. As AWR and STATSPACK (previous version of AWR) as based on V$ views those may reflect the wrong statistics as results of the issues I am going to discuss in this blog post.

Please don’t get me wrong, I still believe that V$ and related frameworks are a great source for performance diagnostic (sometimes;). The point I am would like to make is you need to be careful reading statistics and do not take anything reported for granted.

This is my first blog post on this topic. I am planning to show you more interesting examples in the next posts.

Have a look at the statistics bellow. These statistics have been taken from V$SQLAREA view over 15 minutes interval. PR columns shows physical reads numbers for a particular SQL (‘5rm0tanhd47wq’). For research purposes I took a snapshots on a minute basis.

Do you see several negative numbers? Have you see anything like that before? I think most of us would explain the negative numbers in the statistics by the fact that an SQL may be aged from a shared pool. This is a very valid explanation. However we would expect a relative line to disappear from V$SQLAREA. In this case it still exists and has some statistics recorded against the SQL.

Have a look at the following output from V$SQL for the same period of time. The output not only explains the negative numbers but shows some really interesting effects related to the same issue. Just to remind you that the significant difference between V$SQL and V$SQLAREA is the fact that V$SQL reflects the information per SQL child basis.

As you can see the negative -490 physical reads statistics reported because the child SQL #2 has been aged from memory with statistics reading 550 and the new child #1 with stats 22 have been created (between 42th and 43th minutes). If we do the arithmetic as correctly as possible then we would have (15404-15366)+22 = 38 + 22 = 60 physical reads reported (we don’t know tho how much reads the child #2 did before disappearing). Instead of +60 physical reads Oracle reports -490. Please notice that neither AWR or STATSPACK operates on V$SQL level and therefore are exposed to this reporting issue. Those frameworks read statistics from V$SQLAREA (and in latest versions from V$SQLSTATS).  The detailed output shows that -16180 is there because child #0 was aged from memory.

You may say: Hey Yury! It isn’t a big deal! We can ignore negative numbers because they don’t make too big a difference. And my answer is YES and NO. I as mentioned I will show more interesting examples in my next post. Let me give you a hypothetical example that I just made up to show how the behavior can hide significant statistics.

In such a scenario Oracle should report (243-136)+163000=16407 physical reads. However STATSPACK will report 136 times smaller figure (120 physical reads).

Once again! AWR and STATSPACK are great sources of performance related diagnostic data. However please be careful reading statistics reported.

Stay tuned …

Yury Velikanov

<!–[if gte mso 9]> <![endif]–><!–[if gte mso 9]> Normal 0 false false false false EN-CA X-NONE X-NONE <![endif]–><!–[if gte mso 9]> <![endif]–>

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

About the Author

Yury is a nice person who enjoys meeting and working with people on challenging projects in the Oracle space. He started to work as an Oracle DBA 14 years ago (1997). For the past 10 years (since 2001) his main area of expertise is Oracle e-Business Suite. Yury is an OCP 7,8,9,10g and OCM 9i,10g. He is a frequent presenter at Oracle related conferences such as Hotsos, UKOUG and AUOUG. Yury is a socially active person. Apart from Social Media (Twitter, Bloging, Facebook) he is the primary organizer of Sydney Oracle Meetup group (250 people). So if you happen to be in Sydney (Australia) drop Yury a message and stop by at one of his Meetups.

1 Comment. Leave new

Daniel Andrade Costa Silva
May 16, 2016 5:58 am

Your article is just awesome!!!!!! I was having the same problem of negative numbers reported on statspack for CPU_TIME for a very frequent query. The aged out child explains it very well. Thank you very much!!!!


Leave a Reply

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