Chinaunix首页 | 论坛 | 博客

dingjun123的blog

首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1364767
  • 博文数量： 172
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3831
  • 用 户 组： 普通用户
  • 注册时间： 2011-03-30 13:00
个人简介

About me:Oracle ACE pro,optimistic,passionate and harmonious. Focus on ORACLE,MySQL and other database programming,peformance tuning,db design, j2ee,Linux/AIX,Architecture tech,etc

文章分类

全部博文（172）

文章存档

2024年（27）

2023年（28）

2022年（43）

2020年（62）

2014年（3）

2013年（9）

我的朋友
最近访客
推荐博文
相关博文
绑定变量peeking关闭导致like走index range scan原因及分析

分类： Oracle

2024-04-29 09:14:53

一般情况下LIKE查询有三种情况：
1）%后通配可以走INDEX RANGE SCAM,
2）%前通配不能走INDEX RANGE SCAN,如果数据不能全部从索引里获取，还需要回表，则走全表扫描
3）%前通配不能走INDEX RANGE SCAN,如果数据全部从索引里获取可以走INDEX FAST FULL SCAN


数据库版本：ORACLE 19C
实际上本文问题至少从ORACLE 11G开始持续到19C一直存在。

建表语句如下：

点击(此处)折叠或打开

  1. drop table bind_test;
  2. create table bind_test as select * from dba_objects;
  3. create index idx_bind_test on bind_test(object_name);
  4. exec dbms_stats.gather_table_stats(ownname=>user,tabname=>'bind_test',no_invalidate=>false);

1.正常情况下的LIKE访问路径：

1)like后通配+非绑定变量，走INDEX RANGE SCAN，正常

点击(此处)折叠或打开

  1. select *
  2. from bind_test
  3. where object_name like 'TA%';

  5. 45 rows selected.

  7. Elapsed: 00:00:00.00

  9. Execution Plan
  10. ----------------------------------------------------------
  11. Plan hash value: 293636637

  13. -----------------------------------------------------------------------------------------------------
  14. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  15. -----------------------------------------------------------------------------------------------------
  16. | 0 | SELECT STATEMENT | | 7 | 924 | 7 (0)| 00:00:01 |
  17. | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BIND_TEST | 7 | 924 | 7 (0)| 00:00:01 |
  18. |* 2 | INDEX RANGE SCAN | IDX_BIND_TEST | 7 | | 3 (0)| 00:00:01 |
  19. -----------------------------------------------------------------------------------------------------

  21. Predicate Information (identified by operation id):
  22. ---------------------------------------------------

  24.    2 - access("OBJECT_NAME" LIKE 'TA%')
  25.        filter("OBJECT_NAME" LIKE 'TA%')


  28. Statistics
  29. ----------------------------------------------------------
  30.           0 recursive calls
  31.           0 db block gets
  32.          37 consistent gets
  33.           0 physical reads
  34.           0 redo size
  35.        8468 bytes sent via SQL*Net to client
  36.         436 bytes received via SQL*Net from client
  37.           4 SQL*Net roundtrips to/from client
  38.           0 sorts (memory)
  39.           0 sorts (disk)
  40.          45 rows processed
2）like前通配+非绑定变量，走全表扫描，正常

点击(此处)折叠或打开

  1. select *
  2. from bind_test
  3. where object_name like '%TA';

  5. Elapsed: 00:00:00.02

  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 1250996780

  11. -------------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. -------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 3638 | 468K| 395 (1)| 00:00:01 |
  15. |* 1 | TABLE ACCESS FULL| BIND_TEST | 3638 | 468K| 395 (1)| 00:00:01 |
  16. -------------------------------------------------------------------------------

  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------

  21.    1 - filter("OBJECT_NAME" LIKE '%TA' AND "OBJECT_NAME" IS NOT NULL)


  24. Statistics
  25. ----------------------------------------------------------
  26.           1 recursive calls
  27.           0 db block gets
  28.        1429 consistent gets
  29.           0 physical reads
  30.           0 redo size
  31.       17156 bytes sent via SQL*Net to client
  32.         546 bytes received via SQL*Net from client
  33.          14 SQL*Net roundtrips to/from client
  34.           0 sorts (memory)
  35.           0 sorts (disk)
  36.         194 rows processed

3）like前通配+非绑定变量，如果不需要回表，数据全部在索引里获取，则走INDEX FAST FULL SCAN，正常

