执行计划优化例子

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语法，强制走索引

那么这段SQL真实走了索引了吗？

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

2.获取真实的执行计划

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
   AND msi.secondary_inventory_name IS NOT NULL

我们使用魔术注释，然后查询下面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,只是告诉了成本优化器要这么做，可能成本优化器会否决你的优化，提现在上面的查询结果就是，仍然走的全表扫描

这时我们取消掉hint,来模拟hint失效的效果

像这样

3.收集统计信息

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;

4.查询计划基线

即使在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'

5.固定计划基线

我们将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;