Home > Oracle > Gathering statistics with DBMS_STATS procedures

Gathering statistics with DBMS_STATS procedures

Gather stats using the DBMS_STATS package.  The package can also be used to modify , view , export , import and delete statistics

Collect statistics on Table level


exec dbms_stats.gather_table_stats ( -
     ownname          => 'SCOTT', -
     tabname          => 'EMP', -
     estimate_percent => dbms_stats.auto_sample_size, -
     method_opt       => 'for all columns size auto', -
     cascade          => true, -
     degree           => 5 -
)
<strong>/
</strong><span style="color: #000000;">PL/SQL procedure successfully completed.</span>

<span style="color: #000000;">

 

Collect Statsitics on Schema Level

 

exec dbms_stats.gather_schema_stats ( -
     ownname          => 'SCOTT', -
     options          => 'GATHER', -
     estimate_percent => dbms_stats.auto_sample_size, -
     method_opt       => 'for all columns size auto', -
     cascade          => true, -
     degree           => 5 -
)
<strong>/
</strong><span style="color: #000000;">PL/SQL procedure successfully completed.</span>

<span style="color: #000000;">

 

options

gather Reanalyzes the whole schema
gather empty Only analyzes tables that have no existing statistics
gather stale Only reanalyzes tables with more than 10% modifications (inserts, updates, deletes).
gather auto Reanalyzes objects which currently have no statistics and objects with stale statistics (Using gather auto is like combining gather stale and gather empty.)

useful Views

  • USER_TAB_COL_STATISTICS
  • USER_PART_COL_STATISTICS
  • USER_SUBPART_COL_STATISTICS
  • 
    select
    COLUMN_NAME, NUM_DISTINCT, HISTOGRAM, NUM_BUCKETS,
    to_char(LAST_ANALYZED,'yyyy-dd-mm hh24:mi:ss') LAST_ANALYZED
    from user_tab_col_statistics
    where table_name='SKEWED_DATA'
    
    

    if you need to know more about method_opt good site to look at is

    http://structureddata.org/2008/10/14/dbms_stats-method_opt-and-for-all-indexed-columns/

    About these ads
    Categories: Oracle
    1. No comments yet.
    1. No trackbacks yet.

    Leave a Reply

    Fill in your details below or click an icon to log in:

    WordPress.com Logo

    You are commenting using your WordPress.com account. Log Out / Change )

    Twitter picture

    You are commenting using your Twitter account. Log Out / Change )

    Facebook photo

    You are commenting using your Facebook account. Log Out / Change )

    Google+ photo

    You are commenting using your Google+ account. Log Out / Change )

    Connecting to %s

    Follow

    Get every new post delivered to your Inbox.

    Join 123 other followers

    %d bloggers like this: