Oracle: DBMS_UTILITY.ANALYZE_SCHEMA

DBMS_UTILITY.ANALYZE_SCHEMA  is used to gather statistics for all the tables, clusters and indexes of a schema.

Examples:

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','COMPUTE');

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_rows => 1000);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','ESTIMATE', estimate_percent => 25);

exec DBMS_UTILITY.ANALYZE_SCHEMA('SCOTT','DELETE');

Note: It's also possible to analyze the whole database with the DBMS_UTILITY.ANALYZE_DATABASE('COMPUTE'); command.

Related Posts:
- DBMS_STATS Gather Statistics of Schema, Tables, Indexes
- 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

5 comments:

  1. Hi All,

    What is the difference between stats gathering and analyzing the table or index or shema level. It would be great if you could assist me on this. Thanks in advance!

    Regards
    Karthik

    ReplyDelete
    Replies
    1. This link may help you
      https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:4347359891525

      Delete
  2. I'm gone to tell my little brother, that he should also pay a visit this website on regular basis to take updated from most up-to-date news.

    ReplyDelete
  3. Thanks on your marvelous posting! I definitely enjoyed reading it, you
    happen to be a great author.I will be sure to bookmar youyr blog and
    will eventually come back later in life. I want to encourage continue your great
    writing, have a nice day!

    ReplyDelete
  4. I always emailed this weblog post page to all my associates, as if like to read it then my contacts will too.

    ReplyDelete