|
为了保证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.
|
