数据被直接读取到PGA内存中时,发生的等待,如排序由于内存不足,被写到磁盘上,然后重新读取。为了在v$session_wait中看到,在公司的环境上找了一个大表,进行测试,建大表的步骤省略。
session1:
SQL> select * from v$version;
BANNER
--------------------------------------------------------------------------------
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - Production
PL/SQL Release 11.2.0.1.0 - Production
CORE 11.2.0.1.0 Production
TNS for 32-bit Windows: Version 11.2.0.1.0 - Production
NLSRTL Version 11.2.0.1.0 - Production
SQL> select distinct sid from v$mystat;
SID
----------
139
SQL> alter system set pga_aggregate_target = 1M;
SQL> alter session set tracefile_identifier = '11111111';
SQL> set autotrace traceonly;
SQL> alter session set events '10046 trace name context forever ,level 12' ;
SQL> create table TEST_DPW as select * from TEST_DPR;
session2:
SQL> select sid,event from v$session_wait w where w.SID = 139;
SID EVENT
---------- ----------------------------------------------------------------
139 SQL*Net message from client
SQL> /
SID EVENT
---------- ----------------------------------------------------------------
139 direct path write
SQL> /
SID EVENT
---------- ----------------------------------------------------------------
139 direct path write
session3:
create table TEST_DPW as select * from TEST_DPR
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.07 0 1 0 0
Execute 1 1.26 17.32 30345 30646 34068 1085999
Fetch 0 0.00 0.00 0 0 0 0
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 2 1.26 17.40 30345 30647 34068 1085999
Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
0 LOAD AS SELECT (cr=31506 pr=30345 pw=30345 time=0 us)
1085999 TABLE ACCESS FULL TEST_DPR (cr=30350 pr=30345 pw=0 time=617937 us cost=8310 size=3046085760 card=1312968)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
Disk file operations I/O 15 0.13 0.60
direct path read 62 0.16 2.88
direct path write 679 0.18 7.65
control file sequential read 84 0.07 0.58
db file sequential read 8 0.00 0.03
Data file init write 4 0.00 0.00
db file single write 4 0.00 0.00
control file parallel write 12 0.00 0.00
rdbms ipc reply 4 0.10 0.13
buffer busy waits 3 0.72 1.91
asynch descriptor resize 2 0.00 0.00
log file sync 1 0.00 0.00
SQL*Net message to client 1 0.00 0.00
SQL*Net message from client 1 21.20 21.20
********************************************************************************