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
  • 30. Re: An optimizer Question
    729555 Newbie
    Currently Being Moderated
    Column HD_PERSON_ID is indexed and that's what you can see the plan for first table. It is LX_PATIENT_FACTS_HD_PERSON_ID.
    Now how can we check that what's wrong with stats of that partition, moreover, how can we fix it.

    Thanks
    Tarun
  • 31. Re: An optimizer Question
    705249 Newbie
    Currently Being Moderated
    Hi Tarune,

    what is the result of these 2 following queries:

    select num_rows from user_tab_statistics where table_name='PATIENT_FACTS' and partition_position=55;

    select num_rows from user_tab_statistics where table_name='PATIENT_FACTS' and partition_name is null;
  • 32. Re: An optimizer Question
    729555 Newbie
    Currently Being Moderated
    Hi,

    I've modified you queries and here's the result

    select num_rows from dba_tab_statistics where table_name='PATIENT_FACTS' and partition_position=55 and owner = 'ANALYTIC_CNM';
    *54589125*

    select num_rows from dba_tab_statistics where table_name='PATIENT_FACTS' and partition_name is null and owner = 'ANALYTIC_CNM';
    *488481692*

    Thanks
    Tarun
  • 33. Re: An optimizer Question
    705249 Newbie
    Currently Being Moderated
    Your statistics seem to be wrong: with dynamic sampling the CBO rekons that there's going to be 552 rows in the partition 55 and with object statistics the CBO estimates 79M.

    What is the value for LASt_ANALYZED column?

    What is the statement that you use to gather statistics on PATIENT_FACTS and FACT_REF table ?
  • 34. Re: An optimizer Question
    729555 Newbie
    Currently Being Moderated
    For patient_facts
    Last_Analyzed_date is 17/10/2009 06:10:24

    Fact_ref is very small table with 435 rows and is pretty static.
    it's last_analyzed_date is 14/05/2009 22:05:59

    I've used following method to gather stats

    begin

    dbms_stats.gather_table_stats(
    ownname=> 'ANALYTIC_CNM',
    tabname=> 'PATIENT_FACST',
    estimate_percent=> 1.0,
    granularity=> 'DEFAULT',
    block_sample=> TRUE,
    cascade=> TRUE,
    degree=> DBMS_STATS.DEFAULT_DEGREE,
    method_opt=> 'FOR ALL COLUMNS SIZE AUTO');

    end;
  • 35. Re: An optimizer Question
    705249 Newbie
    Currently Being Moderated
    could you launch now the gather_table_stats procedure using:
    granularity => 'AUTO'
    estimate_percent => dbms_stats.auto_sample_size
    method_opt => 'for all columns size auto'

    Then execute again the 2 select statements in order to check if the number of row in the partition 55 and in the table correspond to the reality.
  • 36. Re: An optimizer Question
    729555 Newbie
    Currently Being Moderated
    Thanks that solve the issue.
    Seems like I need to go through dbms_stats options in details.

    Thanks
    Tarun
  • 37. Re: An optimizer Question
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    tsharma@healthdialog.com wrote:
    Thanks that solve the issue.
    Seems like I need to go through dbms_stats options in details.

    Tarun
    Tarun,

    does this mean that after running the DBMS_STATS.GATHER_TABLE_STATS as outlined both queries use the same "good" execution plan?

    One question that remains is why the optimizer didn't choose the index when you correctly used the dynamic sampling hint. The estimated cardinality of 552 should have lead to an execution plan that uses the existing and available index. That looks odd and would probably need a look into the 10053 optimizer trace debug file, but is obviously now only a side note since your issue seems to be resolved.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 38. Re: An optimizer Question
    705249 Newbie
    Currently Being Moderated
    what is now the number of rows for the partition only and for the table ?
  • 39. Re: An optimizer Question
    729555 Newbie
    Currently Being Moderated
    The number of counts for partition is 56358710
    and for whole table is 475066625.

    But the plan is showing correct cardinality for the hd_person_id i.e. 254
  • 40. Re: An optimizer Question
    729555 Newbie
    Currently Being Moderated
    Yes, after running DBMS_STATS.GATHER_TABLE_STATS using the parameters mentioned by farenhiet it starts using the same plan for both queries.
    Randolf Geist wrote:

    One question that remains is why the optimizer didn't choose the index when you correctly used the dynamic sampling hint. The estimated cardinality of 552 should have lead to an execution plan that uses the existing and available index. That looks odd and would probably need a look into the 10053 optimizer trace debug file, but is obviously now only a side note since your issue seems to be resolved.
    I'm not a DBA so don't know how and hwere to look for 10053 optimizer trace debug file.
    But, if you tell me where and what to look I'll try to find it.
    Though, problem is resolved, I would like to go to the root of issue.

    Thanks
    Tarun
  • 41. Re: An optimizer Question
    Randolf Geist Oracle ACE Director
    Currently Being Moderated
    tsharma@healthdialog.com wrote:
    I'm not a DBA so don't know how and hwere to look for 10053 optimizer trace debug file.
    But, if you tell me where and what to look I'll try to find it.
    Though, problem is resolved, I would like to go to the root of issue.

    Thanks
    Tarun
    Tarun,

    the root cause very likely were unrepresentative partition level statistics, since the optimizer estimated 2681K rows (not 79M, which was probably a mistake due to the bad formatting) for the HD_PERSON_ID=83738142 predicate with the old statistics. With the new statistics in place you say that the estimates are correct - so I think this might be a sufficient explanation of your original issue.

    My point was in particular about the fact that you've posted an EXPLAIN PLAN that showed an cardinality estimate of 552 for this predicate, likely generated by dynamic sampling, but still refused to use the index LX_PATIENT_FACTS_HD_PERSON_ID. This is potentially a different issue, but probably you can't reproduce this easily now that you have different statistics in place.

    If you're keen on checking this particular issue, you can:

    1. Restore the previous statistics using DBMS_STATS.RESTORE_TABLE_STATS

    2. Run again an explain plan for the statement with the correct dynamic sampling hint, but activating the 10053 optimizer trace before:
    ALTER SESSION SET TRACEFILE_IDENTIFIER = '10053_optimizer_trace';
    
    ALTER SESSION SET EVENTS '10053 trace name context forever, level 1';
    
    EXPLAIN PLAN FOR <your_statement>;
    
    ALTER SESSION SET EVENTS '10053 trace name context off';
    3. Restore the current statistics using DBMS_STATS.RESTORE_TABLE_STATS or simply re-gather with the options that worked for you.

    The trace file generated will reside in the USER_DUMP_DEST of the server and will have "10053_optimizer_trace" as part of the filename for easier identification if you followed above steps.

    Regards,
    Randolf

    Oracle related stuff blog:
    http://oracle-randolf.blogspot.com/

    SQLTools++ for Oracle (Open source Oracle GUI for Windows):
    http://www.sqltools-plusplus.org:7676/
    http://sourceforge.net/projects/sqlt-pp/
  • 42. Re: An optimizer Question
    635471 Expert
    Currently Being Moderated
    sorry to have dropped off there -- busy times.

    An option you might look into is backing up the current table and partition statistics, dropping them from the table completely and relying on dynamic sampling.

    You'd probably want to lock the statistics (well, the lack of statistics really) to make sure some automatic job doesn't go in and regather them over night, or some DBA doesn't just take it into his/her head to break your system by insisting on regathering statistics without telling or asking the development team why the table has no statistics, thus bringing down your daily load and causing you to be paged at 3am. Ask me how I know.
1 2 3 Previous Next

Legend

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