oracle等待事件-direct path read/write

一、direct path read

1、与直接读取相关联的等待事件。当ORACLE将数据块直接读入会话的PGA（进程全局区）中，同时绕过SGA（系统全局区）。PGA中的数据并不和其他的会话共享。即表明，读入的这部分数据该会话独自使用，不放于共享的SGA中。

2、在排序操作(order by/group by/union/distinct/rollup/合并连接)时，由于PGA中的SORT_AREA_SIZE空间不足，造成需要使用临时表空间来保存中间结果(这个位置将导致direct path write temp)，当从临时表空间读入排序结果时，产生direct path read等待事件。

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

4、使用并行扫描的SQL语句也会影响系统范围的direct path read等待事件。在并行执行过程中，direct path read等待事件与从属查询有关，而与父查询无关，运行父查询的会话基本上会在PX Deq:Execute Reply上等待，从属查询会产生direct path read等待事件。

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

 

6、该事件一般不可能显示为主要的瓶颈，但它实际上也许是就是祸首。由于ORACLE统计等待时间的方式会造成统计的时间量不准确(如：从属查询产生的时间无法进行统计)，所以对该事件不应该使用v$session_event视图中的total_wait或time_waited进行估计，应该使用v$sesstat视图中的直接读取操作次数(physical reads direct)进行判断：
select a.NAME,b.SID,b.VALUE,round((sysdate - c.LOGON_TIME) * 24) hours_connected from v$statname a, v$sesstat b, v$session c where b.SID = c.SID and a.STATISTIC# = b.STATISTIC# and b.VALUE > 0 and a.NAME = 'physical reads direct' order by b.VALUE

7、由direct path read事件产生的原因，我们需要判断该事件正在读取什么段(如：散列段、排序段、一般性的数据文件)，由此可判断产生该事件的原因是什么，可使用以下语句进行查询：
SELECT a.event,a.sid,c.sql_hash_value hash_vale,decode(d.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',NULL) AS segment_type,b.tablespace_name,b.file_name FROM v$session_wait a, dba_data_files b, v$session c, x$ktsso d WHERE c.saddr = d.ktssoses(+) AND c.serial# = d.ktssosno(+) AND d.inst_id(+) = userenv('instance') AND a.sid = c.sid AND a.p1 = b.file_id AND a.event = 'direct path read' UNION ALL SELECT a.event,a.sid,d.sql_hash_value hash_value,decode(e.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX',NULL) AS segment_type,b.tablespace_name,b.file_name FROM v$session_wait a,dba_temp_files b,v$parameter c,v$session d,x$ktsso e WHERE d.saddr = e.ktssoses(+) AND d.serial# = e.ktssosno(+) AND e.inst_id(+) = userenv('instance') AND a.sid = d.sid AND b.file_id = a.p1 - c.VALUE AND c.NAME = 'db_files' AND a.event = 'direct path read';
注：如果是从临时文件中读取排序段的会话，则表明SORT_AREA_SIZE或PGA_AGGREGATE_TARGET的设置是不是偏小。如果是从临时文件中读取HASH段的会话，则表明HASH_AREA_SIZE或PAG_AGGREGATE_TARGET的设置是不是偏小。

