This routine will generate statistics on an individual schema level. It is used for analyzing all tables, clusters and indexes.

It takes the following parameters:

• schema - Name of the schema
• method - Estimation method, COMPUTE or ESTIMATE. DELETE can be used to remove statistics.
• estimate_rows - The number of rows to be considered for estimation.
• estimate_percent - The percentage of rows to be considered for estimation.
• method_opt - Method options. Generate statistics FOR TABLE, FOR ALL COLUMNS, FOR ALL INDEXED COLUMNS, FOR ALL INDEXES.

If the ESTIMATE method is used, then either estimate_rows or estimate_percent should be specified; these actually specify the sample size to be considered.

Call syntax

dbms_utility.analyze_schema(schema, method, estimate_rows, estimate_percent,  method_opt)

e.g.: Computing statistics for a schema

SQL> exec dbms_utility.analyze_schema('SYSTEM', 'COMPUTE');

PL/SQL procedure successfully completed.

e.g.: Estimating statistics for a schema, sample size is 1024 row.

SQL> exec dbms_utility.analyze_schema('FEM', 'ESTIMATE', estimate_rows => 1024);

PL/SQL procedure successfully completed.

e.g.: Estimating statistics for FA schema, sample size is 10 percent of rows.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 10);

PL/SQL procedure successfully completed.

e.g.: Deleting statistics for FA schema

SQL> exec dbms_utility.analyze_schema('FA', 'DELETE');

PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for all indexes in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 

          method_opt => 'FOR ALL INDEXES');

PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for columns with indexes in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 

          method_opt => 'FOR ALL INDEXED COLUMNS');

PL/SQL procedure successfully completed.

e.g.: Estimating statistics with 5 percent rows for all columns in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 

          method_opt => 'FOR ALL COLUMNS');

PL/SQL procedure successfully completed.

e.g.: Estimating statistics for all tables in a schema.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => 5, 

          method_opt => 'FOR TABLE');

PL/SQL procedure successfully completed.

e.g.: Proper sample size should be given, otherwise ORA-01493 is encountered.

SQL> exec dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => -5);

BEGIN dbms_utility.analyze_schema('FA', 'ESTIMATE', estimate_percent => -5); END;

*

ERROR at line 1:

ORA-01493: invalid SAMPLE size specified

ORA-06512: at "SYS.DBMS_DDL", line 179

ORA-06512: at "SYS.DBMS_UTILITY", line 331