概述

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

关注点

表统计:

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

--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方面内容，感兴趣的朋友可以关注下！