可以改写下面的sql语句。


Description
Displays the values of any System Statistics in place or in a stats table
Parameters
&statlabel - Statistics Label
&tname - Table name of the system stats
SQL Source
REM Copyright (C) Think Forward.com 1998- 2005. All rights reserved.


set serveroutput on size 1000000
set verify off
set echo off


REM
REM this file queries the values in a stored system stats table.


spool getsysstats.lst


ACCEPT tname PROMPT 'Enter value for stattabname : '
ACCEPT statlabel PROMPT 'Enter value for stat id : '


DECLARE


l_stattab_owner VARCHAR2(30) := 'SYSTEM';
l_statid       VARCHAR(20) := '&statlabel';
l_stattab_name VARCHAR2(30) := '&tname';


STATUS VARCHAR2(20);
DSTART DATE;
DSTOP DATE;
PVALUE NUMBER;
PNAME VARCHAR2(30);


BEGIN
  PNAME := 'cpuspeed';
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner);
  DBMS_OUTPUT.PUT_LINE('Status                                                   : '||status);
  DBMS_OUTPUT.PUT_LINE('Start date                                               : '||to_char(dstart,'dd-mon-yyyy hh24:mi'));
  DBMS_OUTPUT.PUT_LINE('Stop Date                                                : '||to_char(dstop,'dd-mon-yyyy hh24:mi'));
  DBMS_OUTPUT.PUT_LINE('cpu in mhz                                               : '||pvalue);
  PNAME := 'sreadtim';
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner);
  DBMS_OUTPUT.PUT_LINE('single block readtime (random read) in ms                : '||pvalue);
  PNAME := 'mreadtim';
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner);
  DBMS_OUTPUT.PUT_LINE('multiblock readtime (sequental read) in ms               : '||pvalue);
  PNAME := 'mbrc';
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner);
  DBMS_OUTPUT.PUT_LINE('average multiblock readcount for sequenial read in blocks: '||pvalue);
  PNAME := 'maxthr';
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner);
  DBMS_OUTPUT.PUT_LINE('maximum IO system throughput in bytes/sec                : '||pvalue);
  PNAME := 'slavethr';
  DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue, stattab =>l_stattab_name, statid => l_statid, statown => l_stattab_owner);
  DBMS_OUTPUT.PUT_LINE('maximum IO slave throughput in bytes/sec                 : '||pvalue);
END;
/   

为了保证ORACLE能够产生效率较高的执行计划，有时候我们可能需要收集系统的统计信息来告诉ORACLE你的硬件系统的能力。
我们可以使用DBMS_STATS.GATHER_SYSTEM_STATS来收集系统的统计信息。

这个过程的参数如下：

DBMS_STATS.GATHER_SYSTEM_STATS (
   gathering_mode   VARCHAR2 DEFAULT 'NOWORKLOAD',
   interval         INTEGER  DEFAULT NULL,
   stattab          VARCHAR2 DEFAULT NULL,
   statid           VARCHAR2 DEFAULT NULL,
   statown          VARCHAR2 DEFAULT NULL);

主要用到的参数是gathering_mode，这个参数有4个选项：NOWORKLOAD,INTERVAL,START|STOP。

其中START和STOP一般是成对使用的。

对于这几个参数联机文档时这样描述的：

NOWORKLOAD: Will capture characteristics of the I/O system. Gathering may take a few minutes and depends on the size of the
database. During this period Oracle will estimate the average read seek time and transfer speed for the I/O system. This mode is
suitable for the all workloads. Oracle recommends to run GATHER_SYSTEM_STATS ('noworkload') after creation of the database and
tablespaces. To fine tune system statistics for the workload use 'START' and 'STOP' or 'INTERVAL' options. If you gather both
'NOWORKLOAD' and workload specific (statistics collected using 'INTERVAL' or 'START' and 'STOP' ), the workload statistics will be
used by optimizer. Collected components: cpuspeednw, ioseektim, iotfrspeed.

