Oracle Analyze
Version 12.1.0.1

General Information
Library Note Morgan's Library Page Footer
The Library is currently in the process of being upgraded from Oracle Database Version 11.2.0.3 to 12.1.0.1. Demos are being upgraded to reflect the new Container paradigm as well as EBR (Edition Based Redefinition) and may contain references to CDBs, PDBs, and other objects you may not be familiar with such as CDB_OBJECTS_AE: Welcome to 12c.
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
CDB_ANALYZE_OBJECTS DBA_ANALYZE_OBJECTS INVALID_ROWS
CHAINED_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;

Related Topics
Clusters
Compression
DBMS_STATS
DBMS_UTILITY
Indexes
Tables

Morgan's Library Page Footer
This site is maintained by Dan Morgan. Last Updated: This site is protected by copyright and trademark laws under U.S. and International law. © 1998-2014 Daniel A. Morgan All Rights Reserved