DM8执行计划总结
一. 统计信息
- 统计信息简介
达梦数据库的统计数据对象分三种:表统计信息、列统计信息和索引统计信息。统计信息生成过程分三个步骤:
确定采样的对象:根据数据对象,确定需要分析哪些数据。
1)表:计算表的行数、所占的页数目、平均记录长度。
2)列:统计列数据的分布特征。
3)索引:统计索引列的数据分布特征。
确定采样率。用户根据统计数据对象的大小,确定采样率。如缺省,则默认通过内部算法确定数据的采样率。
生成统计信息。根据不同的数据对象生成不同的统计信息。
1)表:表的行数、所占的页数目、平均记录长度等汇总数据。
2)列和索引:将采样的数据按照不同的分布特征生成相应的直方图。有两种类型的直方图:频率直方图和等高直方图。根据算法分析表的数据分布特征(以不同值的数据量 1 万个为分界线),确定直方图的类型。频率直方图的每个桶(保存统计信息的对象)的高度不同,等高直方图每个桶的高度相同。生成直方图时,如果不同值少于 1 万个则用频率直方图,否则用等高直方图。 - 收集统计信息
2.1收集时机
收集统计信息的时机有两种:一是在查询之前进行静态收集;二是在查询的同时进行动态收集。
静态收集是在查询之前完成。和查询操作互不干涉,因此不影响查询性能。
动态收集是在查询的过程中完成。具体为在构造查询计划阶段进行,统计信息收集完成之后再继续构造计划,因此会影响计划阶段性能,特别是在高并发场景中。
从性能角度考虑,推荐用户使用静态收集。
2.2静态收集
静态收集统计信息有两种方式。一是自动收集;二是手动收集。采用任意一种即可。静态收集的结果均会保存到系统表中。
在 INI 参数 AUTO_STAT_OBJ 为 1 或 2 前提下,执行 SP_CREATE_AUTO_STAT_ TRIGGER 过程实现自动收集。
手动收集有三种可选方式。下面分别介绍:
通过《DM8 系统包使用手册》里 DBMS_STATS 包中的方法(GATHER_TABLE_STATS、GATHER_INDEX_STATS 或 GATHER_SCHEMA_STATS)进行生成并收集统计信息。
通过《DM8_SQL 语言使用手册》中设置 STAT 语法为表、列、索引生成统计信息。
例 对 SYSOBJECTS 表上 ID 列生成统计信息,采样率的百分比为 30%。使用按新比例收集的列统计信息覆盖之前老的统计信息。
2.3动态收集
当已收集的统计信息失效或未曾收集时,用户可通过配置 DM.INI 参数 OPTIMIZER_DYNAMIC_SAMPLING,开启动态收集统计信息。
OPTIMIZER_DYNAMIC_SAMPLING 为动态会话级参数,取值范围:012。0:不启用;110:启用,采用率 10%100%,收集动态统计信息,但收集结果不会保存到系统表;11:启用,由优化器自动确定采样率(0.1%99.9%),收集结果也不会保存到系统表;12:和 11 相同,但收集的结果会保存到系统表。
2.4涉及存储过程的使用
1、收集数据库的统计信息,对库上所有模式下的所有用户表以及表上的所有索引生成统计信息
CALL SP_DB_STAT_INIT ();
2、清空库的统计信息
CALL SP_DB_STAT_DEINIT ();
3、对某张表或某个索引生成统计信息 CALL SP_TAB_STAT_INIT (‘模式名’, ‘表名或索引名’);
对表 SYSOBECTS 生成统计信息 CALL SP_TAB_STAT_INIT (‘SYS’, ‘SYSOBJECTS’);
4、清空某张表的统计信息 CALL SP_TAB_STAT_DEINIT (‘模式名’, ‘表名’);
清空表 SYSOBECTS 的统计信息 CALL SP_TAB_STAT_DEINIT (‘SYS’, ‘SYSOBJECTS’);
5、对某个表上所有的列生成统计信息 CALL SP_TAB_COL_STAT_INIT (‘模式名’, ‘表名’);
对’SYSOBJECTS’表上所有的列生成统计信息 CALL SP_TAB_COL_STAT_INIT (‘SYS’, ‘SYSOBJECTS’);
6、清空某个表上所有的列的统计信息 CALL SP_TAB_COL_STAT_DEINIT (‘模式名’, ‘表名’);
清空’SYSOBJECTS’表上所有的列统计信息 CALL SP_TAB_COL_STAT_DEINIT (‘SYS’, ‘SYSOBJECTS’);
7、对指定的列生成统计信息,不支持大字段列和虚拟列 CALL SP_COL_STAT_INIT (‘模式名’, ‘表名’, ‘列名’);
对表 SYSOBJECTS 的 ID 列生成统计信息 CALL SP_COL_STAT_INIT (‘SYS’, ‘SYSOBJECTS’, ‘ID’);
8、清空指定列的统计信息 CALL SP_COL_STAT_DEINIT (‘模式名’, ‘表名’, ‘列名’);
清空表 SYSOBJECTS 的 ID 列统计信息 CALL SP_COL_STAT_INIT (‘SYS’, ‘SYSOBJECTS’, ‘ID’);
9、对表上所有的索引生成统计信息 CALL SP_TAB_INDEX_STAT_INIT (‘模式名’, ‘表名’);
对 SYSOBJECTS 表上所有的索引生成统计信息 CALL SP_TAB_INDEX_STAT_INIT (‘SYS’, ‘SYSOBJECTS’);
10、对指定的索引生成统计信息 CALL SP_INDEX_STAT_INIT (‘模式名’, ‘索引名’);
对指定的索引 IND 生成统计信息 CALL SP_INDEX_STAT_INIT (‘SYSDBA’, ‘IND’);
11、清空指定的索引的统计信息 CALL SP_INDEX_STAT_DEINIT (‘模式名’, ‘索引名’);
清空指定的索引 IND 的统计信息 CALL SP_INDEX_STAT_DEINIT (‘SYSDBA’, ‘IND’);
12、对某个 SQL 查询语句中涉及的所有表和过滤条件中的列(不包括大字段、ROWID)生成统计信息。可能返回的错误提示:1) 语法分析出错,sql 语句语法错误 2) 对象不支持统计信息,统计的表或者列不存在,或者不允许被统计 CALL SP_SQL_STAT_INIT (‘SQL’);
对’SELECT * FROM SYSOBJECTS’语句涉及的所有表生成统计信息 CALL SP_SQL_STAT_INIT (‘SELECT * FROM SYSOBJECTS’);
13、对指定的列生成统计信息,不支持大字段列和虚拟列 CALL SP_COL_STAT_INIT(‘模式名’,‘表名’,‘列名’)
对表 SYSOBJECTS 的 ID 列生成统计信息 CALL SP_COL_STAT_INIT (‘SYS’, ‘SYSOBJECTS’, ‘ID’);
14、清空指定的列的统计信息 CALL SP_COL_STAT_DEINIT(‘模式名’,‘表名’,‘列名’)
清空表 SYSOBJECTS 的 ID 列统计信息 CALL SP_COL_STAT_DEINIT (‘SYS’, ‘SYSOBJECTS’, ‘ID’);
15、对某个表上所有的列,按照指定的采样率生成统计信息 CALL SP_STAT_ON_TABLE_COLS(‘模式名’,‘表名’,‘采样率’)
对’SYSOBJECTS’表上所有的列生成统计信息,采样率 90 CALL SP_STAT_ON_TABLE_COLS(‘SYS’,‘SYSOBJECTS’,90);
16、删除一个表的多维统计信息 CALL SP_TAB_MSTAT_DEINIT(‘模式名’,‘表名’)
删除表 SYSDBA.L1 上所有的多维统计信息 CALL SP_TAB_MSTAT_DEINIT(‘SYSDBA’,‘L1’);
二. 优化器的使用
1.INI参数提示
DBA 可以通过 HINT 方式对 INI 参数的值进行语句级的指定。语句中的 HINT 对 INI 参数值的设置优先级高于 INI 文件中参数值的设置。通过 HINT 方式只会修改 INI 参数的在本会话中的值,不会改变它在 INI 文件中的值。
支持使用 HINT 的 INI 参数可通过 V$HINT_INI_INFO 动态视图查询。支持 HINT 的 INI 参数分为两类:一是 HINT_TYPE 为“OPT”,表示分析阶段使用的参数;二是 HINT_TYPE 为“EXEC”,表示运行阶段使用的参数,运行阶段使用的参数对于视图无效。
2.索引提示
2.1索引提示
/+ INDEX (表名[,] 索引名) {INDEX (表名[,] 索引名)} /
SELECT /+INDEX(t1, idx_t1_id) / * FROM t1 WHERE id > 2011 AND name < ‘xxx’;
2.2不使用索引
/+ NO_INDEX (表名[,] 索引名) { NO_INDEX (表名[,] 索引名)} /
3.连接方法提示
3.1 USE_HASH
SELECT /+ USE_HASH(T1, T2) / * FROM T1, T2 WHERE T1.id = T2.id;
3.2 no_use_hash
SELECT /+ NO_USE_HASH(T1, T2) / * FROM T1, T2 WHERE T1.id = T2.id;
3.3 use_nl
SELECT /+ USE_NL(a, b) / * FROM T1 a, T2 b WHERE a.ID = b.ID;
3.4 no_use_nl
SELECT /+ NO_USE_NL(a, b) / * FROM T1 a, T2 b WHERE a.ID = b.ID;
3.5 use_nl_with_index
SELECT /+ USE_NL_WITH_INDEX(T1, IDX_T2_ID) / * FROM T1, T2 WHERE T1.ID = T2.ID;
3.6 use_merge
SELECT /+ USE_MERGE(T1,T2) / * FROM T1, T2 WHERE T1.ID = T2.ID AND T1.ID < 1 AND T2.ID < 1;
3.7 semi_gen_cross
SELECT /+ SEMI_GEN_CROSS OPTIMIZER_MODE(1) / COUNT() FROM T1 A WHERE A.ID IN (SELECT B.ID FROM T1 B);
4.连接顺序提示
SELECT /+ ORDER(T1, T2, T3 )/ FROM T1, T2 , T3, T4 WHERE …
5.统计信息提示
/+ STAT (表名, 行数) /
SELECT /+ STAT(T_S,1M) / * FROM T_S WHERE C1 <= 10;
6.忽略重复键值
/+IGNORE_ROW_ON_DUPKEY_INDEX(<表名> [(<列名>{,<列名>})])/