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.