In this example, a client expressed confusion as
to why their Oracle system was grinding to a halt. There was a
serious degradation in SQL performance immediately after the
implementation of partitioned tablespaces in a 16-CPU Solaris 64-bit
Oracle 9i database. The changes had been thoroughly tested in their
development and QA instances so everyone was confused about the
slowdowns.
As it turned out,
analyze table and
analyze index commands
had been used to gather the CBO statistics, a very bad act for
partitioned tables since only 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 is the full-table scans are parallelized with 15 parallel
query processes to speed-up the statistics collection:
