Note: Burleson Oracle Consulting is the leader in Oracle Emergency Support and is available to assist with any Oracle database problem, anywhere in the world, anytime.

 

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.       Guarantee your Success! Oracle is the world's most complex, robust and flexible database, considered impossible to master without a mentor. That's why all BC Oracle trainers are working professionals, experts in Oracle who share their tips and secrets.