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）
- cbo（54）
- tuning&performan（32）
- concepts&archite（4）
- troubleshooting（5）
- new feature（7）
- sql（50）
- pl/sql（8）
- miscellaneous（3）
- mysql（3）
- 未分配的博文（0）
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
- ·
分类： oracle
2020-08-31 16:00:09
直方图与绑定变量的自适应查询优化问题是困扰sql性能优化的一个典型问题：一方面绑定变量是为了让执行计划共享，从而减少或避免解析，但是如果一个列分布不均，传入不同的值最佳执行计划应该不一样，比如当status=’invalid’的时候最佳执行计划是走索引，当status=’valid’时候最佳执行计划是要求全表扫描，遇到这种情况，必须要再次窥视传入的绑定变量值，才能走正确执行计划，因此，11g引入了adaptive cursor sharing(acs)来解决这个问题，但是因为bug多，一般情况下生产库是建议关闭的。那么还能不能解决这个问题呢？在11.2及之后答案是肯定的。在11.2的时候，我们使用sql patch来解决，这个类似sql profile。如下例所示：
对于表t的status列分布如下：
status count(*)
-------------- ----------
valid 72398
invalid 1
其中status列有索引，显然，传入invalid时候应该走索引，传入valid时候应该走全表扫描。如果关闭了acs，则谁先执行，后面的共享前面的执行计划。如下先执行invalid走索引：
但是再执行valid时候，还是一样执行计划：
可以看出，现在返回行数从1行变成72396行，但是执行计划没有变，根本原因是没有再次对绑定变量进行peeking，peeking的值还是原来的invalid。知道这点就好办了，其实acs的本质是使用了hints：bind_aware，那么在11.2时候使用sql patch就可以了，如下所示：
11g写法如下（dbms_sqldiag_internal这个是内部存储过程，一般不建议使用）：
|
declare
|
12.2及之后可以使用官方的dbms_sqldiag.create_sql_patch：
|
declare l varchar2(32767); begin l := sys.dbms_sqldiag.create_sql_patch( sql_id => 'bbj7tdztdu843', hint_text => q'[bind_aware]', name => 'bind_aware_bbj7tdztdu843'); end; /
|
在19c里测试，使用sql profile也是有效的，11g里使用sql profile无效。
|
declare |
这样，我们先执行invalid，还是走索引：
但是再执行valid，可以看到，绑定变量以及窥视了，变成valid，走了全表扫描，通过note也可以看到走了sql patch。
可以看出，使用sql patch特性，可以很好地解决绑定变量与直方图的自适应查询优化问题。