VERY SLOW EXPDP IN 12.1.0.2 // CURSOR: PIN S WAIT ON X AND PX DEQ: PARSE REPLY WAIT EVENTS OBSERVED (Doc ID 2482758.1)

Last updated on APRIL 17, 2023

Applies to:

Symptoms

 SQL ID: <SQL_ID_STRING> Plan Hash: 1864011845

SELECT /*+ ALL_ROWS */ COUNT(*) FROM <OBJECT_NAME> V WHERE V.OBJECT_OWNER = :B3 AND V.OBJECT_NAME = :B2 AND  (POLICY_NAME LIKE '%xdbrls%' OR POLICY_NAME LIKE '%$xd_%') AND V.FUNCTION =  :B1


call count cpu elapsed disk query current rows
------- ------ -------- ---------- ---------- ---------- ---------- ----------
Parse 2 0.00 0.00 0 0 0 0
Execute 80 0.37 0.38 1 8 0 0
Fetch 80 204.06 46279.42 78 3337 0 80
------- ------ -------- ---------- ---------- ---------- ---------- ----------
total 162 204.43 46279.81 79 3345 0 80.
Elapsed times include waiting on following events:
 Event waited on Times Max. Wait Total Waited
 ---------------------------------------- Waited ---------- ------------
 db file sequential read 79 0.00 0.08
 PX Deq: Join ACK 131311 0.03 16.86
 PX Deq: Parse Reply 65660 2.60 45953.96
 enq: PS - contention 65658 0.00 108.00
 PX qref latch 435 0.00 0.00
 latch: shared pool 465 0.00 0.01
 latch free 29 0.11 0.38
 PX Deq: Execute Reply 20 0.01 0.02
 PX Deq: Table Q Normal 2 0.00 0.00
 PX Deq: Signal ACK EXT 4 0.00 0.00
 PX Deq: Slave Session Stats 4 0.00 0.00
******************************************************************************

The idle wait  PX Deq: Parse Reply above is a consequence of the library cache cursor: pin S wait on X wait seen in the AWR below.

From AWR:

Top 10 Foreground Events by Total Wait Time

Event Waits Total Wait Time (sec) Wait Avg(ms) % DB time Wait Class
DB CPU 3664.5 53.8
cursor: pin S wait on X 9,279 3133.2 337.66 46.0 Concurrency........................High wait on cursor pin during parsing
kksfbc child completion 964 24.3 25.21 .4 Other
db file sequential read 9,849 7.6 0.77 .1 User I/O
library cache lock 2,113 1.3 0.61 .0 Concurrency
db file scattered read 765 1.1 1.39 .0 User I/O
direct path read 2,203 1.1 0.48 .0 User I/O
db file parallel read 58 1 16.86 .0 User I/O
library cache: mutex X 2,881 .5 0.18 .0 Concurrency
log file switch (checkpoint incomplete) 2 .4 223.30 .0 Configuration


Foreground Wait Events

Event Waits %Time -outs Total Wait Time (s) Avg wait (ms) Waits /txn % DB time
cursor: pin S wait on X 9,279 3,133 337.66 96.66                             46.01
kksfbc child completion 964 50 24 25.21 10.04 0.36
db file sequential read 9,849 8 0.77 102.59 0.11

Changes

 NONE

Cause

My Oracle Support provides customers with access to over a million knowledge articles and a vibrant support community of peers and Oracle experts.