Analyzed Tables

 

Analyzed Tables

ANALYZED TABLE NOTES:

Owner - Owner of the table

Analyzed - Number of analyzed tables

Not Analyzed - Number of tables that have not be analyzed

Total - Total number of tables owned by user

The ANALYZE statement allows you to validate and compute statistics for an index, table, or cluster. These statistics are used by the cost-based optimizer when it calculates the most efficient plan for retrieval. In addition to its role in statement optimization, ANALYZE also helps in validating object structures and in managing space in your system. You can choose the following operations: COMPUTER, ESTIMATE, and DELETE. Early version of Oracle7 produced unpredicatable results when the ESTIMATE operation was used. It is best to compute your statistics.

A COMPUTE will cause a table-level lock to be placed on the table during the operation.

select OWNER,
 sum(decode(nvl(NUM_ROWS,9999), 9999,0,1)) analyzed,
 sum(decode(nvl(NUM_ROWS,9999), 9999,1,0)) not_analyzed,
 count(TABLE_NAME) total
from  dba_tables
where  OWNER not in ('SYS', 'SYSTEM')
group  by OWNER