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]
分别指出了在不同环境中正确收集主机性能指标的方法。
Comment