3 More SYSTEM related statistics in 10gr2 system, dictionary and fixed_objects
# Gather system statistics
execute dbms_stats.gather_system_stats('Start');
-- some time delay while the database is under a typical workload
execute dbms_stats.gather_system_stats('Stop');
-- OR
exec dbms_stats.gather_system_stats('interval', interval=>N);
where N is the number of minutes stats will be gathered
# collect OLTP and BATCH related system stats and import it when desired
exec dbms_stats.create_stat_table('SYS','CSX_STAT');
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLTP');
END;
/
BEGIN
DBMS_STATS.GATHER_SYSTEM_STATS(
gathering_mode => 'interval',
interval => 720,
stattab => 'CSX_STAT',
statid => 'OLAP');
END;
/
During the day, the following jobs import the OLTP statistics for the daytime run:
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLTP'');'
SYSDATE, 'SYSDATE + 1');
COMMIT;
END;
/
During the night, the following jobs import the OLAP statistics for the nighttime run:
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'DBMS_STATS.IMPORT_SYSTEM_STATS(''CSX_STAT'',''OLAP'');'
SYSDATE + 0.5, 'SYSDATE + 1');
COMMIT;
END;
/
# Gather statistics on dictionay
EXEC DBMS_STATS.GATHER_DICTIONARY_STATS;
-- or
begin
dbms_stats.gather_dictionary_stats(
comp_id => null,
estimate_percent => 15,
granularity => 'DEFAULT',
cascade => TRUE);
end;
/
# gather fixed object(synamic performance tables eg. x$) stats
exec dbms_stats.gather_fixed_objects_stats;
-- or
SQL> conn / as sysdba
SQL> exec DBMS_STATS.CREATE_STAT_TABLE('sys','csxfixedtablestat');
SQL> EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS ('csxfixedtablestat');
# Flush statistics from memory
DBMS_STATS.FLUSH_DATABASE_MONITORING_INFO
# view
USER_TAB_MODIFICATIONS
# system statistics views
select pname, pval1
from sys.aux_stats$
where sname = 'SYSSTATS_MAIN';
