oracle dbms 存放位置,系统统计信息的保存位置

虽然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

2FROM V$SESSION S, V$PROCESS P

3WHERE S.PADDR = P.ADDR

4AND SID IN

5(SELECT SID

6FROM V$MYSTAT

7WHERE 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 ct=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 ct=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 ct=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 ct=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

acdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00

acflg=13 fl2=206001 frm=01 csi=852 siz=32 ff=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 ct=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

acdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00

acflg=13 fl2=206001 frm=01 csi=852 siz=32 ff=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 ct=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

acdty=01 mxl=32(30) mxlc=00 mal=00 scl=00 pre=00

acflg=13 fl2=206001 frm=01 csi=852 siz=32 ff=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 ct=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 ct=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

acdty=123 mxl=4000(4000) mxlc=00 mal=00 scl=00 pre=00

acflg=00 fl2=1000000 frm=00 csi=00 siz=4000 ff=0

kxsbbbfp=056e555c bln=4000 avl=00 flg=15

Bind#1

acdty=02 mxl=22(22) mxlc=00 mal=00 scl=00 pre=00

acflg=01 fl2=1000000 frm=00 csi=00 siz=24 ff=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 ct=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$;

SNAMEPNAMEPVAL1 PVAL2

-------------------- --------------- ---------- ------------------------

SYSSTATS_INFOSTATUSCOMPLETED

SYSSTATS_INFODSTART09-10-2011 08:33

SYSSTATS_INFODSTOP09-10-2011 08:33

SYSSTATS_INFOFLAGS1

SYSSTATS_MAINCPUSPEEDNW1555.208

SYSSTATS_MAINIOSEEKTIM11.312

SYSSTATS_MAINIOTFRSPEED4096

SYSSTATS_MAINSREADTIM

SYSSTATS_MAINMREADTIM

SYSSTATS_MAINCPUSPEED

SYSSTATS_MAINMBRC

SYSSTATS_MAINMAXTHR

SYSSTATS_MAINSLAVETHR

已选择13行。

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

SQL> SET SERVEROUT ON SIZE 100000

SQL> DECLARE

2V_STATUS VARCHAR2(30);

3V_DSTART DATE;

4V_DSTOP DATE;

5V_PVALUE NUMBER;

6BEGIN

7DBMS_STATS.GET_SYSTEM_STATS(V_STATUS, V_DSTART, V_DSTOP, 'cpuspeednw',

V_PVALUE);

8DBMS_OUTPUT.PUT_LINE(RPAD('STATUS', 31) || RPAD('START', 24) ||

RPAD('STOP', 24) || 'VALUE');

9DBMS_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);

11END;

12/

STATUSSTARTSTOPVALUE

COMPLETED2011-10-09

08:33:00 2011-10-09 08:33:001555.208

PL/SQL过程已成功完成。