Oracle----Bug 8226792 when exec statspack.snap

Oracle 10205:

select sid,event,p1,p2 from v$session_wait where event not like '%message%'

SID EVENT P1 P2
---------- -------------------------------------------------- ---------- ----------
5368 control file sequential read 0 92
5441 Streams AQ: qmn coordinator idle wait 0 0
5443 Streams AQ: qmn slave idle wait 0 0
5450 Streams AQ: waiting for time management or cleanup 0 0
tasks

5458 smon timer 300 0
5504 DIAG idle wait 1 1
5505 pmon timer 300 0

7 rows selected.

alter session set timed_statistics=true;
alter session set statistics_level=all;
alter session set max_dump_file_size=unlimited;
alter session set events '10046 trace name context forever,level 12';
alter session set events '1652 trace name errorstack level 3';
exec statspack.snap;

SQL> select tablespace_name,sum(bytes/1024/1024) from dba_temp_files group by tablespace_name;

TABLESPACE_NAME SUM(BYTES/1024/1024)
------------------------------ --------------------
TEMP 20450

SQL>
SQL> select tablespace_name,USED_BLOCKS*8192/1024/1024,FREE_BLOCKS*8192/1024/1024 from v$sort_segment
TABLESPACE_NAME USED_BLOCKS*8192/1024/1024 FREE_BLOCKS*8192/1024/1024
-------------------- -------------------------- --------------------------
TEMP 17344 2996

INSERT INTO STATS$FILESTATXS ( SNAP_ID , DBID , INSTANCE_NUMBER , TSNAME ,
FILENAME , PHYRDS , PHYWRTS , SINGLEBLKRDS , READTIM , WRITETIM ,
SINGLEBLKRDTIM , PHYBLKRD , PHYBLKWRT , WAIT_COUNT , TIME ) SELECT :B3 ,
:B2 , :B1 , TSNAME , FILENAME , PHYRDS , PHYWRTS , SINGLEBLKRDS , READTIM ,
WRITETIM , SINGLEBLKRDTIM , PHYBLKRD , PHYBLKWRT , WAIT_COUNT , TIME FROM
STATS$V$FILESTATXS

Rows Row Source Operation
------- ---------------------------------------------------
0 HASH JOIN (cr=0 pr=0 pw=0 time=12 us)
198419490 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=198456308 us)
19842 NESTED LOOPS (cr=0 pr=0 pw=0 time=84600130 us)
165009 MERGE JOIN CARTESIAN (cr=0 pr=0 pw=0 time=182094 us)
34 FIXED TABLE FULL X$KCCTS (cr=0 pr=0 pw=0 time=1325 us)
165009 BUFFER SORT (cr=0 pr=0 pw=0 time=181483 us)
5000 FIXED TABLE FULL X$KCFIO (cr=0 pr=0 pw=0 time=5011 us)
19842 FIXED TABLE FIXED INDEX X$KCCFE (ind:1) (cr=0 pr=0 pw=0 time=84140519 us)
198419490 BUFFER SORT (cr=0 pr=0 pw=0 time=138732 us)
10000 FIXED TABLE FULL X$KCBFWAIT (cr=0 pr=0 pw=0 time=7 us)
0 VIEW GV$DATAFILE (cr=0 pr=0 pw=0 time=0 us)
0 SORT ORDER BY (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 NESTED LOOPS (cr=0 pr=0 pw=0 time=0 us)
0 FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=0 us)
0 FIXED TABLE FIXED INDEX X$KCVFH (ind:1) (cr=0 pr=0 pw=0 time=0 us)
0 FIXED TABLE FIXED INDEX X$KCCFE (ind:1) (cr=0 pr=0 pw=0 time=0 us)
0 FIXED TABLE FULL X$KCCFN (cr=0 pr=0 pw=0 time=0 us)

Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
control file sequential read 350954 0.02 76.08

call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 916.38 1018.70 1 2 122 0
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 916.38 1018.70 1 2 122 0

Misses in library cache during parse: 1
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 38 (recursive depth: 1)

执行exec statspack.snap时，临时表空间被耗光，最后报错临时表空间不足

MetaLink上确认是bug 8226792

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/26078027/viewspace-1053189/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/26078027/viewspace-1053189/