DBMS_STATS.GATHER_SYSTEM_STATS

不建立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,