一、基础概念

1.1 优化目标
DM数据库查询优化器的优化目标为"最快响应时间"，通过设置参数FIRST_ROWS来决定优先返回多少条记录，而不需要等待全部结果确定后再输出，参数范围1～1000000（单位：行）。

1.2 查询优化器
查询优化器通过分析可用的执行方式和查询所涉及的对象统计信息来生成最优的执行计划。
优化器所做的操作有：查询转换、估算代价、生成计划。

查询转换
把经过语法、语义分析的查询块之间的连接类型、嵌套关系进行调整，生成一个更好的查询计划。常用的查询转换技术包括以下两方面内容：

过滤条件下放：在连接查询中将部分过滤条件下移，通过在连接之前先进行过滤，以此减少连接操作的数据量
相关子查询的去相关性：与子查询相关的外表与内表以半连接方式执行，放弃默认的嵌套连接

估算代价
对执行计划的成本进行估算，衡量指标包括选择率、基数、代价

生成计划
计划生成器对给定的查询按照连接方式、连接顺序、访问路径生成不同的执行计划，选择代价最小的一个作为最终的执行计划

1.3 访问路径
从数据库中检索数据的方法。包括全表扫描、聚集索引扫描、二级索引扫描等。

聚集索引扫描
由于在聚集索引中，包含了表中所有的列值，所以检索数据时只需要扫描这一个索引就可以得到所有需要的数据

二级索引扫描
由于二级索引只包含索引列以及对应的 ROWID ，如果查询列不在二级索引中则还需要扫描聚集索引来得到所需要的数据

1.4 连接
当查询语句中的FROM字句包含多个表时需要使用连接查询。生成连接查询的执行计划需要考虑以下三方面因素：

访问路径
采用何种方式来获取数据（全表扫描、索引扫描），优化器会估算每种扫描方式的代价并选择代价最小的访问方式

连接方式

嵌套连接（Nested Loop Join）
当两张表进行非等值连接时会选择嵌套连接，相当于进行笛卡尔积操作。优化器会选择一张代价较小的表作为外表（驱动表），另一张作为内表，外表的每条记录与内表进行一次连接操作 。实现简单但效率较低

哈希连接（Hash Join）
当两张表进行等值连接时会选择哈希连接。以一张表的连接列为哈希键构造哈希表，对另一张表的连接列进行哈希探测，找到满足条件的记录。哈希连接处理速度快，尤其在大数据量情况下，但需要额外的内存来存储哈希表

归并连接（Merge Join）
当两张表的连接列均为索引列时，则可以按照索引顺序进行归并，一次归并就可以找出满足条件的记录。如果查询列也属于索引列的子集，则归并连接只需扫描索引。当两表连接条件不是等值（如 <，<=，>，>=）情况下时，归并排序连接效率更好

外连接
分为左外连接、右外连接和全连接。作为外表的数据会全部返回，如果没有与外表匹配的记录则填充NULL值。全外连接是执行左外连接和右外连接后返回两次连接的UNIO结果集

子查询连接
子查询会转换成半连接，共有以下四种连接方式
1）哈希半连接：以外表的连接列为KEY构造哈希表，对内表的连接列进行探测来查找满足连接条件的记录
2）索引半连接：如果子查询的连接列为索引前导列时可采用索引半连接。外表的数据对子查询使用索引查找，返回满足条件的记录
3）归并半连接：如果相关子查询的连接条件列均为索引列时可采用归并半连接。按照索引顺序对外表和内表进行同步扫描，返回满足条件的记录
4）嵌套半连接：如果连接条件为非等值时可转换为嵌套半连接。外表的每条记录去遍历内表，返回满足条件的记录

连接顺序
当超过2 张表进行连接时就需要考虑表之间的连接顺序，不合适的连接顺序对执行效率有较大影响
基本原则是：经过连接可以产生较小结果集的表优先处理

二、统计信息

2.1 统计信息概述

DM数据库的统计数据对象分三种：

表统计信息
列统计信息
索引统计信息

收集统计信息的时机有两种（推荐用户使用静态收集）：

查询之前进行静态收集（和查询操作互不干涉，不影响查询性能）
查询的同时进行动态收集（在构造查询计划阶段进行，会影响计划阶段的性能）

查询之前进行静态收集

2.2 静态收集统计信息

2.2.1 自动收集

通过DM数据库内置的方式可以创建一个触发器（且只能创建一个）用于自动收集指定对象的统计信息
默认情况下当表的数据变动量达到10%时会触发自动收集机制

