Global Information
------------------------------
Status : EXECUTING
Instance ID : 7
Session : ROXD074D (1499:6041)
SQL ID : gm16wrskxtnc6
SQL Execution ID : 117440512
Execution Started : 06/10/2019 14:28:49
First Refresh Time : 06/10/2019 14:28:51
Last Refresh Time : 06/11/2019 16:46:02
Duration : 94633s
Module/Action : ROXD074D/SW_EXCLUDE_PATIENTS
Service : rxdwp0
Program : sqlplus@rri2exeapa1 (TNS V1-V3)
PLSQL Entry Ids (Object/Subprogram) : 18977647,1
PLSQL Current Ids (Object/Subprogram) : 18977647,1
Global Stats
====================================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Cell |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload |
====================================================================================================================================
| 1881 | 1532 | 1.18 | 0.00 | 0.00 | 0.65 | 346 | 7M | 1410 | 14MB | 12 | 3MB | -17.65% |
====================================================================================================================================
Parallel Execution Details (DOP=6 , Servers Allocated=290084)
Instances : 6
=============================================================================================================================================================================================================================
| Instance | Name | Type | Group# | Server# | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Cell | Wait Events |
| | | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | (sample #) |
=============================================================================================================================================================================================================================
| 7 | PX Coordinator | QC | | | 1794 | 1446 | 1.08 | 0.00 | 0.00 | 0.63 | 346 | 4M | 1398 | 11MB | | . | NaN% | PX Deq: Execute Reply (1) |
| | | | | | | | | | | | | | | | | | | IPC send completion sync (1) |
| | | | | | | | | | | | | | | | | | | PX Deq: Signal ACK EXT (2) |
| | | | | | | | | | | | | | | | | | | PX Deq: Slave Session Stats (2) |
| | | | | | | | | | | | | | | | | | | PX Deq: reap credit (11) |
| | | | | | | | | | | | | | | | | | | PX qref latch (1) |
| | | | | | | | | | | | | | | | | | | enq: PS - contention (132) |
| | | | | | | | | | | | | | | | | | | latch free (4) |
| | | | | | | | | | | | | | | | | | | reliable message (61) |
| 2 | p00t | Set 1 | 1 | 1 | 1.21 | 1.21 | | | | | | 90800 | | . | | . | NaN% | |
| 8 | p00o | Set 1 | 1 | 1 | | | | | | | | | | . | | . | NaN% | |
| 7 | p03w | Set 1 | 1 | 1 | 1.20 | 1.17 | | | | | 0.03 | 92175 | | . | | . | NaN% | |
| 6 | p000 | Set 1 | 1 | 1 | | | | | | | | | | . | | . | NaN% | library cache: mutex X (1) |
| | | | | | | | | | | | | | | | | | | |
| 5 | p00u | Set 1 | 1 | 1 | 1.38 | 1.36 | | | | 0.01 | 0.02 | 93231 | | . | | . | NaN% | |
| 4 | p01g | Set 1 | 1 | 1 | 1.06 | 1.06 | | | | | | 98705 | | . | | . | NaN% | |
| 2 | p00u | Set 1 | 1 | 2 | 1.18 | 1.16 | | | | | 0.03 | 90962 | | . | | . | NaN% | |
| 8 | p02h | Set 1 | 1 | 2 | | | | | | | | | | . | | . | NaN% | |
| 7 | p03x | Set 1 | 1 | 2 | 1.21 | 1.19 | | | | | 0.02 | 96064 | | . | | . | NaN% | |
| 6 | p001 | Set 1 | 1 | 2 | | | | | | | | | | . | | . | NaN% | |
| 5 | p011 | Set 1 | 1 | 2 | 1.51 | 1.50 | | | | 0.01 | | 101K | | . | | . | NaN% | |
| 4 | p00v | Set 1 | 1 | 2 | 1.08 | 1.08 | | | | | 0.00 | 94628 | | . | | . | NaN% | |
| 2 | p00v | Set 1 | 1 | 3 | 1.24 | 1.24 | | | | | | 115K | | . | | . | NaN% | |
| 8 | p00q | Set 1 | 1 | 3 | | | | | | | | | | . | | . | NaN% | |
| 7 | p03y | Set 1 | 1 | 3 | 1.27 | 1.24 | | | | | 0.03 | 98334 | | . | | . | NaN% | |
| 6 | p002 | Set 1 | 1 | 3 | | | | | | | | | | . | | . | NaN% | |
| 5 | p00w | Set 1 | 1 | 3 | 1.37 | 1.36 | | | | 0.00 | 0.01 | 96021 | | . | | . | NaN% | |
| 4 | p01i | Set 1 | 1 | 3 | 1.11 | 1.11 | | | | | 0.00 | 91780 | | . | | . | NaN% | |
| 2 | p00w | Set 1 | 1 | 4 | 1.21 | 1.21 | | | | | | 87092 | | . | | . | NaN% | |
| 8 | p00r | Set 1 | 1 | 4 | | | | | | | | | | . | | . | NaN% | |
| 7 | p03z | Set 1 | 1 | 4 | 1.20 | 1.03 | | | | | 0.17 | 96840 | | . | | . | NaN% | |
| 6 | p003 | Set 1 | 1 | 4 | | | | | | | | | | . | | . | NaN% | |
| 5 | p00x | Set 1 | 1 | 4 | 1.41 | 1.40 | | | | 0.00 | | 93096 | | . | | . | NaN% | |
| 4 | p01j | Set 1 | 1 | 4 | 1.04 | 1.04 | | | | | 0.00 | 98300 | | . | | . | NaN% | |
| 2 | p00g | Set 1 | 2 | 1 | 1.18 | 1.18 | | | | | 0.00 | 88844 | | . | | . | NaN% | |
| 8 | p008 | Set 1 | 2 | 1 | 1.28 | 1.28 | | | | | 0.00 | 93474 | | . | | . | NaN% | |
| 7 | p000 | Set 1 | 2 | 1 | 1.13 | 1.12 | | | | | 0.01 | 86488 | | . | | . | NaN% | |
| 6 | p01b | Set 1 | 2 | 1 | 1.19 | 1.16 | | | | | 0.03 | 90660 | | . | | . | NaN% | |
| 5 | p01c | Set 1 | 2 | 1 | 1.32 | 1.31 | | | | | 0.01 | 93690 | | . | | . | NaN% | |
| 4 | p004 | Set 1 | 2 | 1 | 1.28 | 1.28 | | | | | | 96531 | | . | | . | NaN% | |
| 2 | p00h | Set 1 | 2 | 2 | 1.21 | 1.21 | | | | | | 97755 | | . | | . | NaN% | |
| 8 | p009 | Set 1 | 2 | 2 | 1.33 | 1.33 | | | | | 0.00 | 98201 | | . | | . | NaN% | |
| 7 | p001 | Set 1 | 2 | 2 | 1.15 | 1.14 | | | | | 0.01 | 101K | | . | | . | NaN% | |
| 6 | p01c | Set 1 | 2 | 2 | 1.19 | 1.17 | | | | | 0.02 | 96785 | | . | | . | NaN% | |
| 5 | p001 | Set 1 | 2 | 2 | 1.32 | 1.32 | | | | | 0.00 | 93760 | | . | | . | NaN% | |
| 4 | p005 | Set 1 | 2 | 2 | 1.25 | 1.25 | | | | | 0.00 | 97376 | | . | | . | NaN% | |
| 2 | p00i | Set 1 | 2 | 3 | 1.20 | 1.20 | | | | | 0.00 | 95779 | | . | | . | NaN% | |
| 8 | p00a | Set 1 | 2 | 3 | 1.27 | 1.26 | | | | | 0.01 | 94914 | | . | | . | NaN% | |
| 7 | p002 | Set 1 | 2 | 3 | 1.12 | 1.10 | | | | | 0.03 | 93960 | | . | | . | NaN% | |
| 6 | p01d | Set 1 | 2 | 3 | 1.19 | 1.17 | | | | | 0.02 | 98677 | | . | | . | NaN% | |
| 5 | p01e | Set 1 | 2 | 3 | 1.33 | 1.33 | | | | | | 95895 | | . | | . | NaN% | |
| 4 | p006 | Set 1 | 2 | 3 | 1.19 | 1.19 | | | | | 0.00 | 93887 | | . | | . | NaN% | |
| 2 | p00j | Set 1 | 2 | 4 | 1.21 | 1.12 | | | | | 0.08 | 97908 | | . | | . | NaN% | |
| 8 | p00b | Set 1 | 2 | 4 | 1.32 | 1.32 | | | | | | 96824 | | . | | . | NaN% | |
| 7 | p003 | Set 1 | 2 | 4 | 1.22 | 1.22 | | | | | | 102K | | . | | . | NaN% | |
| 6 | p01e | Set 1 | 2 | 4 | 1.19 | 1.13 | | | | | 0.06 | 89886 | | . | | . | NaN% | |
| 4 | p007 | Set 1 | 2 | 4 | 1.19 | 1.19 | | | | | | 95619 | | . | | . | NaN% | |
| 5 | p01h | Set 1 | 2 | 4 | 1.32 | 1.22 | | | | | 0.10 | 95381 | | . | | . | NaN% | |
| 6 | p00k | Set 1 | 3 | 1 | 6.54 | 6.47 | 0.08 | | | | | | 2 | 496KB | 2 | 496KB | -49.25% | |
| 6 | p00l | Set 1 | 3 | 2 | 6.19 | 6.19 | 0.00 | | | | | | 2 | 496KB | 2 | 496KB | -49.25% | |
| 6 | p00m | Set 1 | 3 | 3 | 6.23 | 6.23 | 0.00 | | | | | | 2 | 496KB | 2 | 496KB | -49.25% | |
| 6 | p00n | Set 1 | 3 | 4 | 6.01 | 5.99 | 0.02 | | | | | | 2 | 496KB | 2 | 496KB | -49.25% | |
| 6 | p00o | Set 1 | 3 | 5 | 6.20 | 6.20 | 0.00 | | | | | | 2 | 496KB | 2 | 496KB | -49.25% | |
| 6 | p00p | Set 1 | 3 | 6 | 6.28 | 6.28 | 0.00 | | | | | | 2 | 496KB | 2 | 496KB | -49.25% | |
=============================================================================================================================================================================================================================
Instance Drill-Down
===============================================================================================================================================================================================================================================================
| Instance | Process Names | Elapsed | Cpu | IO | Application | Concurrency | Cluster | Other | Buffer | Read | Read | Write | Write | Cell | Wait Events |
| | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | Offload | |
===============================================================================================================================================================================================================================================================
| 2 | p00t p00u p00v p00w p00g p00h p00i p00j | 10 | 10 | | | | | 0.11 | 764K | | | | | NaN% | library cache lock (13) |
| | | | | | | | | | | | | | | | PX Deq: reap credit (1) |
| | | | | | | | | | | | | | | | latch free (6) |
| | | | | | | | | | | | | | | | cell list of blocks physical read (1) |
| 4 | p01g p00v p01i p01j p004 p005 p006 p007 | 9.21 | 9.21 | | | | | 0.01 | 767K | | | | | NaN% | PX Deq: reap credit (3) |
| 5 | p00u p011 p00w p00x p01c p001 p01e p01h | 11 | 11 | | | | 0.02 | 0.14 | 762K | | | | | NaN% | |
| 6 | p000 p001 p002 p003 p01b p01c p01d p01e p00k p00l p00m p00n p00o p00p | 42 | 42 | 0.10 | | | | 0.13 | 376K | 12 | 3MB | 12 | 3MB | -49.25% | library cache: mutex X (1) |
| | | | | | | | | | | | | | | | |
| 7 | QC p03w p03x p03y p03z p000 p001 p002 p003 | 1803 | 1456 | 1.08 | 0.00 | 0.00 | 0.63 | 346 | 4M | 1398 | 11MB | | | NaN% | latch: cache buffers chains (1) |
| | | | | | | | | | | | | | | | PX Deq: Execute Reply (1) |
| | | | | | | | | | | | | | | | IPC send completion sync (1) |
| | | | | | | | | | | | | | | | PX Deq: Signal ACK EXT (2) |
| | | | | | | | | | | | | | | | PX Deq: Slave Session Stats (2) |
| | | | | | | | | | | | | | | | PX Deq: reap credit (11) |
| | | | | | | | | | | | | | | | PX qref latch (1) |
| | | | | | | | | | | | | | | | enq: PS - contention (132) |
| | | | | | | | | | | | | | | | latch free (4) |
| | | | | | | | | | | | | | | | reliable message (61) |
| 8 | p00o p02h p00q p00r p008 p009 p00a p00b | 5.20 | 5.19 | | | | | 0.01 | 383K | | | | | NaN% | library cache: mutex X (1) |
| | | | | | | | | | | | | | | | PX Deq: reap credit (3) |
| | | | | | | | | | | | | | | | latch free (5) |
| | | | | | | | | | | | | | | | Disk file operations I/O (1) |
===============================================================================================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=2942520749)
===============================================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Temp | Activity | Activity Detail | Progress |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | | | (%) | (# samples) | |
===============================================================================================================================================================================================================================================
| 0 | UPDATE STATEMENT | | | | 94611 | +29 | 1 | 0 | | | | | | | 0.01 | Cpu (8) | |
| 1 | UPDATE | PGN_SCRPT_TMP_TBL | | | 94611 | +29 | 1 | 0 | 1398 | 11MB | | | | | | | |
| 2 | FILTER | | | | 94621 | +21 | 1 | 19001 | | | | | | | 0.00 | Cpu (1) | |
| 3 | PX COORDINATOR | | | | 94621 | +21 | 7 | 75942 | | | | | | | | | |
| 4 | PX SEND QC (RANDOM) | :TQ30002 | 78274 | 476 | 94238 | +355 | 6 | 77997 | | | | | | | | | |
| 5 | HASH JOIN BUFFERED | | 78274 | 476 | 94238 | +355 | 6 | 77997 | 12 | 3MB | 12 | 3MB | 16M | 4M | | | 100% |
| 6 | PX RECEIVE | | 78274 | 240 | 1380 | +355 | 6 | 78511 | | | | | | | | | |
| 7 | PX SEND HYBRID HASH | :TQ30000 | 78274 | 240 | | | | | | | | | | | | | |
| 8 | STATISTICS COLLECTOR | | | | | | | | | | | | | | | | |
| 9 | PX BLOCK ITERATOR | | 78274 | 240 | | | | | | | | | | | | | |
| 10 | TABLE ACCESS STORAGE FULL | PGN_SCRPT_TMP_TBL | 78274 | 240 | | | | | | | | | | | | | |
| 11 | PX RECEIVE | | 83230 | 236 | 1380 | +355 | 6 | 83845 | | | | | | | | | |
| 12 | PX SEND HYBRID HASH | :TQ30001 | 83230 | 236 | | | | | | | | | | | | | |
| 13 | PX BLOCK ITERATOR | | 83230 | 236 | | | | | | | | | | | | | |
| 14 | TABLE ACCESS STORAGE FULL | PGN_SCRPT_TMP_TBL | 83230 | 236 | | | | | | | | | | | | | |
| 15 | PX COORDINATOR | | | | 21533 | +73064 | 212K | 0 | | | | | | | 0.49 | Cpu (194) | |
| | | | | | | | | | | | | | | | | PX Deq: Execute Reply (1) | |
| | | | | | | | | | | | | | | | | IPC send completion sync (1) | |
| | | | | | | | | | | | | | | | | PX Deq: Signal ACK EXT (2) | |
| | | | | | | | | | | | | | | | | PX Deq: Slave Session Stats (2) | |
| | | | | | | | | | | | | | | | | PX Deq: reap credit (10) | |
| | | | | | | | | | | | | | | | | PX qref latch (1) | |
| | | | | | | | | | | | | | | | | enq: PS - contention (125) | |
| | | | | | | | | | | | | | | | | latch free (4) | |
| | | | | | | | | | | | | | | | | reliable message (60) | |
| 16 | PX SEND QC (RANDOM) | :TQ10000 | 1 | 101K | 1 | +88487 | 142K | 0 | | | | | | | 0.00 | Cpu (1) | |
| 17 | PX BLOCK ITERATOR | | 1 | 101K | 20146 | +73065 | 142K | 0 | | | | | | | 0.07 | Cpu (51) | |
| | | | | | | | | | | | | | | | | PX Deq: reap credit (7) | |
| -> 18 | TABLE ACCESS STORAGE FULL FIRST ROWS | RULE_CONFIG_FILE_PARM | 1 | 101K | 94097 | +546 | 12M | 0 | 17 | 136KB | | | | | 95.63 | gc cr multi block request (8) | |
| | | | | | | | | | | | | | | | | latch: cache buffers chains (1) | |
| | | | | | | | | | | | | | | | | Cpu (77768) | |
| | | | | | | | | | | | | | | | | latch free (11) | |
| | | | | | | | | | | | | | | | | latch: gcs resource hash (6) | |
| | | | | | | | | | | | | | | | | Disk file operations I/O (1) | |
| | | | | | | | | | | | | | | | | cell multiblock physical read (13) | |
| 19 | PX COORDINATOR | | | | 94513 | +37 | 3593 | 1004 | | | | | | | 0.02 | Cpu (7) | |
| | | | | | | | | | | | | | | | | PX Deq: reap credit (1) | |
| | | | | | | | | | | | | | | | | enq: PS - contention (7) | |
| | | | | | | | | | | | | | | | | reliable message (1) | |
| 20 | PX SEND QC (RANDOM) | :TQ20000 | 1 | 101K | 20089 | +74460 | 857 | 86 | | | | | | | 0.00 | Cpu (1) | |
| 21 | PX BLOCK ITERATOR | | 1 | 101K | 20089 | +74460 | 857 | 86 | | | | | | | 0.00 | Cpu (4) | |
| 22 | TABLE ACCESS STORAGE FULL FIRST ROWS | RULE_CONFIG_FILE_PARM | 1 | 101K | 81110 | +13491 | 73673 | 86 | | | | | | | 3.71 | Cpu (3022) | |
===============================================================================================================================================================================================================================================
Elapsed: 00:00:10.84
SQL> select A.USERNAME,a.sql_id,B.SID,b.qcsid,b.DEGREE,b.REQ_DEGREE
from gv$session a,gv$px_session b
where a.sid=b.qcsid
and a.serial#=b.qcserial#
and a.sql_id='&SQL_ID';
Enter value for sql_id: gm16wrskxtnc6
USERNAME SQL_ID SID QCSID DEGREE REQ_DEGREE
------------------------------ ------------- ----- ---------- ---------- ----------
ROXD074D gm16wrskxtnc6 777 1499 4 4
ROXD074D gm16wrskxtnc6 727 1499 4 4
ROXD074D gm16wrskxtnc6 1018 1499 4 4
ROXD074D gm16wrskxtnc6 1154 1499 4 4
ROXD074D gm16wrskxtnc6 1636 1499 6 6
ROXD074D gm16wrskxtnc6 1836 1499 6 6
ROXD074D gm16wrskxtnc6 1927 1499 6 6
ROXD074D gm16wrskxtnc6 201 1499 6 6
ROXD074D gm16wrskxtnc6 2127 1499 6 6
ROXD074D gm16wrskxtnc6 1117 1499 6 6
ROXD074D gm16wrskxtnc6 1159 1499 6 6
ROXD074D gm16wrskxtnc6 4 1499 6 6
ROXD074D gm16wrskxtnc6 105 1499 6 6
ROXD074D gm16wrskxtnc6 147 1499 6 6
ROXD074D gm16wrskxtnc6 919 1499 6 6
ROXD074D gm16wrskxtnc6 1258 1499 6 6
16 rows selected.
Elapsed: 00:00:00.80
SQL>
SQL>
SQL>
SQL> select inst_id, PX_SERVERS_EXECUTIONS,DELTA_PX_SERVERS_EXECUTIONS from gv$sqlstats where sql_id='gm16wrskxtnc6';
INST_ID PX_SERVERS_EXECUTIONS DELTA_PX_SERVERS_EXECUTIONS
---------- --------------------- ---------------------------
5 37811 37811
4 39016 244
2 10608 0
8 32814 552
7 42844 1088
6 23871 408
1 26221 0
7 rows selected.
Elapsed: 00:00:00.07
7 rows selected.