Repair Obsolete CBO Statistics Gathering
This shop called from Australia complaining about a serious
degradation in SQL performance after implementing partitioned
tablespaces in a 16-CPU Solaris 64-bit Oracle 9.0.4 system.
They said that they thoroughly tested the change in their
development and QA instances, and they could not understand
why they system was grinding to a halt.
Upon inspection, it turned out that they were using analyze
table and analyze index commands to gather their
CBO statistics. As we may know, the dbms_stats utility
gathers partition-wise statistics. There was not time to pull
a deep-sample collection, so a dbms_stats was issued
with a 10 percent sample size. Note that I parallelized it
with 15 parallel processes to speed-up the statistics
collection:
exec dbms_stats.gather_schema_stats( -
ownname => 'SAPR4', -
options => 'GATHER AUTO', -
estimate_percent => 10, -
method_opt => 'for all columns size repeat', -
degree => 15 -
)
This took less than 30 minutes and the improved CBO statistics
tripled the performance of the entire database.
If you like Oracle tuning, you might enjoy my latest book
“Oracle Tuning: The Definitive Reference” by Rampant TechPress.
It’s only $41.95(I don’t think it is right to charge a fortune
for books!) and you can buy it right now at this link:
http://www.rampant-books.com/book_ 2005_1_awr_proactive_tuning.htm
