Oracle数据字典:aux_stats$基表

AUX_STATS$基表用于存放一些优化器使用的辅助统计信息。该基表会在创建数据库(create database)调用sql.bsq(11g以前)或者doptim.bsq(sql.bsq->dboptim.sql 11g以后)时被创建。   其中sname、pname 2列维护主键， 在这2列上还有一个唯一索引  i_aux_stats$。  

Rem The aux_stats$ table contains auxiliary statistics used by optimizer.
Rem sname and pname maintain primary key where sname stores name of set
Rem of parameters and pname is name of parameter. pval1 or pval2 store
Rem parameter value in character or number format.
create table aux_stats$ (
  sname varchar2("M_IDEN") not null, /* Name of set */
  pname varchar2("M_IDEN") not null, /* Name of parameters*/
  pval1 number,                      /* NUMBER parameter value */
  pval2 varchar2(255)                /* VARCHAR2 parameter value */
)
/
create unique index i_aux_stats$ on aux_stats$(sname, pname)
/

procedure gather_system_stats (
  gathering_mode  varchar2 default 'NOWORKLOAD',
  interval  integer  default 60,
  stattab   varchar2 default null,
  statid    varchar2 default null,
  statown   varchar2 default null);
--
-- This procedure gathers system statistics.
--
-- Input arguments:
--   mode - Allowable values: INTERVAL, START, STOP.
--     INTERVAL:
--       In INTERVAL mode user can provide interval parameter. After <interval>
--       minutes elapsed system statistics in dictionary or stattab will be
--       updated or created. This statistics captures system activity during
--       specified interval.
--     START | STOP:
--       START will initiate gathering statistics. STOP will calculate
--       statistics for elapsed period of time (since START) and refresh
--       dictionary or stattab. Interval in these modes is ignored.
--   interval - Specifies period of time in minutes for gathering statistics
--      in INTERVAL mode.
--   stattab - The user stat table identifier describing where to save
--      the current statistics.
--   statid - The (optional) identifier to associate with these statistics
--      within stattab.
--   statown - The schema containing stattab (if different then ownname)
--
-- Exceptions:
--   ORA-20000: Object does not exist or insufficient privileges
--   ORA-20001: Bad input value
--   ORA-20002: Bad user statistics table, may need to upgrade it
--   ORA-20003: Unable to gather system statistics
--   ORA-20004: Error in "INTERVAL" mode :
--              system parameter job_queue_processes must be > 0




SQL> col sname for a30
SQL> col pname for a30
SQL> col pval1 for 99999999
SQL> col pval2 for a30

SQL> set linesize 200 pagesize 1400



SQL> select * from aux_stats$;

SNAME                          PNAME                              PVAL1 PVAL2
------------------------------ ------------------------------ --------- ------------------------------
SYSSTATS_INFO                  STATUS                                   COMPLETED
SYSSTATS_INFO                  DSTART                                   09-17-2011 10:21
SYSSTATS_INFO                  DSTOP                                    09-17-2011 10:21
SYSSTATS_INFO                  FLAGS                                  1
SYSSTATS_MAIN                  CPUSPEEDNW                          1752
SYSSTATS_MAIN                  IOSEEKTIM                             10
SYSSTATS_MAIN                  IOTFRSPEED                          4096
SYSSTATS_MAIN                  SREADTIM
SYSSTATS_MAIN                  MREADTIM
SYSSTATS_MAIN                  CPUSPEED
SYSSTATS_MAIN                  MBRC
SYSSTATS_MAIN                  MAXTHR
SYSSTATS_MAIN                  SLAVETHR

$ sqlplus / as sysdba
alter session set nls_date_format='DD-MM-YY hh24:mi:ss';
set serveroutput on ;
exec DBMS_STATS.CREATE_STAT_TABLE ('SYS','sys_stats');

--
-- The following gather system stats should be done at the peak workload time frame to get reliable stats
--
BEGIN
   DBMS_STATS.GATHER_SYSTEM_STATS ('interval',interval => 180, stattab => 'sys_stats', statid => 'OLTP');
END;
/

DECLARE
  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 => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('status : ' || status);
  DBMS_OUTPUT.PUT_LINE('cpu in mhz : ' || pvalue);
  DBMS_OUTPUT.PUT_LINE('start :' || dstart);
  DBMS_OUTPUT.PUT_LINE('stop :' || dstop);
  PNAME := 'sreadtim';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('single block readtime in ms : ' || pvalue);
  PNAME := 'mreadtim';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('multiblock readtime in ms : ' || pvalue);
  PNAME := 'mbrc';
  DBMS_STATS.GET_SYSTEM_STATS(status,
                              dstart,
                              dstop,
                              pname,
                              pvalue,
                              stattab => 'sys_stats',
                              statid  => 'OLTP',
                              statown => 'SYS');
  DBMS_OUTPUT.PUT_LINE('average multiblock readcount: ' || pvalue);
END;
/

status : COMPLETED
cpu in mhz : 727
start :13-02-10 14:00:00
stop :13-02-10 17:00:00
single block readtime in ms : 15.294
multiblock readtime in ms : 29.6
average multiblock readcount: 4

PL/SQL procedure successfully completed.

  可以看到aux_stats$实际存放的是Oracle通过DBMS_STATS.GATHER_SYSTEM_STATS收集到的一些主机系统的性能指标，包括CPU速度、IO寻道速度等等常见的主机性能参数。(The output from dbms_stats.gather_system_stats is stored in the AUX_STATS$ table and hence the above query output will provide the captured details)   Note:How to Collect and Display System Statistics (CPU and IO) for CBO use [ID 149560.1] Note:System Statistics: How to gather system stats for a particular batch of work [ID 427939.1] How To Gather and Display The Workload System Statistics? [ID 1148577.1] 分别指出了在不同环境中正确收集主机性能指标的方法。