Thank you very much to all those who attended my session "Stats with Confidence". Unfortunately I was delayed by the keynote running late. With the big party coming up, I appreciate the spirit of those brave souls who stayed back. The late start didn't allow me to show the demo completely. But here are the scripts; hope you will be able to follow it along and run it on your own infrastructure.
It contains the presentation as well. Thanks for attending and hope you will find it useful.
2 comments:
I got some questions from a reader Sunil P.C., which I feel are very relevant to a lot of readers. Thanks for the questions, Sunil. Here are the questions you asked and answers:
Q#1: You mentioned about using pending statistics for partitioned tables.
Is n't it better to collect stats for partitions and publish it immediately
after load instead of waiting for all partitions to be loaded?
This will benefit for all queries going against partitions. Also the global
statistics can be maintained without spending lot of time using incremental
maintenance feature in 11g.
http://blogs.oracle.com/optimizer/2009/02/
Answer:
It's true that the incremental stats will help in paritioned tables. Please note, however, that this is not about performance; but about the timing of the publication of stats. When specifically would you want to show the local stats? Would you want the optimizer to see the local stats as soon as they are loaded or wait until all the partitions are loaded? If the latter, then the technique I mentioned is very helpful.
Q#2 You mentioned setting preference using set_database_prefs
There is another procedure called set_global_prefs.
set_database_prefs loops over all the tables in the system and
set the preference for all the existing tables. set_global_prefs
is applicable to even the tables we create in future. gather stats
command first look at table preference and if not set, it will look
at global preference. So I guess for you purpose set_global_prefs
is better?
Answer:
The set_database_prefs is for all database tables except for sys owned tables. The set_global_prefs is for all tables. So, no, I would still prefer set_database_prefs. Messing with SYS owned objects is generally not a good idea.
Q#3 I did n't quite understand the need for collect stats in pending
if you have base line plan for every statement in the system.
If you already have a baseline plan and collect stats and publish
it immediately, it is not going to change the plan unless you
evolve. So I guess you don't need to collect stats in pending area?
Answer
The purpose of the stats collection with pending stats is to identify if the pending stats make th eplan better (or, worse). The point is there may be a different plan under pending stats. Using SPM we can capture that plan. Why?
Consider this situation: one query was better but another was worse under the pending stats, what would you do? Clearly, you would have to drop the pending stats. But the one query that was helped, you may want to keep the plan, because it is better. So, you may want to *fix* it. That is the idea behind the SPM under pending stats.
Hope this helps
Thanks arup the question you pointed out is really a nice idea and its helpful this clear my ideas and release my confusion thanks keep it up.
Post a Comment