Oracle: Analyze table vs Gather_Table_Stats vs Gather_Schema_Stats

Question

Currently in my DB, my predecessor who was working on DB has created a job which on every weekend gathers a list of all tables in the DB and individually for every table executes below two commands in that order:

ANALYZE TABLE xxTable_Namexx ESTIMATE STATISTICS FOR ALL INDEXED COLUMNS SIZE 75 SAMPLE 100 PERCENT;

EXEC dbms_stats.gather_table_stats(xxSchemaxx,xxTable_Namexx,cascade=>TRUE);

Recently someone suggested to me that:

1.) ANALYZE table is an old method of gathering stats and the optimizer doesn't use it anymore? is it true that the stats from this command is useless throughout the DB or is it being used somewhere?

2.) Instead of doing all this, it is enough to daily run:

dbms_stats.gather_schema_stats(xxSchemaxx,cascade=>true);

What is the size/sample % if nothing is specified ??

3.) What is the general practice/frequency for gather stats in a database. (Data is being updated,inserted,deleted on a daily basis). I still prefer doing it every week-end.

Thanks in advance.

Answer 1

Yes, ANALYZE is hardly used nowadays:

For the collection of most statistics, use the DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. See Oracle Database PL/SQL Packages and Types Reference for more information on the DBMS_STATS package.

Use the ANALYZE statement (rather than DBMS_STATS) for statistics collection not related to the cost-based optimizer:

• To use the VALIDATE or LIST CHAINED ROWS clauses

• To collect information on freelist blocks

What is the size/sample % if nothing is specified ??

Parameter is called estimate_percent:

Percentage of rows to estimate (NULL means compute): The valid range is [0.000001,100]. Use the constant DBMS_STATS.AUTO_SAMPLE_SIZE to have Oracle determine the appropriate sample size for good statistics. This is the default.The default value can be changed using the SET_DATABASE_PREFS Procedure, SET_GLOBAL_PREFS Procedure, SET_SCHEMA_PREFS Procedure and SET_TABLE_PREFS Procedure.

Default value you can interrogate by function DBMS_STATS.get_param('ESTIMATE_PERCENT') or DBMS_STATS.GET_PREFS('ESTIMATE_PERCENT')

By default Oracle creates an internal scheduler job at installation which gathers the statistics automatically at night time. (called BSLN_MAINTAIN_STATS_JOB)