6 Replies Latest reply on Jun 13, 2019 11:28 AM by AndrewSayer

    Slaves allocated in the sql monitor report for parallel query-

    Bhavani Dhulipalla

      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.

       

       

        • 1. Re: Slaves allocated in the sql monitor report for parallel query-
          AndrewSayer

          Max_parallel_servers can be different on each of your instances. The DOP=6 looks to real Be Reporting That you’re using 6 instances.

           

          The majority of the time taken in this execution doesn’t seem to have anything to do with the amount of parallelism, it looks to be mostly due to the “TABLE ACCESS STORAGE FULL FIRST ROWS“ which seems to be executed 12 million times on line 18. This looks to be a result of your statement written using scalar subqueries with rownum filters in them - forcing a row-by-row (slow by slow!) type of plan that doesn’t take advantage of bulky techniques that would fly through this work in parallel.

           

          If you share your SQL, I’m sure we can suggest a much better way to write it.

          • 2. Re: Slaves allocated in the sql monitor report for parallel query-
            Jonathan Lewis

            Which version of Oracle ?

            What does the query look like.

             

            Key point to note about your plan, it shows 3 "DFO trees", and each DFO tree could run at a different DOP. (this blog note of mine is reading)

            On top of that there are cases where a scalar subquery will report the PX slaves in the SQL Monitor summary once for EVERY execution of the aubquery,

             

            Working backwards from the plan I think your query might be something like:

             

            update table t1 set ...

            where exists (parallel subquery 1)

            and/or exists (parallel subquery)   -- I suspect OR, otherwise I think you'd see two unnested subquery and a pair of hash join.

             

            You'll notice that operation 15 reports 142K starts - which is the right ballpark for reporting 290K summary lines if that subquery runs parallel 2.

             

            This behaviour is a bug - and I may have written about it before - that appeared then disappeared in a relatively recent version of Oracle. I'll see if I can find the note.

             

            Regards

            Jonathan Lewis

            • 3. Re: Slaves allocated in the sql monitor report for parallel query-
              Bhavani Dhulipalla

              Hi Andrew -Thank you for the help -

               

              Version is 12.1 and below is the SQL TEXT -

               

              UPDATE pgn_scrpt_tmp_tbl scrpt_tbl
              SET
                  scrpt_tbl.rx_nbr = NULL,
                  scrpt_tbl.days_supply_qty = 0
              WHERE
              scrpt_tbl.rowid IN (
                  SELECT
                      ROWID row_id
                  FROM
                      pgn_scrpt_tmp_tbl
                  WHERE
                          pgn_clas IS NULL
                      AND (
                              EXISTS (
                                  SELECT
                                      1
                                  FROM
                                      pci_vw.rule_config_file_parm config
                                  WHERE
                                          config.config_file_id = 101
                                      AND
                                          config.config_parm1_id = 1001
                                      AND
                                          config.config_parm1_val = rxc_pat_id
                                      AND
                                          config.curr_ind = 'Y'
                              )
                          OR
                              EXISTS (
                                  SELECT
                                      1
                                  FROM
                                      pci_vw.rule_config_file_parm config
                                  WHERE
                                          config.config_file_id = 104
                                      AND
                                          config.config_parm1_id = 1003
                                      AND
                                          config.config_parm1_val = store_nbr
                                      AND
                                          config.curr_ind = 'Y'
                              )
                          OR
                              dea BETWEEN 2 AND 5
                          OR
                              trunc(months_between(
                                  TO_DATE('06012019','MMDDYYYY'),
                                  trunc(birth_dt)
                              ) / 12) <= 18
                          OR
                              trunc(months_between(
                                  TO_DATE('06012019','MMDDYYYY'),
                                  trunc(birth_dt)
                              ) / 12) >= 120
                      )
              )
              

               

               

              Thanks

              Bhavani

              • 4. Re: Slaves allocated in the sql monitor report for parallel query-
                Bhavani Dhulipalla

                hi Jonathon --Thank you for helping me with this request -

                 

                The Version is 12.1 and I just updated the SQL_TEXT as well -

                 

                we want to find the Actual slaves allocated for all the application query's  and I was basically using the SQL Monitor report Allocated Slaves section to find it  -

                 

                The Reason why we want to find Actual slaves:

                 

                we are Planning to use the DBRM to limit the DOP for the application and also want to Limit the Application only to 3 nodes where as the same application is using 8 nodes currently -That means the parallel query's are also limited to only 3 nodes despite the setting of parallel_force_local to TRUE Value  

                 

                The parallel_max_server value is 204 currently and application currently can use 8*204 parallel slaves if it wants -but after the DBRM since we are limiting to only 3 nodes ,it can only use 3*204 slaves --

                 

                So we want to see current utilization of parallel slaves and see if we can increase the value of parallel_max_servers  if required -

                 

                Question:

                 

                Is SQL Monitor report is reliable way of Finding the Slaves allocated?

                 

                is there any other best method to do this?

                 

                 

                Thanks

                Bhavani

                • 5. Re: Slaves allocated in the sql monitor report for parallel query-
                  Jonathan Lewis

                  Question:

                  Is SQL Monitor report is reliable way of Finding the Slaves allocated?

                   

                  Clearly it is not reliable since you've said your maximum possible number of PX processes is 8 * 204 and the report says that is has allocated 290,084 which is rather larger than the limit.  On the other hand I have explained that the report can count the number of processes once for every execution of the subquery, so that would explain the extreme excess, and it would be something that you want to investigate and modify the code to avoid since allocation and de-allocation of the same small set of slaves MIGHT REALLY be happening and increasing the workload dramatically.

                   

                  is there any other best method to do this [find number of slaves allocated] ?

                  I suspect there is no method that will guarantee to give the right answer in all cases - every query with multiple DFO trees probably needs to be examined individually and watched while it is running if you want to find the maximum number of parallel servers sets (and slaves) it ever has allocated. There is the report_sql_monitor output which has a few holes, there is the v$pq_tqstat view (after you run a query) which has a few holes - both improve with version; and every time you upgrade you find that Oracle has enhanced parallel query processing to reduce resouse wastage.  (You don't say whether your version of Oracle is 12.1.0.1 or 12.1.0.2 - but the plan suggests it might be 12.1.0.1 - and in 12.2.0.1 you might find that Oracle either does some clever tricks with sub query unnesting or uses "single server  parallel access" or "expression evaluation" to eliminate the 2nd and 3rd DFO tress entirely).

                   

                  Possibly counting the number of p0xx lines in the Parallel Execution Details section would be accurate enough - though I think in 11.2.0.4 I managed to get a report with thousands of lines (matched the value of Servers Allocated.

                   

                  Regards

                  Jonathan Lewis

                  1 person found this helpful
                  • 6. Re: Slaves allocated in the sql monitor report for parallel query-
                    AndrewSayer

                    Bhavani Dhulipalla wrote:

                     

                    Hi Andrew -Thank you for the help -

                     

                    Version is 12.1 and below is the SQL TEXT -

                     

                    1. UPDATEpgn_scrpt_tmp_tblscrpt_tbl
                    2. SET
                    3. scrpt_tbl.rx_nbr=NULL,
                    4. scrpt_tbl.days_supply_qty=0
                    5. WHERE
                    6. scrpt_tbl.rowidIN(
                    7. SELECT
                    8. ROWIDrow_id
                    9. FROM
                    10. pgn_scrpt_tmp_tbl
                    11. WHERE
                    12. pgn_clasISNULL
                    13. AND(
                    14. EXISTS(
                    15. SELECT
                    16. 1
                    17. FROM
                    18. pci_vw.rule_config_file_parmconfig
                    19. WHERE
                    20. config.config_file_id=101
                    21. AND
                    22. config.config_parm1_id=1001
                    23. AND
                    24. config.config_parm1_val=rxc_pat_id
                    25. AND
                    26. config.curr_ind='Y'
                    27. )
                    28. OR
                    29. EXISTS(
                    30. SELECT
                    31. 1
                    32. FROM
                    33. pci_vw.rule_config_file_parmconfig
                    34. WHERE
                    35. config.config_file_id=104
                    36. AND
                    37. config.config_parm1_id=1003
                    38. AND
                    39. config.config_parm1_val=store_nbr
                    40. AND
                    41. config.curr_ind='Y'
                    42. )
                    43. OR
                    44. deaBETWEEN2AND5
                    45. OR
                    46. trunc(months_between(
                    47. TO_DATE('06012019','MMDDYYYY'),
                    48. trunc(birth_dt)
                    49. )/12)<=18
                    50. OR
                    51. trunc(months_between(
                    52. TO_DATE('06012019','MMDDYYYY'),
                    53. trunc(birth_dt)
                    54. )/12)>=120
                    55. )
                    56. )

                     

                     

                    Thanks

                    Bhavani

                    You would probably get away with running this with much fewer DOP if you rewrote those ORs. Either you could do something like 3 separate deletes:

                     

                    UPDATE pgn_scrpt_tmp_tbl scrpt_tbl

                    SET

                        scrpt_tbl.rx_nbr = NULL,

                        scrpt_tbl.days_supply_qty = 0

                    WHERE  pgn_clas IS NULL

                    AND    EXISTS (  SELECT

                                         1

                                     FROM

                                         pci_vw.rule_config_file_parm config

                                     WHERE

                                             config.config_file_id = 101

                                         AND

                                             config.config_parm1_id = 1001

                                         AND

                                             config.config_parm1_val = rxc_pat_id

                                         AND

                                             config.curr_ind = 'Y'

                                 )

                     

                     

                    UPDATE pgn_scrpt_tmp_tbl scrpt_tbl

                    SET

                        scrpt_tbl.rx_nbr = NULL,

                        scrpt_tbl.days_supply_qty = 0

                    WHERE  pgn_clas IS NULL

                    AND    EXISTS (

                                   SELECT

                                       1

                                   FROM

                                       pci_vw.rule_config_file_parm config

                                   WHERE

                                           config.config_file_id = 104

                                       AND

                                           config.config_parm1_id = 1003

                                       AND

                                           config.config_parm1_val = store_nbr

                                       AND

                                           config.curr_ind = 'Y'

                               )

                     

                    UPDATE pgn_scrpt_tmp_tbl scrpt_tbl

                    SET

                        scrpt_tbl.rx_nbr = NULL,

                        scrpt_tbl.days_supply_qty = 0

                    WHERE  pgn_clas IS NULL

                    AND    (

                               dea BETWEEN 2 AND 5

                           OR

                               trunc(months_between(

                                   TO_DATE('06012019','MMDDYYYY'),

                                   trunc(birth_dt)

                               ) / 12) <= 18

                           OR

                               trunc(months_between(

                                   TO_DATE('06012019','MMDDYYYY'),

                                   trunc(birth_dt)

                               ) / 12) >= 120

                    )

                     

                    Or you could try to use union all in the exists filters. Those changes should allow a hash join to be used for the existance check rather than having to execute them once per row (which just won't scale).

                    1 person found this helpful