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.
Those ANALYZE capabilities not recommended by Oracle are not documented here. |
| |
| Create Table To Hold Validation Output |
utlvalid.sql |
SQL> @?/rdbms/admin/utlvalid.sql
desc invalid_rows |
| Create Table To Hold Chained Row Output |
utlvalid.sql |
SQL>
@?/rdbms/admin/utlchn1.sql
desc chained_rows |
| |
| CLUSTER |
Create Demo Cluster |
CREATE CLUSTER uw_cluster (
table_name VARCHAR2(30))
SIZE 512;
CREATE INDEX ix_tabnames ON CLUSTER uw_cluster;
CREATE TABLE uwtables
CLUSTER uw_cluster (table_name) AS
SELECT table_name, tablespace_name
FROM all_tables;
CREATE TABLE uwindexes
CLUSTER uw_cluster (table_name) AS
SELECT table_name, index_name
FROM all_indexes;
SELECT COUNT(*) FROM uwtables;
SELECT COUNT(*) FROM uwindexes; |
| List Chained Rows |
ANALYZE CLUSTER <cluster_name> LIST CHAINED ROWS
INTO <table_name>; |
ANALYZE CLUSTER uw_cluster LIST CHAINED ROWS INTO chained_rows;
SELECT * FROM chained_rows; |
| Validate Structure |
ANALYZE CLUSTER <cluster_name> VALIDATE STRUCTURE CASCADE
INTO <table_name> <OFFLINE | ONLINE> |
|
ANALYZE CLUSTER uw_cluster VALIDATE STRUCTURE CASCADE;
|
| |
| INDEX |
Create Demo Table & Index |
CREATE TABLE test
PCTFREE 0
AS SELECT object_name, object_type
FROM all_objects
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';
CREATE INDEX ix_test
ON test (object_name, object_type)
PCTFREE 0; |
Validate Structure |
ANALYZE INDEX <index_name>
[PARTITION <partition_name>]
[SUBPARTITION <subpartition_name>]
VALIDATE STRUCTURE CASCADE
INTO <table_name> <OFFLINE | ONLINE> |
desc index_stats
set linesize 121
SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space
FROM index_stats
WHERE name = 'IX_TEST';
ANALYZE INDEX ix_test VALIDATE STRUCTURE;
SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space
FROM index_stats
WHERE name = 'IX_TEST';
SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats;
DROP INDEX ix_test;
CREATE INDEX ix_test
ON test (object_name, object_type)
PCTFREE 0
COMPRESS 1;
ANALYZE INDEX ix_test VALIDATE STRUCTURE;
SELECT height, blocks, lf_rows, lf_blks, br_rows, br_blks, btree_space, used_space
FROM index_stats
WHERE name = 'IX_TEST';
SELECT opt_cmpr_count, opt_cmpr_pctsave
FROM index_stats;
|
| |
| TABLE |
List Chained Rows
Note: While pm.online_media has chained rows in previous versions of Oracle it does not in the 11gR2 beta |
ANALYZE TABLE <table_name> LIST CHAINED ROWS
INTO <table_name>; |
conn / as sysdba
SELECT owner, table_name
FROM dba_tables
WHERE chain_cnt > 0;
conn pm/pm
SQL> @?/rdbms/admin/utlchn1.sql
desc chained_rows;
ANALYZE TABLE online_media LIST CHAINED ROWS INTO chained_rows;
set linesize 121
col owner_name format a10
col table_name format a15
col cluster_name format a7
col partition_name format a9
col subpartition_name format a12
col head_rowid format a20
SELECT * FROM chained_rows; |
Compute Statistics
Deprecated: Use DBMS_STATS |
ANALYZE TABLE <table_name> <COMPUTE | DELETE | ESTIMATE> STATISTICS |
conn
uwclass/uwclass
SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len
FROM user_tables
WHERE table_name ='TEST';
ANALYZE TABLE test COMPUTE STATISTICS;
SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len
FROM user_tables
WHERE table_name ='TEST';
ANALYZE TABLE test DELETE STATISTICS;
SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len
FROM user_tables
WHERE table_name ='TEST';
ANALYZE TABLE test ESTIMATE STATISTICS;
SELECT num_rows, blocks, empty_blocks, avg_space, avg_row_len
FROM user_tables
WHERE table_name ='TEST'; |
| Validate Structure |
ANALYZE TABLE <table_name>
[PARTITION <partition_name>]
[SUBPARTITION <subpartition_name>]
VALIDATE STRUCTURE CASCADE
[INTO <table_name>] <OFFLINE | ONLINE> |
conn
uwclass/uwclass
ANALYZE TABLE test VALIDATE STRUCTURE CASCADE ONLINE;
SELECT * FROM invalid_rows; |
