Table stats gather commands

                                            Table stats gather

The Oracle RDBMS allows you to collect statistics of many different kinds as an aid to improving performance.DBMS_STATS.gather_table_stats package is used to gather stats for table.


Here are some useful commands for table stats gather.

exec dbms_stats.gather_table_stats(ownname=>'ADM',tabname=>'PS_SCH',cascade=>TRUE,estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'FOR ALL  COLUMNS SIZE AUTO');

select 'exec dbms_stats.gather_table_stats(ownname=>'||''''||'SYSADM'||''''||',tabname=>'||''''||table_name||''''||',cascade=>TRUE,estimate_percent
=>DBMS_STATS.AUTO_SAMPLE_SIZE ,method_opt=>'||''''||'FOR ALL COLUMNS SIZE AUTO'||''''||');' from dba_tables  where TABLE_NAME like 'PS_GP\_%' ESCAPE '\' and owner='ADM'


Comment me if you find this post is useful also let me know if you need any new topics.