点击(此处)折叠或打开

  1. select object_name
  2. from bind_test
  3. where object_name like '%TA';

  5. Elapsed: 00:00:00.02

  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 2253044292

  11. --------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. --------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 3638 | 124K| 128 (1)| 00:00:01 |
  15. |* 1 | INDEX FAST FULL SCAN| IDX_BIND_TEST | 3638 | 124K| 128 (1)| 00:00:01 |
  16. --------------------------------------------------------------------------------------

  18. Predicate Information (identified by operation id):
  19. ---------------------------------------------------

  21.    1 - filter("OBJECT_NAME" LIKE '%TA' AND "OBJECT_NAME" IS NOT NULL)


  24. Statistics
  25. ----------------------------------------------------------
  26.           1 recursive calls
  27.           0 db block gets
  28.         485 consistent gets
  29.           8 physical reads
  30.           0 redo size
  31.        6708 bytes sent via SQL*Net to client
  32.         556 bytes received via SQL*Net from client
  33.          14 SQL*Net roundtrips to/from client
  34.           0 sorts (memory)
  35.           0 sorts (disk)
  36.         194 rows processed

2.like+绑定变量关闭peeking有问题

如果绑定变量peeking关闭有BUG，不管是前通配或后通配，都走INDEX RANGE SCAN，
如果绑定变量值是后通配的问题不大，如果是前通配的，会导致严重性能问题
绑定变量peeking打开没有问题。
11g到19c都一样。

绑定变量peeking打开正常：
1)peeking打开，非前通配，正常走INDEX RANGE SCAN

点击(此处)折叠或打开

  1. var objname varchar2(100);
  2. exec :objname := 'TA%';
  3. set serveroutput off
  4. alter session set statistics_level=all;
  5. select *
  6. from bind_test
  7. where object_name like :objname;
  8. select * from table(dbms_xplan.display_cursor(null,null,'allstats last +peeked_binds'));

  10. PLAN_TABLE_OUTPUT
  11. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  12. SQL_ID dmvrs44wk5w89, child number 0
  13. -------------------------------------
  14. select * from bind_test where object_name like :objname

  16. Plan hash value: 293636637

  18. ---------------------------------------------------------------------------------------------------------------
  19. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  20. ---------------------------------------------------------------------------------------------------------------
  21. | 0 | SELECT STATEMENT | | 1 | | 45 |00:00:00.01 | 37 |
  22. | 1 | TABLE ACCESS BY INDEX ROWID BATCHED| BIND_TEST | 1 | 7 | 45 |00:00:00.01 | 37 |
  23. |* 2 | INDEX RANGE SCAN | IDX_BIND_TEST | 1 | 7 | 45 |00:00:00.01 | 6 |
  24. ---------------------------------------------------------------------------------------------------------------

  26. Peeked Binds (identified by position):
  27. --------------------------------------

  29.    1 - :1 (VARCHAR2(30), CSID=873): 'TA%'

  31. Predicate Information (identified by operation id):
  32. ---------------------------------------------------

  34.    2 - access("OBJECT_NAME" LIKE :OBJNAME)
  35.        filter("OBJECT_NAME" LIKE :OBJNAME)
2）peeking打开，前通配走TABLE ACCESS FULL正常

点击(此处)折叠或打开

  1. select address,hash_value from v$sql where sql_id='dmvrs44wk5w89';
  2. ADDRESS HASH_VALUE
  3. ---------------- ----------
  4. 00007FFE7D7138B8 958591241

  6. 先purge cursor,以防cursor共享导致演示不准：
  7. exec sys.dbms_shared_pool.purge('00007FFE7D7138B8,958591241','C');


  10. var objname varchar2(100);
  11. exec :objname := '%TA';
  12. set serveroutput off
  13. alter session set statistics_level=all;
  14. select *
  15. from bind_test
  16. where object_name like :objname;
  17. select * from table(dbms_xplan.display_cursor(null,null,'allstats last +peeked_binds'));


  20. PLAN_TABLE_OUTPUT
  21. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  22. SQL_ID dmvrs44wk5w89, child number 0
  23. -------------------------------------
  24. select * from bind_test where object_name like :objname

  26. Plan hash value: 1250996780

  28. -----------------------------------------------------------------------------------------
  29. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  30. -----------------------------------------------------------------------------------------
  31. | 0 | SELECT STATEMENT | | 1 | | 194 |00:00:00.01 | 1429 |
  32. |* 1 | TABLE ACCESS FULL| BIND_TEST | 1 | 3638 | 194 |00:00:00.01 | 1429 |
  33. -----------------------------------------------------------------------------------------

  35. Peeked Binds (identified by position):
  36. --------------------------------------

  38.    1 - :1 (VARCHAR2(30), CSID=873): '%TA'

  40. Predicate Information (identified by operation id):
  41. ---------------------------------------------------

  43.    1 - filter("OBJECT_NAME" LIKE :OBJNAME)

