Oracle执行计划优化SPM案例

1.现象

执行下面这段代码，发现子库存表走了全表扫描

SELECT msi.secondary_inventory_name, --子库存
       msi.description --库存说明
  FROM inv.mtl_secondary_inventories msi
      ,csi_item_instances            cii
 WHERE msi.secondary_inventory_name = cii.inv_subinventory_name
   AND msi.secondary_inventory_name IS NOT NULL

在这里插入图片描述
子库存表里面的索引

于是我们加上库存组织后再进行查询

仍旧是全表扫描
这时我们使用hint语法，强制走索引

2.确认hint文实际生效

我们在客户端按F5,显示的是解释计划，如果我们要看真正的执行计划，需要使用xplan方法

SELECT /*TOTO20210526*/ /*+ gather_plan_statistics */
/*+ index(msi MTL_SECONDARY_INVENTORIES_U1)*/
       msi.secondary_inventory_name, --子库存
       msi.description --库存说明
  FROM inv.mtl_secondary_inventories msi
      ,csi_item_instances            cii
 WHERE msi.secondary_inventory_name = cii.inv_subinventory_name
 --and msi.organization_id=cii.inv_organization_id

我们使用魔术注释，然后查询下面SQL

SELECT t.*
  FROM v$sql s,
       table(DBMS_XPLAN.DISPLAY_CURSOR(s.sql_id, s.child_number)) t
 WHERE sql_text LIKE '%TOTO20210526%';

在这里插入图片描述
可以看到确实走了索引。但是使用hint,只是告诉了成本优化器要这么做。此时一种方法是直接改写代码中的sql加上这个hint，然后重新部署上线。第二种方法就是利用SPM固定带有hint的执行计划。

3.查询计划基线

即使在xplan中显示已经使用了索引，但是除非是该计划基线已经被固定，否装不会使用该执行计划
我们打开一个新窗口，执行下面语句，捕获计划

alter session set optimizer_capture_sql_plan_baselines =true;

执行查询语句

SELECT /*TOTO20210526-4*/
/*+ index(msi MTL_SECONDARY_INVENTORIES_U1)*/
       msi.secondary_inventory_name, --子库存
       msi.description --库存说明
  FROM inv.mtl_secondary_inventories msi
      ,csi_item_instances            cii
 WHERE msi.secondary_inventory_name = cii.inv_subinventory_name
   and msi.organization_id=cii.inv_organization_id
   AND msi.secondary_inventory_name IS NOT NULL

关闭捕获

alter session set optimizer_capture_sql_plan_baselines =false;

查询计划基线

select ENABLED, --    指示计划基准是已启用（YES）还是已禁用（NO）
       ACCEPTED, --   表示计划基线是否被接受（YES）否（NO）
       FIXED, --    指示计划基准是否固定（YES）（NO） 
       spb.*
  from dba_sql_plan_baselines spb;

在这里插入图片描述
上图sql_text内容就是我们的SQL文本
查询该计划基线对应的执行计划，将SQL_HANDLE传入

select * from table(dbms_xplan.DISPLAY_SQL_PLAN_BASELINE('SYS_SQL_b06e9c3ae9259fe3'));

在这里插入图片描述
可以看出已经使用了索引，上图有一个Plan hash value: 1786343847
我们也可以执行下列查询

select * from V$SQL_PLAN s where s.PLAN_HASH_VALUE='1786343847'

4.固定计划基线

我们将sql_handle作为参数，在sysdba角色下执行下列脚本，固定计划基线，优化完成。

declare 
l_plans_altered pls_integer;
 
begin
l_plans_altered:=dbms_spm.alter_sql_plan_baseline(sql_handle      =>'SYS_SQL_b06e9c3ae9259fe3' ,
                                                  plan_name       =>null ,
                                                  attribute_name  =>'fixed' ,
                                                  attribute_value =>'YES' );
end;
 
DECLARE
  report CLOB;
BEGIN
  report := dbms_spm.evolve_sql_plan_baseline(sql_handle => 'SYS_SQL_b06e9c3ae9259fe3');
  dbms_output.put_line(report);
END;

5.收集统计信息（补充）

很多情况下，没有走理想的索引和计划是由于统计信息过旧导致的，数据库体量允许的情况下，可以重新收集统计信息。
比如此案例中CBO基于错误的或者历史的统计信息，判断子库存表全表扫描速度更快。
收集索引统计信息dbms_stats.gather_index_stats(‘schema’, ‘table_name’);

BEGIN
  dbms_stats.gather_table_stats(ownname          => 'INV'
                               ,tabname          => 'MTL_SECONDARY_INVENTORIES'
                               ,estimate_percent => dbms_stats.auto_sample_size
                               ,method_opt       => 'FOR ALL COLUMNS SIZE AUTO');
END;