系统统计信息的保存位置

Posted on 2011/10/09 by yangtingkun

从9i开始，Oracle就提供了收集系统统计信息的能力，不过直到11.2的文档中，Oracle都从来没有提过统计信息可以从哪个视图中获取。
虽然Oracle提供了一个GET_SYSTEM_STATS的功能，但是使用非常不方便，不如直接查看视图那么简单直观。
既然Oracle自己没有说明，可以通过TRACE的方式来获取Oracle将统计信息存储到哪个基表中：

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';
会话已更改。
SQL> EXEC DBMS_STATS.GATHER_SYSTEM_STATS
PL/SQL 过程已成功完成。
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';
会话已更改。
SQL> SELECT SPID 
  2  FROM V$SESSION S, V$PROCESS P
  3  WHERE S.PADDR = P.ADDR
  4  AND SID IN 
  5  (SELECT SID
  6  FROM V$MYSTAT
  7  WHERE ROWNUM = 1);
SPID
------------
17816

查看对应的TRACE信息：

*** 2011-10-10 08:32:39.008
*** ACTION NAME:() 2011-10-10 08:32:38.847
*** MODULE NAME:(SQL*Plus) 2011-10-10 08:32:38.847
*** SERVICE NAME:(SYS$USERS) 2011-10-10 08:32:38.847
*** SESSION ID:(144.3) 2011-10-10 08:32:38.847
WAIT #9: nam='SQL*Net message to client' ela= 4 driver id=1111838976 #bytes=1 p3=0 obj#=358 tim=68276628744
WAIT #9: nam='SQL*Net message from client' ela= 271 driver id=1111838976 #bytes=1 p3=0 obj#=358 tim=68276853358
=====================
.
.
.
=====================
PARSING IN CURSOR #7 len=44 dep=0 uid=57 oct=47 lid=57 tim=68330942448 hv=3102768059 ad='242ca1cc'
BEGIN DBMS_STATS.GATHER_SYSTEM_STATS; END;
END OF STMT
PARSE #7:c=0,e=2137,p=0,cr=0,cu=0,mis=1,r=0,dep=0,og=1,tim=68330942444
BINDS #7:
=====================
PARSING IN CURSOR #8 len=80 dep=1 uid=0 oct=3 lid=0 tim=68331143167 hv=374192777 ad='2849d9a0'
SELECT /*+ rule */ VALUE FROM V$PARAMETER WHERE UPPER(NAME) = 'STATISTICS_LEVEL'
END OF STMT
PARSE #8:c=0,e=21803,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=3,tim=68331143162
BINDS #8:
EXEC #8:c=15601,e=33726,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=3,tim=68331248401
FETCH #8:c=0,e=8267,p=0,cr=0,cu=0,mis=0,r=1,dep=1,og=3,tim=68331272092
=====================
.
.
.
=====================
PARSING IN CURSOR #10 len=56 dep=1 uid=57 oct=7 lid=57 tim=68354281222 hv=311428414 ad='2853a860'
DELETE FROM SYS.AUX_STATS$ WHERE SNAME = 'SYSSTATS_INFO'
END OF STMT
PARSE #10:c=31200,e=117964,p=12,cr=157,cu=0,mis=1,r=0,dep=1,og=1,tim=68354281219
BINDS #10:
EXEC #10:c=0,e=24330,p=0,cr=1,cu=8,mis=0,r=4,dep=1,og=1,tim=68354305618
=====================
PARSING IN CURSOR #9 len=73 dep=1 uid=57 oct=2 lid=57 tim=68354305942 hv=454001916 ad='2853a638'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'STATUS', NULL, :B1 )
END OF STMT
PARSE #9:c=0,e=187,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=68354305940
BINDS #9:
kkscoacd
Bind#0
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=0565dbe4 bln=32 avl=09 flg=09
VALUE="COMPLETED"
WAIT #9: nam='db file sequential read' ela= 18196 file#=1 block#=4537 blocks=1 obj#=572 tim=68354324705
EXEC #9:c=0,e=19012,p=1,cr=1,cu=3,mis=1,r=1,dep=1,og=1,tim=68354325026
=====================
PARSING IN CURSOR #11 len=73 dep=1 uid=57 oct=2 lid=57 tim=68354325455 hv=3821777836 ad='2853a410'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'DSTART', NULL, :B1 )
END OF STMT
PARSE #11:c=0,e=304,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=68354325452
BINDS #11:
kkscoacd
Bind#0
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=0565dc04 bln=32 avl=16 flg=09
VALUE="10-10-2011 08:33"
EXEC #11:c=0,e=657,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=1,tim=68354326236
=====================
PARSING IN CURSOR #12 len=72 dep=1 uid=57 oct=2 lid=57 tim=68354326455 hv=912777027 ad='2853a1e8'
INSERT INTO SYS.AUX_STATS$ VALUES ('SYSSTATS_INFO', 'DSTOP', NULL, :B1 )
END OF STMT
PARSE #12:c=0,e=148,p=0,cr=0,cu=0,mis=1,r=0,dep=1,og=1,tim=68354326452
BINDS #12:
kkscoacd
Bind#0
oacdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00
oacflg=13 fl2=206001 frm=01 csi=852 siz=32 off=0
kxsbbbfp=0565dc24 bln=32 avl=16 flg=09
VALUE="10-10-2011 08:33"
EXEC #12:c=0,e=419,p=0,cr=1,cu=3,mis=1,r=1,dep=1,og=1,tim=68354326949
=====================
.
.
.
=====================
PARSING IN CURSOR #26 len=6 dep=1 uid=57 oct=44 lid=57 tim=68354566045 hv=255718823 ad='0'
COMMIT
END OF STMT
PARSE #26:c=0,e=11,p=0,cr=0,cu=0,mis=0,r=0,dep=1,og=0,tim=68354566043
XCTEND rlbk=0, rd_only=0
EXEC #26:c=0,e=178,p=0,cr=0,cu=1,mis=0,r=0,dep=1,og=0,tim=68354566282
EXEC #7:c=530404,e=23458716,p=42,cr=578,cu=75,mis=0,r=1,dep=0,og=1,tim=68354566323
WAIT #7: nam='log file sync' ela= 855 buffer#=1807 sync scn=1998734 p3=0 obj#=-1 tim=68354567211
WAIT #7: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=68354567508
WAIT #7: nam='SQL*Net message from client' ela= 333 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=68354567875
=====================
PARSING IN CURSOR #27 len=52 dep=0 uid=57 oct=47 lid=57 tim=68354567976 hv=1029988163 ad='28480080'
BEGIN DBMS_OUTPUT.GET_LINES(:LINES, :NUMLINES); END;
END OF STMT
PARSE #27:c=0,e=29,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=1,tim=68354567974
BINDS #27:
kkscoacd
Bind#0
oacdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00
oacflg=00 fl2=1000000 frm=00 csi=00 siz=4000 off=0
kxsbbbfp=056e555c bln=4000 avl=00 flg=15
Bind#1
oacdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00
oacflg=01 fl2=1000000 frm=00 csi=00 siz=24 off=0
kxsbbbfp=0ab6bde8 bln=22 avl=22 flg=05
VALUE=###
An invalid NUMBER has been seen.Memory contents are :
Dump OF memory FROM 0x0AB6BDE8 TO 0x0AB6BDFE
AB6BDE0 000010C1 00000000 [........]
AB6BDF0 00000000 00000000 00000000 00000000 [................]
WAIT #27: nam='SQL*Net message to client' ela= 1 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=68354568397
EXEC #27:c=0,e=416,p=0,cr=3,cu=0,mis=0,r=1,dep=0,og=1,tim=68354568514
*** 2011-10-10 08:34:55.278
WAIT #27: nam='SQL*Net message from client' ela= 58488791 driver id=1111838976 #bytes=1 p3=0 obj#=-1 tim=68413057389
=====================
PARSING IN CURSOR #25 len=55 dep=0 uid=57 oct=42 lid=57 tim=68413057883 hv=524428051 ad='0'
ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF'
END OF STMT
PARSE #25:c=0,e=132,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=68413057880
EXEC #25:c=0,e=163,p=0,cr=0,cu=0,mis=0,r=0,dep=0,og=0,tim=68413058129

