### 9.1) DBMS_UTILITY

Oracle
provides two procedures under the DBMS_UTILITY package related to statistics
generation. (Oracle recommends use of DBMS_STATS package for generating
statistics).

**DBMS_UTILITY.ANALYZE_SCHEMA**

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
ORA-06512: at line 1

**DBMS_UTILITY.ANALYZE_DATABASE**

DBMS_UTILITY.ANALYZE_DATABASE
is used for analyzing all tables, clusters and indexes at database level. It takes
the same set of parameters as above except for the schema name.

e.g.:
Estimating statistics for database with 30 percent sample.

SQL> exec dbms_utility.analyze_database('ESTIMATE', estimate_percent => 30);
PL/SQL procedure successfully completed.

### 9.2) ANALYZE command

The
ANALYZE command can also be used to collect statistics for individual objects.
The Object to be analyzed should belong to the local schema or the user should
have ANALYZE ANY TABLE system privilege. This command can be used for the
following purpose:

- Collect statistics for individual objects.
- Validate the structure of an object.
- To list migrated or chained rows.
- Validate REF links.
- Collect statistics not used by the optimizer.

Oracle
recommends the use of the DBMS_STATS package for collecting statistics. The
ANALYZE command can be used for the other 4 points mentioned above. Statistics are
not collected for columns of type- REFs, varrays, nested tables, LOBs , LONG or
object types.

If
no sample size is provided when estimating statistics with the ANALYZE command,
Oracle will take a default sample size of the first 1064 rows. This may not be
effective and most often will result in bad queries.

If the ESTIMATE sample size is greater than 50%, it is as good as the
COMPUTE option.

Columns
such as EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_SPACE_FREELIST_BLOCKS and NUM_FREELIST_BLOCKS
are not populated using the DBMS_STATS package. These are populated by using
the ANALYZE command and could be used for maintenance and administration
activities.

The below examples are given for statistics generation only.

e.g.:
Gathering statistics for a table. This will also compute for individual columns
and related indexes.

orAP>analyze table am_statchk compute statistics;
Table analyzed.

e.g.: Deleting statistics for a table. This
will delete statistics related to table, columns and related indexes.

orAP> analyze table am_statchk delete statistics;
Table analyzed.

e.g.: Estimating statistics for a table with
20 percent rows.

orAP>analyze table am_statchk estimate statistics sample 20 percent;
Table analyzed.

e.g.: Estimating statistics for a table with
1000 sample rows.

orAP>analyze table am_statchk estimate statistics sample 1000 rows;
Table analyzed.

e.g.: Gathering statistics for indexed
columns of a table.

orAP>analyze table am_statchk compute statistics for all indexed columns;
Table analyzed.

e.g.: Computing statistics for all columns in
a table.

orAP>analyze table am_statchk compute statistics for all columns;
Table analyzed.

e.g.: Computing statistics for individual
indexes.

orAP>analyze index am_statchk_n1 compute statistics;
Index analyzed.

e.g.: Deleting statistics for an index.

orAP>analyze index am_statchk_n1 delete statistics;
Index analyzed.

e.g.: Computing statistics for all indexes
and all indexed columns.

orAP>analyze table am21 estimate statistics sample 5 percent for all indexes for all indexed columns;
Table analyzed.

### 9.3) DBMS_DDL

DBMS_DDL has a routine ANALYZE_OBJECT meant for generating
statistics for individual objects (Oracle
recommends use of DBMS_STATS package for generating statistics).

*Parameters*

- type - TABLE, INDEX or CLUSTER.
- schema - schema name.
- name - name of the object.
- method - ESTIMATE, COMPUTE or DELETE.
- estimate_rows - sampling no. of rows.
- estimate_percent - sampling percentage of rows.
- method_opt - method options
FOR ALL TABLES, FOR ALL COLUMNS, FOR ALL INDEXED COLUMNS,
FOR ALL INDEXES.
- partname - partition, if present, to be analyzed.

e.g.: Below call will generate statistics for
table AM21 and its columns and indexes.

orAP>exec dbms_ddl.analyze_object(type => 'TABLE', schema => 'APPS', name => 'AM21', -
> method => 'ESTIMATE', estimate_percent => 5);
PL/SQL procedure successfully completed.

e.g.: Deleting all statistics related to a
table

orAP>exec dbms_ddl.analyze_object(type => 'TABLE', schema => 'APPS', name => 'AM21', -
> method => 'DELETE');
PL/SQL procedure successfully completed.