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
        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
          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
            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
              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
                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
                  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
                    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
                      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
                        what is now the number of rows for the partition only and for the table ?
                        • 39. Re: An optimizer Question
                          729555
                          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
                            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
                              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
                                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