3.绑定变量peeking关闭，like :bind 或like '%'||:bind形式的都走INDEX RANGE SCAN
前面的好理解，因为peeking关闭，like :bind CBO可能把绑定变量当成不带%的计算选择率，这样一般选择率density，density一般很小，走索引
（事实上不是这样，是和like '%'||:bind选择率计算方式一样）。

后面的是like '%'||:bind,这种显式写了前通配，因为peeking关闭导致走INDEX RANGE SCAN,可以认为是BUG(这个BUG貌似一直存在），因为
这种走索引，肯定是从索引{BANNED}最佳左侧扫描全部索引树，除非有rownum<...可能只需要前面少量leaf block。

关闭peeking来演示:
 alter session set "_optim_peek_user_binds"=false;

1）绑定变量peeking关闭，传入的值是前通配，也走INDEX RANGE SCAN。


点击(此处)折叠或打开

  1. var objname varchar2(100);
  2. exec :objname := '%TA';
  3. set serveroutput off
  4. alter session set statistics_level=all;
  5. select *
  6. from bind_test
  7. where object_name like :objname;
  8. select * from table(dbms_xplan.display_cursor(null,null,'allstats last +peeked_binds'));

  10. dingjun123@ORCLPDB> select * from table(dbms_xplan.display_cursor(null,null,'allstats last +peeked_binds'));

  12. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  13. SQL_ID dmvrs44wk5w89, child number 1
  14. -------------------------------------
  15. select * from bind_test where object_name like :objname

  17. Plan hash value: 4236497223

  19. -------------------------------------------------------------------------------------------------------
  20. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  21. -------------------------------------------------------------------------------------------------------
  22. | 0 | SELECT STATEMENT | | 1 | | 221 |00:00:00.05 | 542 |
  23. | 1 | TABLE ACCESS BY INDEX ROWID| BIND_TEST | 1 | 3840 | 221 |00:00:00.05 | 542 |
  24. |* 2 | INDEX RANGE SCAN | IDX_BIND_TEST | 1 | 691 | 221 |00:00:00.05 | 396 |
  25. -------------------------------------------------------------------------------------------------------

  27. Predicate Information (identified by operation id):
  28. ---------------------------------------------------

  30.    2 - access("OBJECT_NAME" LIKE :OBJNAME)
  31.        filter("OBJECT_NAME" LIKE :OBJNAME)
这种E-Row计算方式和2）like '%'||:bind一样，见下面的分析，与猜想的按照类似等值查询不一样，如果按照等值查询返回行是：
num_rows*density=76807*.00002114=2行。

2）绑定变量peeking关闭，显式编码前通配，使用like '%'||:bind形式，这种按理是和peeking无关的，走不了INDEX RANGE SCAN,
但是执行计划缺走了INDEX RANGE SCAN，这是BUG，可能导致严重性能问题。

点击(此处)折叠或打开

  1. var objname varchar2(100);
  2. exec :objname := 'TA';
  3. set serveroutput off
  4. alter session set statistics_level=all;
  5. select *
  6. from bind_test
  7. where object_name like '%'||:objname||'%';
  8. select * from table(dbms_xplan.display_cursor(null,null,'allstats last +peeked_binds'));


  11. PLAN_TABLE_OUTPUT
  12. --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
  13. SQL_ID bg9sh13chmu99, child number 1
  14. -------------------------------------
  15. select * from bind_test where object_name like '%'||:objname||'%'

  17. Plan hash value: 4236497223

  19. -------------------------------------------------------------------------------------------------------
  20. | Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers |
  21. -------------------------------------------------------------------------------------------------------
  22. | 0 | SELECT STATEMENT | | 1 | | 4337 |00:00:00.07 | 3066 |
  23. | 1 | TABLE ACCESS BY INDEX ROWID| BIND_TEST | 1 | 3840 | 4337 |00:00:00.07 | 3066 |
  24. |* 2 | INDEX RANGE SCAN | IDX_BIND_TEST | 1 | 691 | 4337 |00:00:00.06 | 671 |
  25. -------------------------------------------------------------------------------------------------------

  27. Predicate Information (identified by operation id):
  28. ---------------------------------------------------

  30.    2 - access("OBJECT_NAME" LIKE '%'||:OBJNAME||'%')
  31.        filter("OBJECT_NAME" LIKE '%'||:OBJNAME||'%')

上面的like前导%的走INDEX RANGE SCAN，而且ID=2返回行是691比ID=1的3840少很多，这很让人迷惑，ID=1没有条件，按理说ID=1应该
只会比ID=2少，唯一可能的情况是ID=2的选择率计算方式和ID=1的不一样，还是通过10053看：

10053 Access path analysis计算如下：

点击(此处)折叠或打开

  1. ***************************************
  2. BASE STATISTICAL INFORMATION
  3. ***********************
  4. Table Stats::
  5.   Table: BIND_TEST Alias: BIND_TEST
  6.     #Rows: 76807 #Blks: 1120 AvgRowLen: 98.00 ChainCnt: 0.00
  7. Index Stats::
  8.   Index: IDX_BIND_TEST Col#: 2
  9.     LVLS: 2 #LB: 379 #DK: 47304 LB/K: 1.00 DB/K: 1.00 CLUF: 38540.00
  10. Access path analysis for BIND_TEST
  11. ***************************************
  12. SINGLE TABLE ACCESS PATH
  13.   Single Table Cardinality Estimation for BIND_TEST[BIND_TEST]
  14.   Column (#2): OBJECT_NAME(
  15.     AvgLen: 25 NDV: 47304 Nulls: 0 Density: 0.000021
  16.   Table: BIND_TEST Alias: BIND_TEST
  17.     Card: Original: 76807.000000 Rounded: 3840 Computed: 3840.35 Non Adjusted: 3840.35
  18.   Access Path: TableScan
  19.     Cost: 307.14 Resp: 307.14 Degree: 0
  20.       Cost_io: 305.00 Cost_cpu: 45073703
  21.       Resp_io: 305.00 Resp_cpu: 45073703
  22. kkofmx: index filter:"BIND_TEST"."OBJECT_NAME" LIKE '%'||:B1||'%'

  24.   Access Path: index (RangeScan)
  25.     Index: IDX_BIND_TEST
  26.     resc_io: 353.00 resc_cpu: 2949828
  27.     ix_sel: 0.009000 ix_sel_with_filters: 0.009000
  28.     Cost: 304.14 Resp: 304.14 Degree: 1
  29.   Best:: AccessPath: IndexRange
  30.   Index: IDX_BIND_TEST
  31.          Cost: 304.14 Degree: 1 Resp: 304.14 Card: 3840.35 Bytes: 0
         
结论：为什么绑定变量peeking会出现不管%是显式写还是写在绑定变量的值里，都可能走INDEX RANGE SCAN呢？


从10053看，CBO没有考虑显式前导%走不了索引的问题，当然%写在绑定变量值里也不会考虑，
主要是索引访问选择率ix_sel是0.009,相当于0.9%，因为CBO没有考虑%，这可能导致走索引。


这个0.009貌似是个默认值（无peeking的like默认sel)：
在10053里回表过滤的选择率也是0.009,但是执行计划显示的回表选择率还是按照5%（实际回表计算的sel和10053显示不一样）


也就是ix_sel实际上是按照0.009,ix_sel_with_filters实际上按照0.05，导致ID=2的返回行计算是691行，然后ID=1回表没有任何过滤条件，
因为回表按照5%计算，导致行数比ID=2的多很多，这是优化器算法层面的BUG。


具体计算过程如下：
ID=2 INDEX RANGE SCAN E-Rows = round(76807*.009) = 691行
ID=1 TABLE ACCESS BY INDEX ROWID E-Rows = round(76807*.05) = 3840行


实际计算ix_sel:0.009,ix_sel_with_filters:0.05
与10053显示的回表过滤选择率ix_sel_with_filters不一样，10053的如下：
ix_sel: 0.009000  ix_sel_with_filters: 0.009000 


一般情况下，绑定变量+无peeking要使用默认选择率，如下所示：

点击(此处)折叠或打开

  1. = 条件选择率：
  2. greatest(1/NDV,DENSITY)*(num_rows-null_rows)/num_rows

  4. >,<,<=,>= 条件选择率,一般情况就用0.05了：
  5. greatest(0.05,1/NDV,DENSITY)*(num_rows-null_rows)/num_rows

  7. like全表扫描选择率或回表选择率：常数是0.05
  8. greatest(0.05,1/NDV,DENSITY)*(num_rows-null_rows)/num_rows

  10. like条件index range scan选择率：常数不是0.05,是0.009
  11. greatest(0.009,1/NDV,DENSITY)*(num_rows-null_rows)/num_rows
为什么公式里还有density,因为如果带直方图的话，density就不是1/NDV,没有直方图density=1/NDV:

为了验证like带绑定变量，无peeking的选择率计算公式，可以找个density比0.009大的验证下：

点击(此处)折叠或打开

  1. select column_name,num_distinct,density,num_nulls from dba_tab_col_statistics where table_name='BIND_TEST';

  3. COLUMN_NAME NUM_DISTINCT DENSITY NUM_NULLS
  4. ------------------------------ ------------ ---------- ----------
  5. EDITION_NAME 0 0 76807
  6. NAMESPACE 21 .047619048 0
  7. SECONDARY 2 .5 0
  8. GENERATED 2 .5 0
  9. TEMPORARY 2 .5 0
  10. STATUS 2 .5 0
  11. TIMESTAMP 1663 .000601323 0
  12. LAST_DDL_TIME 1583 .000631712 0
  13. CREATED 1529 .000654022 0
  14. OBJECT_TYPE 45 .022222222 0
  15. DATA_OBJECT_ID 10551 .000094778 66206
  16. OBJECT_ID 76807 .00001302 0
  17. SUBOBJECT_NAME 271 .003690037 76154
  18. OBJECT_NAME 47304 .00002114 0
  19. OWNER 36 .027777778 0
可以用owner列，density=.027777778 > 0.009,为了验证，建个索引：

点击(此处)折叠或打开

  1. create index idx_bind_test_owner on bind_test(owner);

  3. var owner varchar2(100);
  4. select * from bind_test
  5. where owner like :owner;

  7. Execution Plan
  8. ----------------------------------------------------------
  9. Plan hash value: 585688886

  11. ---------------------------------------------------------------------------------------------------
  12. | Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
  13. ---------------------------------------------------------------------------------------------------
  14. | 0 | SELECT STATEMENT | | 3840 | 367K| 65 (0)| 00:00:01 |
  15. | 1 | TABLE ACCESS BY INDEX ROWID| BIND_TEST | 3840 | 367K| 65 (0)| 00:00:01 |
  16. |* 2 | INDEX RANGE SCAN | IDX_BIND_TEST_OWNER | 2135 | | 7 (0)| 00:00:01 |
  17. ---------------------------------------------------------------------------------------------------

  19. Predicate Information (identified by operation id):
  20. ---------------------------------------------------

  22.    2 - access("OWNER" LIKE :OWNER)
  23.        filter("OWNER" LIKE :OWNER)
ID=2的选择率按照公式：greatest(0.009,1/NDV,DENSITY)*(num_rows-null_rows)/num_rows=  .027777778
计算的行数  
select 76807 * .027777778 from dual;     
2134 接近2135


然后ID=1还是按照0.05计算=3840行。


另外从12c开始还有几个like :bind写法的BUG也是走index range scan,上述例子在11g里同样存在：
见BUG：Index SCAN Instead of FAST FULL SCAN With COL LIKE '%bind Variable%' (Doc ID 2781949.1)

综上所述，建议如下：
1）影响优化器的默认参数，除非出现重大影响或确定影响生产的BUG的需要关闭，一般都用默认值，特别是
以_optimizer开头的隐含参数，比如：_optimizer_cost_based_transformation、_optimizer_squ_bottomup


2）绑定变量peeking{BANNED}最佳好不要关闭，关闭了影响直方图使用，无法计算真实的数据分布，
如果查询总是查询某个分布很少的值，有了peeking会走索引，没有peeking就按照默认选择率，可能不走索引。
比如status=1,0,其中1很多，0很少，基本按照status='0'查询，这样走索引好，如果
关闭peeking,按照where status = :var查询，选择率是max(1/2,density)则走不了索引.


关闭peeking,可能会导致like :bind或like '%'||:bind形式的条件走INDEX RANGE SCAN，见本篇文章。


如果绑定变量peeking遇到直方图，传入的值对应分布区间变动频繁，不同分布要求不一样执行计划，
ORACLE有adaptive cursor sharding（BUG多，一般关闭）或可以使用BIND_AWARE HINTS+SQL DIRECTIVE/SQL PROFILE绑定解决（建议使用）。




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

上一篇：12C批量加载自动收集统计信息特性和相关BUG、限制

下一篇：快速从一个表里随机获取N行数据的方法

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

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

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

16024965号-6