| GENERAL |
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;
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 |
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; |
