Object Statistics - Troubleshooting Oracle Performance - page 205

UPDATE t SET val1 = NULL WHERE val1 < 0;

ALTER TABLE t ADD CONSTRAINT t_pk PRIMARY KEY (id);

CREATE INDEX t_val1_i ON t (val1);

CREATE INDEX t_val2_i ON t (val2);

BEGIN
dbms_stats.gather_table_stats(
ownname => user,
tabname => 'T',
estimate_percent => 100,
method_opt => 'for columns size skewonly id, val1 size 15, val2, val3 size 5, pad',
cascade => TRUE
);
END;
/

Table Statistics
The following query shows how to get the most important table statistics for a table:

SQL> SELECT num_rows, blocks, empty_blocks, avg_space, chain_cnt, avg_row_len
2 FROM user_tab_statistics
3 WHERE table_name = 'T';

NUM_ROWS BLOCKS EMPTY_BLOCKS AVG_SPACE CHAIN_CNT AVG_ROW_LEN
-------- ------ ------------ --------- --------- -----------
1000 44 0 0 0 266

Here is an explanation of the table statistics returned by this query:

•

num_rows is the number of rows in the table.

•

blocks is the number of blocks below the high watermark in the table.

•

empty_blocks is the number of blocks above the high watermark in the table. This value isn't
computed by the dbms_stats package. It's set to 0 (unless another value is already stored in
the data dictionary).

•

avg_space is the average free space (in bytes) in the table's data blocks. This value isn't
computed by the dbms_stats package. It's set to 0 (unless another value is already stored in
the data dictionary).

•

chain_cnt is the sum of the rows in the table that are chained or migrated to another block
(chained and migrated rows are described in Chapter 16). Even though the query optimizer
uses this value, the dbms_stats package doesn't compute it. It's set to 0 (unless another value
is already stored in the data dictionary).

•

avg_row_len is the average size (in bytes) of a row in the table.