Re: System Stats opinions Please - WORKLOAD vs. NOWORKLOAD?

There are 2 schools of thought, those who keep stats untouched if the 
performance is acceptable (regather stats only if there are any significant 
changes being introduced i.e. converting table to a partitioned table, creating 
new indexes, db upgrades, etc) and those who keep gathering stats on a 
scheduled basis.

I've seen systems where bind peeking is disabled (no major data skewness) and 
stats gathering is mostly frozen, simply because performance stability and 
consistency is far more important than unpredictability of few sql execution 
plans turning poor. This may change with sql plan baselines.

Even if you verify change in execution plans after gathering system stats, 
catching things like a query using a different index which has very poor 
caching (and hence more disk I/O's and slower response time) is going to be 
difficult even though the original index may incur couple more logical I/O's 
for each execution but with better caching (by virtue of other queries using 
this index more often) response time is going to be faster.

Unless you are not satisfied with current system performance, I don't recommend 
gathering system stats again as amount of test cycle to go through can rather 
be combined with your 11gR2 upgrade.

I've worked on IBM power 7 system with Oracle 11gR2 on it, no major stability 
issues observed.

Thanks,
 Sai
http://sai-oracle.blogspot.com

Other related posts: