Oracle’s dbms_utility package contains a lot of neat functionality
for the database developer. Today we?re going to look at the
analyze_schema procedure.

The analyze_schema procedure can be used to generate statistics
for Oracle’s cost-based optimizer. The parameters are:

Procedure analyze_schema (
Schema varchar2,
Method varchar2,
Estimate_rows number default null,
Estimate_percent number default null);

The first parameter is the schema owner (for example, ‘scott’) that
will be analyzed. The next parameter should either be null to do a
complete analysis (which may take quite a while!) or the string

If you choose to do an estimate you have to specify the number of
rows to use (using the parameter estimate_rows) or a percentage of
rows (using estimate_percent).

For example, to analyze the scott user, you would use the following
command from SQL*Plus:

exec dbms_utility.analyze_schema(‘SCOTT’,’ESTIMATE’,null,10);

Note that (at least in version 8.1.5) the schema name is
case-sensitive — it must be uppercase.

Usually 10 to 30 percent is plenty to get a good representation of
your data distributions for the optimizer.

How to use DBMS_UTILITY to analyse a schema
Tagged on:

Leave a Reply

Your email address will not be published. Required fields are marked *

+ nine = ten