direct path read

problem:

 

direct path read 等待，temp表空间使用达100%

有10 session 有direct path read 等待，而且好像一直都在那里等着，从昨天我就看到了这些等待，到今天还在，这些进程占很大cpu资源，但temp表空间使用达到100%，有没有办法解决，增加pga大小，还是加大temp表空间?

 

 ###############################################################

 

methord:

------------------------------------------

1.最好是Tune SQL

2.次好是Add PGA

3。实在不行加temp tablespace

4.当然，继续等待也是没有办法中的办法。

 

-------------------------------------------------

sort need compare. compare need cpu ...

You can get free temp tablespace blocks from v$sort_usage v$tempfile.

And make sure your sort_area_size is enough to make sure sort is completed in one merge pass.

You can set  sort_area_size to nG is your OS permit.

-----------------------------------------------
看看SQL语句的执行计划。联合查询导致大量排序操作，耗尽PGA后进而引起TEMP IO。看是否可以从优化SQL入手。
-------------------------------------------------------------

版本？8i增加sort_area_size ，hash_area_size，9i pga如auto管理方式，增加pga_aggregate_target

但首先tunning SQL！减少sort，sort尽可能小的结果集
------------------------------------------------------------------------------------------------------------

direct path read 等待一般为 order by,group by 语句执行不合理引起的,最好能从语句方面解决
---------------------------------------------------------------------------------

Are p1,p2,p3 values changing in v$session_wait where event='direct path read'? If not, check your OS or filesystem. But sometimes you just can't find the root cause of the problem and you have to shutdown immediate and restart. I recall having this problem twice in the past few years.

If the values change, it means they are still doing real work but your system doesn't have enough capacity. What kind of box is it? Number of CPUs, memory, disk RAID etc.?

Always post Oracle version and OS version. What's your pga_aggregate_target?

As everyone says, tune SQL and tune SQL again.

Yong Huang
--------------------------------------------------------------------------------------------------

direct path read
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 thebuffers. This can indicate an overloaded I/O system.
--------------------------------------------------------------------------------------------

昨天也碰到类似的问题了,等在direct path read temp上,OS里看到没有什么IO,一个CPU满负荷空转.

SQL是由程序自动生成的,的确是又臭又长,把其中的一个子查询用一个临时表替换后,马上就跑完了

感觉像是有点bug的味道

from:

http://www.itpub.net/thread-357879-1-339.html

#################################################################

 

今天生产数据库出现了几个direct path read等待事件，参考oracle文档，得知asynchronous disk operate有关，随即检查fuser /dev/async，kctune |grep aio_max_ops，show paramete disk_asynch_io 均正常。继续检查v$session_wait，发现file number 并不在dba_data_files中，据此推测此问题发生在temp tablespace中，随即检查v$sort_usage，发现出现的session，与v$session_wait中“direct path read”中的session吻合。继续检查pga的使用情况select * from v$pagstat发现，pga使用已较高（参考workarea_size_policy+pga_aggregate_target），综上所述，pga使用达到一定程度后（2G*5%*6process=600M)，只能使用temp tablespace完成排序操作，由于temp tablespace使用的disk，因此出现上述direct path read 也是正常的。解决此问题的方法为使用较大的将workarea_size_policy更改为manual，加大sort_area_size，减少使用temp tablespace的使用。

from:

http://space.itpub.net/13132547/viewspace-310245

 

#################################################################

 

direct path read/write （直接路径读／写）

from:

http://www.dbtan.com/2010/04/direct-path-readwrite.html

#################################################################

模拟direct path read temp等待事件:

from:

http://www.dbconf.net/analog-direct-path-read-temp-wait-event.html