|Oracle® Database Administrator's Guide
11g Release 2 (11.2)
Part Number E17120-05
Collect and manage statistics for it
Verify the validity of its storage format
Identify migrated and chained rows of a table or cluster
Note:Do not use the
ANALYZEto collect optimizer statistics. These clauses have been deprecated. Instead, use the
DBMS_STATSpackage, which lets you collect statistics in parallel, collect global statistics for partitioned objects, and fine tune your statistics collection in other ways. The cost-based optimizer, which depends upon statistics, will eventually use only statistics that have been collected by
DBMS_STATS. See Oracle Database PL/SQL Packages and Types Reference for more information on the
You must use the
ANALYZE statement (rather than
DBMS_STATS) for statistics collection not related to the cost-based optimizer, such as:
To use the
To collect information on freelist blocks
The following topics are discussed in this section:
You can use the
DBMS_STATS package or the
ANALYZE statement to gather statistics about the physical storage characteristics of a table, index, or cluster. These statistics are stored in the data dictionary and can be used by the optimizer to choose the most efficient execution plan for SQL statements accessing analyzed objects.
Oracle recommends using the more versatile
DBMS_STATS package for gathering optimizer statistics, but you must use the
ANALYZE statement to collect statistics unrelated to the optimizer, such as empty blocks, average space, and so forth.
DBMS_STATS package allows both the gathering of statistics, including utilizing parallel execution, and the external manipulation of statistics. Statistics can be stored in tables outside of the data dictionary, where they can be manipulated without affecting the optimizer. Statistics can be copied between databases or backup copies can be made.
DBMS_STATS procedures enable the gathering of optimizer statistics:
Oracle Database Performance Tuning Guide for information about using
DBMS_STATS to gather statistics for the optimizer
Oracle Database PL/SQL Packages and Types Reference for a description of the
To verify the integrity of the structure of a table, index, cluster, or materialized view, use the
ANALYZE statement 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.
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 the index is corrupt, you can drop and re-create it.
If a table, index, or cluster is corrupt, you should drop it and re-create it. If a materialized view is corrupt, perform a complete refresh and ensure that you have remedied the problem. If the problem is not corrected, drop and re-create the materialized view.
The following statement analyzes the
ANALYZE TABLE emp VALIDATE STRUCTURE;
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE;
By default the
CASCADE option performs a complete validation. Because this operation can be resource intensive, you can perform a faster version of the validation by using the
FAST clause. This version checks for the existence of corruptions using an optimized check algorithm, but does not report details about the corruption. If the
FAST check finds a corruption, you can then use the
CASCADE option without the
FAST clause to locate it. The following statement performs a fast validation on the
emp table and all associated indexes:
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE FAST;
You can specify that you want to perform structure validation online while DML is occurring against the object being validated. There can be a slight performance impact when validating with ongoing DML affecting the object, but this is offset by the flexibility of being able to perform
ANALYZE online. The following statement validates the
emp table and all associated indexes online:
ANALYZE TABLE emp VALIDATE STRUCTURE CASCADE ONLINE;
See Also:Oracle Database SQL Language Reference for more information on the
You can look at the chained and migrated rows of a table or cluster using the
ANALYZE statement with the
ROWS clause. The results of this statement are stored in a specified table created explicitly to accept the information returned by the
ROWS clause. These results are useful in determining whether you have enough room for updates to rows.
To create the table to accept data returned by an
ROWS statement, execute the
UTLCHN1.SQL script. These scripts are provided by the database. They create a table named
CHAINED_ROWS in the schema of the user submitting the script.
Note:Your choice of script to execute for creating the
CHAINED_ROWStable is dependent upon the compatibility level of your database and the type of table you are analyzing. See the Oracle Database SQL Language Reference for more information.
CHAINED_ROWS table is created, you specify it in the
INTO clause of the
ANALYZE statement. For example, the following statement inserts rows containing information about the chained rows in the
emp_dept cluster into the
ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;
ANALYZE statement to collect information about migrated and chained rows.
ANALYZE TABLE order_hist LIST CHAINED ROWS;
Query the output table:
SELECT * FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST'; OWNER_NAME TABLE_NAME CLUST... HEAD_ROWID TIMESTAMP ---------- ---------- -----... ------------------ --------- SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAA 04-MAR-96 SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAB 04-MAR-96 SCOTT ORDER_HIST ... AAAAluAAHAAAAA1AAC 04-MAR-96
The output lists all rows that are either migrated or chained.
If the output table shows that you have many migrated or chained rows, then you can eliminate migrated rows by continuing through the following steps:
Create an intermediate table with the same columns as the existing table to hold the migrated and chained rows:
CREATE TABLE int_order_hist AS SELECT * FROM order_hist WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST');
Delete the migrated and chained rows from the existing table:
DELETE FROM order_hist WHERE ROWID IN (SELECT HEAD_ROWID FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST');
Insert the rows of the intermediate table into the existing table:
INSERT INTO order_hist SELECT * FROM int_order_hist;
Drop the intermediate table:
DROP TABLE int_order_history;
Delete the information collected in step 1 from the output table:
DELETE FROM CHAINED_ROWS WHERE TABLE_NAME = 'ORDER_HIST';
ANALYZE statement again, and query the output table.
Any rows that appear in the output table are chained. You can eliminate chained rows only by increasing your data block size. It might not be possible to avoid chaining in all situations. Chaining is often unavoidable with tables that have a
LONG column or large