This was first published on https://blog.dbi-services.com/yesterdays-awr-straight-to-the-goal (2015-04-14)
Republishing here for new followers. The content is related to the the versions available at the publication date
Yesterday I was a speaker at Collaborate15 and did my presentation about reading an AWR report. That was great. Many people (and not enough seats).
Here are some aswers about questions that came later.
I did that presentation at DOAG last year in a much bigger room. But because of the bigger room there was no questions, and because of the tension I felt in that big room, I made it too fast. Yesterday’s was better in my opinion, able to cover everything, including questions, in one hour.
I was a bit surprised by the number of people having Diagnostic Pack. About 50%. This is not the case with my customers. Probably there are biggest companies in US that can affort higher licence costs.
Anyway some people are in EE without Diagnostic Pack, or in SE and they run Statspack. I said that they can do exacltly the same approach as long as the take level 7 statspack reports.
How do you set it? Here is how to do after installing Statspack:
SQL> exec STATSPACK.MODIFY_STATSPACK_PARAMETER (i_snap_level=>7, i_modify_parameter=>'true', i_instance_number=>null);
If you are in RAC, just do the same for each instance, with the right instance number.
There is a warning I always add when I give our tuning workshop : if you do it afterwards, please run one snapshot just to check there is no problem. From my experience, I have seen the following issues:
Besides that, I don’t see know any problem having level 7 as the default. It adds segment statistics and it adds execution plan.
I said that predicates are missing in the plans. Here is the reason. And – maybe – here is a way to have that fixed one day. Please vote up for that idea on OTN.
Some people that were not at IOUG Collaborate have asked if the presentation is public. Not yet. I’ve submitted it for other events this year, so it cannot be public. But maybe you can try to drop me a mail. People being there can download the pdf and white paper from here.
You want to go further than the AWR report? You can query AWR views. Learn that at Maris Elsins session today: Mining the AWR: Alternative Methods for Identification of the Top SQLs in Your Database
Please, don’t forget to give feedback from the Collaborate app. Thanks a lot to the attendees, as well as the IOUG Forum organisation.
6 minutes to go and the room is already full for @FranckPachot! Congrats pic.twitter.com/jHY7n9Po0P
— Ludovico Caldara (@ludodba) April 13, 2015
Good Morning Frank,
I live in Brazil and i would like to receive the presentation material about Interpreting AWR Reports. I searched for your email but i didn’t find.
I subscribe to your posts and thank you for always post. I am learning a lot.
Regards
Hi Rogê, Thanks for your comment. You can send me your e-mail address at franck.pachot@dbi-services.com and I’ll share the Prezi presentation. Regards, Franck.
Hi Frank, Your articles have been very informative and of a timely help to the DBA community grappling with time, to search for a reliable and authentic information.
May I please have access to the ppt materials and white paper on “Interpreting AWR Reports” Regards, Satish
Hi Satish, Thanks for your feedback. You should have access to it on prezi.com with the e-mail you have sent to me. Regards, Franck.
Hi Franck,
Wich I can use to calculate % Usage CPU: CPUs or Cores? There are many controversies about it.
Hi Fabio, If you want to see the threads utilization, then use the number of cpus given by the OS. But you should know that 100% cannot be reached and that you may already have contention on physical processor even when some threads are idle. If you want to see the physical processor utilisation, then use the cores. But then, you should know that for some workloads it’s not abnormal to see higher values than 100% The efficiency of hyper-threading depends on the workload (access to RAM or compute in CPU) so the optimal percentage, whatever the calculation is, the ‘good’ value depends on the type of workload. Personally, I prefer to compare with physical cores because I’ve seen too many people thinking their system is not overloaded because they see a few %idle. Regards, Franck.