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,
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/10856805/viewspace-1012287/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/10856805/viewspace-1012287/