Oracle7 Server Administrator's Guide

Contents Index Home Previous Next

Analyzing Tables, Indexes, and Clusters

This section describes how to analyze tables, indexes, and clusters, and includes the following topics:

You can analyze a table, index, or cluster to gather data about it, or to verify the validity of its storage format. To analyze a table, cluster, or index, you must own the table, cluster, or index or have the ANALYZE ANY system privilege.

These schema objects can also be analyzed to collect or update statistics about specific objects. When a DML statement is issued, the statistics for the referenced objects are used to determine the most efficient execution plan for the statement. This optimization is called "cost-based optimization." The statistics are stored in the data dictionary.

A table, index, or cluster can be analyzed to validate the structure of the object. For example, in rare cases such as hardware or other system failures, an index can become corrupted and not perform correctly. When validating the index, you can confirm that every entry in the index points to the correct row of the associated table. If a schema object is corrupt, you can drop and re-create it.

A table or cluster can be analyzed to collect information about chained rows of the table or cluster. These results are useful in determining whether you have enough room for updates to rows. For example, this information can show whether PCTFREE is set appropriately for the table or cluster.

See Also: For more information about analyzing tables, indexes, and clusters for performance statistics and the optimizer, see the Oracle7 Server Tuning guide.

Using Statistics for Tables, Indexes, and Clusters

Statistics about the physical storage characteristics of a table, index, or cluster can be gathered and stored in the data dictionary using the SQL command ANALYZE with the STATISTICS option. Oracle can use these statistics when cost-based optimization is employed to choose the most efficient execution plan for SQL statements accessing analyzed objects. You can also use statistics generated by this command to write efficient SQL statements that access analyzed objects.

You can compute or estimate statistics using the ANALYZE command, with either the COMPUTE STATISTICS or ESTIMATE STATISTICS option:

COMPUTE STATISTICS When computing statistics, an entire object is scanned to gather data about the object. This data is used by Oracle to compute exact statistics about the object. Slight variances throughout the object are accounted for in these computed statistics. Because an entire object is scanned to gather information for computed statistics, the larger the size of an object, the more work that is required to gather the necessary information.
ESTIMATE STATISTICS When estimating statistics, Oracle gathers representative information from portions of an object. This subset of information provides reasonable, estimated statistics about the object. The accuracy of estimated statistics depends upon how representative the sampling used by Oracle is. Only parts of an object are scanned to gather information for estimated statistics, so an object can be analyzed quickly. You can optionally specify the number or percentage of rows that Oracle should use in making the estimate.
Note: When calculating statistics for tables or clusters, the amount of temporary space required to perform the calculation is related to the number of rows specified. For COMPUTE STATISTICS, enough temporary space to hold and sort the entire table plus a small overhead for each row is required. For ESTIMATE STATISTICS, enough temporary space to hold and sort the requested sample of rows plus a small overhead for each row is required. For indexes, no temporary space is required for analyzing.

Viewing Object Statistics

Whether statistics for an object are computed or estimated, the statistics are stored in the data dictionary. The statistics can be queried using the following data dictionary views:

Note: Rows in these views contain entries in the statistics columns only for indexes, tables, and clusters for which you have gathered statistics. The entries are updated for an object each time you ANALYZE the object.

Table Statistics You can gather the following statistics on a table: Note: The * symbol indicates that the numbers will always be an exact value when computing statistics.

Note: Statistics for all indexes associated with a table are automatically gathered when the table is analyzed.

Index Statistics You can gather the following statistics on an index:

Cluster Statistics The only statistic that can be gathered for a cluster is the average cluster key chain length; this statistic can be estimated or computed. Statistics for tables in a cluster and all indexes associated with the cluster's tables (including the cluster key index) are automatically gathered when the cluster is analyzed for statistics. Note: If the data dictionary currently contains statistics for the specified object when an ANALYZE statement is issued, the new statistics replace the old statistics in the data dictionary.

