Oracle Database Discussions

Hi everybody

I encounter some performance problem, I've made a tkprof on a select statement and I saw that more than 95% of the elapsed time is due to event PX Deq: Execute Reply.

This request is not CPU or paging consuming. What is this event and how could I reduce it ? Could it be a disk problem ?

Thanks a lot, best regards

Greg

Here is a sample of my tkprof:


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 1 0.03 0.03 0 0 0 0
Execute 1 0.22 2.16 68 177 12 0
Fetch 2 0.17 511.97 38 40 0 1
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 4 0.42 514.16 106 217 12 1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 38

Rows Row Source Operation
------- ---------------------------------------------------
1 PX COORDINATOR (cr=202 pr=103 pw=0 time=513984636 us)
0 PX SEND QC (RANDOM) :TQ10003 (cr=0 pr=0 pw=0 time=0 us)
0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND HASH :TQ10002 (cr=0 pr=0 pw=0 time=0 us)
0 HASH GROUP BY (cr=0 pr=0 pw=0 time=0 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND BROADCAST :TQ10000 (cr=0 pr=0 pw=0 time=0 us)
473 TABLE ACCESS FULL DIM_CALL_DISTANCE (cr=8 pr=7 pw=0 time=27259 us)
0 HASH JOIN (cr=0 pr=0 pw=0 time=0 us)
0 BUFFER SORT (cr=0 pr=0 pw=0 time=0 us)
0 PX RECEIVE (cr=0 pr=0 pw=0 time=0 us)
0 PX SEND BROADCAST :TQ10001 (cr=0 pr=0 pw=0 time=0 us)
4 TABLE ACCESS FULL DIM_AUDIT_CALL (cr=32 pr=31 pw=0 time=35037 us)
0 PX BLOCK ITERATOR PARTITION: 1 16 (cr=0 pr=0 pw=0 time=0 us)
0 TABLE ACCESS FULL FACT_CALL PARTITION: 1 48 (cr=0 pr=0 pw=0 time=0 us)


Elapsed times include waiting on following events:
Event waited on Times Max. Wait Total Waited
---------------------------------------- Waited ---------- ------------
db file sequential read 67 0.05 0.95
os thread startup 4 0.21 0.80
PX Deq: Join ACK 4 0.00 0.00
PX Deq: Parse Reply 3 0.13 0.17
SQL*Net message to client 2 0.00 0.00
PX Deq: Execute Reply 304 1.96 511.68
db file scattered read 6 0.01 0.03
PX qref latch 12 0.00 0.00
SQL*Net message from client 2 94.93 94.94
PX Deq: Signal ACK 6 0.10 0.11
enq: PS - contention 1 0.00 0.00
********************************************************************************