1 2 3 Previous Next 42 Replies Latest reply: Nov 6, 2009 2:13 AM by 635471 Go to original post RSS
      • 15. Re: An optimizer Question
        108476
        Hi Tarun,
        Now when I force index hint on second query and check the plan the cost is much more.
        Pay no attention to the costs, they don't reflect the "real" execution times . . .

        Remember, the “Cost” figures are very misleading and should not be used as guidelines for SQL tuning for several reasons:

        -The optimizers costing can be wrong because of stale or missing metadata (especially histograms)

        - The lowest cost value does not always indicate the "real" lowest cost that is select by the optimizer.

        - In first_rows_n optimization, extra I/O is required to access the data via indexes and get the row back quickly. Hence, the optimizer may choose a more expensive plan because it will return rows faster than a "cheaper" plan that uses less machine resources.

        If you are optimizing for fast response time, just "set timing on" and see which runs fastest for you!
        • 16. Re: An optimizer Question
          108476
          DUP POST.

          Edited by: burleson on Oct 24, 2009 8:15 AM
          • 17. Re: An optimizer Question
            729555
            Hi Burleson,

            I've switch on the timing the first query with id 70154929 took 0.75 seconds,
            the second one (bad plan) with id 83738142 took 72.406 secs.
            Moreover, I've checked the plan in my QA database for the two queries,
            and both queries are using good plan.
            So, what do you think is wrong, or how can I fix the issue?

            Thanks
            Tarun
            • 18. Re: An optimizer Question
              635471
              tsharma@healthdialog.com wrote:
              Moreover, I've checked the plan in my QA database for the two queries,
              and both queries are using good plan.
              How about posting the plans? (use DBMS_XPLAN.DISPLAY)

              Could be a histogram thing, could be a dynamic sampling thing, could be that the different execution plans are entirely appropriate or they could be inappropriate. Very difficult to know without at least the explain plan output.

              Edit: It's interesting to think of all the other things that could cause this as well as histograms or dynamic sampling. Theoretically could iteven be a hash (sub)partitioned table with a local index in which the two values are in different segments that have different clustering factors on the local index statistics? That'd be sweet.

              Edited by: David_Aldridge on Nov 2, 2009 9:26 AM
              • 19. Re: An optimizer Question
                729555
                David_Aldridge wrote:
                Edit: It's interesting to think of all the other things that could cause this as well as histograms or dynamic sampling. Theoretically could iteven be a hash (sub)partitioned table with a local index in which the two values are in different segments that have different clustering factors on the local index statistics? That'd be sweet.

                Edited by: David_Aldridge on Nov 2, 2009 9:26 AM
                I've checked there's no histogram associated with the column.
                However, second possibility seems intersting. How can I verify it.

                Thanks
                Tarun
                • 20. Re: An optimizer Question
                  635471
                  tsharma@healthdialog.com wrote:
                  David_Aldridge wrote:
                  Edit: It's interesting to think of all the other things that could cause this as well as histograms or dynamic sampling. Theoretically could iteven be a hash (sub)partitioned table with a local index in which the two values are in different segments that have different clustering factors on the local index statistics? That'd be sweet.

                  Edited by: David_Aldridge on Nov 2, 2009 9:26 AM
                  I've checked there's no histogram associated with the column.
                  However, second possibility seems intersting. How can I verify it.

                  Thanks
                  Tarun
                  Interesting but incredibly unlikely.

                  Post the execution plan.
                  • 21. Re: An optimizer Question
                    729555
                    Plan for first Query
                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                    Plan hash value: 3848362163
                    --------------------------------------------------------------------------------
                    | Id | Operation | Name | Ro
                    --------------------------------------------------------------------------------
                    | 0 | SELECT STATEMENT | |
                    | 1 | NESTED LOOPS | |
                    | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| PATIENT_FACTS |
                    |* 3 | INDEX RANGE SCAN | LX_PATIENT_FACTS_HD_PERSON_ID |
                    | 4 | TABLE ACCESS BY INDEX ROWID | FACT_REF |
                    |* 5 | INDEX UNIQUE SCAN | PK_FACT_REF |
                    --------------------------------------------------------------------------------
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    3 - access("HD_PERSON_ID"=70154929)
                    filter(TBL$OR$IDX$PART$NUM("ANALYTIC_CNM"."PATIENT_FACTS",0,1,0,ROWID)=55
                    5 - access("A"."FACT_NAME"="R"."FACT_NAME" AND "R"."REC_EXPIRED_DT"=TO_DATE('
                    hh24:mi:ss') AND "R"."PROD_STATUS"='P')

                    Plan for second query

                    PLAN_TABLE_OUTPUT
                    --------------------------------------------------------------------------------
                    Plan hash value: 1962984235
                    --------------------------------------------------------------------------------
                    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| T
                    --------------------------------------------------------------------------------
                    | 0 | SELECT STATEMENT | | 2689K| 166M| 68238 (2)| 0
                    |* 1 | HASH JOIN | | 2689K| 166M| 68238 (2)| 0
                    |* 2 | TABLE ACCESS FULL | FACT_REF | 364 | 12376 | 6 (0)| 0
                    | 3 | PARTITION RANGE SINGLE| | 2681K| 79M| 68218 (2)| 0
                    |* 4 | TABLE ACCESS FULL | PATIENT_FACTS | 2681K| 79M| 68218 (2)| 0
                    --------------------------------------------------------------------------------
                    Predicate Information (identified by operation id):
                    ---------------------------------------------------
                    1 - access("A"."FACT_NAME"="R"."FACT_NAME")
                    2 - filter("R"."REC_EXPIRED_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd
                    "R"."PROD_STATUS"='P')
                    4 - filter("HD_PERSON_ID"=83738142)
                    • 22. Re: An optimizer Question
                      635471
                      You seem to have lost the rows/bytes etc. columns from the first plan. Can you include them as well? also, use the code tags to make it a bit readable.

                      Anyway, line 4 in the second query implies that the optimizer estimates that filter("HD_PERSON_ID"=83738142) returns 79 million rows from PATIENT_FACTS. I expect that's wrong -- try including a dynamic sampling hint in there for now to see if that gives a plan with an estimate closer to reality. Unless 79 million really is correct.
                      • 23. Re: An optimizer Question
                        729555
                        Here's the plan_1 again

                        PLAN_TABLE_OUTPUT
                        --------------------------------------------------------------------------------------------------------------------------------------------
                        Plan hash value: 3848362163

                        -------------------------------------------------------------------------------------------------------------------------------------
                        | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
                        -------------------------------------------------------------------------------------------------------------------------------------
                        | 0 | SELECT STATEMENT | | 1 | 65 | 6 (0)| 00:00:01 | | |
                        | 1 | NESTED LOOPS | | 1 | 65 | 6 (0)| 00:00:01 | | |
                        | 2 | TABLE ACCESS BY GLOBAL INDEX ROWID| PATIENT_FACTS | 1 | 31 | 5 (0)| 00:00:01 | 55 | 55 |
                        |* 3 | INDEX RANGE SCAN | LX_PATIENT_FACTS_HD_PERSON_ID | 1 | | 4 (0)| 00:00:01 | | |
                        | 4 | TABLE ACCESS BY INDEX ROWID | FACT_REF | 1 | 34 | 1 (0)| 00:00:01 | | |
                        |* 5 | INDEX UNIQUE SCAN | PK_FACT_REF | 1 | | 0 (0)| 00:00:01 | | |

                        PLAN_TABLE_OUTPUT
                        --------------------------------------------------------------------------------------------------------------------------------------------
                        -------------------------------------------------------------------------------------------------------------------------------------

                        Predicate Information (identified by operation id):
                        ---------------------------------------------------

                        3 - access("HD_PERSON_ID"=70154929)
                        filter(TBL$OR$IDX$PART$NUM("ANALYTIC_CNM"."PATIENT_FACTS",0,1,0,ROWID)=55)
                        5 - access("A"."FACT_NAME"="R"."FACT_NAME" AND "R"."REC_EXPIRED_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd
                        hh24:mi:ss') AND "R"."PROD_STATUS"='P')

                        20 rows selected.

                        ------------------------
                        Sorry, if formating is still not very clear.
                        I've checked the no. of rows. For id 70154929 no. of rows in partition are 262 and for other id 83738142 no. of rows are 254.

                        I've tried using DYNAMIC_SAMPLING hint, but no difference in no. of rows in plan.
                        • 24. Re: An optimizer Question
                          635471
                          tsharma@healthdialog.com wrote:
                          Here's the plan_1 again
                          I've checked the no. of rows. For id 70154929 no. of rows in partition are 262 and for other id 83738142 no. of rows are 254.

                          I've tried using DYNAMIC_SAMPLING hint, but no difference in no. of rows in plan.
                          Did the execution plan confirm that dynamic sampling occured? Post the query you used and the plan please.

                          Edited by: David_Aldridge on Nov 3, 2009 3:30 AM
                          • 25. Re: An optimizer Question
                            729555
                            Query used
                            explain plan
                            set statement_id = 'PLAN_2' for
                            select /*+ DYNAMIC_SAMPLING(a) */ a.fact_name,
                            decode(r.fact_type,
                            'DATE',
                            to_char(FACT_VALUE_DATE, 'dd-mon-yyyy'),
                            FACT_VALUE_INTEGER || FACT_VALUE_DATE || FACT_VALUE_STRING ||
                            FACT_VALUE_NUMBER) as fact_val
                            from analytic_CNM.patient_facts partition(part_200907) a,
                            REF_HD.fact_ref r
                            where hd_person_id = 83738142
                            and a.fact_name = r.fact_name
                            and r.rec_expired_dt = to_date('99991231', 'YYYYMMDD')
                            and r.prod_status = upper('P')

                            and Plan is

                            PLAN_TABLE_OUTPUT
                            --------------------------------------------------------------------------------------------------------------------------------------------
                            Plan hash value: 1962984235

                            ---------------------------------------------------------------------------------------------------------
                            | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
                            ---------------------------------------------------------------------------------------------------------
                            | 0 | SELECT STATEMENT | | 2689K| 166M| 68238 (2)| 00:15:56 | | |
                            |* 1 | HASH JOIN | | 2689K| 166M| 68238 (2)| 00:15:56 | | |
                            |* 2 | TABLE ACCESS FULL | FACT_REF | 364 | 12376 | 6 (0) | 00:00:01 | | |
                            | 3 | PARTITION RANGE SINGLE| | 2681K| 79M| 68218 (2)| 00:15:56 | 55 | 55 |
                            |* 4 | TABLE ACCESS FULL | PATIENT_FACTS | 2681K| 79M| 68218 (2)| 00:15:56 | 55 | 55 |
                            ---------------------------------------------------------------------------------------------------------

                            PLAN_TABLE_OUTPUT
                            --------------------------------------------------------------------------------------------------------------------------------------------

                            Predicate Information (identified by operation id):
                            ---------------------------------------------------

                            1 - access("A"."FACT_NAME"="R"."FACT_NAME")
                            2 - filter("R"."REC_EXPIRED_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                            "R"."PROD_STATUS"='P')
                            4 - filter("HD_PERSON_ID"=83738142)
                            • 26. Re: An optimizer Question
                              635471
                              Your dynamic sampling hint syntax is incorrect: http://cs.felk.cvut.cz/10gr2/server.102/b14200/sql_elements006.htm#BABDCGAA

                              I suggest you use level 4 btw
                              • 27. Re: An optimizer Question
                                635471
                                Oh incidentally, your query is using single partition pruning, so the optimiser is looking for statistics against partition 55, not against the table. Check the statistics/histograms etc for that partition.
                                • 28. Re: An optimizer Question
                                  729555
                                  I've used DYNAMIC_SAMPLING as you've advised
                                  Here's the new plan

                                  PLAN_TABLE_OUTPUT
                                  --------------------------------------------------------------------------------------------------------------------------------------------
                                  Plan hash value: 1962984235

                                  ---------------------------------------------------------------------------------------------------------
                                  | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
                                  ---------------------------------------------------------------------------------------------------------
                                  | 0 | SELECT STATEMENT | | 553 | 35945 | 68214 (2)| 00:15:55 | | |
                                  |* 1 | HASH JOIN | | 553 | 35945 | 68214 (2)| 00:15:55 | | |
                                  |* 2 | TABLE ACCESS FULL | FACT_REF | 364 | 12376 | 6 (0)| 00:00:01 | | |
                                  | 3 | PARTITION RANGE SINGLE| | 552 | 17112 | 68207 (2)| 00:15:55 | 55 | 55 |
                                  |* 4 | TABLE ACCESS FULL | PATIENT_FACTS | 552 | 17112 | 68207 (2)| 00:15:55 | 55 | 55 |
                                  ---------------------------------------------------------------------------------------------------------

                                  PLAN_TABLE_OUTPUT
                                  --------------------------------------------------------------------------------------------------------------------------------------------

                                  Predicate Information (identified by operation id):
                                  ---------------------------------------------------

                                  1 - access("A"."FACT_NAME"="R"."FACT_NAME")
                                  2 - filter("R"."REC_EXPIRED_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                  "R"."PROD_STATUS"='P')
                                  4 - filter("HD_PERSON_ID"=83738142)

                                  Note
                                  -----

                                  PLAN_TABLE_OUTPUT
                                  --------------------------------------------------------------------------------------------------------------------------------------------
                                  - dynamic sampling used for this statement
                                  • 29. Re: An optimizer Question
                                    635471
                                    tsharma@healthdialog.com wrote:
                                    I've used DYNAMIC_SAMPLING as you've advised
                                    Here's the new plan

                                    PLAN_TABLE_OUTPUT
                                    --------------------------------------------------------------------------------------------------------------------------------------------
                                    Plan hash value: 1962984235

                                    ---------------------------------------------------------------------------------------------------------
                                    | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
                                    ---------------------------------------------------------------------------------------------------------
                                    | 0 | SELECT STATEMENT | | 553 | 35945 | 68214 (2)| 00:15:55 | | |
                                    |* 1 | HASH JOIN | | 553 | 35945 | 68214 (2)| 00:15:55 | | |
                                    |* 2 | TABLE ACCESS FULL | FACT_REF | 364 | 12376 | 6 (0)| 00:00:01 | | |
                                    | 3 | PARTITION RANGE SINGLE| | 552 | 17112 | 68207 (2)| 00:15:55 | 55 | 55 |
                                    |* 4 | TABLE ACCESS FULL | PATIENT_FACTS | 552 | 17112 | 68207 (2)| 00:15:55 | 55 | 55 |
                                    ---------------------------------------------------------------------------------------------------------

                                    PLAN_TABLE_OUTPUT
                                    --------------------------------------------------------------------------------------------------------------------------------------------

                                    Predicate Information (identified by operation id):
                                    ---------------------------------------------------

                                    1 - access("A"."FACT_NAME"="R"."FACT_NAME")
                                    2 - filter("R"."REC_EXPIRED_DT"=TO_DATE(' 9999-12-31 00:00:00', 'syyyy-mm-dd hh24:mi:ss') AND
                                    "R"."PROD_STATUS"='P')
                                    4 - filter("HD_PERSON_ID"=83738142)

                                    Note
                                    -----

                                    PLAN_TABLE_OUTPUT
                                    --------------------------------------------------------------------------------------------------------------------------------------------
                                    - dynamic sampling used for this statement
                                    So this suggests that there are around 552 rows in that partition for ("HD_PERSON_ID"=83738142) which is obviously much closer to 254. I'm sure that there must be something very wrong about the statistics for that partition. Have you looked at the partition statistics?

                                    Also, this column would seem to be a logical choice for indexing. Is there an index present?