Oracle FAQ | Your Portal to the Oracle Knowledge Grid |
Home -> Community -> Usenet -> c.d.o.server -> Re: Analyze Table Compute Statistics but slowed down performance ??
On Thu, 01 Jun 2000 22:01:18 +0800, Raymond <kudoshizuka_at_hotmail.com> wrote:
>After one week, I have
>(5) performed the command "Analyze table ... compute statistics;" for
>all the tables in the database. The optimizer_mode is CHOOSE.
>However, the same posting job as point (4) ran for almost 6 hours to
>complete! It caused the system slowed down dramatically.
If you did not have statistics before, than computing statistics causes Oracle to use the cost-based optimizer rather than the rule-based optimizer. This is not always good, as you have discovered. Try changing your optimizer_mode to RULE, and see if that makes a difference.
Hmmm... Be careful changing the mode globally. If you have apps designed for the cost-based optimizer, you could cause problems with those. Try to think of a relatively safe way to test this.
Jonathan