8 Replies Latest reply: Jul 19, 2010 2:53 PM by Timur Akhmadeev RSS

    PQ_DISTRIBUTE question

    OraDBA02
      select *from v$version;
      
      BANNER
      --------------------------------------------------------------------------------
      Oracle Database 11g Enterprise Edition Release 11.1.0.7.0 - 64bit Production
      PL/SQL Release 11.1.0.7.0 - Production
      CORE    11.1.0.7.0      Production
      TNS for Linux: Version 11.1.0.7.0 - Production
      NLSRTL Version 11.1.0.7.0 - Production
      I am trying to understand PQ_DISTRIBUTION method. I am doing some test and stuck in interpreting execution plan when it comes to PQ_DISTRIBUTION.

      CASE-1 Without any PQ_DISTRIBUTE hint

      AE and JEL tables are monthly partitioned on ACCOUNTING_DATE column.
      set autot traceonly
      select *
                                from journal_entry_lines jel,
                                     acctg_events ae
                                     where ae.acctg_event_id = jel.acctg_event_id
                                     and ae.client_code = 'CAMPS'
                                     and jel.accounting_date >= to_date('06/30/2010', 'mm/dd/yyyy')
                                     and jel.accounting_date < to_date('06/30/2010', 'mm/dd/yyyy') + 1
                                     and jel.set_of_books = '222'
                                     and jel.je_line_detail_table = 2
                                     and jel.gl_batch_id <> -10
      /     
      
      870650 rows selected.
      Elapsed: 00:13:26.73
      Plan hash value: 3439450812
      --------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      --------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT        |                     |   842K|   271M|   524K  (2)| 01:44:56 |       |       |        |      |            |
      |   1 |  PX COORDINATOR         |                     |       |       |            |          |       |       |        |      |            |
      |   2 |   PX SEND QC (RANDOM)   | :TQ10002            |   842K|   271M|   524K  (2)| 01:44:56 |       |       |  Q1,02 | P->S | QC (RAND)  |
      |*  3 |    HASH JOIN BUFFERED   |                     |   842K|   271M|   524K  (2)| 01:44:56 |       |       |  Q1,02 | PCWP |            |
      |   4 |     PX RECEIVE          |                     |   839K|   175M|  7032   (1)| 00:01:25 |       |       |  Q1,02 | PCWP |            |
      |   5 |      PX SEND HASH       | :TQ10000            |   839K|   175M|  7032   (1)| 00:01:25 |       |       |  Q1,00 | P->P | HASH       |
      |   6 |       PX BLOCK ITERATOR |                     |   839K|   175M|  7032   (1)| 00:01:25 |   129 |   129 |  Q1,00 | PCWC |            |
      |*  7 |        TABLE ACCESS FULL| JOURNAL_ENTRY_LINES |   839K|   175M|  7032   (1)| 00:01:25 |   129 |   129 |  Q1,00 | PCWP |            |
      |   8 |     PX RECEIVE          |                     |   751M|    83G|   516K  (2)| 01:43:24 |       |       |  Q1,02 | PCWP |            |
      |   9 |      PX SEND HASH       | :TQ10001            |   751M|    83G|   516K  (2)| 01:43:24 |       |       |  Q1,01 | P->P | HASH       |
      |  10 |       PX BLOCK ITERATOR |                     |   751M|    83G|   516K  (2)| 01:43:24 |     1 |    80 |  Q1,01 | PCWC |            |
      |* 11 |        TABLE ACCESS FULL| ACCTG_EVENTS        |   751M|    83G|   516K  (2)| 01:43:24 |     1 |    80 |  Q1,01 | PCWP |            |
      --------------------------------------------------------------------------------------------------------------------------------------------
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access("AE"."ACCTG_EVENT_ID"="JEL"."ACCTG_EVENT_ID")
         7 - filter("JEL"."JE_LINE_DETAIL_TABLE"=2 AND "JEL"."SET_OF_BOOKS"='222' AND "JEL"."GL_BATCH_ID"<>(-10))
        11 - filter("AE"."CLIENT_CODE"='CAMPS')
      Statistics
      ----------------------------------------------------------
               77  recursive calls
                3  db block gets
         19578304  consistent gets
         19436090  physical reads
              636  redo size
        228785499  bytes sent via SQL*Net to client
             2252  bytes received via SQL*Net from client
              176  SQL*Net roundtrips to/from client
               80  sorts (memory)
                0  sorts (disk)
           870650  rows processed
      
      set autot off
      SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
      FROM v$pq_tqstat
      ORDER BY dfo_number DESC, tq_id, server_type DESC, process;
      
      DFO_NUMBER      TQ_ID SERVER_TYPE                    PROCESS                          NUM_ROWS      BYTES
      ---------- ---------- ------------------------------ ------------------------------ ---------- ----------
               1          0 Producer                       P008                               118401   34188427
                            Producer                       P009                                89973   26044552
                            Producer                       P010                               104927   30341606
                            Producer                       P011                               150535   43642705
                            Producer                       P012                               110959   31951898
                            Producer                       P013                               110489   32026211
                            Producer                       P014                                69530   20011267
                            Producer                       P015                               115836   33428684
                            Consumer                       P000                               108546   31366540
                            Consumer                       P001                               108490   31355412
                            Consumer                       P002                               108846   31461078
                            Consumer                       P003                               109236   31569624
                            Consumer                       P004                               108250   31282750
                            Consumer                       P005                               108718   31422268
                            Consumer                       P006                               109394   31619070
                            Consumer                       P007                               109170   31558608
                          1 Producer                       P008                             91947187 1489054585
                            Producer                       P009                             89830033 1605194494
                            Producer                       P010                             91781613 1738341746
                            Producer                       P011                             90123858 1335132558
                            Producer                       P012                            112915823 3097474621
                            Producer                       P013                             92374010 1941680072
                            Producer                       P014                             85984550  996237091
                            Producer                       P015                             84574176 1469162033
                            Consumer                       P000                             92437241 1708225118
                            Consumer                       P001                             92451080 1710462005
                            Consumer                       P002                             92430918 1707653178
                            Consumer                       P003                             92455013 1711555216
                            Consumer                       P004                             92442948 1709292497
                            Consumer                       P005                             92453211 1710595887
                            Consumer                       P006                             92438263 1708654596
                            Consumer                       P007                             92422576 1705838703
                          2 Producer                       P000                               108546   52478106
                            Producer                       P001                               108490   52461404
                            Producer                       P002                               108846   52638700
                            Producer                       P003                               109236   52818826
                            Producer                       P004                               108250   52338072
                            Producer                       P005                               108718   52572952
                            Producer                       P006                               109394   52903004
                            Producer                       P007                               109170   52802748
                            Consumer                       QC                                 870650  421013812
      CASE-2 With pq_distribute(ae,BROADCAST,NONE) hint (Elapsed time is almost reduce to half)
      set autot traceonly
      select /*+ pq_distribute(ae,BROADCAST,NONE) */   *
                                from journal_entry_lines jel,
                                     acctg_events ae
                                     where ae.acctg_event_id = jel.acctg_event_id
                                     and ae.client_code = 'CAMPS'
                                     and jel.accounting_date >= to_date('06/30/2010', 'mm/dd/yyyy')
                                     and jel.accounting_date < to_date('06/30/2010', 'mm/dd/yyyy') + 1
                                     and jel.set_of_books = '222'
                                     and jel.je_line_detail_table = 2
                                     and jel.gl_batch_id <> -10
      /     
      870650 rows selected.
      Elapsed: 00:06:59.82
      Plan hash value: 810058038
      
      --------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
      --------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT        |                     |   842K|   271M|   524K  (2)| 01:44:56 |       |       |        |      |            |
      |   1 |  PX COORDINATOR         |                     |       |       |            |          |       |       |        |      |            |
      |   2 |   PX SEND QC (RANDOM)   | :TQ10001            |   842K|   271M|   524K  (2)| 01:44:56 |       |       |  Q1,01 | P->S | QC (RAND)  |
      |*  3 |    HASH JOIN            |                     |   842K|   271M|   524K  (2)| 01:44:56 |       |       |  Q1,01 | PCWP |            |
      |   4 |     PX RECEIVE          |                     |   839K|   175M|  7032   (1)| 00:01:25 |       |       |  Q1,01 | PCWP |            |
      |   5 |      PX SEND BROADCAST  | :TQ10000            |   839K|   175M|  7032   (1)| 00:01:25 |       |       |  Q1,00 | P->P | BROADCAST  |
      |   6 |       PX BLOCK ITERATOR |                     |   839K|   175M|  7032   (1)| 00:01:25 |   129 |   129 |  Q1,00 | PCWC |            |
      |*  7 |        TABLE ACCESS FULL| JOURNAL_ENTRY_LINES |   839K|   175M|  7032   (1)| 00:01:25 |   129 |   129 |  Q1,00 | PCWP |            |
      |   8 |     PX BLOCK ITERATOR   |                     |   751M|    83G|   516K  (2)| 01:43:24 |     1 |    80 |  Q1,01 | PCWC |            |
      |*  9 |      TABLE ACCESS FULL  | ACCTG_EVENTS        |   751M|    83G|   516K  (2)| 01:43:24 |     1 |    80 |  Q1,01 | PCWP |            |
      --------------------------------------------------------------------------------------------------------------------------------------------
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - access("AE"."ACCTG_EVENT_ID"="JEL"."ACCTG_EVENT_ID")
         7 - filter("JEL"."JE_LINE_DETAIL_TABLE"=2 AND "JEL"."SET_OF_BOOKS"='222' AND "JEL"."GL_BATCH_ID"<>(-10))
         9 - filter("AE"."CLIENT_CODE"='CAMPS')
      
      Elapsed: 00:06:59.82
      Statistics
      ----------------------------------------------------------
               70  recursive calls
                3  db block gets
         19575169  consistent gets
         19447517  physical reads
              592  redo size
        220287857  bytes sent via SQL*Net to client
             2252  bytes received via SQL*Net from client
              176  SQL*Net roundtrips to/from client
               80  sorts (memory)
                0  sorts (disk)
           870650  rows processed
      
      set autot off
      
      SELECT dfo_number, tq_id, server_type, process, num_rows, bytes
      FROM v$pq_tqstat
      ORDER BY dfo_number DESC, tq_id, server_type DESC, process;
      DFO_NUMBER      TQ_ID SERVER_TYPE                    PROCESS                          NUM_ROWS      BYTES
      ---------- ---------- ------------------------------ ------------------------------ ---------- ----------
               1          0 Producer                       P042                               937293  270163229
                            Producer                       P072                               985236  284172630
                            Producer                       P073                               819154  236780264
                            Producer                       P074                               812707  236511618
                            Producer                       P075                               840084  242875885
                            Producer                       P076                               841967  243613230
                            Producer                       P077                               858109  247325386
                            Consumer                       P034                               870650  251634606
                            Consumer                       P035                               870650  251634606
                            Consumer                       P036                               870650  251634606
                            Consumer                       P037                               870650  251634606
                            Consumer                       P039                               870650  251634606
                            Consumer                       P040                               870650  251634606
                            Consumer                       P041                               870650  251634606
                          1 Producer                       P034                                    0         24
                            Producer                       P035                               456252  220576660
                            Producer                       P036                                    0         24
                            Producer                       P037                                 2174    1098406
                            Producer                       P039                               323218  156364802
                            Producer                       P040                                89006   42974232
                            Producer                       P041                                    0         24
                            Consumer                       QC                                 870650  421014172
      Can i ask why there is a drastic difference in elapsed time just by changing pq distribution method to BROADCAST ? Is there any way to know which slave set is scanning which table ?

      Edited by: OraDBA02 on Jul 13, 2010 3:33 AM

      Edited by: OraDBA02 on Jul 13, 2010 3:35 AM
        • 1. Re: PQ_DISTRIBUTE question
          657203
          OraDBA02 wrote:
          Can i ask why there is a drastic difference in elapsed time just by changing pq distribution method to BROADCAST ? Is there any way to know which slave set is scanning which table ?
          Pls, check TQ column in execution plan.

          As you see,
          ACCTG_EVENTS was scanned by slave set : Q1,01
          JOURNAL_ENTRY_LINES was scanned by slave set : Q1,00

          Aslo, hash join was done by Q1,01.

          Regards,
          Teymur Hajiyev
          Oracle 10g Certified Master
          http://teymur-hajiyev.blogspot.com
          http://dba.az
          • 2. Re: PQ_DISTRIBUTE question
            OraDBA02
            Hi Teymur,

            Yes...i observed that. So..in case-2,Q1,01 is having two different roles at different time...First it is behaving as PRODUCER who scans AE table using 8 slaves and then during HASH JOIN it is again a PRODUCER.
            My question here is : After producer slave set Q1,01 scans AE, to whom it sends those rows (i mean who is consumer here)? Q1,00 is also scanning JEL but i dont know to whom it sends (BROADCAST) those rows ? According to execution plan, it is sending all rows (jel rows) to table queue :TQ10000 using BROADCAST PQ distribution method and Q1,01 is its receiver !

            Is that something like, producer slave set Q1,01 is sending all AE records directly to QC ?
            Why BROADCAST distribution method is improving elapsed time ?
            • 3. Re: PQ_DISTRIBUTE question
              736705
              Hi, OraDBA02.

              Would you like to supply following additional informations to clarify the row distribution result?
              select count(*) from acctg_events;
              
              select count(*) from acctg_events where client_code = 'CAMPS';
              
              select count(*) from journal_entry_lines;
              
              select count(*) from journal_entry_lines 
              where 
              accounting_date >= to_date('06/30/2010', 'mm/dd/yyyy')    
              and accounting_date < to_date('06/30/2010', 'mm/dd/yyyy') + 1 
              and set_of_books = '222'                                      
              and je_line_detail_table = 2                                  
              and gl_batch_id  -10;
              • 4. Re: PQ_DISTRIBUTE question
                OraDBA02
                Sure...
                select count(*) from acctg_events;
                COUNT(*)
                ----------
                1879551648
                
                select count(*) from acctg_events where client_code = 'CAMPS';
                  COUNT(*)
                ----------
                 741344277
                 
                select count(*) from journal_entry_lines;
                COUNT(*)
                ----------
                4634556418
                 
                select count(*) from journal_entry_lines 
                where 
                accounting_date >= to_date('06/30/2010', 'mm/dd/yyyy')    
                and accounting_date < to_date('06/30/2010', 'mm/dd/yyyy') + 1 
                and set_of_books = '222'                                      
                and je_line_detail_table = 2                                  
                and gl_batch_id <> -10
                ;
                  COUNT(*)
                ----------
                    870650
                
                NOTE: There was a type in SQL i gave earlier...in last sql, last filter is : and gl_batch_id <> -10
                • 5. Re: PQ_DISTRIBUTE question
                  Jonathan Lewis
                  Teymur Hajiyev wrote:
                  Pls, check TQ column in execution plan.

                  As you see,
                  ACCTG_EVENTS was scanned by slave set : Q1,01
                  JOURNAL_ENTRY_LINES was scanned by slave set : Q1,00
                  Teymur,

                  q1,01 is not a process, it is a "pipeline", or "virtual table", or "table queue".
                  One set of processes writes data into the table queue for the other set of processes to read.

                  Regards
                  Jonathan Lewis
                  • 6. Re: PQ_DISTRIBUTE question
                    Jonathan Lewis
                    OraDBA02 wrote:

                    I am trying to understand PQ_DISTRIBUTION method. I am doing some test and stuck in interpreting execution plan when it comes to PQ_DISTRIBUTION.
                    Plan hash value: 3439450812
                    --------------------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                    --------------------------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT        |                     |   842K|   271M|   524K  (2)| 01:44:56 |       |       |        |      |            |
                    |   1 |  PX COORDINATOR         |                     |       |       |            |          |       |       |        |      |            |
                    |   2 |   PX SEND QC (RANDOM)   | :TQ10002            |   842K|   271M|   524K  (2)| 01:44:56 |       |       |  Q1,02 | P->S | QC (RAND)  |
                    |*  3 |    HASH JOIN BUFFERED   |                     |   842K|   271M|   524K  (2)| 01:44:56 |       |       |  Q1,02 | PCWP |            |
                    |   4 |     PX RECEIVE          |                     |   839K|   175M|  7032   (1)| 00:01:25 |       |       |  Q1,02 | PCWP |            |
                    |   5 |      PX SEND HASH       | :TQ10000            |   839K|   175M|  7032   (1)| 00:01:25 |       |       |  Q1,00 | P->P | HASH       |
                    |   6 |       PX BLOCK ITERATOR |                     |   839K|   175M|  7032   (1)| 00:01:25 |   129 |   129 |  Q1,00 | PCWC |            |
                    |*  7 |        TABLE ACCESS FULL| JOURNAL_ENTRY_LINES |   839K|   175M|  7032   (1)| 00:01:25 |   129 |   129 |  Q1,00 | PCWP |            |
                    |   8 |     PX RECEIVE          |                     |   751M|    83G|   516K  (2)| 01:43:24 |       |       |  Q1,02 | PCWP |            |
                    |   9 |      PX SEND HASH       | :TQ10001            |   751M|    83G|   516K  (2)| 01:43:24 |       |       |  Q1,01 | P->P | HASH       |
                    |  10 |       PX BLOCK ITERATOR |                     |   751M|    83G|   516K  (2)| 01:43:24 |     1 |    80 |  Q1,01 | PCWC |            |
                    |* 11 |        TABLE ACCESS FULL| ACCTG_EVENTS        |   751M|    83G|   516K  (2)| 01:43:24 |     1 |    80 |  Q1,01 | PCWP |            |
                    --------------------------------------------------------------------------------------------------------------------------------------------
                    
                    DFO_NUMBER      TQ_ID SERVER_TYPE                    PROCESS                          NUM_ROWS      BYTES
                    ---------- ---------- ------------------------------ ------------------------------ ---------- ----------
                    1          0 Producer                       P008                               118401   34188427
                    Producer                       P009                                89973   26044552
                    Producer                       P010                               104927   30341606
                    Producer                       P011                               150535   43642705
                    Producer                       P012                               110959   31951898
                    Producer                       P013                               110489   32026211
                    Producer                       P014                                69530   20011267
                    Producer                       P015                               115836   33428684
                    Consumer                       P000                               108546   31366540
                    Consumer                       P001                               108490   31355412
                    Consumer                       P002                               108846   31461078
                    Consumer                       P003                               109236   31569624
                    Consumer                       P004                               108250   31282750
                    Consumer                       P005                               108718   31422268
                    Consumer                       P006                               109394   31619070
                    Consumer                       P007                               109170   31558608
                    1 Producer                       P008                             91947187 1489054585
                    Producer                       P009                             89830033 1605194494
                    Producer                       P010                             91781613 1738341746
                    Producer                       P011                             90123858 1335132558
                    Producer                       P012                            112915823 3097474621
                    Producer                       P013                             92374010 1941680072
                    Producer                       P014                             85984550  996237091
                    Producer                       P015                             84574176 1469162033
                    Consumer                       P000                             92437241 1708225118
                    Consumer                       P001                             92451080 1710462005
                    Consumer                       P002                             92430918 1707653178
                    Consumer                       P003                             92455013 1711555216
                    Consumer                       P004                             92442948 1709292497
                    Consumer                       P005                             92453211 1710595887
                    Consumer                       P006                             92438263 1708654596
                    Consumer                       P007                             92422576 1705838703
                    2 Producer                       P000                               108546   52478106
                    Producer                       P001                               108490   52461404
                    Producer                       P002                               108846   52638700
                    Producer                       P003                               109236   52818826
                    Producer                       P004                               108250   52338072
                    Producer                       P005                               108718   52572952
                    Producer                       P006                               109394   52903004
                    Producer                       P007                               109170   52802748
                    Consumer                       QC                                 870650  421013812
                    Order of execution:

                    Line 6, slave set 1 scans the journal_entry_lines and randomly distributes the data (by hashing on the join column) through table queue q1,0 to slave set 2. This is about 100,000 rows read from table and written to table queue by each process.

                    Line 3, slave set 2 bulids an in-memory hash table from the contents of virtual table q1,0. This is about 100,000 rows read from table queue by each process.

                    Line 11, slave set 1 scans the acctg_events and randomly distributes the data (by hashing on the join column) through table queue q1,1 to slave set 2. This is about 90,000,000 rows read from table and written to table queue by each process - this interprocess messaging is responsible for a lot of the extra time.

                    Line 3, slave set 2 reads the incoming data and performs the hash join, buffering the results until the result set is complete (this buffering is unnecessary - if it's really happening then I would call it a bug), then it passes the data to the query coordinator by writing it through table queue q1,2

                    --------------------------------------------------------------------------------------------------------------------------------------------
                    | Id  | Operation               | Name                | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                    --------------------------------------------------------------------------------------------------------------------------------------------
                    |   0 | SELECT STATEMENT        |                     |   842K|   271M|   524K  (2)| 01:44:56 |       |       |        |      |            |
                    |   1 |  PX COORDINATOR         |                     |       |       |            |          |       |       |        |      |            |
                    |   2 |   PX SEND QC (RANDOM)   | :TQ10001            |   842K|   271M|   524K  (2)| 01:44:56 |       |       |  Q1,01 | P->S | QC (RAND)  |
                    |*  3 |    HASH JOIN            |                     |   842K|   271M|   524K  (2)| 01:44:56 |       |       |  Q1,01 | PCWP |            |
                    |   4 |     PX RECEIVE          |                     |   839K|   175M|  7032   (1)| 00:01:25 |       |       |  Q1,01 | PCWP |            |
                    |   5 |      PX SEND BROADCAST  | :TQ10000            |   839K|   175M|  7032   (1)| 00:01:25 |       |       |  Q1,00 | P->P | BROADCAST  |
                    |   6 |       PX BLOCK ITERATOR |                     |   839K|   175M|  7032   (1)| 00:01:25 |   129 |   129 |  Q1,00 | PCWC |            |
                    |*  7 |        TABLE ACCESS FULL| JOURNAL_ENTRY_LINES |   839K|   175M|  7032   (1)| 00:01:25 |   129 |   129 |  Q1,00 | PCWP |            |
                    |   8 |     PX BLOCK ITERATOR   |                     |   751M|    83G|   516K  (2)| 01:43:24 |     1 |    80 |  Q1,01 | PCWC |            |
                    |*  9 |      TABLE ACCESS FULL  | ACCTG_EVENTS        |   751M|    83G|   516K  (2)| 01:43:24 |     1 |    80 |  Q1,01 | PCWP |            |
                    --------------------------------------------------------------------------------------------------------------------------------------------
                    
                    DFO_NUMBER      TQ_ID SERVER_TYPE                    PROCESS                          NUM_ROWS      BYTES
                    ---------- ---------- ------------------------------ ------------------------------ ---------- ----------
                    1          0 Producer                       P042                               937293  270163229
                    Producer                       P072                               985236  284172630
                    Producer                       P073                               819154  236780264
                    Producer                       P074                               812707  236511618
                    Producer                       P075                               840084  242875885
                    Producer                       P076                               841967  243613230
                    Producer                       P077                               858109  247325386
                    Consumer                       P034                               870650  251634606
                    Consumer                       P035                               870650  251634606
                    Consumer                       P036                               870650  251634606
                    Consumer                       P037                               870650  251634606
                    Consumer                       P039                               870650  251634606
                    Consumer                       P040                               870650  251634606
                    Consumer                       P041                               870650  251634606
                    1 Producer                       P034                                    0         24
                    Producer                       P035                               456252  220576660
                    Producer                       P036                                    0         24
                    Producer                       P037                                 2174    1098406
                    Producer                       P039                               323218  156364802
                    Producer                       P040                                89006   42974232
                    Producer                       P041                                    0         24
                    Consumer                       QC                                 870650  421014172
                    Order of processing:
                    Line 7: The first slave set reads the journal_entry_lines table and every slave passes every row it reads to every process in the second set of slaves, writing it to table queue q1,0. I am baffled by the report that every process in the first slave set read about 850,000 rows - they should have read about 110,000 rows each ()one eighth of the table). It is correct though that the second slave set should have received 850,000 rows because that is the intent of the broadcast.
                    Line 3: The second set of slaves builds the in-memory hash table.
                    Line 9: the SECOND set of slaves scans the acctg_events table
                    Line 3: .. and probes the in-memory hash table to perform the join, passing the results to the query coordinator by writing into queue table q1,1. The second set can do the scan and join because each process has a copy of the whole journal_entry_lines table.

                    The uneven distribution of rows in v$pq_tqstat is just luck - the data in the second table that could join to the first table was probably clustered in a few small areas of the second table, which happened to be in the granules allocate to just a few of the processes in the second slave set.
                    Can i ask why there is a drastic difference in elapsed time just by changing pq distribution method to BROADCAST ?
                    Mostly it's because the broadcast method allows the second set of slaves to the second table, rather than the first set of slaves reading it and distributing it to the second set.

                    See also: http://jonathanlewis.wordpress.com/2008/11/05/px-buffer/

                    Regards
                    Jonathan Lewis
                    • 7. Re: PQ_DISTRIBUTE question
                      OraDBA02
                      Hi Jonathan,

                      Perfect...My understanding was much similar to yours. Thanks for your detail explanation.
                      Many times, i observed that BROADCAST gives quick results (short elapsed time) with better utilization of #px slaves (As you pointed out that consumer already have broadcasted results,so they can directly perform joins with the producers).
                      Is there any way we can find what should be the ADEQUATE broadcast size? I even observed that if larger row source is broadcasted then, it will cause some kind of BUFFERED operation (HASH JOIN BUFFER Or BUFFER SORT) which ultimately spills up on TEMP tablespace causing lot of two-pass sorts.
                      Can 10104 trace be helpful ?

                      Can i ask ask you what role parallel_execution_message_size parameter play ? I have 6 GB PGA with 96 parallel_max_servers. My parallel_execution_message_size is set to 2152. I believe, it is undersized....
                      • 8. Re: PQ_DISTRIBUTE question
                        Timur Akhmadeev
                        OraDBA02 wrote:
                        I have 6 GB PGA with 96 parallel_max_servers. My parallel_execution_message_size is set to 2152. I believe, it is undersized....
                        Starting with 11.2 it defaults to 16K on most platforms, so it's most likely undersized in your case. Just don't forget about consequences of increasing PARALLEL_EXECUTION_MESSAGE_SIZE.