视频1 视频21 视频41 视频61 视频文章1 视频文章21 视频文章41 视频文章61 推荐1 推荐3 推荐5 推荐7 推荐9 推荐11 推荐13 推荐15 推荐17 推荐19 推荐21 推荐23 推荐25 推荐27 推荐29 推荐31 推荐33 推荐35 推荐37 推荐39 推荐41 推荐43 推荐45 推荐47 推荐49 关键词1 关键词101 关键词201 关键词301 关键词401 关键词501 关键词601 关键词701 关键词801 关键词901 关键词1001 关键词1101 关键词1201 关键词1301 关键词1401 关键词1501 关键词1601 关键词1701 关键词1801 关键词1901 视频扩展1 视频扩展6 视频扩展11 视频扩展16 文章1 文章201 文章401 文章601 文章801 文章1001 资讯1 资讯501 资讯1001 资讯1501 标签1 标签501 标签1001 关键词1 关键词501 关键词1001 关键词1501 专题2001
越界值导致执行计划走错
2020-11-09 08:23:28 责编:小采
文档
点击下载本文 文档为doc格式


最近客户生产上遇到一个统计信息陈旧涉及的 越界 值查询 导致 执行 计划 走错的案例： 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 <= '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')







下载本文
显示全文
专题
动视 51dongshi.net 版权所有
Copyright © 2019-2025