In my previous post I described some sections that are typically useful when interpreting AWR data. However, sometimes the answer comes from an unexpected source. For example, the workload profile section of the report contains key information for understanding what the database looks like, but it seldom gives a direct answer to the problem (except for maybe excessive parsing and excessive commits). But recently I came across a case when this section was enough to identify the root cause of a non-trivial issue:
Per Second Per Transaction
Redo size: 1,895,241.12 12,004.40
Logical reads: 832,945.54 5,275.85
Block changes: 11,937.82 75.61
Physical reads: 7,458.75 47.24
Physical writes: 759.33 4.81
User calls: 449.83 2.85
Parses: 225.18 1.43
Hard parses: 15.90 0.10
Sorts: 467.90 2.96
Logons: 1.38 0.01
Executes: 103,266.84 654.09
Transactions: 157.88
This excerpt was coming from an AWR report for a database that virtually froze with 100% CPU consumption on the box. The question was what causing this high CPU consumption (the SAs ruled out possibility of blaming other processes on the box).
When looking carefully at the numbers above, one could notice that executes per second looks enormous. This becomes even more apparent when looking at the rate of user calls, which is a few orders of magnitude lower. These numbers, combined with high CPU usage, are enough to suspect context switching as the primary suspect: a SQL statement containing a PL/SQL function, which executes a SQL statement hundreds of thousands of time per function call.
Further investigation confirmed that it was indeed the case. There was a stats job running shortly before the incident, leading to invalidation of the SQL plan, and the new plan was calling the PL/SQL function at an early stage, before most rows were eliminated.
The point I am trying to make is that one should try to maintain a good balance between focusing on just few key performance indicators, and paying attention to secondary details as well.
Oracle Diagnostician 
Very Nice… Unfortunately,my job is to analyze AWR before the problem arises and take some proactive measures to prevent the problem to occur. I have couple of questions regarding interpreting AWR. Hope you don’t mind if i ask you?
Hi Yasir,
thanks for visiting my blog. Yes, go ahead with your questions, I’ll be happy to answer them.
Best regards,
Nikolay
Hi Nikolay
Can you explain..:
1)DB CPU
2)CPU TIME
3)CPU used by this session
If I have a 30 min awr report, how should i deduce how much CPU does oracle took during these 20 mins?
Thanks
I’m impressed. You’ve rlealy raised the bar with that.
Hi Mukesh,
thanks for visiting my blog!
Hi Yasir,
thanks for an excellent question. It calls for a full-size blog post with some examples, but I only have time to answer it briefly.
The meaning of all these metrics is essentially the same (CPU time by all user calls, i.e. except background sessions), and it should be assessed versus (elapsed time) x (number of CPUs). Differences in these values have to do with how they are calculated and how frequently they are updated (e.g. see http://jonathanlewis.wordpress.com/2009/05/26/cpu-used/).
In order to understand database CPU usage it is also helpful to look at CPU consumption on the OS level (can be calculated from OS statistics section as BUSY_TIME/(BUSY_TIME+IDLE_TIME).
Another quantity that is useful for troubleshooting CPU problems is time wait to get CPU, but it’s not readily available (there are wait to estimate it from ASH, e.g. http://dboptimizer.com/2011/07/21/oracle-cpu-time/).
Best regards,
Nikolay
PS. I’ve added “ask me a question” page so that in the future one could ask questions which aren’t related directly to any post, feel free to use it :)
Good day!This was a really binillart blog!I come from itlay, I was fortunate to discover your subject in bingAlso I obtain much in your Topics really thanks very much i will come daily
Thank you very much Vencislav!
I forgot to mention that there is an option to subscribe for updates for this blog — that way you can be sure that you won’t miss anything interesting :)
I’ve been exploring for a liltte for any high quality articles or weblog posts in this sort of house . Exploring in Yahoo I at last stumbled upon this web site. Studying this information So i’m satisfied to express that I have a very excellent uncanny feeling I found out exactly what I needed. I most indisputably will make certain to do not disregard this web site and give it a look on a constant basis.
Thanks for visiting my blog Kaylane!
Excellent Info Savvinov!! Really glad to be in a place to know and learn so much. Thanks again and keep Blogging!! :-)
Dev
Hi
Its really nice, I am a regular reader of your posts, its really useful
Thanks
Rajeev
Thanks Rajeev,
I’m glad you’re finding my blog useful!
Savvinov its really tough to find such good material on AWR on google. Thanks for your blog really helpful.
Thanks
Jamsher
The answer of an exprte. Good to hear from you.