CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Analyze
Version 11.1
 
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
WHERE SUBSTR(object_name,1,1) BETWEEN 'A' AND 'W';

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

Note: While pm.online_media has chained rows in previous versions of Oracle it does not in the 11gR2 beta
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
DBMS_STATS
DBMS_UTILITY
Indexes
Tables
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [68 users online]    © 2010 psoug.org