Oracle查询等待问题

 

 

1. PX Deq:execute Reply

Occurs when the query coordinator is waiting for a response from a parallel slave. This is normally considered an idle event, but can cause excessive CPU in some cases.

Solution

Reduce the degree of parallelism in the query if excessive CPU usage is detected.

10046trace对比如下：

（1）数据库初始化参数

（2）10046 Trace

_PX_use_large_pool

并行执行从属进程一起工作时会交换数据和信息，需要从shared pool或large pool中分配内存，这个取决于PARALLEL_AUTOMATIC_TUNING参数值的设置，_PX_use_large_pool所起的作用跟PARALLEL_AUTOMATIC_TUNING参数差不多。当PARALLEL_AUTOMATIC_TUNING=TRUE时从large pool中分配内存，否则从shared pool分配。

10g中,PX信息缓存在large pool中分配，如果：

1. parallel_automatic_tuning = true (弃用) or
2. _PX_use_large_pool = true orc.) or
3. sga_target is set

 

https://support.oracle.com/epmos/faces/SearchDocDisplay?_adf.ctrl-state=1csc36s6b1_4&_afrLoop=359264016887100

MOS相关连接：

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=359587020130090&id=270916.1&_afrWindowMode=0&_adf.ctrl-state=1csc36s6b1_92

https://support.oracle.com/epmos/faces/DocumentDisplay?_afrLoop=359692889536275&parent=DOCUMENT&sourceId=270916.1&id=191103.1&_afrWindowMode=0&_adf.ctrl-state=1csc36s6b1_141

1. 1. 查询命令

SELECT SID,OBJECT_NAME,TYPE,ID1,ID2,LMODE FROM GV$LOCK L,DBA_OBJECTS O WHERE O.OBJECT_ID=L.ID1 AND L.TYPE in ('TM','TX');