Re: dbms_stats / 9204

From: Jan Jansen <jan_at_jansen.com>
Date: Tue, 28 Dec 2004 13:36:57 +0100
Message-ID: <1104237419.57999.0@doris.uk.clara.net>

Oradba Linux wrote:
> Are you using dbms_stats on 9204? Did you come across a bug 3301328 ?
> Index stats being collected is not accurate when compared to analyze
> commands.
> How about analyze commands using partition tables ? Any issues ......
>
>

We tried dbms_stats for a while, with various settings and parameters on 9.2.0.5, and had following observations:

takes much much longer than analyze .. compute statistics in most cases

did not improve query (optimizer) results, in several cases it got worse

need to do an analyze .. estimate statistics in any case if you want to see statistics on chained rows etc.

harder to quickly call interactively (may/should wrap normal cases in own SP's though).

advantage: can be called without dynamic sql ('execute immediate') in SP's.

So we decided to wait and keep using analyze table for the time being. Received on Tue Dec 28 2004 - 06:36:57 CST