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
- Doubt: Truncate Command and Table Statistics

36 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
  9. Tambem guarde ߋѕ cartoes em um porta-cartoes decente.

    ReplyDelete
  10. Voce dеve e' perder ɑ vergonha dе dar cartoes.

    ReplyDelete
  11. Estes dias me pediram ᥙma cartao ᥱm papel
    aspen!

    ReplyDelete
  12. Oi, adorei artigo, e' muito simples de seguir.

    ReplyDelete
  13. Nao me canso de vir e ir ao seu blog. Sucesso!
    Ola'.

    ReplyDelete
  14. Cartao ficou mսito bonito, e diferente!

    ReplyDelete
  15. wonderful points altogether, you just gained a new reader.
    What would you suggest about your publish that you just made some
    days in the past? Any certain?

    ReplyDelete
  16. Voce ainda nao uѕa um cartao Ԁе visita?

    ReplyDelete
  17. In fact when someone doesn't be aware of then its up to other visitors that
    they will help, so here it takes place.

    ReplyDelete
  18. I think that what you posted was actually very reasonable.
    However, think on this, suppose you added a
    little information? I am not suggesting your information isn't good, but
    what if you added a title that makes people desire more?
    I mean "Oracle: DBMS_STATS Gather Statistics of Schema, Tables, Indexes" is a little plain. You might peek at Yahoo's home page and see how they create
    post titles to get viewers interested. You
    might try adding a video or a pic or two to get people excited about
    what you've got to say. In my opinion, it would make your posts a
    little bit more interesting.

    ReplyDelete
  19. That is absolutely the situation for those who are self-employed that home based.
    By building a consistent working arrangements, you can stick to task and time.

    ReplyDelete
  20. I ѡas recommended this blog by my cousin. I'm not sure whether this post іs written by him as nobody else know such detаiled about my trouble.
    You are incredible! Tһanks!

    ReplyDelete
  21. Excellent beat ! I would like to apprentice while you amend your web site, how can i subscribe for a blog
    site? The account aided me a acceptable deal. I had been a little bit acquainted of this your broadcast provided bright clear idea

    ReplyDelete
  22. Why people still use to read news papers when in this technological
    globe the whole thing is available on web?

    ReplyDelete
  23. Whhat a incormation of un-ambiguity and preserveness of precious know-how
    about unexpected emotions.

    ReplyDelete
  24. It's acttually a nice annd useful piece of information. I'm happy
    that you just shared this helpful information with
    us. Please keep us informed like this. Thanks for sharing.

    ReplyDelete