direct path read[转]

直接路径读（direct path read）通常发生在直接读数据到进程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通过内部提示（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 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 的一些特性，来进一步研究一下直接路径读／写与临时文件。
首先在一个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$kcctftf, 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# fromv$sort_usage;
USERNAME     SEGTYPE    SEGFILE#    SEGBLK#    EXTENTS   SEGRFNO#
------------ --------- ---------- ---------- ---------- ----------
SYS          LOB_DATA        201     340361          1          1

看到这里的SEGFILE#=201，而在v$tempfile是找不到这个信息的：

sys@CCDB> selectfile#,rfile#,ts#,status,blocks fromv$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 fromx$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是不确切的。

+++++++++++++++++++++++++++++++++++++++++++++++++++++++

select column_name,cache,logging from dba_lobs where owner='USERNAME' and table_name='TBL_NAME';

 

SET SERVEROUTPUT ON

select dbms_metadata.get_ddl('TABLE','TBL_NAME','USERNAME') FROM DUAL;

 

==将clob字段cache

alter table USERNAME.TABLE_NAME modify lob(xmlrecord.xmldata) (storage (buffer_pool DEFAULT) cache);