凯发app官方网站-凯发k8官网下载客户端中心 | | 凯发app官方网站-凯发k8官网下载客户端中心

dingjun123的blog

凯发app官方网站首页　| 　博文目录　| 　关于我

dingjun123

  • 博客访问： 1156193
  • 博文数量： 166
  • 博客积分： 0
  • 博客等级： 民兵
  • 技术积分： 3760
  • 用 户 组： 普通用户
  • 注册时间： 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

文章分类

全部博文（166）

文章存档

2024年（21）

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);

绑定变量peeking关闭导致like走index range scan原因及分析-凯发app官方网站

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

如果绑定变量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绑定解决（建议使用）。




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

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

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

给主人留下些什么吧！~~
| | | | |

凯发app官方网站 copyright 2001-2010 chinaunix.net all rights reserved 北京皓辰网域网络信息技术有限公司. 凯发k8官网下载客户端中心的版权所有

感谢所有关心和支持过chinaunix的朋友们
")); function link(t){ var href= $(t).attr('href'); href ="?url=" encodeuricomponent(location.href); $(t).attr('href',href); //setcookie("returnouturl", location.href, 60, "/"); }
网站地图