DBMS STATS

From Robs_Wiki
Jump to: navigation, search

Gather Schema Stats

spool gather_stats.log
prompt Starting Gather Stats ....
EXECUTE DBMS_STATS.GATHER_SCHEMA_STATS('&SCHEMA',DBMS_STATS.AUTO_SAMPLE_SIZE);
spool off;
quit;

Gather Table Stats

EXEC DBMS_STATS.gather_table_stats('SCOTT', 'EMP');

Gather Table Partition Stats

exec dbms_stats.gather_table_stats(OWNNAME=>'SCOTT', TABNAME=>'SCOTT_SESSION_LOG', PARTNAME=>'SCOTT_SESSION_LOG_2018', DEGREE=> 8, CASCADE=> TRUE, ESTIMATE_PERCENT=>dbms_stats.auto_sample_size);

Script to gather stats on all table partitions: admin_stats_partitions.sql