模拟direct path read 等待事件-CSDN博客

引起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这个关键字)，不知道并行查询会不会，有时间做个并行查询的等待事件观察下