ANALYZE TABLE
The first way the ANALYZE command is used is to analyze a table. ANALYZE TABLE causes Oracle to determine how many rows are in the table and how storage is allocated. It also calculates the number of chained rows.
The most important pieces of information the optimizer gets from this process are the number of rows and the number of blocks. When joining two or more tables, the optimizer will attempt to use the table with the fewest number of rows or blocks as the driving table. This should reduce the total amount of disk I/O necessary, and thus improve performance.
You should analyze all tables that will ever be used in a join, which probably means that you must analyze every table in your application.
Warning
Oracle specifically advises us not to ANALYZE tables belonging to SYS. The internal access paths used to query the data dictionary have already been optimized in the kernel code. Furthermore, in some releases of Oracle7, analyzing tables owned by SYS can cause an ORA-600 error.
An example of the ANALYZE TABLE command is shown here:
ANALYZE TABLE scott.emp COMPUTE STATISTICS FOR TABLE;
When you analyze a table, Oracle populates the following columns in the DBA_TABLES, ALL_TABLES, and USER_TABLES data dictionary views:
- NUM_ROWS
The number of rows in the table.
- BLOCKS
The number of data blocks in use.
- EMPTY_BLOCKS
The number of data blocks above the highwater mark. Note that BLOCKS + EMPTY_BLOCKS + 1 equals the total number of blocks allocated to the table. The highwater mark is represented by the BLOCKS value. Remember that the first extent of every table must be equal to at least two blocks, with the first block being used for the segment header.
- AVG_SPACE
The average number of free bytes in each block.
- CHAIN_CNT
The number of chained rows in the table. You can use the optional parameter LIST_CHAINED_ROWS to populate a table with the ROWID of every chained row. The table is of a specific format, and can be created using the SQL script $ORACLE_HOME/rdbms/admin/utlchain.sql.
- AVG_ROW_LEN
The average length of all the rows in the table.
- AVG_SPACE_FREELIST_BLOCKS (Oracle8 only)
The average free space in all blocks in the freelist.
- NUM_FREELIST_BLOCKS (Oracle8 only)
The number of blocks in the freelist.
- SAMPLE_SIZE (Oracle8 only)
The number of rows used in determining the statistics. A value of indicates that all rows were used.
- LAST_ANALYZED (Oracle8 only)
Timestamp of the last ANALYZE command.