Chinaunix首页 | 论坛 | 博客

美丽人生yanshoupeng.blog.chinaunix.net

首页　| 　博文目录　| 　关于我

TOMSYAN

  • 博客访问： 2880810
  • 博文数量： 599
  • 博客积分： 16398
  • 博客等级： 上将
  • 技术积分： 6875
  • 用 户 组： 普通用户
  • 注册时间： 2009-11-30 12:04
个人简介

WINDOWS下的程序员出身，偶尔也写一些linux平台下小程序， 后转行数据库行业，专注于ORACLE和DB2的运维和优化。 同时也是ios移动开发者。欢迎志同道合的朋友一起研究技术。 数据库技术交流群：58308065，23618606

文章分类

全部博文（599）

文章存档

2014年（12）

2013年（56）

2012年（199）

2011年（105）

2010年（128）

2009年（99）

我的朋友
最近访客
推荐博文
相关博文
越界值导致执行计划走错

分类： Oracle

2013-05-17 15:56:13

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

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 <= '2012/04/11' and
  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"<='2012/04/11')

SQL> select  *
  2    from dsf_bankapplhistory t
  3   where bankdate <= '2012/04/12' and
  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"<='2012/04/12')
   2 - access("RECORDNUM"='P00Y0ABFG1E220120204358' AND "MONTHNM"='16')







阅读(1497) | 评论(0) | 转发(0) |
0

上一篇：查找坏块脚本

下一篇：SQOOP与ORACLE交互要注意的地方

给主人留下些什么吧！~~
关于我们 | 关于IT168 | 联系方式 | 广告合作 | 法律声明 | 免费注册

Copyright 2001-2010 ChinaUnix.net All Rights Reserved 北京皓辰网域网络信息技术有限公司. 版权所有

感谢所有关心和支持过ChinaUnix的朋友们

16024965号-6