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
1 Comment. Leave new
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!!!!