使用SQL PATCH解决绑定变量与直方图的自适应查询优化问题

  直方图与绑定变量的自适应查询优化问题是困扰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
 l_sql_text clob;
begin
 SELECT sql_fulltext INTO l_sql_text FROM v$sql WHERE sql_id = 'bbj7tdztdu843' AND ROWNUM < 2;
 sys.dbms_sqldiag_internal.i_create_patch
(    sql_text  => l_sql_text,
     hint_text => 'BIND_AWARE',
     name=> 'bind_aware_bbj7tdztdu843',
    description => 'test_sql_patch');
end ;
/

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无效。

19c中测试sql profile也可以增加bind_aware,在11g里bind_aware/monitor /gather_plan_statistics  是sql  profile 是用不了的 ，12c未测试

declare
v_hints sys.sqlprof_attr;
v_sql_id clob;
begin
v_hints := sys.sqlprof_attr
(
   q'[BEGIN_OUTLINE_DATA]',
   q'[BIND_AWARE]',
   q'[IGNORE_OPTIM_EMBEDDED_HINTS]',
   q'[END_OUTLINE_DATA]'
);
select sql_text into v_sql_id from v$sql where sql_id='bbj7tdztdu843';
dbms_sqltune.import_sql_profile(v_sql_id,v_hints,'bind_aware_bbj7tdztdu843',force_match => TRUE);
end;
/  

  这样，我们先执行INVALID，还是走索引：

  但是再执行VALID，可以看到，绑定变量以及窥视了，变成VALID，走了全表扫描，通过NOTE也可以看到走了SQL PATCH。

  可以看出，使用SQL PATCH特性，可以很好地解决绑定变量与直方图的自适应查询优化问题。