New Features for DBA’s

Subscribe to Posts by Email


 

Subscriber Count

    699

Disclaimer

All information is offered in good faith and in the hope that it may be of use for educational purpose and for Database community purpose, but is not guaranteed to be correct, up to date or suitable for any particular purpose. db.geeksinsight.com accepts no liability in respect of this information or its use. This site is independent of and does not represent Oracle Corporation in any way. Oracle does not officially sponsor, approve, or endorse this site or its content and if notify any such I am happy to remove. Product and company names mentioned in this website may be the trademarks of their respective owners and published here for informational purpose only. This is my personal blog. The views expressed on these pages are mine and learnt from other blogs and bloggers and to enhance and support the DBA community and this web blog does not represent the thoughts, intentions, plans or strategies of my current employer nor the Oracle and its affiliates or any other companies. And this website does not offer or take profit for providing these content and this is purely non-profit and for educational purpose only. If you see any issues with Content and copy write issues, I am happy to remove if you notify me. Contact Geek DBA Team, via geeksinsights@gmail.com

Pages
«  
  »

11g Compare statistics

Compare statistics, good idea, Ok, here is the scenario

You have a large table where you have collected the statistics with sample of 10%, and due to some reasons you want to collect the statistics with auto or 100% sample size, but you are unsure of the difference it make. Note its not about to restoring the old statistics, but its about to compare the statistics changes over the period.

Oracle 11g (R1) comes up with three new procedures in dbms_stats package.

  • DBMS_STAT.DIFF_TABLE_STATS_IN_HISTORY (opt stats history)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_PENDING (pending stats)
  • DBMS_STAT.DIFF_TABLE_STATS_IN_STATTAB (statistics table , backup of your statistics)

    The DIFF_TABLE_STATS_* statistics can be used to compare statistics for a table from two different sources. The statistics can be from:

    • two different user statistics tables

    a single user statistics table containing two sets of statistics that can be identified using statids

    a user statistics table and dictionary history

    pending statistics

      The functions also compare the statistics of the dependent objects (indexes, columns, partitions). They displays statistics of the object(s) from both sources if the difference between those statistics exceeds a certain threshold (%). The threshold can be specified as an argument to the function, with a default of 10%. The statistics corresponding to the first source (stattab1 or time1) will be used as basis for computing the differential percentage.

  • For example,

    you table collected the stats at some estimate percent.

    -- Gather statistics with a manually specified sample size

    BEGIN dbms_stats.gather_table_stats('SH','SALES', estimate_percent=>'0.000001'); END; /

    -- Copy statisitcs gathered with manually set sample size to

    exec dbms_stats.export_table_stats  ('SH','SALES',stattab=>'MY_STAT_TAB',statid=>'manually_set_sample_size'); END; /

    -- delete existing statistics

    BEGIN dbms_stats.delete_table_stats('SH','SALES'); END; /

    -- Gather statistics with AUTO_SAMPLE_SIZE

    BEGIN dbms_stats.gather_table_stats('SH','SALES'); END; /

    Now using dbms_stats.diff_table_stats_in_stattab you can compare the existing statistics and the old statistics in the statistics table.

    set long 99999999

    set longchunksize 99999

    Select * From table(dbms_stats.diff_table_stats_in_stattab('SH','SALES', 'MY_STAT_TAB', statid1=>'MANUALLY_SET_SAMPLE_SIZE')); --

    Sample Output:-

    Source:- https://blogs.oracle.com/optimizer/entry/how_do_i_compare_statistics

    -Thanks

    Geek DBA

    • November 22nd, 2012 | Tags: | Category: Statistics

    Comments are closed.