direct path read temp等待如何解决

homeworld80

SQL> select * from v$session_wait where event = 'direct path read temp';

SID  SEQ#   EVENT                          P1TEXT        P1   P1RAW                      P2TEXT         P2          P2RAW           
----   -----      ----------------------         -----------      ---   ----------------             ---------        -------       ----------------
321 30451   direct path read temp  file number 201 00000000000000C9  first dba      1669385 0000000000197909

P3TEXT    P3 P3RAW                       WAIT_CLASS_ID WAIT_CLASS# WAIT_CLASS  WAIT_TIME SECONDS_IN_WAIT   STATE            
---------    --  ----------------                -------------           -----------         ----------          ----------      ---------------               -----------------
block cnt  1  0000000000000001    1740759767           8                   User I/O           -1               8                            WAITED SHORT TIME

一个过程正常的话几秒就执行完了，但最近该过程执行到一个insert的时候出现了这个等待事件，15000多秒还没执行完。
请问一般情况下都有哪些原因，也就是我需要确认哪些东西是否正常
另外，要如何解决，谢谢！！！

p1＝201是临时文件，观察了一下，平均12秒左右读一个block

carcase

看看是否有 一些 不合理的 磁盘排序
排序区是否太小？

wa0362

找出原因来
10.3.5 direct path read and direct path read tempWhen a session is reading buffers from disk directly into the PGA (opposed to the buffer cache in SGA), it waits on this event. If the I/O subsystem does not support asynchronous I/Os, then each wait corresponds to a physical read request.
If the I/O subsystem supports asynchronous I/O, then the process is able to overlap issuing read requests with processing the blocks already existing in the PGA. When the process attempts to access a block in the PGA that has not yet been read from disk, it then issues a wait call and updates the statistics for this event. Hence, the number of waits is not necessarily the same as the number of read requests (unlike db file scattered read and db file sequential read).
Check the following V$SESSION_WAIT parameter columns:

• P1 - File_id for the read call
• P2 - Start block_id for the read call
• P3 - Number of blocks in the read call
  

10.3.5.1 CausesThis happens in the following situations:

• The sorts are too large to fit in memory and some of the sort data is written out directly to disk. This data is later read back in, using direct reads.
• Parallel slaves are used for scanning data.
• The server process is processing buffers faster than the I/O system can return the buffers. This can indicate an overloaded I/O system.
  

10.3.5.2 ActionsThe file_id shows if the reads are for an object in TEMP tablespace (sorts to disk) or full table scans by parallel slaves. This is the biggest wait for large data warehouse sites. However, if the workload is not a DSS workload, then examine why this is happening.
10.3.5.2.1 Sorts to DiskExamine the SQL statement currently being run by the session experiencing waits to see what is causing the sorts. Query V$TEMPSEG_USAGE to find the SQL statement that is generating the sort. Also query the statistics from V$SESSTAT for the session to determine the size of the sort. See if it is possible to reduce the sorting by tuning the SQL statement. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the SORT_AREA_SIZE for the system (if the sorts are not too big) or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET. See "PGA Memory Management".

10.3.5.2.2 Full Table ScansIf tables are defined with a high degree of parallelism, then this could skew the optimizer to use full table scans with parallel slaves. Check the object being read into using the direct path reads. If the full table scans are a valid part of the workload, then ensure that the I/O subsystem is configured adequately for the degree of parallelism. Consider using disk striping if you are not already using it or Automatic Storage Management (ASM).

10.3.5.2.3 Hash Area SizeFor query plans that call for a hash join, excessive I/O could result from having HASH_AREA_SIZE too small. If WORKAREA_SIZE_POLICY is MANUAL, then consider increasing the HASH_AREA_SIZE for the system or for individual processes. If WORKAREA_SIZE_POLICY is AUTO, then investigate whether to increase PGA_AGGREGATE_TARGET.

homeworld80

原帖由 carcase 于 2008-11-19 10:25 发表
看看是否有 一些 不合理的 磁盘排序
排序区是否太小？

请问一下什么是 不合理的 磁盘排序？
pga_aggregate_target是1G，我想已经够大了

homeworld80

＃3的这段话我也一直在看，但还是不得要领
1、不是排序区太小
2、没有并行
3、The server process is processing buffers faster than the I/O system can return the buffers，这个有可能
但我的系统有很多过程在执行，而且处理的数据量比这个过程要大的多都没问题的
感觉要么和这个语句的执行计划有关，要么和其中的某个表有关
大致语句是：insert into t(...) select * from a,b,c,d where ...
看执行计划，对其中一个表（7000记录）做sort join，然后和另一个表的索引做merge join，没有其他排序操作

carcase

就是 这段时间内的 排序太多了
你的pga是1G，5%是50M，排序大于50M，就是要磁盘排序，磁盘排序多 可以理解为 不合理

你可以手工 执行一下你的sql语句，看看sort(disk) 是不是多

homeworld80

select * from V$SESSTAT a,v$statname b
where a.STATISTIC# = b.STATISTIC# and name like '%sorts%' and sid = 294

sid          statistic#  value        name                      class        stat_id
294        349        688910        sorts (memory)        64        2091983730
294        350        1        sorts (disk)        64        2533123502
294        351        2962856        sorts (rows)        64        3757672740

不知道这样是不是正常？

carcase

select sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,last_tempseg_size
from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and sql_text like '%select object_name from test3 order by owner%';

select object 用你的sql代替   
2962856        sorts (rows)   292万的排序 有点多吧

homeworld80

select sql_text,operation_type,policy,last_memory_used/1024/1024,last_execution,last_tempseg_size
from v$sql l,v$sql_workarea a
where l.hash_value=a.hash_value
and l.sql_id = '5q7a34qct8fun'

SQL_TEXT                               OPERATION_TYPE   POLICY   LAST_MEMORY_USED/1024/1024 LAST_EXECUTION       LAST_TEMPSEG_SIZE
------------------------                 ----------------           -------- --------------------------                        --------------------          -----------------
INSERT INTO OFENTRUST SE   BUFFER                   AUTO                              0                            OPTIMAL

[ 本帖最后由 homeworld80 于 2008-11-19 14:06 编辑 ]

Meets

也用这条语句
select se.USERNAME,
       se.OSUSER,
       se.SID,
       p.SPID,
       su.BLOCKS * 8192/1024/1024 Space,
       s.SQL_TEXT
  from v$sort_usage su, v$session se, v$sqltext s, v$process p
where su.SESSION_ADDR = se.SADDR
   and se.SQL_ADDRESS = s.ADDRESS
   and se.SQL_HASH_VALUE = s.HASH_VALUE
   and p.ADDR = se.PADDR;

查出来sql  然后看看 为什么会有那么多的排序