越界值导致执行计划走错

最近客户生产上遇到一个统计信息陈旧涉及的 越界 值查询 导致 执行 计划 走错的案例： SQL查询 bankdate = '2013/03/19' 就走到了不合适的索引 IDX_DSF_BANKAPPLHISTORY_02. 下面的 执行 计划 可以忽略DSF_BANKCODE这块。 下面的 执行 计划 可以忽略DSF_BANK

最近客户生产上遇到一个统计信息陈旧涉及的越界值查询导致执行计划走错的案例：

SQL查询bankdate >= '2013/03/19' 就走到了不合适的索引IDX_DSF_BANKAPPLHISTORY_02.

下面的执行计划可以忽略DSF_BANKCODE这块。

下面的执行计划可以忽略DSF_BANKCODE这块。


SQL> select *
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/19' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3875365240

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_02 |     1 |       |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16' AND
              "OPERTYPE"='1')
   3 - access("BANKDATE">='2013/03/19')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")



生产上的这条SQL语句走错了索引，本身应该走到索引IDX_DSF_BANKAPPLHISTORY_10的，缺走了IDX_DSF_BANKAPPLHISTORY_02。

?2个索引的情况如下：
?
? IDX_DSF_BANKAPPLHISTORY_10(RECORDNUM,MONTHNM??)?
? IDX_DSF_BANKAPPLHISTORY_02(BANKDATE)?


如果查询采用bankdate >= '2013/03/18' 就能走到正确的索引。

SQL> select *
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/18' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1807757810

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_10 |     1 |       |     4   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "BANKDATE">='2013/03/18' AND "OPERTYPE"='1')
   3 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")
   
采用3月18日之前的日期都能走到正确的索引，而采用3月19日及其之后日期都会走到错误的IDX_DSF_BANKAPPLHISTORY_02索引上。

其实3月18日和3月19日走索引idx_dsf_bankapplhistory_10的成本都没有变化。

SQL> select  /*+index(t,idx_dsf_bankapplhistory_10)*/*
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/18' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1');

Execution Plan
----------------------------------------------------------
Plan hash value: 1807757810

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_10 |     1 |       |     4   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "BANKDATE">='2013/03/18' AND "OPERTYPE"='1')
   3 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")





SQL> select  /*+index(t,idx_dsf_bankapplhistory_10)*/*
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/19' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 1807757810

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_10 |     1 |       |     4   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "BANKDATE">='2013/03/19' AND "OPERTYPE"='1')
   3 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")

而3月18日和3月19日走索引idx_dsf_bankapplhistory_02的成本却降低了1，而估算出的基数也降为1，导致CBO选择了这个错误的索引。

SQL> select  /*+index(t,idx_dsf_bankapplhistory_02)*/*
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/18' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3875365240

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     7   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     7   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_02 |    21 |       |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16' AND
              "OPERTYPE"='1')
   3 - access("BANKDATE">='2013/03/18')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")
   
SQL> select  /*+index(t,idx_dsf_bankapplhistory_02)*/*
  2    from dsf_bankapplhistory t
  3   where (((((bankdate >= '2013/03/19' and
  4         recordnum = 'P00Y0ABFG1E220120204358') and monthnm = '16') and
  5         bankcode in
  6         (select bankcode
  7               from dsf_bankcode
  8              where (bankcode = '800000000000' or
  9                    get_bankcode = '800000000000'))) and flag = '2') and
 10         opertype = '1')
 11  /

Execution Plan
----------------------------------------------------------
Plan hash value: 3875365240

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                            |     1 |   631 |     6   (0)| 00:00:01 |
|   1 |  NESTED LOOPS SEMI           |                            |     1 |   631 |     6   (0)| 00:00:01 |
|*  2 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     4   (0)| 00:00:01 |
|*  3 |    INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_02 |     1 |       |     3   (0)| 00:00:01 |
|*  4 |   TABLE ACCESS BY INDEX ROWID| DSF_BANKCODE               |     2 |    52 |     2   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN          | IDX_BANKCODE_1             |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - filter("FLAG"='2' AND "RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16' AND
              "OPERTYPE"='1')
   3 - access("BANKDATE">='2013/03/19')
   4 - filter("GET_BANKCODE"='800000000000' OR "BANKCODE"='800000000000')
   5 - access("BANKCODE"="BANKCODE")
   
3月18日估算出的基数为21，而3月19日估算出的基数为1(实际上这里估算出的基数应该为0,但一般CBO不允许CARDINALITY为0)。

经查询统计信息过于陈旧：

SQL> select table_name,last_analyzed from dba_tables where table_name='DSF_BANKAPPLHISTORY';

TABLE_NAME           LAST_ANALYZED
-------------------- -------------------
DSF_BANKAPPLHISTORY  2012-11-25 21:17:26

这很容易让人联想到越界值的查询，对于越界值的查询将会导致选择性的线性降低，如下图所示：





下面的查询可以查询到BANKDATE的最大和最小值。

SQL> declare
  2     v_high_date date;
  3     v_low_date  date;
  4     v_high_value dba_tab_col_statistics.high_value%type;
  5     v_low_value  dba_tab_col_statistics.low_value%type;
  6  begin
  7     select high_value,low_value into v_high_value,v_low_value
  8     from dba_tab_col_statistics 
  9     where table_name='DSF_BANKAPPLHISTORY' and column_name='BANKDATE';
 10     dbms_stats.convert_raw_value(v_high_value,v_high_date);
 11     dbms_stats.convert_raw_value(v_low_value,v_low_date);
 12     dbms_output.put_line('high date:'||to_char(v_high_date,'YYYY-MM-DD HH24:MI:SS'));
 13     dbms_output.put_line('low  date:'||to_char(v_low_date,'YYYY-MM-DD HH24:MI:SS'));
 14  end;
 15  /
high date:2012-11-25 00:00:00
low  date:2012-08-03 00:00:00

PL/SQL procedure successfully completed.



为什么3月19日是转折点，下面的查询可以解析这一点：

SQL> select date '2012-11-25'+(date '2012-11-25' - date '2012-08-03') from dual;

DATE'2012-11-25'+11
-------------------
2013-03-19 00:00:00

其实还有一个转折点就是2012-04-11

SQL> select date '2012-08-03'-(date '2012-11-25' - date '2012-08-03') from dual;

DATE'2012-08-03'-(D
-------------------
2012-04-11 00:00:00

下面我们来看看2012-04-11这个查询的执行计划。







SQL> select  *
  2    from dsf_bankapplhistory t
  3   where bankdate
  4         recordnum = 'P00Y0ABFG1E220120204358' and monthnm = '16'
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 471247677

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |     1 |   605 |     4   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     4   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_02 |     1 |       |     3   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')
   2 - access("BANKDATE"

SQL> select  *
  2    from dsf_bankapplhistory t
  3   where bankdate
  4         recordnum = 'P00Y0ABFG1E220120204358' and monthnm = '16'
  5  /

Execution Plan
----------------------------------------------------------
Plan hash value: 477419360

----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                       | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                            |     1 |   605 |     5   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| DSF_BANKAPPLHISTORY        |     1 |   605 |     5   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_DSF_BANKAPPLHISTORY_10 |     1 |       |     4   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("BANKDATE"
   2 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')