Query to Generate Gather Statistics on Tables

Query to automatically generate GATHER table statistics for all tables that have stale statistics. DBMS_STATS is the preferred method of gathering table statistics vs the deprecated ANALYZE table command since Oracle 9i.

— Please note that the sample size/estimate_percent I use is at 100%, you may set it to lower for bigger tables as it will affect gather stats duration.

select 'EXECUTE DBMS_STATS.GATHER_TABLE_STATS (''' || owner || ''',''' || table_name || ''',estimate_percent=>100, degree => 4, METHOD_OPT => ''FOR ALL COLUMNS SIZE AUTO'', CASCADE => TRUE, GRANULARITY => ''ALL'');' from dba_tab_statistics where stale_stats <> 'NO' order by num_rows asc;

This query will generate a gather stats command like this:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('[TABLE_OWNER]','[TABLE_NAME]',estimate_percent=>100, degree => 4, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE, GRANULARITY => 'ALL');

Use the query below for gathering stats of tables partitions:

EXECUTE DBMS_STATS.GATHER_TABLE_STATS ('[TABLE_OWNER]','[TABLE_NAME]',partname=>'[PARTITION_NAME]',estimate_percent=>100, degree => 4, METHOD_OPT => 'FOR ALL COLUMNS SIZE AUTO', CASCADE => TRUE, GRANULARITY => 'ALL');

Tips to improve gather stats performance:

  1. Set METHOD_OPT to FOR ALL COLUMNS SIZE 1 – this causes the gather stats procedure to not generate histograms on the columns.
  2. Set degree to a higher value – Degree indicates the number of parallel processes/cpu threads the gather stats operation use when generating statistics. So you can check the number of cpus allocated to your database by executing this command “show parameter cpu_cout”. The number of cpus indicate the maximum number or parallel processes you can use for the degree parameter.

If you need more help with database issues, feel free to send me an email (jimmycdo@yahoo.com) and I'll gladly help.

About Jimbart