delete fast full scan 优化方案.txt

现在发现在12c版本的数据库中，有很多业务SQL执行计划应该选择index range scan，但是选择了index fast full scan，消耗了多余的IO。
我们可以系统级别修改_fix_control参数，通过_fix_control" = '17908541:0'来禁用index fast full scan， 具体分析如下：

1、CRMDB4个库、 JZDB34库中很多delete语句走了index fast full scan的执行计划。暂时的规避方法是绑定sql_profile让这些sql走索引范围扫描，但是此类sql量很大，一旦sqltext发生变化profile会失效，这个办法并不完美。
2、搜集了表和索引的统计信息后语句的执行计划没有改变。
   经常dml操作的流水表，分析索引结构（analyze index IDX_TEST partition(P_201701) validate structure;）的时候发现索引的leaf_block非常大，但是搜集统计信息的时候，dba_indexes或者dba_ind_partitions中索引的leaf_block很小。
   oracle在评估索引数据块的时候，读取的是dba_indexes或者dba_ind_partitions里面的leaf_block，索引删除产生的空块的成本是不会被全索引快速扫描的执行计划所评估到的。
  （叶子块中存储的索引键值即使全部被删除，数据库也不会移除这个叶子块结构，oracle重用这些空块是有一定的条件的，并不会百分之百重用之前的空块）
3、表的数据量很少且dba_indexes或者dba_ind_partitions中索引的leaf_blocks、num_rows、num_distinct值很少的时候，优化器认为只需要很少次数的IO就可以读取全部的索引块，所以选择全索引扫描的执行计划。
   而实际情况是索引存在很多不能被reuse的空块，所以index fast full scan的实际执行成本会远远高于索引范围扫描。


建议：
1、系统级别修改_fix_control参数，通过_fix_control" = '17908541:0'来禁用index fast full scan
     关于该参数的说明：
     select BUGNO,DESCRIPTION from v$system_fix_control where bugno like '17908541%';

     BUGNO         DESCRIPTION
     ----------    ----------------------------------------------------------------
     17908541      consider index fast full scan on DELETE to non-IOT


2、如果无法禁用该特性，对于一些dml频繁的流水表，建议定期rebuild index（索引的高水位是无法评估的，只能通过研发层面去筛查）。


暂时没有发现通过_fix_control" = '17908541:0'来禁用index fast full scan 会带来其他什么影响。


##查询库中delete走全索引快速全扫描的SQL数量

sys@CRMDB4>select count(*) from (
  2  select distinct a.sql_id,a.OBJECT_OWNER,a.OBJECT_NAME,a.ACCESS_PREDICATES,a.OPERATION,a.OPTIONS,b.SQL_TEXT from v$sql_plan a,v$sql b
  3  where a.OPERATION = 'INDEX' and a.OPTIONS='FAST FULL SCAN'
  4    and a.SQL_ID=b.SQL_ID
  5    and b.SQL_FULLTEXT like 'DELETE%'
  6    and a.OBJECT_OWNER in ('ORDERS','PROD','TBCS','SUBS','COMMON'));

  COUNT(*)
----------
       609



以下测试是在12.1.0.2版本：
create table test_yhem_0412 (id number,deal_date date)
           partition by range (deal_date)
           (
           partition p_201701 values less than (TO_DATE('2017-02-01', 'YYYY-MM-DD')),
           partition p_201702 values less than (TO_DATE('2017-03-01', 'YYYY-MM-DD')),
           partition p_201703 values less than (TO_DATE('2017-04-01', 'YYYY-MM-DD')),
           partition p_201704 values less than (TO_DATE('2017-05-01', 'YYYY-MM-DD')),
     partition p_201705 values less than (TO_DATE('2017-06-01', 'YYYY-MM-DD'))
           );

insert into test_yhem_0412 (id,deal_date)
      select rownum,
             to_date( to_char(sysdate-365,'J')+TRUNC(DBMS_RANDOM.VALUE(0,365)),'J')
        from dual
      connect by rownum <= 100000;
commit;