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 
'ESTIMATE.'

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.