Oracle Tips by
The DBMS_DDL package contains only two procedures—Alter_Compile()
and Analyze_Object() —neither of which performs traditional DDL
commands like CREATE TABLE:
The Alter_Compile() procedure is used to
recompile procedures, functions, packages, and package bodies.
The Analyze_Object() procedure is used
to calculate statistics for use by the cost-based optimizer.
Each of these procedures is described in the following sections,
with definitions of the types and numbers of parameters.
The Alter_Compile() Procedure
The Alter_Compile() procedure is called to recompile a
specific stored PL/SQL object. The procedure accepts three parameters:
type, schema, and name.
PROCEDURE Alter_Compile (type IN varchar2,
schema IN varchar2,
name IN varchar2)
The type parameter indicates if the object is a procedure,
function, package body, or package spec. The schema parameter
indicates the name of the schema that owns the specified object. The
name parameter is the name of the object that should be
The Analyze_Object() Procedure
The Analyze_Object() procedure is called to estimate or
calculate statistics for a table, cluster, or index. The statistics
generated are used by the cost-based optimizer to determine the
optimal execution path for DML statements. The procedure accepts six
parameters: type, schema, name, method,
estimate_rows, and estimate_percent.
PROCEDURE Analyze_Object (type IN varchar2,
schema IN varchar2,
name IN varchar2,
method IN varchar2,
estimate_rows IN number := NULL,
estimate_percent IN number := NULL)
The first three parameters identify the object for which statistics
will be generated:
type—Indicates the type of the object
for which statistics will be generated. This must be ‘CLUSTER’,
‘INDEX’, or ‘TABLE’.
schema—Indicates the owner of the
name—Indicates the name of the object.
The remaining parameters instruct the procedure about how to
generate statistics for the object:
method—Must either be NULL or
contain the string ‘ESTIMATE’. If the parameter isn’t NULL,
then one of the next two parameters must contain a value that
indicates the sampling to be performed so that statistics can be
estimated for the object. If the parameter is NULL,
statistics will be computed.
estimate_rows—Specifies the number of
rows from the object that should be used to estimate statistics for
percentage of rows from the object that should be used to estimate
This is an excerpt from the book "High Performance Oracle
Database Automation" by Jonathan Ingram and Donald K.
Burleson, Series Editor.