INTERVAL: Captures system activity during a specified interval. This works in combination with the interval parameter. You should
provide an interval value in minutes, after which system statistics are created or updated in the dictionary or stattab. You can
use GATHER_SYSTEM_STATS (gathering_mode=>'STOP') to stop gathering earlier than scheduled. Collected components: maxthr, slavethr,
cpuspeed, sreadtim, mreadtim, mbrc.

START | STOP: Captures system activity during specified start and stop times and refreshes the dictionary or stattab with
statistics for the elapsed period. Interval value is ignored. Collected components: maxthr, slavethr, cpuspeed, sreadtim, mreadtim,
mbrc.

如果启用使用INTERVAL参数,ORACLE会利用DBMS_SCHEDULER包建立2个JOB来收集系统统计信息。如下所示：

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('INTERVAL',60);

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.04

SQL> SELECT JOB_NAME,JOB_ACTION FROM DBA_SCHEDULER_JOBS;

JOB_NAME               JOB_ACTION
---------------------- --------------------------------------------------------------------------------
STATJOB$_66            dbms_stats.gather_system_stats(gathering_mode=>'flush_cache');
STATJOB$_65            begin dbms_stats.gather_system_stats(gathering_mode => 'AUTO_STOP', statown => '
                       SYS'); end;
AUTO_SPACE_ADVISOR_JOB
GATHER_STATS_JOB
FGR$AUTOPURGE_JOB      sys.dbms_file_group.purge_file_group(NULL);
PURGE_LOG
MGMT_STATS_CONFIG_JOB  ORACLE_OCM.MGMT_CONFIG.collect_stats
MGMT_CONFIG_JOB        ORACLE_OCM.MGMT_CONFIG.collect_config
RLM$SCHDNEGACTION      begin dbms_rlmgr_dr.execschdactions('RLM$SCHDNEGACTION'); end;
RLM$EVTCLEANUP         begin dbms_rlmgr_dr.cleanup_events; end;
JOB$_23                INSERT_DATA
JOB$_22                BEGIN INSERT INTO YSP.JOB_TEST VALUES(SYSDATE); COMMIT; END;
12 rows selected.
Elapsed: 00:00:00.00

如果想取消掉可以利用如下方式：

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.05

SQL> SELECT JOB_NAME,JOB_ACTION FROM DBA_SCHEDULER_JOBS;

JOB_NAME               JOB_ACTION
---------------------- --------------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB
GATHER_STATS_JOB
FGR$AUTOPURGE_JOB      sys.dbms_file_group.purge_file_group(NULL);
PURGE_LOG
MGMT_STATS_CONFIG_JOB  ORACLE_OCM.MGMT_CONFIG.collect_stats
MGMT_CONFIG_JOB        ORACLE_OCM.MGMT_CONFIG.collect_config
RLM$SCHDNEGACTION      begin dbms_rlmgr_dr.execschdactions('RLM$SCHDNEGACTION'); end;
RLM$EVTCLEANUP         begin dbms_rlmgr_dr.cleanup_events; end;
JOB$_23                INSERT_DATA
JOB$_22                BEGIN INSERT INTO YSP.JOB_TEST VALUES(SYSDATE); COMMIT; END;
10 rows selected.

系统统计信息的收集默认是放到SYS.AUX_STATS$系统表中。如下：

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('START');

PL/SQL procedure successfully completed.

SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS('STOP');

PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.AUX_STATS$;

SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_INFO        STATUS                          COMPLETED
SYSSTATS_INFO        DSTART                          07-02-2010 15:46
SYSSTATS_INFO        DSTOP                           07-02-2010 15:47
SYSSTATS_INFO        FLAGS                         1
SYSSTATS_MAIN        CPUSPEEDNW             1085.791
SYSSTATS_MAIN        IOSEEKTIM                 6.751
SYSSTATS_MAIN        IOTFRSPEED            22377.453
SYSSTATS_MAIN        SREADTIM
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED                   1086
SYSSTATS_MAIN        MBRC
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR
13 rows selected.

SNAME='SYSSTATS_INFO'主要显示的是系统收集状态的，是否收集完成、收集时间等等。

我们主要看带有SYSSTATS_MAIN 的行。

各个PNAME的含义如下：
CPUSPEEDNW  平均每秒CPU周期 以百万为单位。NW表示以NOWORKLOAD方式收集的统计信息。
IOSEEKTIM   寻道时间+延迟时间+系统开销。以毫秒为单位。
IOTFRSPEED  每毫秒的I/O传输字节数
SREADTIM    平均单块读的时间。以毫秒为单位。
MREADTIM    平均多块读的时间。以毫秒为单位。
CPUSPEED    同CPUSPEEDNW。只不过收集方式不是以NOWORKLOAD。
MBRC        平均连续多块读取的数
MAXTHR      最大系统I/O吞吐量，以字节/秒为单位
SLAVETHR    平均从属I/O吞度量，以字节/秒为单位

我们也可以手工设置这些组件的值：
如：
SQL> EXEC DBMS_STATS.SET_SYSTEM_STATS('MBRC',64);

PL/SQL procedure successfully completed.

SQL> SELECT * FROM SYS.AUX_STATS$;

SNAME                PNAME                     PVAL1 PVAL2
-------------------- -------------------- ---------- ------------------------------
SYSSTATS_INFO        STATUS                          COMPLETED
SYSSTATS_INFO        DSTART                          07-02-2010 16:00
SYSSTATS_INFO        DSTOP                           07-02-2010 16:00
SYSSTATS_INFO        FLAGS                         1
SYSSTATS_MAIN        CPUSPEEDNW             1085.791
SYSSTATS_MAIN        IOSEEKTIM                 6.751
SYSSTATS_MAIN        IOTFRSPEED            22377.453
SYSSTATS_MAIN        SREADTIM
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED                   1086
SYSSTATS_MAIN        MBRC                         64
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR
13 rows selected.

同样我们也可以用DBMS_STATS.GET_SYSTEM_STATS得到某个组件的值，如下：

SQL> DECLARE
  2    l_status VARCHAR2(30);
  3    l_dstart DATE;
  4    l_dstop  DATE;
  5    l_pvalue NUMBER;
  6  BEGIN
  7    DBMS_STATS.GET_SYSTEM_STATS(
  8    status=>l_status,
  9    dstart=>l_dstart,
10    dstop=>l_dstop,
11    pname=>'MBRC',
12    pvalue=>l_pvalue);
13    DBMS_OUTPUT.PUT_LINE('The value of MBRC is : '||TO_CHAR(l_pvalue));
14  END;
15  /

The value of MBRC is : 64

PL/SQL procedure successfully completed.

SQL>
SQL> SET SERVEROUTPUT ON
SQL> DECLARE
  2   STATUS VARCHAR2(20);
  3   DSTART DATE;
  4   DSTOP DATE;
  5   PVALUE NUMBER;
  6   PNAME VARCHAR2(30);
  7  BEGIN
  8   PNAME := 'sreadtim';
  9   DBMS_STATS.GET_SYSTEM_STATS(status, dstart, dstop, pname, pvalue);
10   DBMS_OUTPUT.PUT_LINE('single block readtime in ms : '||pvalue);
11  END;
12  /
single block readtime in ms :

PL/SQL procedure successfully completed.

SQL>

收集啦

问个问题。为什么收集了多次 SYSSTATS_MAIN  都没有统计信息，用的是11g

具体贴一下。

回复 oraunix 的帖子

老师求教个关于系统统计信息的问题：
系统统计信息有两类：
1、noworkload     --系统空闲时收集
2、在系统正常负载下  --会有个时间段有业务在跑可以收集到数据
今天我做了noworkload（测试机）的系统统计信息的收集。
我不明白的地方是：oracle是如何收集的或者可以理解为是否我需要手动做些操作产生些负载然后oracle才能收集到相关数据。
对于我的疑问：我用10046跟踪了下，发现里面跑了两条貌似无关的SQL语句,分别是：
select count(*) cnt from v$datafile where status in ('SYSTEM', 'ONLINE')；
select file#,  blocks, block_size from v$datafile where status in ('SYSTEM', 'ONLINE')；
我想知道的是，oracle是不是跑了这两条sql语句用于收集无负载情况下系统的性能数据，如果oracle什么都没做的话也就谈不到收集无负载的系统性能数据啦，我不知道我的理解对不对，请老师指点下。

好东西好东西好东西