模拟direct path read 等待事件

 引起direct path read这个等待事件 主要有两个原因，一个是磁盘排序，另外一个是并行查询。这里先做一个磁盘排序引起的

direct path read.另外磁盘排序也会记录direct path write 等待事件。

下面是实验步骤:

SQL> select * from v$version;

BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - Prod
PL/SQL Release 10.2.0.3.0 - Production

SQL> alter system set pga_aggregate_target=10m;

系统已更改。
session 1:
select a.table_name,a.comments from dict a,dict b order by a.table_name;

session 2中:
select a.table_name,a.comments from dict a,dict b order by a.table_name;

session 3中:
select a.table_name,a.comments from dict a,dict b order by a.table_name;

SQL> select sid,username, event,p1,p2,p3 from v$session where username is not null;

       SID USERNAME   EVENT                             P1         P2         P3
---------- ---------- ------------------------- ---------- ---------- ----------
       145 ROBINSON   direct path read temp            201      14266          7
       146 ROBINSON   direct path read temp            201      23497          1
       147 ROBINSON   direct path read temp            201      11082          7
       158 SYS        SQL*Net message to client 1111838976          1          0

SQL> SELECT /*+ rule */ DISTINCT a.SID,TABLESPACE, b.sql_text
  2  FROM v$session a, v$sql b, v$sort_usage c
  3  WHERE a.sql_address = b.address AND a.saddr = c.session_addr;

       SID TABLESPACE                      SQL_TEXT
---------- ------------------------------- ---------------------------------------------------------------------------
       145 TEMP                            select a.table_name,a.comments from dict a,dict b order by a.table_name
       146 TEMP                            select a.table_name,a.comments from dict a,dict b order by a.table_name
       147 TEMP                            select a.table_name,a.comments from dict a,dict b order by a.table_name

 

下面的脚本都可以查询到引起磁盘排序的SQL

SELECT /*+ rule */ DISTINCT a.SID, a.process, a.serial#,
TO_CHAR (a.logon_time, 'YYYYMMDD HH24:MI:SS') LOGON, a.osuser,TABLESPACE, b.sql_text
FROM v$session a, v$sql b, v$sort_usage c
WHERE a.sql_address = b.address AND a.saddr = c.session_addr;

 

SELECT a.SID,TABLESPACE, b.sql_text
FROM v$session a, v$sql b, v$sort_usage c
WHERE a.sql_address = b.address AND a.saddr = c.session_addr;

 

select a.sid,a.username,a.program,c.sql_text from v$session a,v$tempseg_usage b,v$sql c where a.saddr=b.session_addr and a.sql_address=c.address ;

 

可以看到引起磁盘排序的等待事件后面有个temp(不知道9i中有没有temp这个关键字)，不知道并行查询会不会，有时间做个并行查询的等待事件观察下