Oracle Analyze
Version 23c

General Information
Library Note Morgan's Library Page Header
ACE Director Alum Daniel Morgan, founder of Morgan's Library, is scheduling complimentary technical Workshops on Database Security for the first 30 Oracle Database customers located anywhere in North America, EMEA, LATAM, or APAC that send an email to asra_us@oracle.com. Request a Workshop for your organization today.
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(128))
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;

COUNT(*)
--------
    2346


SELECT COUNT(*) FROM uwindexes;

 COUNT(*)
---------
     2371
Delete Statistics ANALYZE CLUSTER <[schema_name.]cluster_name> DELETE [SYSTEM] STATISTICS
exec dbms_stats.gather_schema_stats('UWCLASS');

SELECT table_name, num_rows
FROM user_tables
WHERE table_name = 'UWTABLES';

TABLE_NAME      NUM_ROWS
-------------- ---------
UWTABLES            2279

ANALYZE CLUSTER uw_cluster DELETE SYSTEM STATISTICS;

Cluster analyzed.

TABLE_NAME      NUM_ROWS
-------------- ---------
UWTABLES
List Chained Rows ANALYZE CLUSTER <[schema_name.]cluster_name> LIST CHAINED ROWS INTO <table_name>;
ANALYZE CLUSTER uw_cluster LIST CHAINED ROWS INTO chained_rows;

Cluster analyzed.

SELECT * FROM chained_rows;

no rows selected
Validate Structure ANALYZE CLUSTER <[schema_name.]cluster_name> VALIDATE STRUCTURE CASCADE
INTO <table_name> <OFFLINE | ONLINE>;
ANALYZE CLUSTER uw_cluster VALIDATE STRUCTURE CASCADE;

Cluster analyzed.

SELECT * FROM invalid_rows;

no rows selected
 
INDEX
Create Demo Table & Index CREATE TABLE test
PCTFREE 0
AS SELECT object_name, object_type
FROM all_objects;

Table created.

CREATE INDEX ix_test
ON test (object_name, object_type)
PCTFREE 0;

Index created.
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 sys@pdbdev as sysdba

SELECT owner, table_name
FROM dba_tables
WHERE chain_cnt > 0;

conn pm/pm@pdbdev

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@pdbdev

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@pdbdev

ANALYZE TABLE test VALIDATE STRUCTURE CASCADE ONLINE;

SELECT * FROM invalid_rows;

Related Topics
Built-in Functions
Built-in Packages
Clusters
Compression
DBMS_STATS
DBMS_UTILITY
Indexes
Tables
What's New In 21c
What's New In 23c

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-2023 Daniel A. Morgan All Rights Reserved
  DBSecWorx