步骤一、设置参数auto_stat_obj

参数值
作用

1
对所有表进行监控

2
只对用户通过DBMS_STATS.SET_TABLE_PREFS存储过程设置过STALE_PERCENT属性的表对象进行收集

步骤二、调用存储过程设置触发器

通过调用存储过程SP_CREATE_AUTO_STAT_TRIGGER配置统计信息自动收集的频率、时间、运行时长等各个要素
该存储过程调用后会在SYS用户下创建一个触发器"SYSTRG$_AUTO_STAT"
详细参数介绍可参考《DM8-SQL语言使用手册》的附录3章节的第10部分"统计信息"

SP_CREATE_AUTO_STAT_TRIGGER存储过程定义所有参数定义如下：

SP_CREATE_AUTO_STAT_TRIGGER(
type int,
freq_interval int,
freq_sub_interval int,
freq_minut e_interval int,
starttime varchar(128),
during_start_date varchar(128),
max_run_duration int,
enable int
)

参数配置释义：

type：指定调度类型（默认值1）

1表示按天的频率执行
2表示按周的频率执行
3表示在一个月的某一天执行
4表示在一个月的第一周第几天执行
5表示在一个月的第二周第几天执行
6表示在一个月的第三周第几天执行
7表示在一个月的第四周第几天执行
8表示在一个月的最后一周第几天执行

freq_interval：与type参数有关，表示不同调度类型下的发生频率（默认值1）

1表示每几天执行（取值范围1～100）
2表示每几周执行（取值范围1～100）
3表示每几个月中的某一天执行（取值范围1～100）
4表示每几个月的第一周执行（取值范围1～100）
5表示每几个月的第二周执行（取值范围1～100）
6表示每几个月的第三周执行（取值范围1～100）
7表示每几个月的第四周执行（取值范围1～100）
8表示每几个月的最后一周执行（取值范围1～100）

freq_sub_interval：与type和freq_interval参数有关，表示不同type的执行频率在freq_interval的基础上继续指定更为精准的频率（默认值1）

当type=1时无效，系统不做检查

当type=2时表示某一周的某一天执行，可以同时选中7天中的任意几天（取值范围1～127）
DM数据库系统内部用七位二进制来表示选中的日期，从最低位开始算起依次表示周日、周一到周五、周六。选中周几就将该位置1，否则置0 。例如：选中周二和周六，7 位二进制就是 1000100，转化成十进制数就是68

当type=3时表示一个月的第几天执行（取值范围1～31）

当type=4/5/6/7/8时，都表示在某一周内第几天执行

freq_minute_interval：开始时间后，当天每隔几分钟再次执行（取值范围1～1439，默认值1439）

starttime：开始时间（默认值22：00）

during_start_date：开始日期，只有当前时间大于该参数值时定时器才会生效

max_run_duration：收集过程最大执行时间（单位：秒，默认值0），0表示不限制

enable：定时器操作（0表示触发器无效、1表示有效、2表示删除，默认值1）

示例1（从2024年12月16开始每晚22点收集全库统计信息，限制运行时长6小时）
SQL>sp_create_auto_stat_trigger(1,1,1,1439,‘22:00’,‘2024/12/16’,21600,1);

示例2（从2024年12月16开始每周六晚22点收集全库统计信息，限制运行时长10小时）
SQL>sp_create_auto_stat_trigger(2,7,2,1439,‘22:00’,‘2024/12/16’,36000,1);

步骤三、监控统计信息收集过程

创建表AUTO_STAT_INFO用于记录自动收集过程运行的信息
create table auto_stat_info(
task_id INT,
total_stat INT,
table_id INT,
sch_name varchar(24),
table_name varchar(24),
curr_gath_tab_id INT,
curr_gath_sch_name varchar(24),
curr_gath_tab_name varchar(24),
success_stat INT,
fail_stat INT,
task_start_time DATETIME,
task_end_time DATETIME,
gather_tbl_start_time DATETIME,
gather_tbl_end_time DATETIME);

