【国产数据库】GBase学习⑫ - Plan Hint
意义
- 执行计划优化器基于成本(CPU),估算所有可行执行计划的成本,选择最低代价,但并不保证选择的是最好的。
- 成本和 多表的关联方式、成本的因子、数据量有关。
- 表里有多少数据块、内存大小影响索引扫描的计算成本、数据存储的物理顺序和索引顺序的离散度也会影响索引扫描的计算成本。
Plan Hint
Plan Hint为用户提供了直接影响执行计划的手段,用户可以通过指定join顺序,join、scan方法,指定行数等手段来进行执行计划调优,以提升查询性能。
-
指定形式: /*+ */
-
支持范围:
- 指定join方式;
- 指定join顺序;
- 指定行数;
- 指定scan(扫描)方式;
- 指定链接块名;
- custom plan 和 generic plan 选择的hint;
- 指定子查询不扩展;
-
hint 弊端:如果将hint写在应用代码中,当修改执行计划,还需要修改代码,不够灵活。
使用Plan Hint
- 指定join方式:
语法:[no] nestloop | hashjoin | mergejoin(table_list)
no表示不使用hint的join方式
支持常用的join,包括Hash Join、Nested Join、Merge Join
例:
set enable_nestloop = on;
explain select * from t1,t2 where t1.id1=t2.id2;
explain select /*+ nestloop(t1 t2)*/* from t1,t2 where t1.id1= t2.id2;
- 指定join顺序:
语法:
(1) 不指定内外表顺序 leading(join_table_list)
(2) 同时指定join顺序和内外表顺序 leading((join_table_list))
例:
explain select * from t1,t2,t3;
explain select /*+ leading(t1 (t2 t3)) */* from t1,t2,t3;
- 指定行数:
语法:rows(table_list #|+|-|* const)
#表示直接使用后面的行数进行hint
+,-,*表示对原来估算的行数进行加、减、乘操作
const 常量可以是任意非负数秒支持科学计数法
例:
explain select * from t1,t2;
explain select /*+ rows(t1 t2 #5)*/* from t1,t2;
- 指定扫描方式:
语法:[no] <scan_type>(table [index])
no表示不适用hint的scan方式。
<scan_type>支持常用的tablescan、indexscan、indexonlyscan。
table表示hint指定的表,只能指定一个表,如果表存在别名应优先使用别名进行hint。
例:
explain select * from t1,t2 where t1.id1=t2.id2;
explain select /*+ indexscan(t1 t1_id1_inx)*/* from t1,t2 where t1.id1=t2.id2;
- 指定链接块名:
语法:blockname(table)
table表示为该子链接块hint的别名的名称。
链接块指的是 query blockname,在dml语句里,每一个查询块(query block)都会有一个查询的链接名,默认由系统自动生成,就可以通过hint语法,对某一个子查询中自定义一个blockname,当定义之后,结合其他hint方式一起使用。
例:
explain select /*+nestloop(t2 block1)*/* from t2
where id1 in
(select /*+blockname(block1)*/ id1 from t3 group by 1);
custom plan与generic plan
- 生成同样的一条SQL,可能只是查询条件入参不同,要执行很多遍,每次都是同样的执行计划、每次都发生硬解析,则会消耗大量时间。
硬解析:语法解析-语义解析---------》执行器执行
针对这种查询,可以使用PBE的方式执行,来减少硬解析流程,提升效率
P(Parse):解析SQL语句,生成解析树、查询树保存起来。
B(Bind):第一次调用则生成并保存计划,若已有计划则直接使用,并将必要的入参补充完整。
E(Execute):根据计划执行。 - 对于以PBE方式执行的查询语句和DML语句,优化器会基于规则、代价、参数等因素选择生成Custom Plan或Generic Plan执行。
前n次执行时,每次都是硬解析,每一次都产生新的执行计划,叫做custom plan;
当第n+1次开始执行时(一般是5次,第6次就会生成),会生成一个通用的执行计划(generic plan),同时与之前的custom plan进行比较,如果generic plan效率高,则会把执行计划固定下来,此后即使传入的值发生变化,执行计划也不再变化。 - 使用hint可以使此类语句强制选择Custom Plan 或 Generic Plan。
- 指定custom plan:
语法:use_cplan
对于非PBE方式执行的SQL语句,设置本hint不会影响执行方式。
例:
prepare p2 as select /*+ use_cplan*/* from t1,t2 where t1.id1=$1;
explan execute p2(1);
- 指定generic plan:
语法:use_gplan
对于非PBE方式执行的SQL语句,设置本hint不会影响执行方式。
例:
prepare p1 as select /*+ use_gplan */* from t1,t2 where t1.id1=$1;
explain execute p1(1);
- 指定子查询不扩展:
语法:no_expand
数据库在对查询进行逻辑优化时通常会将可以提升的子查询提升到上层来避免嵌套执行,通过此hint可以使子查询不扩展。
大多数情况下不建议使用hint。除非真的定位到是子查询提升导致的查询效率下降。
例:
explain select * from t1 where t1.id1 in (select id2 from t2);
explain select * from t1 where t1.id1 in (select /*+ no_expand*/id2 from t2);