.
\nYesterday I was a speaker at Collaborate15 and did my presentation about reading an AWR report. That was great. Many people (and not enough seats).<\/p>\n
Here are some aswers about questions that came later.<\/p>\n
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.<\/p>\n
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.<\/p>\n
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.<\/p>\n
How do you set it? Here is how to do after installing Statspack:<\/p>\n
SQL> exec STATSPACK.MODIFY_STATSPACK_PARAMETER (i_snap_level=>7, i_modify_parameter=>'true', i_instance_number=>null);<\/code><\/pre>\nIf you are in RAC, just do the same for each instance, with the right instance number.<\/p>\n
There is a warning I always add when I give our tuning workshop<\/a>\u00a0: 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:<\/p>\n\n- System swapiness was configured high. The instance was quite idle, so a big part of the shared pool had been swapped. The level 7 snapshot has to read lot of cursor and execution plan information from shared pool and then the system is stuck for a while, bringing all that back in memory. The solution: configure the instance properly (swapiness=0)<\/li>\n
- No automatic stats gathering were done on PERFSTAT schema. Bad statistics made the insert into STATS$SQL_PLAN very bad. Solution: gather statistics properly.<\/li>\n<\/ul>\n
Besides that, I don’t see know any problem having level 7 as the default. It adds segment statistics and it adds execution plan.<\/p>\n
Predicates in explain plan<\/h3>\n
I said that predicates are missing in the plans. Here<\/a> is the reason. And – maybe – here<\/a> is a way to have that fixed one day. Please vote up for that idea on OTN.<\/p>\nPresentation material<\/h3>\n
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.
\nPeople being there can download the pdf and white paper from here<\/a>.<\/p>\nGet deeper<\/h3>\n
You want to go further than the AWR report? You can query AWR views.
\nLearn that at Maris Elsins session today: Mining the AWR: Alternative Methods for Identification of the Top SQLs in Your Database<\/a><\/p>\nConclusion<\/h3>\n
Please, don’t forget to give feedback from the Collaborate app.
\nThanks a lot to the attendees, as well as the IOUG Forum organisation.<\/p>\n
6 minutes to go and the room is already full for @FranckPachot<\/a>! Congrats pic.twitter.com\/jHY7n9Po0P<\/a><\/p>\n