Skip to Main Content
Forums

Oracle Database Discussions

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

Interested in getting your voice heard by members of the Developer Marketing team at Oracle? Check out this post for AppDev or this post for AI focus group information.

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
2,308 views