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

2 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