Skip to Main Content
Forums

Slaves allocated in the sql monitor report for parallel query-

Bhavani DhulipallaJun 11 2019 — edited Jun 13 2019

hi - I am trying to understand the slaves allocated for the below sql.

The DOP is 6 but the slaves allocated showing as 290K in the SQL Monitoring report I see that it is usually twice and can be more if we have more PX Coordinators-But 290K Shows too much excessive -

The parallel_max_server is 204 at instance level and we have 8 nodes-

SQL Monitoring report is below and all the other details are below --Thank you for the help -

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

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.
This post has been answered by Jonathan Lewis on Jun 12 2019
Jump to Answer
Comments
Post Details
Added on Jun 11 2019
#general-database-discussions
6 comments
1,366 views