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.

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: 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


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

    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

    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?

    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?

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

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

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

    7. Oracle have estimate_percent parameter for dbms_stats.gather_table_stats too. check this

    8. where the query result is stored when we run this command

  2. What is the use of degree in this ?

  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.

    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.

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

    1. check the oracle docs for Automatic Statistics Gathering

  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?

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

    My web-site :: 0 ()

  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.

  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.

  9. Tambem guarde ߋѕ cartoes em um porta-cartoes decente.

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

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

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

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

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

  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?

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

  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.

  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.

  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.

  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!

  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

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

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

  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.

  25. Hello, of course this piece of writing is in fact nice and I
    have learned lot of things from it on the topic of blogging.


  26. Thankfulness to my father whoo shared with me aboout this blog, this web site is genuinely awesome.

  27. I needed to thank you for this excellent read!!
    I absolutely enjoyed every bit of it. I've got you bookmarked to check out new stuff
    you post…

  28. Difference between dbms_stats and analyze command