前言

稿主整理了工作中常用SQL优化方式。

统计信息采集

基于代价的优化器CBO可以根据统计信息选择最佳的查询执行计划。统计信息准确与否会影响SQL执行效率。
DM支持自动、手动的方式采集统计信息。
需要注意的是,采集统计信息时会消耗系统资源,需要在业务空闲的时间段谨慎执行。

采集方式

自动采集

在 INI 参数 AUTO_STAT_OBJ 为 1 或 2 前提下，执行 SP_CREATE_AUTO_STAT_ TRIGGER 过程实现自动收集。

例如以下SQL表示 从2023/8/10开始,每天22:20自动执行统计信息收集。

SP_CREATE_AUTO_STAT_TRIGGER(1, 1, 1, 1,'22:20', '2023/8/10',0,1);

手动采集

通过存储过程收集

#收集TEST_DB.TEST_TB的索引统计信息
SP_TAB_INDEX_STAT_INIT ('TEST_DB', 'TEST_TB');
#对TEST_DB的IDX_1索引收集
SP_INDEX_STAT_INIT ('TEST_DB', 'IDX_1');
#对TEST_DB.TEST_TB的ID字段收集
SP_COL_STAT_INIT('TEST_DB', 'TEST_TB","ID");
#对TEST_DB.TEST_TB所有字段收集
SP_TAB_COL_STAT_INIT('TEST_DB', 'TEST_TB');
#指定采用率对TEST_DB.TEST_TB所有字段收集
SP_STAT_ON_TABLE_COLS('TEST_DB', 'TEST_TB',10);
#对某张表和索引生成统计信息
SP_TAB_STAT_INIT('TEST_DB', 'TEST_TB');

通过stat语法收集

对字段按百分比收集
STAT 30 ON TEST_DB.TEST_TB (ID); 
STAT 30 ON TEST_DB.TEST_TB (PID,NAME); 
对索引按百分比收集
STAT 50 ON INDEX PURCHASING.S1; 
对表生成统计信息
STAT ON SYS.SYSOBJECTS;

执行计划相关参数

动态采集参数OPTIMIZER_DYNAMIC_SAMPLING
当统计信息不可用时是否启用动态统计信息。取值范围:0-12。0：不启用；1-10：启用，采用率 10%-100%；11：启用，由优化器确定采样率（0.1%-99.9%）；12：同 11，但收集的结果会持久化保存。默认值为0

HINT

HINT是一种用于指导查询优化器选择查询执行计划的指令。

指定索引

指定使用IDX_TEST_NAME2索引来查询

select /*+INDEX(TEST, IDX_TEST_NAME2) */ * from TEST where name = 'asd'

指定Join 算法

DM支持的join 的join 算法有: indx nl Join 、merge join 、hash join、嵌套连接(笛卡尔积)。

指定走index nl Join

select  /*+ USE_NL_WITH_INDEX(T2, IDX_TEST_NAME2) */ t1.name from TEST2 t2  join TEST t1 on t2.NAME = t1.NAME;

注意点：
1.要求被驱动表的连接列有索引
2.被驱动表的连接列有索引时默认就是走index nl Join
使用方式: USE_NL_WITH_INDEX(“驱动表”, “包含被驱动表连接列的索引”)

指定走hash join

被驱动表的连接列有索引时默认就是走index nl Join，但是部分场景走hash join效果更佳，此时可以 USE_HASH 优化

select /*+ USE_HASH(T1, T2) */ * from TEST2 t2  join TEST t1 on t1.email=t2.email;

注意点：
1.被驱动表会被加载到内存然后构建成一张hash 表，因此被驱动表太大时不建议使用hash join。

指定走merge join

select /*+use_merge(t2,t1)*/ t1.email from TEST2 t2 ,TEST  t1 where t2.NAME = t1.NAME and t1.id = '1';

注意点:
1.使用方式use_merge(“驱动表”,”被驱动表”)
2.要求驱动表和被驱动表的连接列都有索引
3.要求驱动表要索引覆盖，不能回表。

指定join顺序

inner join 时，可以通过hint 指定驱动表和被驱动表

select /*+ ORDER (T2, T1 ) */ t1.email from TEST2 t2 ,TEST  t1 where t2.NAME = t1.NAME and t1.id = '1';

本地并行查询

参数名	缺省值	属性	说明
MAX_PARALLEL_DEGREE	1	动态，会话级	用来设置单个查询默认的最大并行任务个数。取值范围：1~128。缺省值 1，表示无并行任务。当 PARALLEL_POLICY 值为 1 时该参数值才有效。
PARALLEL_POLICY	0	静态	用来开启或关闭并行。取值范围：0、1 和 2，缺省为 0。其中，0 表示关闭并行；1 表示开启自动并行模式；2 表示开启手动并行模式
PARALLEL_THRD_NUM	10	静态	用来设置并行工作线程总个数。取值范围：1~1024。

• 通过hint 指定并行度

指定所有表的并行度

select /*+ PARALLEL(4) */ b.Name 
from SYSSTATS a left join sysobjects b on a.id=b.id 
order by a.last_gathered desc 

指定某张表的并行度

select /*+ PARALLEL(SYSSTATS 4) */ b.Name 
from SYSSTATS a left join sysobjects b on a.id=b.id 
order by a.last_gathered desc 

适用场景

• 单次执行时，CPU压力大于 IO压力的场景。
• 大型表的连接查询、大量数据的聚合和大型结果集的排序等都很适合采用并行查询

执行计划重用

参数USE_PLN_POOL指定是否重用执行计划

• 0：禁止执行计划的重用；
• 1：启用执行计划的重用功能 ；
• 2：对不包含显式参数的语句进行常量参数化优化；
• 3：即使包含显式参数的语句，也进行常量参数化优化

结果集重用

结果集重用是**基于计划重用**的，如果查询的计划不能缓存，则其查询结果集必然不能缓存。此外，当语句的游标属性为 FORWARD ONLY 时，默认查询不会生成结果集。而参数 BUILD_FORWARD_RS 可以强制在此类查询中生成结果集，以便进行结果集重用。
通过 RS_CAN_CACHE  设置结果集重用，默认为0，手动hint开启,设置为1,强制开启

select /*+ RESULT_CACHE */ id, name from sysobjects;