Computing Statistics

The following statement computes statistics for the EMP table:

ANALYZE TABLE emp COMPUTE STATISTICS;

The following query estimates statistics on the EMP table, using the default statistical sample of 1064 rows:

ANALYZE TABLE emp ESTIMATE STATISTICS;

To specify the statistical sample that Oracle should use, include the SAMPLE option with the ESTIMATE STATISTICS option. You can specify an integer that indicates either a number of rows or index values, or a percentage of the rows or index values in the table. The following statements show examples of each option:

ANALYZE TABLE emp
   ESTIMATE STATISTICS
      SAMPLE 2000 ROWS;
ANALYZE TABLE emp
   ESTIMATE STATISTICS
      SAMPLE 33 PERCENT;

In either case, if you specify a percentage greater than 50, or a number of rows or index values that is greater than 50% of those in the object, Oracle computes the exact statistics, rather than estimating.

Removing Statistics for a Schema Object

You can remove statistics for a table, index, or cluster from the data dictionary using the ANALYZE command with the DELETE STATISTICS option. For example, you might want to delete statistics for an object if you do not want cost-based optimization to be used for statements regarding the object. The following statement deletes statistics for the EMP table from the data dictionary:

ANALYZE TABLE emp DELETE STATISTICS;

Shared SQL and Analyzing Statistics

Analyzing a table, cluster, or index can affect current shared SQL statements, which are statements currently in the shared pool. Whenever an object is analyzed to update or delete statistics, all shared SQL statements that reference the analyzed object are flushed from memory so that the next execution of the statement can take advantage of the new statistics.

You can call the following procedures:

DBMS_UTILITY.- ANALYZE_SCHEMA() This procedure takes two arguments, the name of a schema and an analysis method ('COMPUTE', 'ESTIMATE', or 'DELETE'), and gathers statistics on all of the objects in the schema.
DBMS_DDL.- ANALYZE_OBJECT() This procedure takes four arguments, the type of an object ('CLUSTER', 'TABLE', or 'INDEX'), the schema of the object, the name of the object, and an analysis method ('COMPUTE', 'ESTIMATE', or 'DELETE'), and gathers statistics on the object.
You should call these procedures periodically to update the statistics.

Validating Tables, Indexes, and Clusters

To verify the integrity of the structure of a table, index, cluster, or snapshot, use the ANALYZE command with the VALIDATE STRUCTURE option. If the structure is valid, no error is returned. However, if the structure is corrupt, you receive an error message. If a table, index, or cluster is corrupt, you should drop it and re-create it. If a snapshot is corrupt, perform a complete refresh and ensure that you have remedied the problem; if not, drop and re-create the snapshot.

The following statement analyzes the EMP table:

ANALYZE TABLE emp VALIDATE STRUCTURE;

You can validate an object and all related objects by including the CASCADE option. The following statement validates the EMP table and all associated indexes:

ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;

Listing Chained Rows of Tables and Clusters

You can look at the chained and migrated rows of a table or cluster using the ANALYZE command with the LIST CHAINED ROWS option. The results of this command are stored in a specified table created explicitly to accept the information returned by the LIST CHAINED ROWS option.

To create an appropriate table to accept data returned by an ANALYZE... LIST CHAINED ROWS statement, use the UTLCHAIN.SQL script provided with Oracle. The UTLCHAIN.SQL script creates a table named CHAINED_ROWS in the schema of the user submitting the script.

After a CHAINED_ROWS table is created, you can specify it when using the ANALYZE command. For example, the following statement inserts rows containing information about the chained rows in the EMP_DEPT cluster into the CHAINED_ROWS table:

ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO chained_rows;

See Also: The name and location of the UTLCHAIN.SQL script are operating system-dependent; see your operating system-specific Oracle documentation.

For more information about reducing the number of chained and migrated rows in a table or cluster, see Oracle7 Server Tuning.

Contents Index Home Previous Next