转载：http://www.dbtan.com/2010/04/direct-path-readwrite.html
direct path read/write （直接路径读／写）：
直接路径读（direct path read）通常发生在Oracle直接读数据到进程PGA时，这个读取不需要经过SGA。直接路径读等待事件的3个参数分别是file number（指绝对文件号）、first dba、block cnt数量。在Oracle 10g/11g中，这个等待事件被归于User I/O一类。
db file sequential read、db file scattered read、direct path read是常见的集中数据读方式，下图简要描述了这3种方式的读取示意。
这类读取通常在以下情况被使用：
·磁盘排序IO操作；
·并行查询从属进程；
·预读操作。
最为常见的是第一种情况。在DSS系统中，存在大量的direct path read是很正常的，但是在OLTP系统中，通常显著的直接路径读（direct path read）都意味着系统应用存在问题，从而导致大量的磁盘排序读取操作。
直接路径写（direct paht write）通常发生在Oracle直接从PGA写数据到数据文件或临时文件，这个写操作可以绕过SGA。直接路径写等待事件的3个参数分别是：file number（指绝对文件号）、first dba和block cnt数量，在Oracle 10g/11g中，这个等待事件同direct path read一样被归于User I/O一类。
这类写入操作通常在以下情况被使用：
·直接路径加载；
·并行DML操作；
·磁盘排序；
·对未缓存的“LOB”段的写入，随后会记录为direct path write(lob)等待。
最为常见的直接路径写，多数因为磁盘排序导致。对于这一写入等待，我们应该找到I/O操作最为频繁的数据文件（如果有过多的排序操作，很有可能就是临时文件），分散负载，加快其写入操作。
1. 磁盘排序诊断：
如果系统存在过多的磁盘排序，会导致临时表空间操作频繁，对于这种情况，可以考虑为不同用户分配不同的临时表空间，使用多个临时文件，写入不同磁盘或者裸设备，从而降低竞争，提高性能；对于Oracle 8i的数据库，应该考虑使用本地管理（Local）的临时表空间，而不是字典（dictionary）管理。
对于这种情况，在Oracle 9i之前，可以适当增加sort_area_size的大小；从Oracle 9i开始，可以适当增大pga_aggregate_target，以缩减磁盘排序对于磁盘的写入，从而提高系统及应用响应。但是通常应该及时检查应用，确认是否因为应用问题导致了过度排序，从而根本上解决问题。
2. 并行查询导致性能问题：
有时候在应用系统中，不正确的使用并行查询也会导致应用问题。Statspack的Top 5时间事件输出显示direct path read消耗了较高的等待时，而内存排序率很高甚至是100%（In-memory Sort %:100.00）显然这里的Direct Path Read并不是由于排序引发的，注意到另外一个等待事件（KJC: Wait for msg sends to complete）和并行有关，所以初步判断这里的direct path read可能和并行有关。
注：在Statspack的报告中，存在一个性能指标，称为内存排序率（In-memory Sort Ratio），用于衡量系统的排序操作，这个指标就是由两个统计信息sorts (disk)和sorts (memory)得出：
In-memory Sort Ratio = sorts (memory) / [sorts (disk) + sorts (memory)]
进一步检查Statspack报告中的SQL部分，发现大量并行查询改写出来的SQL，这些SQL通过内部提示（Hints）固化其执行路径。
在很多情况下，并行也许并不是最好的选择，如果表并不大，并行反而会降低其执行速度。
通过查询dba_tables字典表可以获得degree并行度的记录，并行度大于1的数据表在查询时会启用并行，但是注意事实还会有所不同，degree字段的类型及长度是VARCHAR2(10)。所以注意，当使用类似查询时，可能无法获得返回值：
sys@CCDB> select table_name from dba_tables where degree = '1' or degree = 'DEFAULT';
no rows selected
我们看一下degree以及instances的记录方式：
sys@CCDB> select degree,length(degree) from dba_tables group by degree;
DEGREE LENGTH(DEGREE)
--------------- --------------
1 10
sys@CCDB> select instances,length(instances) from dba_tables group by instances;
INSTANCES LENGTH(INSTANCES)
--------------- -----------------
1 10
0 10
degreee和instances实际上记录了10个字符，左端用空格补齐。在dba_tables的创建语句中，可以找到根本原因，以下是这两个字段的定义来源：
lpad(decode(t.degree, 32767, 'DEFAULT', nvl(t.degree,1)),10),
lpad(decode(t.instances, 32767, 'DEFAULT', nvl(t.instances,1)),10),
而需要注意的是，如果degree设置为DEFAULT，则默认数据库会对该表启用并行。
最后找到相关的SQL，从AUTOTRACE可以看到这些SQL的执行计划，查看涉及数据表的并行度，注意是否有被设置为DEFAULT的表。
将表的并行度修改为1后，问题得以解决：
alter table <table_name> parallel 1;
这个问题给我们的启示是：并行并不总是能够到来性能提升。
3. 磁盘排序与临时文件：
在Oracle 10g/11g中，为了区分特定的对于临时文件的直接读写操作，Oracle对direct path read/write进行了分离，将这类操作分列出来：
sys@CCDB> select event#,name,wait_class
2 from v$event_name
3 where name like 'direct%';
EVENT# NAME WAIT_CLASS
---------- ------------------------- ---------------
177 direct path read User I/O
178 direct path read temp User I/O
179 direct path write User I/O
180 direct path write temp User I/O
可以看到，现在的direct path read/write temp就是单指对于临时文件的直接读写操作。结合Oracle 10g的一些特性，来进一步研究一下直接路径读／写与临时文件。
首先在一个session中执行一个能够引发磁盘排序的查询：
tq@CCDB> select sid from v$mystat where rownum <2;
SID
----------
1066
tq@CCDB> select a.table_name,b.object_name,b.object_type
2 from t1 a,t2 b
3 where a.table_name = b.object_name
4 order by b.object_name,b.object_type;
在另外sessoin查询相应等待事件：
tq@CCDB> select event,p1text,p1,p2text,p2,p3text,p3
2 from v$session_wait_history
3 where sid = 1066;
EVENT P1TEXT P1 P2TEXT P2 P3TEXT P3
------------------------ ------------- ------- ------------ ------- ------------ -------
direct path read temp file number 201 first dba 313512 block cnt 31
direct path read temp file number 201 first dba 313481 block cnt 31
direct path read temp file number 201 first dba 386887 block cnt 31
direct path read temp file number 201 first dba 317736 block cnt 31
direct path read temp file number 201 first dba 317193 block cnt 31
direct path read temp file number 201 first dba 316646 block cnt 31
direct path read temp file number 201 first dba 316134 block cnt 31
direct path read temp file number 201 first dba 315622 block cnt 31
direct path read temp file number 201 first dba 315079 block cnt 31
direct path read temp file number 201 first dba 314567 block cnt 31
10 rows selected.
从以上输出可以看到最近10次等待，direct path read temp就是这个查询引起的磁盘排序。注意这里的file number为201。而实际上，通过v$tempfile来查询，临时文件的文件号仅为1：
tq@CCDB> select file#,name from v$tempfile;
FILE# NAME
---------- -----------------------------------------
1 /oracle/oradata/ccdb/ccdb/temp01.dbf
如果通过10046事件跟踪，也可以获得类似的结果：
WAIT #3: nam='direct path write temp' ela= 1 file number=201 first dba=437862 block cnt=31 obj#=112141 tim=1270780
330976998
WAIT #3: nam='direct path write temp' ela= 1 file number=201 first dba=437416 block cnt=31 obj#=112141 tim=1270780
330977070
WAIT #3: nam='direct path read temp' ela= 7 file number=201 first dba=438471 block cnt=31 obj#=112141 tim=12707803
30982214
WAIT #3: nam='direct path read temp' ela= 4 file number=201 first dba=438502 block cnt=31 obj#=112141 tim=12707803
30983765
WAIT #3: nam='direct path read temp' ela= 8 file number=201 first dba=387015 block cnt=31 obj#=112141 tim=12707803
30993872
在Oracle文档中，file#被定义为绝对文件号（The Absolute File Number）。这里的原因何在呢？研究这个问题要先研究一下v$tempseg_usage这个视图，可以从这个视图出发动手研究一下这个对象究竟来自何方。
查询dba_objects视图，发现v$tempseg_usage原来是一个同义词。
sys@CCDB> select object_type from dba_objects where object_name = 'V$TEMPSEG_USAGE';
OBJECT_TYPE
-------------------
SYNONYM
再追本溯源原来v$tempseg_usage是v_$sort_usage的同义词，也就是和v$sort_usage同源。从Oracle 9i开始，Oracle将v$sort_usage视图从文档中移除了，因为这个名称有所歧义，容易使人误解仅记录排序内容，所以v$tempseg_usage视图被引入，用于记录临时段的使用情况：
sys@CCDB> select * from dba_synonyms where synonym_name = 'V$TEMPSEG_USAGE';
OWNER SYNONYM_NAME TABLE_OWNER TABLE_NAME DB_LINK
---------- -------------------- --------------- --------------- ----------
PUBLIC V$TEMPSEG_USAGE SYS V_$SORT_USAGE
如果再进一步，可以看到这个视图的构建语句：
sys@CCDB> select view_definition from v$fixed_view_definition
2 where view_name = 'GV$SORT_USAGE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select x$ktsso.inst_id, username, username, ktssoses, ktssosno, prev_sql_addr, p
rev_hash_value, prev_sql_id, ktssotsn, decode(ktssocnt, 0, 'PERMANENT', 1, 'TEMP
ORARY'), decode(ktssosegt, 1, 'SORT', 2, 'HASH', 3, 'DATA', 4, 'INDEX', 5, 'LOB_
DATA', 6, 'LOB_INDEX' , 'UNDEFINED'), ktssofno, ktssobno, ktssoexts, ktssoblks,
ktssorfno from x$ktsso, v$session where ktssoses = v$session.saddr and ktssosno
= v$session.serial#
格式化一下，v$sort_usage的创建语句如下：
SELECT x$ktsso.inst_id,username,username,ktssoses,ktssosno,
prev_sql_addr,prev_hash_value,prev_sql_id,ktssotsn,
DECODE (ktssocnt,
0,'PERMANENT',
1,'TEMPORARY'),
DECODE (ktssosegt,
1, 'SORT',
2, 'HASH',
3, 'DATA',
4, 'INDEX',
5, 'LOB_DATA',
6, 'LOB_INDEX',
'UNDEFINED'),
ktssofno,ktssobno,
ktssoexts,ktssoblks,ktssorfno
FROM x$ktsso, v$session
WHERE ktssoses = v$session.saddr AND ktssosno = v$session.serial#;
注意到在Oracle文档中对v$sort_usage字段SEGFILE#的定义为：
SEGFILE# NUMBER File number of initial extent
在视图中，这个字段来自x$ktsso.ktssofno，也就是说这个字段实际上代表的是绝对文件号。那么这个绝对文件号如何与临时文件关联呢？能否与v$tempfile中的file#字段关联呢？
再来看一下v$tempfile的来源，v$tempfile由如下语句创建：
sys@CCDB> select view_definition from v$fixed_view_definition
2 where view_name = 'GV$TEMPFILE';
VIEW_DEFINITION
--------------------------------------------------------------------------------
select tf.inst_id, tf.tfnum, to_number(tf.tfcrc_scn), to_date(tf.tfcrc_tim,'MM/D
D/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'), tf.tftsn, tf.tfrfn, decode(bitand(tf
.tfsta, 2),0,'OFFLINE',2,'ONLINE','UNKNOWN'), decode(bitand(tf.tfsta, 12), 0,'DI
SABLED',4, 'READ ONLY', 12, 'READ WRITE',
'UNKNOWN'), fh.fhtmpfsz*tf.tfbsz, fh.fhtmpfsz, tf.tf
csz*tf.tfbsz,tf.tfbsz, fn.fnnam from x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh whe
re fn.fnfno=tf.tfnum and fn.fnfno=fh.htmpxfil and tf.tffnh=fn.fnnum and tf.tfdu
p!=0 and bitand(tf.tfsta, 32) <> 32 and fn.fntyp=7 and fn.fnnam is not null
格式化v$tempfile如下：
SELECT tf.inst_id,tf.tfnum,TO_NUMBER (tf.tfcrc_scn),
TO_DATE (tf.tfcrc_tim,'MM/DD/RR HH24:MI:SS','NLS_CALENDAR=Gregorian'),
tf.tftsn,tf.tfrfn,
DECODE (BITAND (tf.tfsta, 2), 0, 'OFFLINE', 2, 'ONLINE', 'UNKNOWN'),
DECODE (BITAND (tf.tfsta, 12),
0, 'DISABLED',
4, 'READ ONLY',
12, 'READ WRITE',
'UNKNOWN'),
fh.fhtmpfsz * tf.tfbsz,fh.fhtmpfsz,tf.tfcsz * tf.tfbsz,tf.tfbsz,fn.fnnam
FROM x$kcctf tf, x$kccfn fn, x$kcvfhtmp fh
WHERE fn.fnfno = tf.tfnum
AND fn.fnfno = fh.htmpxfil
AND tf.tffnh = fn.fnnum
AND tf.tfdup != 0
AND BITAND (tf.tfsta, 32) <> 32
AND fn.fntyp = 7
AND fn.fnnam IS NOT NULL;
考察x$kcctf底层表，注意到TFAFN（Temp File Absolute File Number）在这里存在：
sys@CCDB> desc x$kcctf
Name Null? Type
---------------- -------- ----------------
ADDR RAW(8)
INDX NUMBER
INST_ID NUMBER
TFNUM NUMBER
TFAFN NUMBER
TFCSZ NUMBER
TFBSZ NUMBER
TFSTA NUMBER
TFCRC_SCN VARCHAR2(16)
TFCRC_TIM VARCHAR2(20)
TFFNH NUMBER
TFFNT NUMBER
TFDUP NUMBER
TFTSN NUMBER
TFTSI NUMBER
TFRFN NUMBER
TFPFT NUMBER
TFMSZ NUMBER
TFNSZ NUMBER
而这个字段在构建v$tempfile时并未出现，所以不能通过v$sort_usage和v$tempfile直接关联绝对文件号。可以简单构建一个排序段使用，然后来继续研究一下：
sys@CCDB> select username,segtype,segfile#,segblk#,extents,segrfno# from v$sort_usage;
USERNAME SEGTYPE SEGFILE# SEGBLK# EXTENTS SEGRFNO#
------------ --------- ---------- ---------- ---------- ----------
SYS LOB_DATA 201 340361 1 1
看到这里的SEGFILE#=201，而在v$tempfile是找不到这个信息的：
sys@CCDB> select file#,rfile#,ts#,status,blocks from v$tempfile;
FILE# RFILE# TS# STATUS BLOCKS
---------- ---------- ---------- ------- ----------
1 1 3 ONLINE 443520
但是可以从x$kcctf中获得这些信息，v$tempfile.file#实际上来自x$kcctf.tfnum，是临时文件的文件号；而绝对文件号是x$kcctf.tfafn，这个字段才可以与v$sort_usage.segfile#关联：
sys@CCDB> select indx,tfnum,tfafn,tfcsz from x$kcctf;
INDX TFNUM TFAFN TFCSZ
---------- ---------- ---------- ----------
0 1 201 2560
再进一步可以知道，实际上，为了分离临时文件号和数据文件号，Oracle对临时文件的编号以db_files为起点，所以临时文件的绝对文件号应该等于db_files+file#。
db_files参数的缺省值为200：
sys@CCDB> show parameter db_files
NAME TYPE VALUE
------------- -------------- -----------
db_files integer 200
sys@CCDB> select file#,name from v$tempfile;
FILE# NAME
---------- ---------------------------------------------
1 /oracle/oradata/ccdb/ccdb/temp01.dbf
所以在Oracle文档中v$tempfile.file#被定义为The absolute file number是不确切的。
- The End -