This discussion is archived
1 2 3 Previous Next 42 Replies Latest reply: Nov 6, 2009 12:13 AM by 635471 Go to original post RSS
  • 15. Re: An optimizer Question
    108476 Journeyer
    Currently Being Moderated
    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 Journeyer
    Currently Being Moderated
    DUP POST.

    Edited by: burleson on Oct 24, 2009 8:15 AM
  • 17. Re: An optimizer Question
    729555 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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 Newbie
    Currently Being Moderated
    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 Expert
    Currently Being Moderated
    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?

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points