14 Replies Latest reply: Jul 17, 2013 1:28 AM by Martin Preiss RSS

    Help to understand the explain plan

    user10636796

      Hi, Everyone,

       

      i have query :

       

      INSERT INTO ICM_UPSEL_1_ALL

      select  a.customer_no,a.pr_code_bbl,min(b.pr_code_pmm) pr_code_pmm,a.score,a.price_diff,a.flag

      from ICM_UPSEL_MIN_PRDIFF_1 a, icm_pre b

      where a.customer_no = b.customer_no

      and a.pr_code_bbl=b.pr_code_bbl

      and a.score = b.score

      and a.flag = b.flag

      and a.price_diff = b.price_diff

      and b.price_diff > 0

      and b.score >=0.5

      and b.flag = 1

      and b.price_diff >  0 and b.price_diff <= 10

      group by  a.customer_no,a.pr_code_bbl,a.score,a.price_diff,a.flag

       

       

      This query is running in a pl/sql procedure.  The explain plan for this query is :

       

      Plan hash value: 3124235498

       

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

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

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

      |   0 | INSERT STATEMENT                |                                |     1 |   143 |     4  (25)| 00:00:01 |

      |   1 |  LOAD TABLE CONVENTIONAL        | ICM_UPSEL_1_ALL        |       |       |            |          |

      |   2 |   HASH GROUP BY                 |                                |     1 |   143 |     4  (25)| 00:00:01 |

      |   3 |    NESTED LOOPS                 |                                |       |       |            |          |

      |   4 |     NESTED LOOPS                |                                |     1 |   143 |     3   (0)| 00:00:01 |

      |*  5 |      TABLE ACCESS BY INDEX ROWID| ICM_UPSEL_MIN_PRDIFF_1 |     1 |    65 |     1   (0)| 00:00:01 |

      |*  6 |       INDEX RANGE SCAN          | MPD_1_FLAG_IDX                 |     1 |       |     1   (0)| 00:00:01 |

      |*  7 |      INDEX RANGE SCAN           | ICM_PRE_FLAG_IDX               |     1 |       |     2   (0)| 00:00:01 |

      |*  8 |     TABLE ACCESS BY INDEX ROWID | ICM_PRE                |     1 |    78 |     2   (0)| 00:00:01 |

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

       

      Predicate Information (identified by operation id):

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

       

         5 - filter("A"."PRICE_DIFF">0 AND "A"."SCORE">=0.5 AND "A"."PRICE_DIFF"<=10)

         6 - access("A"."FLAG"=1)

         7 - access("B"."FLAG"=1)

         8 - filter("B"."PRICE_DIFF"<=10 AND "B"."SCORE">=0.5 AND "B"."PRICE_DIFF">0 AND

                    "A"."CUSTOMER_NO"="B"."CUSTOMER_NO" AND "A"."PR_CODE_BBL"="B"."PR_CODE_BBL" AND "A"."SCORE"="B"."SCORE"

                    AND "A"."PRICE_DIFF"="B"."PRICE_DIFF")

       

       

      This query is running for 10 hours now and no result yet.  Could anyone please help me where is going wrong... why is it taking so much time to insert the data...

       

      The number of rows in ICM_UPSEL_MIN_PRDIFF_1 : 84,858

      The number of rows in icm_pre :  455,500,944

       

      All the columns are indexed.

       

       

      My database version is 11.1.0.6 running on windows server 2003 R2.

       

      Thanks in advance

        • 1. Re: Help to understand the explain plan
          Martin Preiss

          how many rows you get for:

          select count(*)

            from ICM_UPSEL_MIN_PRDIFF_1 a

          where a.flag = 1

             and A.PRICE_DIFF>0

             AND A.SCORE>=0.5

             AND A.PRICE_DIFF<=10

          The CBO expects 1 row resulting in 1 nested loops execution: if this assuption is wrong you can get a lot of loops and the execution can get very slow.

           

          Regards

           

          Martin

          • 2. Re: Help to understand the explain plan
            user10636796

            Hi, Martin,

             

            i get 84,858 rows for the above query.  Any Suggestions on how i could improve the query?

             

            Thanks & Regards

            Su

            • 3. Re: Help to understand the explain plan
              Martin Preiss

              are the statistics up to date? If they are not you could try to gather new statistics. If they are you could try to use a dynamic_sampling hint (with a sufficient level). In the end the plan should probably use a HASH JOIN instead of the NESTED LOOPS.

              • 4. Re: Help to understand the explain plan
                user10636796

                Hi, Martin,

                 

                Thanks for replying.  The statistics are up to date.  Could you please explain or give an example  how i could use HASH JOIN instead of Nested Loops?

                 

                Thanks & Regards

                Su

                • 5. Re: Help to understand the explain plan
                  Martin Preiss

                  I would start with a dynamic_sampling hint:

                  explain plan for

                  INSERT INTO ICM_UPSEL_1_ALL

                  select /*+ dynamic_sampling(4) +/  a.customer_no,a.pr_code_bbl,min(b.pr_code_pmm) pr_code_pmm,a.score,a.price_diff,a.flag

                  ...

                  The hint instructs Oracle to gather additional statistics and I suppose that the additional information will result in a plan change. The are other options (USE_HASH hint + SPM) but I would start with dynamic sampling. If you are interested in the details you can take a look at http://allthingsoracle.com/dynamic-sampling-i-an-introduction-part-1/ (and following articles written by Randolf Geist)

                  • 6. Re: Help to understand the explain plan
                    34MCA2K2

                    select  a.customer_no,a.pr_code_bbl,min(b.pr_code_pmm) pr_code_pmm,a.score,a.price_diff,a.flag

                    from ICM_UPSEL_MIN_PRDIFF_1 a, icm_pre b

                    where a.customer_no = b.customer_no

                    and a.pr_code_bbl=b.pr_code_bbl

                    and a.score = b.score

                    and a.flag = b.flag

                    and a.price_diff = b.price_diff

                    and b.price_diff > 0

                    and b.score >=0.5

                    and b.flag = 1

                    and b.price_diff >  0 and b.price_diff <= 10

                    group by  a.customer_no,a.pr_code_bbl,a.score,a.price_diff,a.flag

                     

                    It doesnt make sense to have one condition specified twice in the query, although bleak chances that this will affect optimizer. I have written another version of query as below, can you check this?

                     

                    select  a.customer_no,a.pr_code_bbl,min(b.pr_code_pmm) pr_code_pmm,a.score,a.price_diff,a.flag

                    from ICM_UPSEL_MIN_PRDIFF_1 a, icm_pre b

                    where a.customer_no = b.customer_no

                    and a.pr_code_bbl=b.pr_code_bbl

                    and a.score = b.score

                    and a.flag = 1

                    and b.flag = 1

                    and a.price_diff = b.price_diff

                    and b.score >=0.5

                    and b.price_diff >  0 and b.price_diff <= 10

                    group by  a.customer_no,a.pr_code_bbl,a.score,a.price_diff,a.flag

                     

                    Also if your INSERT is slow, you can try APPEND hint. You may get benefitted if ICM_UPSEL_1_ALL is not empty. Although if you can truncate this table before insert, then this will give same results even without APPEND hint.

                     

                    Regards,

                    • 7. Re: Help to understand the explain plan
                      BrendanP

                      If the stats are up to date I wonder if it could be one of those cases where CBO incorrectly assumes independence of multiple predicates, consequently under-estimating cardinality. DBMS_SQLTune might be useful if OP can't wait for query to complete to get an execution plan.

                      • 8. Re: Help to understand the explain plan
                        Nikolay Savvinov

                        Hi,

                         

                        Martin gave you the right approach, but there was an error in his suggestion (there is only one predicate against ICM_UPSEL_MIN_PRDIFF_1, flag = 1, and not 4). Run explain plan on SELECT * FROM  ICM_UPSEL_MIN_PRDIFF_1 WHERE FLAG=1 , and compare estimated number of rows with the actual. Obviously, you're going to see large discrepancy (the actual rowcount will be larger than 84,858 while the estimated rowcount will be probably 1), the question is how large and where it's coming from.

                         

                        The optimizer estimates cardinality as a product of total rowcount (for a table, index or table/index partition) and the predicate selectivity. So when it's underestimated, it means that one of these two quantities is wrong -- i.e. either the table stats that there is only 0 or 1 row in there, or the column stats erraneously claim that flag=1 is a very restrictive condition that selects just 1 row out of many.

                         

                        In order to find out which of the two scenarios takes place, or post the output of

                         

                        SELECT * FROM DBA_TAB_COL_STATISTICS WHERE OWNER = :owner AND TABLE_NAME = :tabname AND COLUMN_NAME = :colname;

                         

                        SELECT * FROM DBA_TAB_STATISTICS WHERE OWNER = :owner AND TABLE_NAME = :tabname;

                         

                        for table ICM_UPSEL_MIN_PRDIFF_1 and its column FLAG.

                         

                        Best regards,

                        Nikolay

                        • 9. Re: Help to understand the explain plan
                          user10636796

                          Hi, Martin,

                           

                          Please see the explain plan as below:

                           

                          Plan hash value: 807005143

                           

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

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

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

                          |   0 | INSERT STATEMENT                 |                                |  3356 |   350K|       | 92450   (1)| 00:18:30 |

                          |   1 |  LOAD TABLE CONVENTIONAL         | ICM_UPSEL_1_ALL        |       |       |       |            |          |

                          |   2 |   HASH GROUP BY                  |                                |  3356 |   350K|       | 92450   (1)| 00:18:30 |

                          |*  3 |    HASH JOIN                     |                                |  3356 |   350K|  4456K| 92449   (1)| 00:18:30 |

                          |   4 |     VIEW                         | VW_GBC_6                       | 79983 |  3514K|       | 91695   (1)| 00:18:21 |

                          |   5 |      SORT GROUP BY               |                                | 79983 |  3436K|    11M| 91695   (1)| 00:18:21 |

                          |*  6 |       TABLE ACCESS BY INDEX ROWID| ICM_PRE                | 79983 |  3436K|       | 90798   (1)| 00:18:10 |

                          |*  7 |        INDEX RANGE SCAN          | ICM_PRE_FLAG_IDX               |  4003K|       |       |  7302   (1)| 00:01:28 |

                          |   8 |     TABLE ACCESS FULL            | ICM_UPSEL_MIN_PRDIFF_1 | 84858 |  5137K|       |   239   (1)| 00:00:03 |

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

                           

                          Predicate Information (identified by operation id):

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

                           

                             3 - access("A"."CUSTOMER_NO"="ITEM_5" AND "A"."PR_CODE_BBL"="ITEM_4" AND "A"."SCORE"="ITEM_3" AND

                                        "A"."FLAG"="ITEM_2" AND "A"."PRICE_DIFF"="ITEM_1")

                             6 - filter("B"."PRICE_DIFF"<=10 AND "B"."SCORE">=0.5 AND "B"."PRICE_DIFF">0)

                             7 - access("B"."FLAG"=1)

                           

                          Note

                          -----

                             - dynamic sampling used for this statement

                           

                          Regards

                          Su

                          • 10. Re: Help to understand the explain plan
                            user10636796

                            Hi, Nikolay,

                             

                            Please see the results below;

                             

                            SELECT * FROM DBA_TAB_COL_STATISTICS

                            WHERE OWNER = 'SAST' AND TABLE_NAME = 'ICM_UPSEL_MIN_PRDIFF_1' AND COLUMN_NAME = 'FLAG';

                             

                            TABLE_NAMECOLUMN_NAMENUM_DISTINCTLOW_VALUEHIGH_VALUEDENSITYNUM_NULLSNUM_BUCKETSLAST_ANALYZEDSAMPLE_SIZEGLOBAL_STATSUSER_STATSAVG_COL_LENHISTOGRAM

                            SAST    ICM_UPSEL_MIN_PRDIFF_1    FLAG    1    C102    C102    0.00000584543291182618    0    1    16/07/13 10:01:42    5544    YES    NO    3    FREQUENCY

                             

                            SELECT * FROM DBA_TAB_STATISTICS WHERE OWNER = 'SAST' AND TABLE_NAME = 'ICM_UPSEL_MIN_PRDIFF_1';

                             

                            TABLE_NAMEPARTITION_NAMEPARTITION_POSITIONSUBPARTITION_NAMESUBPARTITION_POSITIONOBJECT_TYPENUM_ROWSBLOCKSEMPTY_BLOCKSAVG_SPACECHAIN_CNTAVG_ROW_LENAVG_SPACE_FREELIST_BLOCKSNUM_FREELIST_BLOCKSAVG_CACHED_BLOCKSAVG_CACHE_HIT_RATIOSAMPLE_SIZELAST_ANALYZEDGLOBAL_STATSUSER_STATSSTATTYPE_LOCKEDSTALE_STATS
                            SAST    ICM_UPSEL_MIN_PRDIFF_1  TABLE    84858    874    0    0    0    62    0    0  84858    16/07/13 10:02:00    YES    NO  NO

                             

                            Thanks & Regards

                            Su

                            • 11. Re: Help to understand the explain plan
                              Nikolay Savvinov

                              Hi,

                               

                              1) be sure to include column headers, otherwise how are we supposed to understand what is what in table dumps?

                              2) I also asked for an explain plan on SELECT * FROM  ICM_UPSEL_MIN_PRDIFF_1 WHERE FLAG=1, can you provide that?

                               

                              Best regards,

                              Nikolay

                              • 12. Re: Help to understand the explain plan
                                RajuM

                                hi,

                                 

                                Use Hints in order to increase query performance.

                                 

                                as 

                                select /*+  first_rows(50) */  a.customer_no,a.pr_code_bbl,min(b.pr_code_pmm) pr_code_pmm,a.score,a.price_diff,a.flag

                                from ICM_UPSEL_MIN_PRDIFF_1 a, icm_pre b

                                where a.customer_no = b.customer_no

                                and a.pr_code_bbl=b.pr_code_bbl

                                and a.score = b.score

                                and a.flag = b.flag

                                and a.price_diff = b.price_diff

                                and b.price_diff > 0

                                and b.score >=0.5

                                and b.flag = 1

                                and b.price_diff >  0 and b.price_diff <= 10

                                group by  a.customer_no,a.pr_code_bbl,a.score,a.price_diff,a.flag

                                 

                                 

                                all_rows,index hints .......etc

                                 

                                thanks,

                                Raj

                                • 13. Re: Help to understand the explain plan
                                  user10636796

                                  Hi, Nikolay,

                                   

                                  Sorry for not including the headers.  the explain plan is :

                                   

                                   

                                  Plan hash value: 1277466935

                                   

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

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

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

                                  |   0 | SELECT STATEMENT  |                                | 84850 |  5137K|   239   (1)| 00:00:03 |

                                  |*  1 |  TABLE ACCESS FULL| ICM_UPSEL_MIN_PRDIFF_1 | 84850 |  5137K|   239   (1)| 00:00:03 |

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

                                   

                                  Predicate Information (identified by operation id):

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

                                   

                                     1 - filter("FLAG"=1)

                                   

                                  Thanks & Regards

                                  Su

                                  • 14. Re: Help to understand the explain plan
                                    Martin Preiss

                                    this plan looks much more appropriate to me: instead of doing > 80K of loops the CBO now decides to do a single HASH JOIN. I would try to execute the operation with the dynamic_sampling hint. If the system statistics are not completely misleading the operation should now only take some minutes (and not > 10h).

                                     

                                    Regards

                                     

                                    Martin