可以从TRACE结果很容易的得到，Oracle将统计信息放入了SYS.AUX_STATS$表中。
可惜的是，Oracle似乎并没有提供一个视图给所有的用户进行访问，因此只有权限用户才能直接访问这个内部表：

SQL> SELECT * FROM SYS.AUX_STATS$;
SNAME                PNAME                PVAL1 PVAL2
-------------------- --------------- ---------- ------------------------
SYSSTATS_INFO        STATUS                     COMPLETED
SYSSTATS_INFO        DSTART                     09-10-2011 08:33
SYSSTATS_INFO        DSTOP                      09-10-2011 08:33
SYSSTATS_INFO        FLAGS                    1
SYSSTATS_MAIN        CPUSPEEDNW        1555.208
SYSSTATS_MAIN        IOSEEKTIM           11.312
SYSSTATS_MAIN        IOTFRSPEED            4096
SYSSTATS_MAIN        SREADTIM
SYSSTATS_MAIN        MREADTIM
SYSSTATS_MAIN        CPUSPEED
SYSSTATS_MAIN        MBRC
SYSSTATS_MAIN        MAXTHR
SYSSTATS_MAIN        SLAVETHR
已选择13行。

而如果使用DBMS_STATS.GET_SYSTEM_STATS则显然要麻烦得多：

SQL> SET SERVEROUT ON SIZE 100000
SQL> DECLARE
  2   V_STATUS VARCHAR2(30);
  3   V_DSTART DATE;
  4   V_DSTOP DATE;
  5   V_PVALUE NUMBER;
  6  BEGIN
  7   DBMS_STATS.GET_SYSTEM_STATS(V_STATUS, V_DSTART, V_DSTOP, 'cpuspeednw', V_PVALUE);
  8   DBMS_OUTPUT.PUT_LINE(RPAD('STATUS', 31) || RPAD('START', 24) || RPAD('STOP', 24) || 'VALUE');
  9   DBMS_OUTPUT.PUT_LINE(RPAD(V_STATUS, 31) || RPAD(TO_CHAR(V_DSTART, 'YYYY-MM-DD HH24:MI:SS'), 24)
 10    || RPAD(TO_CHAR(V_DSTOP, 'YYYY-MM-DD HH24:MI:SS'), 24) || V_PVALUE);
 11  END;
 12  /  
STATUS                         START                   STOP                    VALUE
COMPLETED                      2011-10-09 08:33:00     2011-10-09 08:33:00     1555.208
PL/SQL 过程已成功完成。
This entry was posted in ORACLE and tagged , , . Bookmark the permalink.

Leave a Reply

Your email address will not be published. Required fields are marked *