Collect table and column statistics in Oracle

The explanations for each column are available in the online documentation. Here, blocks is the number of data blocks below the high water level, and empty_blocks is the number of data blocks above the high water level. Dbms_stats does not calculate EM

The explanations for each column are available in the online documentation. Here, blocks is the number of data blocks below the high water level, and empty_blocks is the number of data blocks above the high water level. Dbms_stats does not calculate EM

When analyzing the performance of some statements, we will analyze some information. Such as tables, columns, indexes, and histograms. This article mainly describes the collection and analysis of statistical information of tables and columns.

I. Table statistics

First, create a test table, update some data, and add some constraints:

Create table t
AS
SELECT rownum AS id,
Round (dbms_randm.normal * 1000) AS val1,
100 + round (ln (rownum/3.25 + 2) AS val2,
100 + round (ln (rownum/3.25 + 2) AS val3,
Dbms_random.string ('P', 250) AS pad
FROM All_Objects
Where rownum <= 1000
Order by dbms_random.value;

Update t set VAL1 = null where VAL1 <0;

Alter table t add constraint t_pk primary key (ID );

Create index t_valinii ON t (val1 );
Create index t_val2_ I ON t (val2 );

BEGIN
DBMS_STATS.GATHER_TABLE_STATS (OWNNAME => USER,
TABNAME => 'T ',
ESTIMATE_PERCENT = & gt; 100,
METHOD_OPT => 'for all columns size skewonly ',
CASCADE => TRUE );
END;

At this time, the table has collected statistics. To view the statistical information of the table, use user_tab_statistics.

SELECT NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN
FROM USER_TAB_STATISTICS
WHERE TABLE_NAME = 'T ';

NUM_ROWS

BLOCKS

EMPTY_BLOCKS

AVG_SPACE

CHAIN_CNT

AVG_ROW_LEN

1000

44

0

0

0

265

The explanations for each column are available in the online documentation. Here, blocks is the number of data blocks below the high water level, and empty_blocks is the number of data blocks above the high water level. Dbms_stats does not calculate EMPTY_BLOCKS, AVG_SPACE, and CHAIN_CNT.