You analyze a schema object (table, index, or cluster) to:
The following topics are discussed in this section:
Oracle recommends using the more versatile
The
The following
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
After a
-
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 COMPUTE
and ESTIMATE
clauses of ANALYZE
to collect optimizer statistics. These clauses are supported for backward compatibility. Instead, use the DBMS_STATS
package, 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 DBMS_STATS
package.
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
VALIDATE
orLIST
CHAINED
ROWS
clauses -
To collect information on freelist blocks
Using DBMS_STATS to Collect Table and Index Statistics
You can use theDBMS_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.The
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.The following
DBMS_STATS
procedures enable the gathering of optimizer statistics:-
GATHER_INDEX_STATS
-
GATHER_TABLE_STATS
-
GATHER_SCHEMA_STATS
-
GATHER_DATABASE_STATS
See Also:-
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
DBMS_STATS
package
-
Oracle Database Performance Tuning Guide for information about using
Validating Tables, Indexes, Clusters, and Materialized Views
To verify the integrity of the structure of a table, index, cluster, or materialized view, use theANALYZE
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
emp
table:ANALYZE TABLE emp VALIDATE STRUCTURE;You can validate an object and all dependent objects (for example, indexes) by including the
CASCADE
option. The following statement validates the emp
table and all associated indexes: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;
Listing Chained Rows of Tables and Clusters
You can look at the chained and migrated rows of a table or cluster using theANALYZE
statement with the LIST
CHAINED
ROWS
clause. The results of this statement are stored in a specified table
created explicitly to accept the information returned by the LIST
CHAINED
ROWS
clause. These results are useful in determining whether you have enough room for updates to rows.Creating a CHAINED_ROWS Table
To create the table to accept data returned by anANALYZE...LIST
CHAINED
ROWS
statement, execute the UTLCHAIN.SQL
or 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_ROWS
table 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 CHAINED_ROWS
table:ANALYZE CLUSTER emp_dept LIST CHAINED ROWS INTO CHAINED_ROWS;
See Also:
-
Oracle Database Reference for a description of the
CHAINED_ROWS
table
-
"Using the Segment Advisor" for information on how the Segment Advisor reports tables with excess row chaining.
Eliminating Migrated or Chained Rows in a Table
You can use the information in theCHAINED_ROWS
table to reduce or eliminate migrated and chained rows in an existing table. Use the following procedure.-
Use the
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';
-
Use the
ANALYZE
statement again, and query the output table.
LONG
column or large CHAR
or VARCHAR2
columns.
No comments:
Post a Comment