达梦7数据库统计信息初探（凝思磐石6.0）

目录

 

一、什么是统计信息

二、统计信息如何生成

三、如何收集和删除统计信息

2.1、系统函数

2.2、系统包

2.3、SQL 

三、如何查询统计信息

3.1、系统包

3.2、SQL

---

一、什么是统计信息

对象统计信息描述数据是如何在数据库中存储的。统计信息是优化器的代价计算的依据，可以帮助优化器较精确地估算成本，对执行计划的选择起着至关重要的作用。 达梦数据库的统计信息分三种类型：表统计信息、列统计信息、索引统计信息。通过直方图来表示。

二、统计信息如何生成

统计信息生成过程分以下三个步骤：

1. 确定采样的数据：根据数据对象，确定需要分析哪些数据。

    1） 表：计算表的行数、所占的页数目、平均记录长度

    2） 列：统计列数据的分布情况

    3） 索引：统计索引列的数据分布情况

2. 确定采样率

根据数据对象的大小，通过内部算法，确定数据的采样率。采样率与数据量成反比。

3. 生成直方图

有两种类型的直方图：频率直方图和等高直方图。根据算法分析表的数据分布特征，确定直方图的类型。频率直方图的每个桶(保存统计信息的对象)的高度不同，等高直方图每个桶的高度相同。例如，对列生成统计信息，当列值分布比较均匀时，会采用等高直方图，否则，采用频率直方图。在执行查询时，如果数据对象存在统计信息，代价算法可以根据统计信息中的数据，比较精确地计算出操作所需花费的成本，以此来确定连接方式、对象访问路径、连接顺序，选择最优的执行计划。用户也可以通过修改 OPTIMIZER_DYNAMIC_SAMPLING 参数值在缺乏统计信息时进行动态统计信息收集。

三、如何收集和删除统计信息

2.1、系统函数

通过查询系统表可查看有哪些系统函数

SQL> SELECT ID,NAME FROM V$IFUN WHERE NAME LIKE 'SP%STAT%INIT' ORDER BY NAME;

行号     ID          NAME                  
---------- ----------- ----------------------
1          708         SP_COL_CTL_STAT_INIT
2          394         SP_COL_STAT_DEINIT
3          387         SP_COL_STAT_INIT
4          384         SP_DB_STAT_DEINIT
5          383         SP_DB_STAT_INIT
6          709         SP_INDEX_CTL_STAT_INIT
7          392         SP_INDEX_STAT_DEINIT
8          381         SP_INDEX_STAT_INIT
9          1727        SP_INDEX_STAT_INIT
10         1726        SP_SQL_STAT_INIT
11         391         SP_SQL_STAT_INIT

行号     ID          NAME                  
---------- ----------- ----------------------
12         398         SP_TAB_COL_STAT_DEINIT
13         389         SP_TAB_COL_STAT_INIT
14         707         SP_TAB_CTL_STAT_INIT
15         386         SP_TAB_INDEX_STAT_INIT
16         399         SP_TAB_STAT_DEINIT
17         390         SP_TAB_STAT_INIT

以下是系统函数的使用说明

1、收集数据库的统计信息，对库上所有模式下的所有用户表以及表上的所有索引生成统计信息
CALL SP_DB_STAT_INIT ();

2、删除库的统计信息
CALL SP_DB_STAT_DEINIT ();

3、收集表的统计信息
CALL SP_TAB_STAT_INIT ('模式名', '表名');

4、删除指定表的统计信息
CALL SP_TAB_STAT_DEINIT ('模式名', '表名');

5、对某个表上所有的列生成统计信息
CALL SP_TAB_COL_STAT_INIT ('模式名', '表名');

6、对指定的列生成统计信息
CALL SP_COL_STAT_INIT ('模式名', '表名', '列名');

7、删除指定列的统计信息
CALL SP_COL_STAT_DEINIT ('模式名', '表名', '列名');

8、收集某张表的所有索引的统计信息
CALL SP_TAB_INDEX_STAT_INIT ('模式名', '表名');

9、对指定的索引生成统计信息
CALL SP_INDEX_STAT_INIT ('模式名', '索引名');
CALL SP_INDEX_STAT_INIT (USER,'索引名');

10、删除指定的索引的统计信息
CALL SP_INDEX_STAT_DEINIT ('模式名', '索引名');

11、收集SQL中的表和过滤条件列的统计信息
CALL SP_SQL_STAT_INIT ('SQL');

2.2、系统包

通过 desc dbms_stats可以查看包体的内容，篇幅问题，没有列全

SQL> desc dbms_stats;
行号     NAME              TYPE$        IO  DEF RT_TYPE
---------- ----------------- ------------ --- --- -------
12           M_N             FLOAT        OUT     
13         COLUMN_STATS_SHOW PROC                  
17         TABLE_STATS_SHOW  PROC                   
20         INDEX_STATS_SHOW  PROC                  

行号     NAME               TYPE$         IO    DEF   RT_TYPE
---------- ------------------ ------------- ----- ----- -------
23         CREATE_STAT_TABLE  PROC                       
28         DROP_STAT_TABLE    PROC   
73         GATHER_TABLE_STATS PROC                           

以下是包内存储过程的使用说明

1、收集模式的统计信息，如果数据量较大，该过程可能较慢
DBMS_STATS.GATHER_SCHEMA_STATS(‘模式名’,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

2、收集表的统计信息
DBMS_STATS.GATHER_TABLE_STATS('模式名', '表名',null,100,TRUE,'FOR ALL COLUMNS SIZE AUTO');

3、对某个表上所有的列按指定采样率生成统计信息，此是100%采样率
DBMS_STATS.GATHER_TABLE_STATS(USER, '表名',null,100,false, 'FOR ALL COLUMNS SIZE AUTO'); 

4、对指定的索引生成统计信息
DBMS_STATS.GATHER_INDEX_STATS(user,'索引名');

2.3、SQL 

收集表中指定列的统计信息，当前只支持一列
STAT <统计信息采样百分比> ON [<模式名>.]<表名>(<列名>);
如：STAT 100 ON TEST(A);

三、如何查询统计信息

3.1、系统包

1、查看某表统计信息
DBMS_STATS.TABLE_STATS_SHOW('模式名','表名');

2、查看指定列的统计信息
DBMS_STATS.COLUMN_STATS_SHOW(USER, '表名','列名');

3、查看指定索引的统计信息
DBMS_STATS.INDEX_STATS_SHOW(USER,'索引名');

3.2、SQL

1、查看某表统计信息
SELECT  B.NAME, A.COLID, C.NAME, A.T_TOTAL ,  A.N_SMAPLE, A.N_DISTINCT, A.BLEVEL, A.N_LEAF_PAGES, A.N_LEAF_USED_PAGES, A.N_BUCKETS FROM SYSSTATS A, SYSOBJECTS B, SYSCOLUMNS C WHERE B.NAME ='表名' AND A.ID   =B.ID AND A.COLID=C.COLID AND B.ID   =C.ID ORDER BY A.COLID ASC;

2、查看某索引的统计信息
SELECT B.NAME,A.T_TOTAL,A.BLEVEL+1 HEIGHT,A.N_LEAF_USED_PAGES FROM SYSSTATS A,SYSOBJECTS B
WHERE A.ID=B.ID AND B.NAME IN('索引名');

备注：统计信息的对数据库的影响后续补充