Thursday, January 22, 2015

A complete script for gathering system, data dictionary and fixed objects stats

alter session set nls_language='AMERICAN';
set timing on
set serveroutput on
set lines 200
col pname format a20

BEGIN
DBMS_STATS.GATHER_DICTIONARY_STATS (
Estimate_Percent  => DBMS_STATS.AUTO_SAMPLE_SIZE
,method_opt        => 'FOR ALL COLUMNS SIZE AUTO'
,Degree            => NULL
,Cascade           => DBMS_STATS.AUTO_CASCADE
,granularity       => 'AUTO'
,No_Invalidate     => DBMS_STATS.AUTO_INVALIDATE);
END;
/

BEGIN
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS;
END;
/

SELECT PNAME, PVAL1 FROM SYS.AUX_STATS$;

PROMPT Gather workload system stats, sample for 1 hour
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS (
GATHERING_MODE=>'INTERVAL',
interval => 60,
statid   => 'DAYTIME');
END;
/

SELECT PNAME, PVAL1 FROM SYS.AUX_STATS$;
exit

Tim Hall has written a good article about how the gather_system_stats procedure works, read it at www.oracle-base.com

No comments:

Post a Comment