直接路径读（direct path read）通常发生在Oracle直接读数据到进程PGA时，这个读取不需要经过SGA。直接路径读等待事件的3个参数分别是file number（指绝对文件号）、first dba、block cnt数量。在Oracle 10g/11g中，这个等待事件被归于User I/O一类。如何模拟出来呢？把PGA设置小一点，弄一张大表做排序。
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> alter system set pga_aggregate_target = 1M;
SQL> select count(1) from TEST_DPR;
COUNT(1)
----------
1085999
SQL> select distinct sid from v$mystat;
SID
----------
18
SQL> alter session set tracefile_identifier = '11111111';
SQL> set autotrace traceonly;
SQL> alter session set events '10046 trace name context forever ,level 12' ;
SQL> select * from TEST_DPR order by record_content;
session2:
SQL> select sid,event from v$session_wait w where w.SID = 18;
SID EVENT
---------- ----------------------------------------------------------------
18 SQL*Net message from client
SQL> /
SID EVENT
---------- ----------------------------------------------------------------
18 direct path read
SQL> /
SID EVENT
---------- ----------------------------------------------------------------
18 direct path write temp
SQL> /
SID EVENT
---------- ----------------------------------------------------------------
18 direct path write temp
session3:
E:\oracle11g\guogang\diag\rdbms\orcl\orcl\trace>tkprof orcl_ora_4616_11111111.trc 111.txt sys=no aggregate=no
select *
from
TEST_DPR order by record_content
call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.00 0.00 0 0 0 0
Execute 1 0.00 0.00 0 0 0 0
Fetch 72401 6.04 18.20 60539 30350 6 1085999
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 72403 6.04 18.20 60539 30350 6 1085999
Misses in library cache during parse: 0
Optimizer mode: ALL_ROWS
Parsing user id: 84
Rows Row Source Operation
------- ---------------------------------------------------
1085999 SORT ORDER BY (cr=30350 pr=60539 pw=30194 time=2977534 us cost=643568 size=3046085760 card=1312968)
1085999 TABLE ACCESS FULL TEST_DPR (cr=30350 pr=30345 pw=0 time=1711998 us cost=8310 size=3046085760 card=1312968)
Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
SQL*Net message to client 72401 0.00 0.04
Disk file operations I/O 2 0.00 0.00
direct path read 241 0.14 2.39
direct path write temp 966 0.15 5.32
asynch descriptor resize 3 0.00 0.00
direct path read temp 1071 0.06 3.93
SQL*Net message from client 72401 0.00 31.69
SQL*Net more data to client 64 0.00 0.00
********************************************************************************