使用DBMS_STATS收集系統狀態

DBMS_STATS.GATHER_SYSTEM_STATS

[@more@]

不建立Stattab:

ShXXXX SQL> show parameter job;

NAME TYPE VALUE

------------------------------------ ----------- ------------------------------

_job_queue_interval integer 60

job_queue_processes integer 4

SQL> exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>20,statid=>'REALTIME');

PL/SQL procedure successfully completed.

檢視捕獲的SYSTEM_STATISTICS

select * from sys.aux_stats$

SYSSTATS_INFO,STATUS,,COMPLETED

SYSSTATS_INFO,DSTART,,10-22-2008 10:10

SYSSTATS_INFO,DSTOP,,10-22-2008 10:30

SYSSTATS_INFO,FLAGS,0,

SYSSTATS_MAIN,SREADTIM,1.323,

SYSSTATS_MAIN,MREADTIM,0.08,

SYSSTATS_MAIN,CPUSPEED,883,

SYSSTATS_MAIN,MBRC,14,

SYSSTATS_MAIN,MAXTHR,54740992,

SYSSTATS_MAIN,SLAVETHR,-1,

不直接UPDATE到Dictionary的方式：

先建立用來儲存SYSTEM STATISTICS的TABLE.

SQL> exec dbms_stats.create_stat_table(ownname=>'SYS',stattab=>'DNB_STATTAB',tblspace=>'PERFSTAT');

PL/SQL procedure successfully completed.

執行Gather_Stats:

SQL>exec dbms_stats.gather_system_stats(gathering_mode=>'INTERVAL',interval=>20,stattab=>'DNB_STATTAB',statid=>'DNB_REAL',statown=>'SYS');

PL/SQL procedure successfully completed.

看一下DNB_STATTAB中的資料：

select * from DNB_STATTAB

DNB_REAL,S,4,0,AUTOGATHERING,10-22-2008 11:33,11-11-2008 11:33,CPU_SERIO,,-1,-1,-1,102285923,282305710,14270552,16698270,8157240,6212109,361,-1,301418488,,,,

DNB_REAL,S,4,0,,,,PARIO,,-1,-1,,,,,,,,,,,,,,

此時Dictionary裡是沒有資料的。

SQL> select * from sys.aux_stats$;

no rows selected

可以透過DBMS_STATS.IMPORT_SYSTEM_STATS來完成將統計狀態update到Dictionary.

SQL> exec dbms_stats.import_system_stats(stattab=>'DNB_STATTAB',statid=>'DNB_REAL',statown=>'SYS');

PL/SQL procedure successfully completed.

檢視sys.aux_stats$

select * from sys.aux_stats$

SYSSTATS_INFO,STATUS,,COMPLETED

SYSSTATS_INFO,DSTART,,10-22-2008 12:59

SYSSTATS_INFO,DSTOP,,10-22-2008 13:19

SYSSTATS_INFO,FLAGS,0,

SYSSTATS_MAIN,SREADTIM,2.799,

SYSSTATS_MAIN,MREADTIM,0.374,

SYSSTATS_MAIN,CPUSPEED,1321,

SYSSTATS_MAIN,MBRC,15,

SYSSTATS_MAIN,MAXTHR,21851136,

SYSSTATS_MAIN,SLAVETHR,-1,