10 Replies Latest reply on Jul 12, 2010 9:48 AM by OraDBA02

    Two different HASH GROUP BY in execution plan

    OraDBA02
      Hi ALL;

      Oracle version
      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
      SQL

      select company_code, account_number, transaction_id,
      decode(transaction_id_type, 'CollectionID', 'SettlementGroupID', transaction_id_type) transaction_id_type,
      (last_day(to_date('04/21/2010','MM/DD/YYYY')) - min(z.accounting_date) ) age,sum(z.amount)
      from
      (
           select /*+ PARALLEL(use, 2) */    company_code,substr(account_number, 1, 5) account_number,transaction_id,
           decode(transaction_id_type, 'CollectionID', 'SettlementGroupID', transaction_id_type) transaction_id_type,use.amount,use.accounting_date
           from financials.unbalanced_subledger_entries use
           where use.accounting_date >= to_date('04/21/2010','MM/DD/YYYY')
           and use.accounting_date < to_date('04/21/2010','MM/DD/YYYY') + 1
      UNION ALL
           select /*+ PARALLEL(se, 2) */  company_code, substr(se.account_number, 1, 5) account_number,transaction_id,
           decode(transaction_id_type, 'CollectionID', 'SettlementGroupID', transaction_id_type) transaction_id_type,se.amount,se.accounting_date
           from financials.temp2_sl_snapshot_entries se,financials.account_numbers an
           where se.account_number = an.account_number
           and an.subledger_type in ('C', 'AC')
      ) z
      group by company_code,account_number,transaction_id,decode(transaction_id_type, 'CollectionID', 'SettlementGroupID', transaction_id_type)
      having abs(sum(z.amount)) >= 0.01
      explain plan
      Plan hash value: 1993777817
      
      --------------------------------------------------------------------------------------------------------------------------------------------
      | Id  | Operation                      | Name                         | Rows  | Bytes | Cost (%CPU)| Time     |    TQ  |IN-OUT| PQ Distrib |
      --------------------------------------------------------------------------------------------------------------------------------------------
      |   0 | SELECT STATEMENT               |                              |       |       | 76718 (100)|          |        |      |            |
      |   1 |  PX COORDINATOR                |                              |       |       |            |          |        |      |            |
      |   2 |   PX SEND QC (RANDOM)          | :TQ10002                     |    15M|  2055M| 76718   (2)| 00:15:21 |  Q1,02 | P->S | QC (RAND)  |
      |*  3 |    FILTER                      |                              |       |       |            |          |  Q1,02 | PCWC |            |
      |   4 |     HASH GROUP BY              |                              |    15M|  2055M| 76718   (2)| 00:15:21 |  Q1,02 | PCWP |            |
      |   5 |      PX RECEIVE                |                              |    15M|  2055M| 76718   (2)| 00:15:21 |  Q1,02 | PCWP |            |
      |   6 |       PX SEND HASH             | :TQ10001                     |    15M|  2055M| 76718   (2)| 00:15:21 |  Q1,01 | P->P | HASH       |
      |   7 |        HASH GROUP BY           |                              |    15M|  2055M| 76718   (2)| 00:15:21 |  Q1,01 | PCWP |            |
      |   8 |         VIEW                   |                              |    15M|  2055M| 76116   (1)| 00:15:14 |  Q1,01 | PCWP |            |
      |   9 |          UNION-ALL             |                              |       |       |            |          |  Q1,01 | PCWP |            |
      |  10 |           PX BLOCK ITERATOR    |                              |    11 |   539 |  1845   (1)| 00:00:23 |  Q1,01 | PCWC |            |
      |* 11 |            TABLE ACCESS FULL   | UNBALANCED_SUBLEDGER_ENTRIES |    11 |   539 |  1845   (1)| 00:00:23 |  Q1,01 | PCWP |            |
      |* 12 |           HASH JOIN            |                              |    15M|   928M| 74270   (1)| 00:14:52 |  Q1,01 | PCWP |            |
      |  13 |            BUFFER SORT         |                              |       |       |            |          |  Q1,01 | PCWC |            |
      |  14 |             PX RECEIVE         |                              |    21 |   210 |     2   (0)| 00:00:01 |  Q1,01 | PCWP |            |
      |  15 |              PX SEND BROADCAST | :TQ10000                     |    21 |   210 |     2   (0)| 00:00:01 |        | S->P | BROADCAST  |
      |* 16 |               TABLE ACCESS FULL| ACCOUNT_NUMBERS              |    21 |   210 |     2   (0)| 00:00:01 |        |      |            |
      |  17 |            PX BLOCK ITERATOR   |                              |    25M|  1250M| 74183   (1)| 00:14:51 |  Q1,01 | PCWC |            |
      |* 18 |             TABLE ACCESS FULL  | TEMP2_SL_SNAPSHOT_ENTRIES    |    25M|  1250M| 74183   (1)| 00:14:51 |  Q1,01 | PCWP |            |
      --------------------------------------------------------------------------------------------------------------------------------------------
      Predicate Information (identified by operation id):
      ---------------------------------------------------
      
         3 - filter(ABS(SUM(SYS_OP_CSR(SYS_OP_MSR(SUM("Z"."AMOUNT"),MIN("Z"."ACCOUNTING_DATE")),0)))>=.01)
        11 - access(:Z>=:Z AND :Z<=:Z)
             filter(("USE"."ACCOUNTING_DATE"<TO_DATE(' 2010-04-22 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                    "USE"."ACCOUNTING_DATE">=TO_DATE(' 2010-04-21 00:00:00', 'syyyy-mm-dd hh24:mi:ss')))
        12 - access("SE"."ACCOUNT_NUMBER"="AN"."ACCOUNT_NUMBER")
        16 - filter(("AN"."SUBLEDGER_TYPE"='AC' OR "AN"."SUBLEDGER_TYPE"='C'))
        18 - access(:Z>=:Z AND :Z<=:Z)
      I have few doubts regarding this execution plan and i am sure my questions would get answered here.

      Q-1: Why am i getting two different HASH GROUP BY operations (Operation id 4 & 7) even though there is only a single GROUP BY clause ? Is that due to UNION ALL operator that is merging two different row sources and HASH GROUP BY is being applied on both of them individually ?
      Q-2: What does 'BUFFER SORT' (Operation id 13) indicate ? Some time i got this operation and sometime i am not. For some other queries, i have observing around 10GB TEMP space and high cost against this operation. So just curious about whether it is really helpful ? if no, how to avoid that ?
      Q-3: Under PREDICATE Section, what does step 18 suggest ? I am not using any filter like this ? access(:Z>=:Z AND :Z<=:Z)
        • 1. Re: Two different HASH GROUP BY in execution plan
          657203
          According Oracle My Support, there is BUG which is related "HASH GROUP BY" feature and excessive TEMP usage in *11.1.0.7*. You should check this before investigating further.


          Bug 7716219 HASH GROUP BY can use excessive TEMP space: ID 7716219.8
          • 2. Re: Two different HASH GROUP BY in execution plan
            OraDBA02
            Thanks Tymur.

            I could see that HASH GROUP BY is replaced by old sort algorithm 'SORT GROUP BY'. But it is using same TEMP space as what HASH GROUP BY was using. Query elapsed time is also same in both the cases.
            • 3. Re: Two different HASH GROUP BY in execution plan
              OraDBA02
              Hi Guys,

              Can i request for reply on this thread ?
              I posted on OTN with a great hope to have reply. I know someone would definitely reply, i m getting impatient due to this burning issue ;-)
              Apologies if i put in in complex manner....
              • 4. Re: Two different HASH GROUP BY in execution plan
                389403
                Hi,

                I looked to Your query and Your questions, problem is that You don't familiar with Parallel Execution concepts. Please visit to this link to official oracle docs to learn more
                http://download.oracle.com/docs/cd/B19306_01/server.102/b14223/usingpe.htm#DWHSG024

                Exactly about Your query. 2 HASH GROUP BY entries in the Plan. When Your query runs please check the v$px_session view, you will see that there is more parallel slaves that You expected,
                because for each parallel degree there will be 2 slaves one for making access to table (producer server) and second for logical operations like a GROUP BY (consumer server). Each consumer server process makes his portion of GROUP BY it is your first GROUP BY in the plan and then one general GROUP BY that groups overall result. About buffer sorts by my opinion it is because of your account_numbers table is accessed serially (I see it in the plan) but second table in a join accessed in parallel causing to make buffer sorts, this memory buffers is related to parallel slave processes, each of them can have 2-3 buffers and more in the RAC configurations. This buffers is used for the internal communications between producers and consumers. In case when one of the tables in a join is accessed serially then buffers can be used for additional operations to merge output from parallel execution and serial scan output. Change the parallel degree for account_numbers to avoid this, set it to 2 as for second table in join.
                Predicate is the internal partitioning of tables for parallel execution.

                And all of this is expected behavior.

                Best regards!
                • 5. Re: Two different HASH GROUP BY in execution plan
                  OraDBA02
                  Hi Aychin,

                  Thanks for your notes. Please accept my apology for delay in reply.
                  I still got some doubt on BUFFER SORT.
                  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  | Id  | Operation                                | Name                          | Rows  | Bytes | Cost (%CPU)| Time     | Pstart| Pstop |    TQ  |IN-OUT| PQ Distrib |
                  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  |   0 | SELECT STATEMENT                         |                               |     1 |   196 | 33174   (1)| 00:06:39 |       |       |        |      |            |
                  |   1 |  PX COORDINATOR                          |                               |       |       |            |          |       |       |        |      |            |
                  |   2 |   PX SEND QC (RANDOM)                    | :TQ10002                      |     1 |   196 | 33174   (1)| 00:06:39 |       |       |  Q1,02 | P->S | QC (RAND)  |
                  |   3 |    HASH GROUP BY                         |                               |     1 |   196 | 33174   (1)| 00:06:39 |       |       |  Q1,02 | PCWP |            |
                  |   4 |     PX RECEIVE                           |                               |     1 |   196 | 33173   (1)| 00:06:39 |       |       |  Q1,02 | PCWP |            |
                  |   5 |      PX SEND HASH                        | :TQ10001                      |     1 |   196 | 33173   (1)| 00:06:39 |       |       |  Q1,01 | P->P | HASH       |
                  |*  6 |       HASH JOIN                          |                               |     1 |   196 | 33173   (1)| 00:06:39 |       |       |  Q1,01 | PCWP |            |
                  |*  7 |        TABLE ACCESS BY GLOBAL INDEX ROWID| MATCHED_INVOICE_ITEMS         |    18 |  1152 |     1   (0)| 00:00:01 | ROWID | ROWID |  Q1,01 | PCWC |            |
                  |   8 |         NESTED LOOPS                     |                               |   498K|    39M| 33169   (1)| 00:06:39 |       |       |  Q1,01 | PCWP |            |
                  |   9 |          PX BLOCK ITERATOR               |                               | 28036 |   520K|  5846   (3)| 00:01:11 |     1 |   127 |  Q1,01 | PCWC |            |
                  |* 10 |           TABLE ACCESS FULL              | DISTRIBUTOR_INVOICES          | 28036 |   520K|  5846   (3)| 00:01:11 |     1 |   127 |  Q1,01 | PCWP |            |
                  |* 11 |          INDEX RANGE SCAN                | I_MII_INVOICE_ID_ITEM_ID      |    35 |       |     0   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
                  |  12 |        BUFFER SORT                       |                               |       |       |            |          |       |       |  Q1,01 | PCWC |            |
                  |  13 |         PX RECEIVE                       |                               |     1 |   113 |     2   (0)| 00:00:01 |       |       |  Q1,01 | PCWP |            |
                  |  14 |          PX SEND BROADCAST               | :TQ10000                      |     1 |   113 |     2   (0)| 00:00:01 |       |       |  Q1,00 | P->P | BROADCAST  |
                  |  15 |           PX BLOCK ITERATOR              |                               |     1 |   113 |     2   (0)| 00:00:01 |     1 |     4 |  Q1,00 | PCWC |            |
                  |* 16 |            TABLE ACCESS FULL             | O_RECEIVED_NOT_INVOICED_ITEMS |     1 |   113 |     2   (0)| 00:00:01 |   549 |   552 |  Q1,00 | PCWP |            |
                  -----------------------------------------------------------------------------------------------------------------------------------------------------------------------
                  
                  Predicate Information (identified by operation id):
                  ---------------------------------------------------
                  
                     6 - access("RNII"."LEGAL_ENTITY_ID"="MII"."LEGAL_ENTITY_ID" AND "RNII"."ORDER_ID"="MII"."DISTRIBUTOR_ORDER_ID" AND
                                "RNII"."SHIPMENT_ID"="MII"."DISTRIBUTOR_SHIPMENT_ID" AND "RNII"."ASIN"="MII"."ASIN")
                     7 - filter("MII"."LEGAL_ENTITY_ID"=101)
                    10 - filter("DI"."GL_DATE">=TO_DATE('2010-04-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "DI"."LEGAL_ENTITY_ID"=101 AND "DI"."INVOICE_STATUS"='4' AND
                                "DI"."GL_DATE"<TO_DATE('2010-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss'))
                    11 - access("MII"."INVOICE_ID"="DI"."INVOICE_ID")
                    16 - filter("RNII"."SNAPSHOT_DAY"=TO_DATE('2010-05-01 00:00:00', 'yyyy-mm-dd hh24:mi:ss') AND "RNII"."LEGAL_ENTITY_ID"=103 AND.
                                "RNII"."RNII_SOURCE_NAME"='distributor_shipment_item')
                  Here, you can see that O_RECEIVED_NOT_INVOICED_ITEMS table is scanned by px slaves and still explain plan is showing BUFFER SORT operation.

                  I think, if HASH table creation is spilled up on disk (TEMP TBS) than, explain plan will show up BUFFER SORT.
                  Correct me if i am wrong. (I am just assuming this from couple of explain plan i have seen)
                  • 6. Re: Two different HASH GROUP BY in execution plan
                    389403
                    Hi,

                    About BUFFER SORT, first of all it is not specific for Parallel Executions. This step in the plan indicates that internal sorting have a place. It doesn't mean that rows will be returned sorted, in other words it doesn't guaranty that rows will be sorted in resulting row set, because it is not the main purpose of this operation. Only ORDER BY clause can guaranty that output row set will be sorted. BUFFER SORT is used for internal inter-operational sorting, it sorts and stores row sets in sorted order. It can be used for join operations, for example. The case can be when one of the tables in the join doesn't have an index on joining column. Oracle doesn't use internal sort operation if he can get it from index, or if it was sorted on some other step in the execution plan. Also additional sorting can be needed if You try to join two tables and one of them is scanned in parallel and other one in serial mode. In other words, it is internal optimization mechanism, in most of cases it can be seen in bad structured queries, oracle tries to compensate this bad structure using additional steps in execution plan.

                    In Your case, I think you need to create index on O_RECEIVED_NOT_INVOICED_ITEMS(LEGAL_ENTITY_ID) to avoid BUFFER SORT operations.

                    Also, did this query returns any results???

                    You have this conditions
                    "MII"."LEGAL_ENTITY_ID"=101
                    "RNII"."LEGAL_ENTITY_ID"=103

                    "RNII"."LEGAL_ENTITY_ID"="MII"."LEGAL_ENTITY_ID"

                    In other words it is like: WHERE 101=103

                    Good Luck
                    • 7. Re: Two different HASH GROUP BY in execution plan
                      Jonathan Lewis
                      aychin wrote:
                      Hi,

                      About BUFFER SORT, first of all it is not specific for Parallel Executions. This step in the plan indicates that internal sorting have a place. It doesn't mean that rows will be returned sorted, in other words it doesn't guaranty that rows will be sorted in resulting row set, because it is not the main purpose of this operation.
                      I've previously suggested that the "buffer sort" should really simply say "buffering", but that it hijacks the buffering mechanism of sorting and therefore gets reported completely spuriously as a sort. (see http://jonathanlewis.wordpress.com/2006/12/17/buffer-sorts/ ).

                      In this case, I think the buffer sort may be a consequence of the broadcast distribution - and tells us that the entire broadcast is being buffered before the hash join starts. It's interesting to note that in the recent of the two plans with a buffer sort the second (probe) table in the hash join seems to be accessed first and broadcast before the first table is scanned to allow the join to occur.



                      Regards
                      Jonathan Lewis
                      http://jonathanlewis.wordpress.com
                      http://www.jlcomp.demon.co.uk

                      To post code, statspack/AWR report, execution plans or trace files, start and end the section with the tag {noformat}
                      {noformat} (lowercase, curly brackets, no spaces) so that the text appears in 
                      fixed format
                      .
                      
                      There is a +"Preview"+ tab at the top of the text entry panel. Use this to check what your message will look like before you post the message. If it looks a complete mess you're unlikely to get a response. (Click on the +"Plain text"+ tab if you want to edit the text to tidy it up.)
                      
                      +"Science is more than a body of knowledge; it is a way of thinking"+
                      +Carl Sagan+                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                                
                      • 8. Re: Two different HASH GROUP BY in execution plan
                        389403
                        Yes Jonathan,
                        You are right it is just a buffer, and it can be used in the different kinds of operations, only Oracle can exactly know when it will be used and for which operation. Mostly, from my observations, it used for buffering of row sets that will be used later in processing or row sets that will be used more than ones in cycle, also for interoperational sorting, I named it internal sorting of some intermediate row sets.
                        • 9. Re: Two different HASH GROUP BY in execution plan
                          OraDBA02
                          Many Thanks Johnathan for your inputs.
                          It's interesting to note that in the recent of the two plans with a buffer sort the second (probe) table in the hash join seems to be accessed first and broadcast before the first table is scanned to allow the join to occur.
                          Can you elaborate more on this ? I assume you are referring 'mii' table as probe table here...is that right ? I also guess that, NL is first performed between mii and di tables and its resultset is BROADCASTED to the rnii table which is joined using HJ...Correct me if i am wrong.
                          • 10. Re: Two different HASH GROUP BY in execution plan
                            OraDBA02
                            Thanks a lot for the documentation link Aychin...I truly appreciate your help.

                            Can i also ask you to give more pointers towards documents/cases/discussion on PQ_DISTRIBUTE methods?