8、当direct path read等待事件是由于并行查询造成的(读取的是一般的数据文件而非临时文件)，父SQL语句的HASHVALUE与子SQL语句的HASHVALUE不同，可以通过以下SQL查询产生子SQL语句的父SQL语句：
SELECT decode(a.qcserial#, NULL, 'PARENT', 'CHILD') stmt_level,a.sid,a.serial#,b.username,b.osuser,b.sql_hash_value,b.sql_address,a.degree,a.req_degree FROM v$px_session a, v$session b WHERE a.sid = b.sid ORDER BY a.qcsid, stmt_level DESC

9、初始化参数db_file_direct_io_count用来设置直接读出和写入操作设置最大的IO缓冲区大小，因此能影响direct path read的性能，该参数在9i中被隐蔽，并改以字节数而不是块数为单位。
>> 使用10046第8层跟踪直接读取操作的ORACLE会话，其中P3参数表明块读取的数量。
>> 也可使用strace,truss追踪直接读取或直接写入操作的UNIX进程，从生成的TRACE文件可获得相应的直接IO大小。
>> 在第1层使用追踪事件10357，启动执行直接IO操作的会话的调试信息。

10、大量的direct path read等待事件最可能是一个应用程序的问题。

11、参数说明：
事件号：212
事件名：direct path read 
参数一：读取数据文件的绝对文件号码file number
参数二：起始块号first dba
参数三：要读取的块数block cnt
由参数P1与P2推得访问的数据对象：
select s.segment_name, s.partition_name from dba_extents s where <P2的值> between s.block_id and (s.block_id + s.blocks -1) and s.file_id = <P1的值>
注：
>> 1. 如果是Temp文件，则表示该会话正在读取它先前用direct path write操作所创建的临时段，查明使用的是什么类型的临时段，有助于了解会话所做的事情。
SELECT DISTINCT decode(t.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX','UNDEFINED') FROM sys.x$ktsso t WHERE t.inst_id = userenv('instance') AND t.kssoses = <当前session地址> AND t.ktssosno = <serial#>
>> 2. 如果是数据文件，则可能是并行查询从属操作在工作，通过P1值确定数据文件的名称：
select s.NAME from v$datafile s where s.FILE# = <P1的值> union all select a.name from v$tempfile a, v$parameter b where b.NAME = 'db_files' and a.FILE# + b.VALUE = <P1的值>

12、等待时间：无超时

二、direct path write(USER I/0类)
 1.直接路径加载；
2.并行DML操作；
·磁盘排序；
·对未缓存的“LOB”段的写入，随后会记录为direct path write(lob)等待。
4、在使用异步IO时，direct path write事件产生的等待时间不准确，所以通过v$sesstat视图来获得直接写入次数来评估该事件的影响情况：
SELECT a.NAME,b.sid,b.VALUE,round((SYSDATE - c.logon_time) * 24) hours_connected FROM v$statname a, v$sesstat b, v$session c WHERE a.statistic# = b.statistic# AND b.sid = c.sid AND b.VALUE > 0 AND a.NAME = 'physical writes direct'
5、参数说明：
事件号：213
事件名：direct path write 
参数一：要写入的绝对文件号file number，可发现所进行的操作性质(如：排序/并行操作)
参数二：起始块号first dba
参数三：块数block cnt，可发现直接写入IO的大小
由参数P1与P2推得访问的数据对象：
select s.segment_name, s.partition_name from dba_extents s where <P2的值> between s.block_id and (s.block_id + s.blocks -1) and s.file_id = <P1的值>
注：
>> 1. 如果是Temp文件，则表示该会话正在写入临时表空间，查明使用临时段的类型，有助于了解会话所做的事情。
SELECT DISTINCT decode(t.ktssosegt,1,'SORT',2,'HASH',3,'DATA',4,'INDEX',5,'LOB_DATA',6,'LOB_INDEX','UNDEFINED') FROM sys.x$ktsso t WHERE t.inst_id = userenv('instance') AND t.kssoses = <当前session地址> AND t.ktssosno = <serial#>
>> 2. 如果是数据文件，则可能正在执行一项直接路径加载操作，通过P1值确定数据文件的名称：
select s.NAME from v$datafile s where s.FILE# = <P1的值> union all select a.name from v$tempfile a, v$parameter b where b.NAME = 'db_files' and a.FILE# + b.VALUE = <P1的值>
6、等待时间：无超

 

三、查看历史记录

第一步：统计在某个时间段，各种阻塞情况，获取到event 事件对应的event id

select event_id, event, COUNT (*) cnt from dba_hist_active_sess_history where to_char(sample_time,'yyyy-MM-dd HH24:mi') > '2018-10-31 14:55' and to_char(sample_time,'yyyy-MM-dd HH24:mi') <= '2018-10-31 15:36' GROUP BY event_id, event ORDER BY 3 desc;

第二步：根据上面的event id 获取到sql 统计

SELECT sql_id, COUNT (*) cnt FROM dba_hist_active_sess_history WHERE to_char(sample_time,'yyyy-MM-dd HH24:mi') > '2018-10-31 10:20' and to_char(sample_time,'yyyy-MM-dd HH24:mi') <= '2018-10-31 10:56' and event_id = '38438084' GROUP BY sql_id HAVING COUNT (*) > 1 ORDER BY 2 desc;

第三步：根据上面获取的sql_id 去查看sql。

select * from v$sql where sql_id='41qrsh1t2wzdm' 

四、查当前各种事件

 

 

使用temp表空间的操作

1.临时表的操作

2.Hash Join

3.Sort-Merge joins

4.CREATE INDEX

5.ANALYZE

6.Select DISTINCT

7.ORDER BY

8.GROUP BY

9.UNION

10.INTERSECT

11.MINUS

12.etc.