Oracle 11g direct path read 等待事件的理解

在Oracle 11g中，全表扫描可能使用direct path read方式，绕过buffer cache，这样的全表扫描就是物理读了。 在10g中，都是通过gc buffer来读的，所以不存在direct path read的问题。

　　direct path read较高的可能原因有：

　　1. 大量的磁盘排序操作，order by, group by, union, distinct, rollup, 无法在PGA中完成排序，需要利用temp表空间进行排序。 当从临时表空间中读取排序结果时，会产生direct path read.

　　2. 大量的Hash Join操作，利用temp表空间保存hash区。

　　3. SQL语句的并行处理

　　4. 大表的全表扫描，在中，全表扫描的算法有新的变化，根据表的大小、高速缓存的大小等信息，决定是否绕过SGA直接从磁盘读Oracle11g取数据。而10g则是全部通过高速缓存读取数据，称为table scan(large)。11g认为大表全表时使用直接路径读，可能比10g中的数据文件散列读(db file scattered reads)速度更快，使用的latch也更少。

　　大量的direct path read等待时间最可能是一个应用程序问题。 direct path read事件由SQL语句驱动，这些SQL语句执行来自临时的或常规的表空间的直接读取操作。 当输入的内容大于PGA中的工作区域时，带有需要排序的函数的SQL语句将排序结果写入到临时表空间中，临时表空间中的排序顺序串随后被合并，用于提供最终的结果。读取排序结果时，Oracle会话在direct path read等待事件上等待。DB_FILE_DIRECT_IO_COUNT初始化参数可能影响direct path read的性能。

　　一个隐含参数：

　　_serial_direct_read = false 禁用direct path read

　　_serial_direct_read = true 启用direct path read

　　alter sytem set "_serial_direct_read"=never scope=both sid='*'; 可以显着减少 direct path read

 

本案例：由于没有走索引，导致全表扫描，数据库event状态是direct path read

-- 查看sql session 执行情况

SELECT S.SID,S.LAST_CALL_ET,
(SELECT ST.VALUE FROM V$SESSTAT ST WHERE ST.SID = S.SID AND ST.STATISTIC# = TO_NUMBER('38') ) CRITERIA_VALUE,
S.BLOCKING_SESSION,S.BLOCKING_INSTANCE,S.BLOCKING_SESSION_STATUS,
(SELECT CASE WHEN NOT S.BLOCKING_SESSION IS NULL THEN 'kill -9 '|| GP.SPID ELSE NULL END FROM GV$PROCESS GP,GV$SESSION GS WHERE GS.PADDR = GP.ADDR AND GS.SID=S.BLOCKING_SESSION AND GS.INST_ID=GP.INST_ID ) KILL_BLOCK_PROC,
(SELECT CASE WHEN NOT S.BLOCKING_SESSION IS NULL THEN 'ALTER SYSTEM KILL SESSION '''|| GS.SID || ',' || GS.SERIAL# ||''' IMMEDIATE;' ELSE NULL END FROM GV$SESSION GS WHERE GS.SID=S.BLOCKING_SESSION AND GS.SID=S.BLOCKING_SESSION AND GS.INST_ID=S.BLOCKING_INSTANCE ) KILL_BLOCK_SESSION,
S.CLIENT_IDENTIFIER,S.SERVER,S.USERNAME,S.MACHINE,S.PROGRAM,S.MODULE,S.ACTION,
(SELECT AC.NAME 
FROM ( SELECT ACTION, NAME FROM AUDIT_ACTIONS UNION SELECT 189, 'MERGE' FROM DUAL ) AC WHERE AC.ACTION = S.COMMAND + CASE WHEN S.COMMAND < 0 THEN 256 ELSE 0 END) COMMAND,
S.STATUS,S.LOCKWAIT,S.EVENT,S.STATE,S.WAIT_CLASS,S.WAIT_CLASS#,S.WAIT_TIME,T.EXECUTIONS,T.OPTIMIZER_MODE,T.OPTIMIZER_COST,T.CPU_TIME,T.DISK_READS,T.BUFFER_GETS,T.SORTS,T.SHARABLE_MEM,
T.PERSISTENT_MEM,T.RUNTIME_MEM,T.ROWS_PROCESSED,T.COMMAND_TYPE,O.OBJECT_ID,O.OBJECT_NAME,F.FILE_ID,F.FILE_NAME,F.TABLESPACE_NAME,
S.ROW_WAIT_BLOCK#,S.ROW_WAIT_ROW#,S.LOGON_TIME,T.SQL_TEXT,T.SQL_FULLTEXT,
'SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('''||T.SQL_ID||''','||T.CHILD_NUMBER||'));' PLAN,
'ALTER SYSTEM KILL SESSION ''' || S.SID || ',' || S.SERIAL# || ''' POST_TRANSACTION IMMEDIATE;' KILL_SESSION,
'SELECT SQL_TEXT,SQL_FULLTEXT,LAST_ACTIVE_TIME,DBMS_SQLTUNE.EXTRACT_BINDS(BIND_DATA) BIND FROM V$SQL WHERE SQL_ID='''||S.SQL_ID||''' ORDER BY LAST_ACTIVE_TIME DESC;' BIND
FROM V$SESSION S,V$SQL T,DBA_OBJECTS O,DBA_DATA_FILES F
WHERE S.SQL_HASH_VALUE = T.HASH_VALUE AND S.STATUS = 'ACTIVE' AND F.FILE_ID=ROW_WAIT_FILE# AND S.ROW_WAIT_OBJ#=O.OBJECT_ID 
ORDER BY LAST_CALL_ET DESC;