【等待事件】User I/O类等待事件（2.7）--direct path read/write temp

【索引】Oracle之不可见索引和虚拟索引的比对

为了排序工作在临时区域读写时，等待direct path read temp、direct path write temp事件。oracle 9i为止是通过direct path read、direct path write等待观察的。在Oracle 10g/11g中，为了区分特定的对于临时文件的直接读写操作，Oracle对direct path read/write进行了分离，将这类操作分列出来：

SELECT A.*

FROM V$EVENT_NAME A

WHERE NAME IN ('direct path read temp', 'direct path write temp');可以看到，现在的direct path read/write temp就是单指对于临时文件的直接读写操作。排序段上的 direct path I/O是在需要排序的数据比排序所分配的PGA内存区大时发生的。因此在排序工作时若大量发生direct path read temp、direct path write temp等待，就可以通过追加分配内存区域而避免等待。

1、应用程序层

检查需要排序的sql语句是否已经最优化。不必要的排序操作会导致CPU浪费、PGA区域浪费、磁盘I/O浪费。从UNION和UNION ALL的性能差异上可以得知，只靠减少不必要的排序操作，也能解决许多问题。

2、oracle内存层

在进程上分配的工作区大小内一次性实现的排序称为One pass sort。与此相反的情况称为Multi pass sort。发生Multi pass sort时，排序工作过程中将排序结果读写到排序段（sort segment）区域，因此发生direct path read temp、direct path write temp等待。如果该等待大量发生，就可以适当提高pga_aggregate_target值，以此消除问题。

oracle在调优指南上推荐如下设定pga_aggregate_target值。

OLTP：pga_aggregate_target=(total_mem * 80%) * 20%

OLAP：pga_aggregate_target=(total_mem * 80%) * 50%

上述的意思是，假设OS本身使用20%左右的内存，OLTP系统上使用剩余内存的20%左右，OLAP系统因为排序工作较多，所以使用剩余内存的50%左右。

结合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是不确切的。

更多相关文章

随机推荐

【等待事件】User I/O类 等待事件（2.7）--direct path read/write temp

更多相关文章

随机推荐

【等待事件】User I/O类等待事件（2.7）--direct path read/write temp