Oracle 9i - Gather statistics

[franco]

Environment:
2 node cluster Fail Safe Oracle 9.0.2.4.0
We have a strange situation:
From OEM, you can Analyze the DB in order to collect statistics and memorize these statistics in the DB.
Before this operation our querys run fast, but after this operation the same querys are really slow.
I then delete the statistics from the DB and everything return normal, the querys are fast again.
Can anyone tell me what could be the problem?
Thank you very much.
Regards.
Franco

[abhaysk]

problem could be due many reasons.. but i think you need to RTM ..

[slimdave]

What's happened here, it seems, is that by analyzing the tables you've invoked the cost-based optimizer instead of the rule-based optimizer.

One common issue with the cost based optimizer, or rather with the people managing the db, is that they do not also collect system statistics (using DBMS_STATS.GATHER_SYSTEM_STATS), or alternatively they do not set the initialization parameter optimizer_index_cost_adj to a resonable value, such as "20" (the default of 100 is way too high).

Gathering system stats is much preferred. I gives the optimizer real information on the relative costs of scattered and sequential reads of data, and ought to restore your good performance under the CBO.

See ...
http://download-west.oracle.com/docs...38.htm#1021444
http://download-west.oracle.com/docs...tats.htm#28810
http://download-west.oracle.com/docs...s2.htm#1010477