【性能调整】等待事件(六) direct path read&write

Direct path read/write相关的等待事件：

版本：10.2.0.4

SQL> select a.EVENT#,

  2         a.NAME name,

  3         a.PARAMETER1 p1,

  4         a.PARAMETER2 p2,

  5         a.PARAMETER3 p3

  6    from v$event_name a

  7   where a.NAME like '%direct path%';

 

    EVENT# NAME                                     P1              P2              P3

---------- ---------------------------------------- --------------- --------------- ---------------

       163 direct path read                         file number     first dba       block cnt

       164 direct path read temp                    file number     first dba       block cnt

       165 direct path write                        file number     first dba       block cnt

       166 direct path write temp                   file number     first dba       block cnt

 

4 rows selected.

 

SQL>

 

版本9.2.0.8：

SQL> select a.EVENT#,

  2         a.NAME name,

  3         a.PARAMETER1 p1,

  4         a.PARAMETER2 p2,

  5         a.PARAMETER3 p3

  6    from v$event_name a

  7   where a.NAME like '%direct path%';

 

    EVENT# NAME                                     P1              P2              P3

---------- ---------------------------------------- --------------- --------------- ---------------

       235 direct path read                         file number     first dba       block cnt

       236 direct path write                        file number     first dba       block cnt

       237 direct path read (lob)                   file number     first dba       block cnt

       238 direct path write (lob)                  file number     first dba       block cnt

 

direct path read

当oracle将数据块直接读入session的PGA中，而不是读入SGA的buffer cache中时发生direct path read。直接读可以在同步io或者异步io模式发生，这取决于硬件平台和DISK_ASYNCH_IO初始化参数的值。直接读io通常用在访问磁盘上的临时段时发生，这些操作包括排序，并行查询，hash joins：

排序操作（order by，group by，union，distinct，rollup，merge sort join等）时，由于pga中的SORT_AREA_SIZE空间不足，需要使用临时表空间来保存中间结果，当从临时表空间读入排序结果时，产生direct path read等待。

使用hash join的语句，临时表存放的hash分区，当需要匹配的时候，直接读入pga中来匹配行，oracle会话在direct path read等待。

使用并行扫描的SQL语句也会影响系统范围的direct path read等待事件。在并行执行过程中，direct path read等待事件与从属查询有关，而与父查询无关，运行父查询的会话基本上会在PX Deq:Execute Reply上等待，从属查询会产生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或者PGA_AGGREGATE_TARGET的设置是不是偏小。

下面的语句用来查询由并行导致direct path read的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;

 

参数说明：

SQL> select a.PARAMETER1 p1, a.PARAMETER2 p2, a.PARAMETER3 p3

  2    from v$event_name a

  3   where a.name = 'direct path read';

 

P1              P2              P3

--------------- --------------- ---------------

file number     first dba       block cnt

 

1 row selected.

 

SQL>

P1:     文件号

P2:     读取的起始block号

P3:     读取的block数量

等待时间：无超时，直到io请求完成

 

direct path write

这个事件刚好和前面的direct path read相反，除了上面的情况以外，在使用直接路径装载数据或者使用insert append的时候发生。

当使用异步io的时候，direct path write事件的等待和等待的时间可能会不准确。

从8.1.7开始direct path write(lob)为独立的等待事件。

参数与等待时间和direct path read的参数也类似。

可以大致看看这个现象（如果有需要可以做个10046追踪）：

在session 130：

SQL> create table t_test_direct as

  2  select * from dba_objects union all

  3  select * from dba_objects union all

  4  select * from dba_objects;

 

Table created

 

SQL> create table t_test_direct1 as select * from t_test_direct a where 1=2;

 

Table created

 

SQL> insert /*+ append */ into t_test_direct1 select * from t_test_direct;

 

165897 rows inserted

 

SQL>

 

在另外一个session，在session 130执行过程中大致查看session执行过程中的wait：

SQL> select a.EVENT,a.P1,a.p2,a.p3 from v$session_wait a where a.SID = 130;

 

EVENT                                                                    P1         P2         P3

---------------------------------------------------------------- ---------- ---------- ----------

db file scattered read                                                    1      71658         16

 

SQL> select a.EVENT,a.P1,a.p2,a.p3 from v$session_wait a where a.SID = 130;

 

EVENT                                                                    P1         P2         P3

---------------------------------------------------------------- ---------- ---------- ----------

Data file init write                                             4294967295         32 2147483647

 

SQL> select a.EVENT,a.P1,a.p2,a.p3 from v$session_wait a where a.SID = 130;

 

EVENT                                                                    P1         P2         P3

---------------------------------------------------------------- ---------- ---------- ----------

direct path write                                                         1      74976          7

 

这样看起来就比较明显了。

 

 

 

 

 

 

 

 

 

 

 

 

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

转载于:http://blog.itpub.net/16179598/viewspace-675816/