创建存储过程GET_AUTO_STAT_INFO_FUNC，将服务器在自动收集统计信息时的过程信息写入AUTO_STAT_INFO表
create or replace procedure get_auto_stat_info_func
(task_id INT,
total_stat INT,
table_id INT,
sch_name varchar(24),
table_name varchar(24),
curr_gath_tab_id INT,
curr_gath_sch_name varchar(24),
curr_gath_tab_name varchar(24),
success_stat INT,
fail_stat INT,
task_start_time DATETIME,
task_end_time DATETIME,
gather_tbl_start_time DATETIME,
gather_tbl_end_time DATETIME) as
BEGIN
insert into auto_stat_info VALUES
(task_id,
total_stat,
table_id,
sch_name,
table_name,
curr_gath_tab_id,
curr_gath_sch_name,
curr_gath_tab_name,
success_stat,
fail_stat,
task_start_time,
task_end_time,
gather_tbl_start_time,
gather_tbl_end_time);
commit;
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
/

2.2.2 手动收集

示例一（收集用户所有表的统计信息，100%收集，8个并行度）
SQL>DBMS_STATS.GATHER_SCHEMA_STATS(
ownname=>‘TAIWEI’,
estimate_percent=>100,
block_sample=>TRUE,
method_opt=>‘FOR ALL COLUMNS SIZE AUTO’,
degree=>8,
cascade=>TRUE);

示例二（收集用户单个表的统计信息，100%收集，8个并行度）
SQL>DBMS_STATS.GATHER_TABLE_STATS(
ownname=>‘TAIWEI’,
tabname=>‘REGION’,
estimate_percent=>100,
block_sample=>TRUE,
method_opt=>‘FOR ALL COLUMNS SIZE AUTO’,
degree=>8,
cascade=>TRUE);

示例三（收集用户单个表分区的统计信息，100%收集，8个并行度）
SQL>DBMS_STATS.GATHER_TABLE_STATS(
ownname=>‘TAIWEI’,
tabname=>‘REGION’,
partname=>‘P202412’,
estimate_percent=>100,
block_sample=>TRUE,
method_opt=>‘FOR ALL COLUMNS SIZE AUTO’,
degree=>8,
cascade=>TRUE);

2.3 动态收集统计信息
当已收集的统计信息失效或未曾收集时，可通过配置DM.INI 参数"optimizer_dynamic_sampling"开启动态收集统计信息
参数取值范围0～12，各级别作用如下表所示：

参数值
作用

0
不启用（默认值）

1～10
启用，采样率10%～100%，动态收集统计信息，结果不会保存到系统表

11
启用，由优化器自行决定采样率（0.1%～99.9%），结果不会保存到系统表

12
和11相同，但结果会保存到系统表

2.4 查看统计信息收集情况
通过手工执行统计信息收集或者动态收集时optimizer_dynamic_sampling参数设置为12，才会将收集保存在系统表内
涉及的系统表名称及作用描述如下表所示：

系统表名称
作用描述

SYSSTATS
记录系统中的统计信息

SYSMSTATS
记录多维统计信息内容

SYSSTATPREFS
记录指定模式下表的统计信息的静态参数对应值

SYSSTATTABLEIDU
记录用户表上一次收集统计信息时的行数和增删改的影响行数、以及是否有TRUNCATE操作

三、执行计划

3.1 概述
执行计划的每一行即为一个计划节点，包含以下三部分信息：

第一部分数据访问操作符（比如CSCN2、SSEK2、PRJT2），表示具体执行的操作
第二部分三元组表示该计划节点的执行代价，具体含义为 [代价，记录行数，字节数]
第三部分表示操作符的补充信息

3.2 常见数据访问操作符

V$SQL_NODE_NAME视图包含所有数据访问操作符的含义解释

常见操作符
参数说明
操作说明

CSCN2
idxname(tabname) ：索引名（表名）NEED_SLCT(TRUE)：是否进行过滤条件下推的优化
聚集索引全扫描（全表扫描）

CSEK2
scan_type：扫描类型idxname(tabname) ：索引名（表名）scan_range：扫描范围
聚集索引数据定位

SSCN
idxname(tabname) ：索引名（表名）
二级索引全扫描

SSEK2
scan_type：扫描类型idxname(tabname) ：索引名（表名）scan_range：扫描范围
二级索引数据定位

BLKUP2
idxname(tabname) ：索引名（表名）
定位查找

SLCT2
(condition)：过滤条件SLCT_PUSHDOWN(TRUE)：是否进行过滤条件下推的优化
关系的“选择”（select）运算，用于查询条件的过滤

SORT3
key_num：排序列个数is_distinct：排序时是否进行去重操作top_flag：是否返回排序后的N行is_adaptive：是否使用排序的自适应优化
排序

PRJT2
exp_num：映射列数is_atom：是否要求单行数据
关系的“投影”(project)运算，用于选择表达式项的计算

NSET2
无参数
结果集收集，一般是查询计划的顶层节点