Oracle | Analyze

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;