Oracle Analyze

Oracle Analyze Version 11.2.0.3

General Information

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.

Data Dictionary Objects

DBA_ANALYZE_OBJECTS

CHAINED_ROWS

INVALID_ROWS

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;