Analyze en oracle 9i

[salvareztur]

When I execute this sentence
dbms_stats.gather_table_stats('zzz','table_name','partition_name')

it takes more than 30 minutes, what can I do??

[pipo]

Is your partition huge ?

You could try estimating the stats instead of computing them, for example on 20% of the rows :

dbms_stats.gather_table_stats ('zzz', 'table_name', 'partition_name', 20);

[salvareztur]

My partitions have between 200.000 ,300.000 rows.

[salvareztur]

I want also to know how much time takes to analyze (average) in analyze one partition with 300.000 rows using dbmstats??

[pipo]

it depends on a lot of things, but shouldn't be longer than a few minutes...

did you try to estimate the stats instead of computing them ?

[slimdave]

is parallelism enabled on the table?

[julian]

Originally posted by slimdave
is parallelism enabled on the table?

Here is what I use in PROD for analyzing the schemas, it works well:

PROCEDURE anal_schema(P_SCHEMA in varchar2)
is
W_SCHEMA varchar2(30);
BEGIN
W_SCHEMA := P_SCHEMA;
DBMS_STATS.GATHER_SCHEMA_STATS(ownname => W_SCHEMA,
estimate_percent => DBMS_STATS.AUTO_SAMPLE_SIZE,
degree => DBMS_STATS.DEFAULT_DEGREE,
options => 'GATHER STALE',
method_opt => 'for all columns size AUTO',
cascade => TRUE);
END;

Just run first:

exec DBMS_STATS.ALTER_SCHEMA_TAB_MONITORING('SCOTT',true);

to turn monitoring on for the SCOTT's schema (for example) in case you wanna gather STALE statistics.

Do you know what is stale statistics?

[salvareztur]

And if I use this sentence??
dbms_stats.gather_table_stats('OWN_SIMEL',reg_CurParti.table_name,reg_CurParti.partition_name,
NULL,
FALSE,'FOR ALL COLUMNS SIZE 1',NULL,'PARTITION');

It's good??