Chinaunix首页 | 论坛 | 博客
  • 博客访问: 730011
  • 博文数量: 103
  • 博客积分: 0
  • 博客等级: 民兵
  • 技术积分: 3079
  • 用 户 组: 普通用户
  • 注册时间: 2011-03-30 13:00
个人简介

About me:Oracle ACE,optimistic,passionate and harmonious. Focus on oracle programming,peformance tuning,db design, j2ee,Linux/AIX,web2.0 tech,etc

文章分类

全部博文(103)

文章存档

2020年(68)

2014年(3)

2013年(32)

分类: 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。如下例所示:


对于表TSTATUS列分布如下:

STATUS           COUNT(*)

-------------- ----------

VALID               72398

INVALID                 1

其中STATUS列有索引,显然,传入INVALID时候应该走索引,传入VALID时候应该走全表扫描。如果关闭了ACS,则谁先执行,后面的共享前面的执行计划。如下先执行INVALID走索引:


 

但是再执行VALID时候,还是一样执行计划:

 

可以看出,现在返回行数从1行变成72396行,但是执行计划没有变,根本原因是没有再次对绑定变量进行PEEKINGPEEKING的值还是原来的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特性,可以很好地解决绑定变量与直方图的自适应查询优化问题。
阅读(5217) | 评论(0) | 转发(0) |
给主人留下些什么吧!~~