1 2 Previous Next 24 Replies Latest reply on Apr 10, 2015 7:22 PM by Lothar Flatz Go to original post
      • 15. Re: Explain plan control - hash join running long
        Lothar Flatz

        I am actually spurprised that the nested loop did not solve the issue. Can you post the sql monitor for it?

        I thought that doing the comparison 13.000 times rather than in the hash join should be much quicker that the has join. Actaully Randolf is pointing out what could go wrong with that hash join.

        In any case I agree with Randolf that you should also delete the filter condition and do a test run. We need to find the root cause of the CPU burning. Even though the CPU consumption is shown in the hash join, the hash join is an overalpping operation with the filer and the hash group by. We do not know for sure were the root cause is.

         

        BTW.: Thanks for providing evidence!

        • 16. Re: Explain plan control - hash join running long
          Sturla Thor

          Hi all and thanks again,

           

           

          I removed the filter conditions that were listed in item 4 and ran the query (only the select part).

          The query has been running 1 hour and 20 min.  Will post sql-monitor and query-plan shortly.

           

           

          Sturla

           

          ps. explain plan generated by sql-tuning advisor did not help either.

          • 17. Re: Explain plan control - hash join running long
            Lothar Flatz

            Martin Preiss wrote:

             

            this looks strange indeed. There are some wrong cardinalities in the plan - but the extreme duration comes from the HASH JOIN with its complex condition:

               4 - access("XTD"."DEFINITION_CODE"="XSR"."DEFINITION_CODE") 

                   filter((NVL("GCC"."SEGMENT1",'0')>=NVL(NVL("XSR"."SEGMENT1_FROM","GCC"."SEGMENT1"),'0') AND 

                          NVL("GCC"."SEGMENT1",'0')<=NVL(NVL("XSR"."SEGMENT1_TO","GCC"."SEGMENT1"),'0') AND 

                          NVL("GCC"."SEGMENT2",'0')>=NVL(NVL("XSR"."SEGMENT2_FROM","GCC"."SEGMENT2"),'0') AND 

                          NVL("GCC"."SEGMENT2",'0')<=NVL(NVL("XSR"."SEGMENT2_TO","GCC"."SEGMENT2"),'0') AND 

                          NVL("GCC"."SEGMENT3",'0')>=NVL(NVL("XSR"."SEGMENT3_FROM","GCC"."SEGMENT3"),'0') AND 

                          NVL("GCC"."SEGMENT3",'0')<=NVL(NVL("XSR"."SEGMENT3_TO","GCC"."SEGMENT3"),'0') AND 

                          NVL("GCC"."SEGMENT4",'0')>=NVL(NVL("XSR"."SEGMENT4_FROM","GCC"."SEGMENT4"),'0') AND 

                          NVL("GCC"."SEGMENT4",'0')<=NVL(NVL("XSR"."SEGMENT4_TO","GCC"."SEGMENT4"),'0') AND 

                          NVL("GCC"."SEGMENT5",'0')>=NVL(NVL("XSR"."SEGMENT5_FROM","GCC"."SEGMENT5"),'0') AND 

                          NVL("GCC"."SEGMENT5",'0')<=NVL(NVL("XSR"."SEGMENT5_TO","GCC"."SEGMENT5"),'0') AND 

                          NVL("GCC"."SEGMENT6",'0')>=NVL(NVL("XSR"."SEGMENT6_FROM","GCC"."SEGMENT6"),'0') AND 

                          NVL("GCC"."SEGMENT6",'0')<=NVL(NVL("XSR"."SEGMENT6_TO","GCC"."SEGMENT6"),'0') AND 

                          NVL("GCC"."SEGMENT7",'0')>=NVL(NVL("XSR"."SEGMENT7_FROM","GCC"."SEGMENT7"),'0') AND 

                          NVL("GCC"."SEGMENT7",'0')<=NVL(NVL("XSR"."SEGMENT7_TO","GCC"."SEGMENT7"),'0') AND 

                          NVL("GCC"."SEGMENT8",'0')>=NVL(NVL("XSR"."SEGMENT8_FROM","GCC"."SEGMENT8"),'0') AND 

                          NVL("GCC"."SEGMENT8",'0')<=NVL(NVL("XSR"."SEGMENT8_TO","GCC"."SEGMENT8"),'0') AND 

                          NVL("GCC"."SEGMENT9",'0')>=NVL(NVL("XSR"."SEGMENT9_FROM","GCC"."SEGMENT9"),'0') AND 

                          NVL("GCC"."SEGMENT9",'0')<=NVL(NVL("XSR"."SEGMENT9_TO","GCC"."SEGMENT9"),'0')))

            Since the CBO expects the input from step 5 to be very small (0 or 1 row) and still does not choose an NESTED LOOPS join I guess there is some reason for this strategy. And I guess that the huge CPU usage comes from the evaluation of the step 4 filter. I did not try to understand what this condition actually means - but I think it's the reason for the performance problem.

            When I was talking about removing the filter I was just referring to the filter condition above Martin mentioned. Of course all other conditions should stay.

            • 18. Re: Explain plan control - hash join running long
              Sturla Thor

              Hi, I removed the filter Lothar suggested and ran the query, been running now for 2 and a half hour and is up to about 4 billion rows.

              The query plan is as below, using Nested LOOPS instead of Hash join.

               

               

              SQL> SELECT * FROM table(DBMS_XPLAN.DISPLAY_CURSOR('6s9vpmzxcyz9g',0));

              SQL_ID  6s9vpmzxcyz9g, child number 0

              -------------------------------------

              SELECT               DECODE(xdl.applied_to_entity_id

              ,NULL                 ,'SOURCE'                 ,'APPLIED')

                            record_type_code          ,xah.entity_id

                             source_entity_id          ,xet.event_class_code

                              event_class_code          ,xah.application_id

                            source_application_id          ,xdl.applied_to_entity_id

                               applied_to_entity_id

              ,xdl.applied_to_application_id             applied_to_application_id

                    ,xah.accounting_date                        gl_date

              ,xal.currency_code                          trx_currency_code    --

              changes for incorrect trial balance amounts bug 6366295          --

              entered_rounded_dr          ,decode(nvl(sum(xdl.unrounded_entered_cr),

              sum(xdl.unrounded_entered_dr)), null, null,           CASE

              xlo.acct_reversal_option_code           WHEN 'SIDE' THEN

              CASE SIGN(                   NVL(SUM(xdl.unrounded

               

               

              Plan hash value: 756758337

               

               

              ---------------------------------------------------------------------------------------------------------------------------------------------------

              | Id  | Operation                                    | Name                       | Rows  | Bytes |TempSpc| Cost (%CPU)| Time     | Pstart| Pstop |

              ---------------------------------------------------------------------------------------------------------------------------------------------------

              |   0 | SELECT STATEMENT                             |                            |       |       |       | 11159 (100)|          |       |       |

              |   1 |  HASH GROUP BY                               |                            |   159K|    45M|    47M| 11159   (1)| 00:02:14 |       |       |

              |*  2 |   FILTER                                     |                            |       |       |       |            |          |       |       |

              |   3 |    NESTED LOOPS                              |                            |   159K|    45M|       |   985   (1)| 00:00:12 |       |       |

              |   4 |     NESTED LOOPS                             |                            |     1 |   288 |       |    88   (0)| 00:00:02 |       |       |

              |   5 |      NESTED LOOPS OUTER                      |                            |     1 |   254 |       |    86   (0)| 00:00:02 |       |       |

              |   6 |       NESTED LOOPS                           |                            |     1 |   234 |       |    85   (0)| 00:00:02 |       |       |

              |   7 |        NESTED LOOPS OUTER                    |                            |     1 |   198 |       |    84   (0)| 00:00:02 |       |       |

              |   8 |         NESTED LOOPS                         |                            |     1 |   141 |       |    79   (0)| 00:00:01 |       |       |

              |   9 |          MERGE JOIN CARTESIAN                |                            |     1 |    95 |       |    73   (0)| 00:00:01 |       |       |

              |  10 |           NESTED LOOPS                       |                            |     1 |    43 |       |     3   (0)| 00:00:01 |       |       |

              |  11 |            NESTED LOOPS                      |                            |     1 |    28 |       |     2   (0)| 00:00:01 |       |       |

              |  12 |             TABLE ACCESS BY INDEX ROWID      | GL_LEDGERS                 |     1 |     7 |       |     1   (0)| 00:00:01 |       |       |

              |* 13 |              INDEX UNIQUE SCAN               | GL_LEDGERS_U2              |     1 |       |       |     0   (0)|          |       |       |

              |  14 |             TABLE ACCESS BY INDEX ROWID      | FND_CURRENCIES             |   249 |  5229 |       |     1   (0)| 00:00:01 |       |       |

              |* 15 |              INDEX UNIQUE SCAN               | FND_CURRENCIES_U1          |     1 |       |       |     0   (0)|          |       |       |

              |* 16 |            TABLE ACCESS BY INDEX ROWID       | XLA_TB_DEFINITIONS_B       |     1 |    15 |       |     1   (0)| 00:00:01 |       |       |

              |* 17 |             INDEX RANGE SCAN                 | XLA_TB_DEFINITIONS_B_N1    |     1 |       |       |     0   (0)|          |       |       |

              |  18 |           BUFFER SORT                        |                            |     1 |    52 |       |    72   (0)| 00:00:01 |       |       |

              |  19 |            PARTITION LIST ALL                |                            |     1 |    52 |       |    70   (0)| 00:00:01 |     1 |    21 |

              |* 20 |             TABLE ACCESS BY LOCAL INDEX ROWID| XLA_AE_HEADERS             |     1 |    52 |       |    70   (0)| 00:00:01 |     1 |    21 |

              |* 21 |              INDEX RANGE SCAN                | XLA_AE_HEADERS_N1          |   510 |       |       |    43   (0)| 00:00:01 |     1 |    21 |

              |  22 |          PARTITION LIST ITERATOR             |                            |     1 |    46 |       |     6   (0)| 00:00:01 |   KEY |   KEY |

              |* 23 |           TABLE ACCESS BY LOCAL INDEX ROWID  | XLA_AE_LINES               |     1 |    46 |       |     6   (0)| 00:00:01 |   KEY |   KEY |

              |* 24 |            INDEX RANGE SCAN                  | XLA_AE_LINES_U1            |     7 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |

              |  25 |         PARTITION LIST ITERATOR              |                            |     1 |    57 |       |     5   (0)| 00:00:01 |   KEY |   KEY |

              |* 26 |          TABLE ACCESS BY LOCAL INDEX ROWID   | XLA_DISTRIBUTION_LINKS     |     1 |    57 |       |     5   (0)| 00:00:01 |   KEY |   KEY |

              |* 27 |           INDEX RANGE SCAN                   | XLA_DISTRIBUTION_LINKS_N3  |    11 |       |       |     3   (0)| 00:00:01 |   KEY |   KEY |

              |  28 |        TABLE ACCESS BY INDEX ROWID           | XLA_EVENT_TYPES_B          |     1 |    36 |       |     1   (0)| 00:00:01 |       |       |

              |* 29 |         INDEX UNIQUE SCAN                    | XLA_EVENT_TYPES_B_U2       |     1 |       |       |     0   (0)|          |       |       |

              |  30 |       TABLE ACCESS BY INDEX ROWID            | XLA_LEDGER_OPTIONS         |     2 |    40 |       |     1   (0)| 00:00:01 |       |       |

              |* 31 |        INDEX UNIQUE SCAN                     | XLA_LEDGER_OPTIONS_U1      |     1 |       |       |     0   (0)|          |       |       |

              |* 32 |      TABLE ACCESS BY INDEX ROWID             | GL_CODE_COMBINATIONS       |     1 |    34 |       |     2   (0)| 00:00:01 |       |       |

              |* 33 |       INDEX UNIQUE SCAN                      | GL_CODE_COMBINATIONS_U1    |     1 |       |       |     1   (0)| 00:00:01 |       |       |

              |* 34 |     INDEX RANGE SCAN                         | XLA_TB_DEF_SEG_RANGES_IDX1 |   199K|  1950K|       |   897   (1)| 00:00:11 |       |       |

              ---------------------------------------------------------------------------------------------------------------------------------------------------

               

               

              Predicate Information (identified by operation id):

              ---------------------------------------------------

               

               

                 2 - filter(:7<=:8)

                13 - access("GL"."LEDGER_ID"=:9)

                15 - access("FDC"."CURRENCY_CODE"="GL"."CURRENCY_CODE")

                16 - filter("XTD"."ENABLED_FLAG"='Y')

                17 - access("XTD"."LEDGER_ID"=:9)

                20 - filter(("XAH"."UPG_BATCH_ID" IS NULL AND "XAH"."AE_HEADER_ID">=:7 AND "XAH"."AE_HEADER_ID"<=:8 AND

                            "XAH"."EVENT_TYPE_CODE"<>'MANUAL' AND INTERNAL_FUNCTION("XAH"."GL_TRANSFER_STATUS_CODE")))

                21 - access("XAH"."GROUP_ID"=:GROUP_ID AND "XAH"."LEDGER_ID"=:9)

                23 - filter("XAL"."ACCOUNTING_CLASS_CODE"='LIABILITY')

                24 - access("XAH"."AE_HEADER_ID"="XAL"."AE_HEADER_ID" AND "XAH"."APPLICATION_ID"="XAL"."APPLICATION_ID")

                     filter(("XAL"."AE_HEADER_ID">=:7 AND "XAL"."AE_HEADER_ID"<=:8 AND "XAH"."APPLICATION_ID"="XAL"."APPLICATION_ID"))

                26 - filter("XAL"."APPLICATION_ID"="XDL"."APPLICATION_ID")

                27 - access("XAL"."AE_HEADER_ID"="XDL"."AE_HEADER_ID" AND "XAL"."AE_LINE_NUM"="XDL"."AE_LINE_NUM")

                     filter(("XDL"."AE_HEADER_ID">=:7 AND "XDL"."AE_HEADER_ID"<=:8))

                29 - access("XAH"."APPLICATION_ID"="XET"."APPLICATION_ID" AND "XAH"."EVENT_TYPE_CODE"="XET"."EVENT_TYPE_CODE")

                     filter("XET"."EVENT_TYPE_CODE"<>'MANUAL')

                31 - access("XLO"."APPLICATION_ID"="XAH"."APPLICATION_ID" AND "XLO"."LEDGER_ID"=:9)

                32 - filter("GCC"."CHART_OF_ACCOUNTS_ID"=:COA_ID)

                33 - access("XAL"."CODE_COMBINATION_ID"="GCC"."CODE_COMBINATION_ID")

                34 - access("XTD"."DEFINITION_CODE"="XSR"."DEFINITION_CODE")

               

               

               

               

              84 rows selected.

               

               

              SQL>

               

               

              • 19. Re: Explain plan control - hash join running long
                Martin Preiss

                that's what Randolf mentioned: the intermediate result is huge and the filtering then returns just a few rows - and the A-rows in the sqlmonitor plan are the filtered rows and so the plan does not show the cruel things that are taking place. (Notice to myself: reminds me of creating a "database idea" to include these information somewhere in the trace facilities; though that's not a new idea, I think).

                 

                So the next question is: what is this filter operation actually doing? Could the result be limited in an earlier step? Or is it an option to split the operation in multiple smaller sub-operations?

                • 20. Re: Explain plan control - hash join running long
                  Sturla Thor

                  Martin: Good questions!  Since this is an Oracle code (Oracle EBS) i will have to take this case to them if it calls for changing or splitting up the operation.  That seems to be the logical step to look at next.

                  Do you agree that there is not much I can do to influence the plan that would improve the query without changing the actual code?

                  • 21. Re: Explain plan control - hash join running long
                    Martin Preiss

                    just added the idea: A second A-Row column for input rows in the traces. Though I hope they will not call it A-Rows-2... (but some years ago I heared about the _fifteenth_spare_parameter, so I would not be too much surprised)

                    • 22. Re: Explain plan control - hash join running long
                      Martin Preiss

                      the query is quite complex and I did not try to understand the logic: maybe the optimizer could come up with a more fitting join order if the estimates for the cardinalities would be correct. At least with the current plan and the construction of an intermediate result with billions of rows I don't see how this operation could be fast. If the filtering could be done at an earlier step maybe this could be more efficient, but to answer this question a careful analysis of the sizes of the different sets and the filter conditions would be necessary.

                      • 23. Re: Explain plan control - hash join running long
                        Lothar Flatz

                        Pretty interessting. Now it seems we see the truth. It would be better to have a sql monitor to see if the execution plans cardinality estimates for the NL join are correct, but I guess considerung the run time they are correct enough.

                        So that DEFINITION_CODE is probably a low cardinality column. Therefore the join does actually proliferate the number of  rows and the extra segment filter condition is needed to get it down again. When we consier that we have not actually one row as estimated  before the join, but rather 13.000 the multiplied result must be considerable.

                        The filter condition seems to be about overlapping segemnts with segment ranges where null values are nuetralized by nvl.

                        The only straing forward way to solve this would be to include the filter condition in the index access which seems impossibe since there are three tables involved in that join. (Menage a trois. Hard to understand that Oracle developers produce that kind of code.)

                        We could include the segments_from /to  in the index ( DEFINITION_CODE, SEGMENT1_FROM, SEGMENT1_TO, SEGMENT2_FROM, SEGMENT2_TO, ...) and try a nested loop. That might resut in sone nasty index leave blocks scans, but I have not better idea right now. At least we have a chance not to try all the combinations of our "almost cartesian product"..

                        Well and as always, you could use brute force:  meaning parallel.

                        • 24. Re: Explain plan control - hash join running long
                          Lothar Flatz

                          I think there is probaly no other way to join in this last table then the one the optimizer uses. IMHO you can't use the filter before the XLA_TB_DEF_SEG_RANGES are joined in due to the silly formular they are using,

                          1 2 Previous Next