Oracle: DBMS_STATS Gather Statistics of Schema, Tables, Indexes

DBMS_STATS package, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. This package is concerned with optimizer statistics only.
dbms_stats is essential to good SQL performance, and it should always be used before adjusting any of the Oracle optimizer initialization parameters.

Syntax:
exec DBMS_STATS.GATHER_SCHEMA_STATS(ownname,estimate_percent, block_sample , method_opt,degree,granularity,cascade,stattab, statid,options,statown ,no_invalidate, gather_temp,gather_fixed);

Code examples:
exec dbms_stats.gather_schema_stats('SCOTT',DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_schema_stats(ownname=>'SCOTT', estimate_percent=>DBMS_STATS.AUTO_SAMPLE_SIZE);

exec dbms_stats.gather_schema_stats(ownname => 'SCOTT', estimate_percent => 25);

exec dbms_stats.gather_table_stats('SCOTT', 'EMPLOYEES');

exec dbms_stats.gather_index_stats('SCOTT', 'EMPLOYEES_PK');

exec dbms_stats.delete_schema_stats('SCOTT');

Related Posts:
- Oracle: DBMS_UTILITY.ANALYZE_SCHEMA
- Oracle: Analyze Table or Index
- Query Optimization Tips for Oracle
- DBMS_PROFILER: Overview and How to Install
- Oracle : TKPROF simple steps
- SP2-0618: Cannot find the Session Identifier. Check PLUSTRACE role is enabled

20 comments:

  1. can u provide brief description of (cascade and estimate_percent options)

    ReplyDelete
    Replies
    1. 1. Cascade determines if index stats should be gathered for the current table.
      2. ESTIMATE_PERCENT is percentage of rows to sample when gathering stats

      Delete
    2. Good Nimish Garg.Supoose I have 2000 rows in a table "hari".If I give estimate_percant=25. Will It gather statictics upto 25% of my total rows in table "hari" that is equal to 500 rows?

      Delete
    3. Good Sir.I am visiting ur blog daily...
      I have a small doubt.Suppose I my table hari contains 2000 rows,If I give estimate_percent is 25,Will It gather statictics upto 500 rows or not?

      Delete
    4. Yes Oracle with gather stats on 25% of random rows on your table.

      Delete
    5. .k thank u for clarified my doubt..

      Delete
    6. How can we analyze the table with 25% of statistics?..

      Delete
    7. Oracle have estimate_percent parameter for dbms_stats.gather_table_stats too. check this http://docs.oracle.com/cd/B28359_01/appdev.111/b28419/d_stats.htm#i1036461

      Delete
    8. where the query result is stored when we run this command
      dbms_stats.gather_table_stats('scott','emp');

      Delete
  2. What is the use of degree in this ?

    ReplyDelete
  3. Hello Nimish,

    I have simple doubt, but need to get clarify.

    What is the purpose of taking gathering stats?
    Can you please explain in brief.

    ReplyDelete
    Replies
    1. There are times when stats stored in database are stale, so oracle optimizer is not able create good execution plan. To help optimizer generating optimal execution plan we need that our database must have latest stats, so we/database itselft gather them.

      Delete
  4. Will Database itself gathers stats? if so can you please let me know the interval?

    ReplyDelete
    Replies
    1. check the oracle docs for Automatic Statistics Gathering
      http://docs.oracle.com/cd/B13789_01/server.101/b10752/stats.htm#40674

      Delete
  5. How do you retrieve the statistic after executing the following query for example:

    exec dbms_stats.gather_index_stats('AFLOBIDW', 'XXAFL_MON_FACT_INTEGRATION_ID');

    I get the following error:

    Error starting at line : 3 in command -
    exec dbms_stats.gather_index_stats('AFLOBIDW', 'XXAFL_MON_FACTS_F')
    Error report -
    ORA-20000: Unable to analyze INDEX "AFLOBIDW"."XXAFL_MON_FACTS_F", insufficient privileges or does not exist
    ORA-06512: at "SYS.DBMS_STATS", line 18341
    ORA-06512: at "SYS.DBMS_STATS", line 18402
    ORA-06512: at line 1
    20000. 00000 - "%s"
    *Cause: The stored procedure 'raise_application_error'
    was called which causes this error to be generated.
    *Action: Correct the problem as described in the error message or contact
    the application administrator or DBA for more information.


    Once I clear the error will the statistics be displayed on the Output screen?

    ReplyDelete
  6. Pretty! This was an incredibly wonderful post.
    Thanks for supplying this information.

    My web-site :: 0 ()

    ReplyDelete
  7. Is it possible to use the GATHER_SCHEMA_STATS while the users are connected to database through an application and working in it? How much time will it take to complete the execution for approximately 90GB database? An early reply is highly appreciated.

    ReplyDelete
  8. It is advisable to gather stats during low load operations although you can gather stats while you are working.
    Understand your question once again, you are gathering stats for schema and your DB size is 90GB, time taken will depend upon the size of the schema not on DB size.

    ReplyDelete