概述

当我们在分析某些语句的性能时，会分析一些信息。像表、列、索引、直方图等等，所以今天主要讲表与列、索引的统计信息收集与分析。

关注点

表统计:

--行数，块数，行平均长度；

--all_tables：NUM_ROWS，BLOCKS，AVG_ROW_LEN；

列统计:

--列中唯一值的数量（NDV），NULL值的数量，数据分布；

--DBA_TAB_COLUMNS：NUM_DISTINCT，NUM_NULLS，HISTOGRAM；

索引统计:

--叶块数量，等级，聚簇因子；

--DBA_INDEXES：LEAF_BLOCKS，CLUSTERING_FACTOR，BLEVEL；

一、表统计信息

1.收集表统计信息

语法如下：

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

2. 查看表的统计信息用dba_tab_statistics。

SELECT OWNER, TABLE_NAME, PARTITION_NAME, OBJECT_TYPE, NUM_ROWS, BLOCKS, EMPTY_BLOCKS, AVG_SPACE, CHAIN_CNT, AVG_ROW_LEN, to_char(LAST_ANALYZED, 'yyyy-mm-dd hh24:mi:ss') LAST_ANALYZED, USER_STATS FROM DBA_TAB_STATISTICS WHERE OWNER='NWPP_TEST' AND TABLE_NAME = 'T_GG_XXGL_TZGG'; 

这里blocks是高水位以下的数据块数，empty_blocks是高水位以上的数据块数。

Dbms_stats不计算EMPTY_BLOCKS、AVG_SPACE、CHAIN_CNT。可以使用ANALYZE收取(ANALYZE TABLE <tabname> COMPUTE STATISTICS)

chain_cnt字段表示行迁移和行链接的数量信息.

二、列统计信息

查看列的统计信息用DBA_TAB_COL_STATISTICS

SELECT COLUMN_NAME, NUM_DISTINCT, LOW_VALUE, HIGH_VALUE, DENSITY , NUM_NULLS , AVG_COL_LEN , HISTOGRAM, NUM_BUCKETS FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = '&TABLE_NAME'; 

统计信息解释一下：

NUM_DISTINCT：该列中唯一值的数量。

LOW_VALUE：该列的最小值。显示为内部存储格式。对于字符串列，只存储前32字节。

HIGH_VALUE：该列的最大值。显示为内部存储格式。对于字符串列，只存储前32字节。

DENSITY：0到1之间的一个小数。接近0表示对于该列的过滤操作能去掉大多数的行。接近1表示对于该列的过滤操作起不到什么作用。如果没有直方图，DENSITY=1/NUM_DISTINCT。

HISTOGRAM：表明是否有直方图信息，如果有，是什么类型？FREQUENCY表示频率类型，HEIGHT BALANCED表示平均分布类型；如果没有，则为NONE。

NUM_BUCKETS：直方图里的桶数。它表示一组同类的数值放在一起。直方图最少由一个桶组成。如果没有直方图，则为1，最大桶数为254。

这里的LOW_VALUE和HIGH_VALUE都是内部格式，所以必须转换为可读懂的格式，有两种方法。

1）使用工具包utl_raw提供的函数cast_to_binary_double、cast_to_binary_float、cast_to_binary_integer、cast_to_number、cast_to_nvarchar2、cast_to_raw和cast_to_varchar2。这些函数就是把内部存储格式转换为实际值。

SELECT UTL_RAW.CAST_TO_NUMBER(LOW_VALUE), UTL_RAW.CAST_TO_NUMBER(HIGH_VALUE) FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T_GG_XXGL_TZGG' AND COLUMN_NAME = 'C_DEL_DATE'; 

2) 使用dbms_stats提供的过程convert_raw_value、convert_raw_value_nvarchar和convert_raw_value_rowid。该过程不能直接在SQL语句中使用，通常只用于PL/SQL程序中,相对比较少用。

DECLARE L_LOW_VALUE DBA_TAB_COL_STATISTICS.LOW_VALUE%TYPE; L_HIGH_VALUE DBA_TAB_COL_STATISTICS.HIGH_VALUE%TYPE; L_VAL1 T.VAL1%TYPE; BEGIN SELECT LOW_VALUE, HIGH_VALUE INTO L_LOW_VALUE, L_HIGH_VALUE FROM DBA_TAB_COL_STATISTICS WHERE TABLE_NAME = 'T_GG_XXGL_TZGG' AND COLUMN_NAME = 'C_DEL_DATE'; DBMS_STATS.CONVERT_RAW_VALUE(L_LOW_VALUE, L_VAL1); DBMS_OUTPUT.PUT_LINE('low_value: ' || L_VAL1); DBMS_STATS.CONVERT_RAW_VALUE(L_HIGH_VALUE, L_VAL1); DBMS_OUTPUT.PUT_LINE('high_value: ' || L_VAL1); END; / 

三、索引统计信息

1.收集索引统计信息

语法如下：

BEGIN SYS.DBMS_STATS.GATHER_INDEX_STATS (OwnName => 'GZCSS_GZBH', IndName => 'PK_T_GG_XXGL_TZGG', Estimate_Percent => 10, Degree => SYS.DBMS_STATS.DEFAULT_DEGREE, No_Invalidate => FALSE); END; 

2.查看索引统计信息

select IND.INDEX_NAME, IND.TABLE_NAME, IND.partition_name, IND.leaf_blocks, IND.distinct_keys, IND.num_rows, IND.last_analyzed, IND.user_stats from DBA_IND_STATISTICS IND WHERE IND.OWNER = 'GZCSS_GZBH' AND IND.index_name = 'PK_T_GG_XXGL_TZGG' 

实际上优化器统计范围还有系统的统计信息，不过重点还是上面三个吧，大家抽空也可以测试一下。

后面会分享更多关于DBA方面内容，感兴趣的朋友可以关注下！