当前位置:  数据库>oracle

Oracle通过BIND_AWARE+SQL PATCH解决SQL绑定变量中数据倾斜的问题

    来源: 互联网  发布时间:2017-06-26

    本文导语: 绑定变量在OLTP系统中是解决硬解析问题的有利手断,但如果某SQL使用的索引中过滤字段存在数据倾斜(数据分布不均匀)的问题,就可能造成数据库性能异常。从Oracle 9i引入的BIND PEEK不能解决这个问题,因为bind peek只是发生在...

绑定变量在OLTP系统中是解决硬解析问题的有利手断,但如果某SQL使用的索引中过滤字段存在数据倾斜(数据分布不均匀)的问题,就可能造成数据库性能异常。从Oracle 9i引入的BIND PEEK不能解决这个问题,因为bind peek只是发生在第一次硬解析;从11G引入了ACS(adaptive cursor sharing)可以解决这个问题,但Bug多多,在最佳实践中会将其关闭。当然我们可以通过HINT:BIND_AWARE加上SQL PATCH在不修改SQL的情况下解决ACS关闭时的这个问题。

以下测试:
DB Version:Oracle 11.2.0.4
OS:CentOS 6.6
测试工具:pl/sql developer
 
1.建立测试表和数据
--建表
create table scott.tb_sql_patch as select * from dba_objects;

--建索引
create index scott.idx_tb_sql_patch_01 on scott.tb_sql_patch(object_id);

--更新数据,使用数据分布不均匀
update scott.tb_sql_patch set object_id=10 where object_id>10;
commit;

select object_id,count(1) from scott.tb_sql_patch group by object_id;
/*
OBJECT_ID   COUNT(1)
           3
6           1
2           1
5           1
4           1
8           1
3           1
7           1
10           86869
9           1
*/

--收集统计信息
begin
  dbms_stats.gather_table_stats('scott','tb_sql_patch', method_opt => 'for columns object_id size auto',cascade=>true);
end;

--查看直方图信息
select table_name,column_name,histogram from dba_tab_col_statistics
where table_name='TB_SQL_PATCH' and column_name='OBJECT_ID';
/*
TABLE_NAME   COLUMN_NAME   HISTOGRAM
TB_SQL_PATCH   OBJECT_ID   FREQUENCY
*/

select * from dba_tab_histograms
where table_name='TB_SQL_PATCH' and column_name='OBJECT_ID';
/*
OWNER   TABLE_NAME   COLUMN_NAME   ENDPOINT_NUMBER   ENDPOINT_VALUE   ENDPOINT_ACTUAL_VALUE
SCOTT   TB_SQL_PATCH   OBJECT_ID   1   2   
SCOTT   TB_SQL_PATCH   OBJECT_ID   2   8   
SCOTT   TB_SQL_PATCH   OBJECT_ID   5559   10   
*/

2.查看ACS(adaptive cursor sharing)和bind peek相关参数
--从下面查询结果可以看到ACS已关闭,BIND PEEK是打开的。如果BIND PEEK关闭,ACS会自动关闭。
select name, value
  from v$parameter
 where name in ('_optimizer_adaptive_cursor_sharing',
                '_optimizer_extended_cursor_sharing_rel',
                '_optimizer_extended_cursor_sharing',
                '_optim_peek_user_binds');
               
/*
NAME   VALUE
_optimizer_extended_cursor_sharing   NONE
_optimizer_extended_cursor_sharing_rel   NONE
_optimizer_adaptive_cursor_sharing   FALSE
_optim_peek_user_binds   TRUE
*/

3.测试
3.1先看看使用非绑定变量的表现
select * from scott.tb_sql_patch where object_id=1;

select * from scott.tb_sql_patch where object_id=10;

--从下面的查询结果可以看出,因为索引字段上存在直方图,SQL根据非绑定变量的实际值走了不同的执行计划。这也是一种解决绑定变量中数据倾斜的方法,但使用非绑定变量,硬解析会加大。可以考虑在程序中先判断变量的值来决定走绑定变量方式还是非绑定变量方式。
select sql_id,plan_hash_value,a.sql_text from v$sql a
where sql_text like 'select * from scott.tb_sql_patch where object_id%';
/*
SQL_ID   PLAN_HASH_VALUE   SQL_TEXT
atdt8tn0pgn5a   815055989   select * from scott.tb_sql_patch where object_id=10
dt85j6g88ztmj   57853615   select * from scott.tb_sql_patch where object_id=1
*/

3.2再来看看使用绑定变量的表现
--清空共享池
alter system flush shared_pool;

--使用绑定变量
--执行下面两个pl/sql,两个绑定变量的数据分布不同
DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'select * from scott.tb_sql_patch where object_id=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 1;
END;


DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'select * from scott.tb_sql_patch where object_id=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 10;
END;

--从下面的查询结果可以看出,两个绑定变量的数据分布不同,但SQL只生成了一个执行计划
select sql_id,plan_hash_value,a.sql_text from v$sql a
where sql_text like 'select * from scott.tb_sql_patch where object_id=:1';
/*
SQL_ID   PLAN_HASH_VALUE   SQL_TEXT   IS_BIND_SENSITIVE   IS_BIND_AWARE
djzfp4cy24dx3   815055989   select * from scott.tb_sql_patch where object_id=:1   N   N   
*/

3.3最后看看使用绑定变量+HINT:BIND_AWARE+SQL PATCH的表现
--执行以下pl/sql,为SQL:djzfp4cy24dx3增加HINT,需要SYS用户去执行
DECLARE
  V_SQL CLOB;
begin
  --取出原SQL的文本
  SELECT SQL_FULLTEXT INTO V_SQL FROM V$SQL WHERE SQL_ID = 'djzfp4cy24dx3' AND ROWNUM = 1;
  --增加HINT
  sys.dbms_sqldiag_internal.i_create_patch(sql_text  => V_SQL,
                                          hint_text => 'BIND_AWARE',
                                          name      => 'sql_djzfp4cy24dx3');
end;
--执行成功后,可在dba_sql_patches视图中查看相关信息

--dbms_sqldiag_internal.i_create_patch在Oracle中是加密的,解密后内容如下:
PACKAGE dbms_sqldiag_internal
  PROCEDURE I_CREATE_PATCH(
          SQL_TEXT      IN CLOB,
          HINT_TEXT    IN VARCHAR2,
          NAME          IN VARCHAR2 := NULL,
          DESCRIPTION  IN VARCHAR2 := NULL,
          CATEGORY      IN VARCHAR2 := 'DEFAULT',
                  VALIDATE      IN BOOLEAN  := TRUE)
  IS
    RET_NAME  VARCHAR2(30);
    HS        SYS.SQLPROF_ATTR;
  BEGIN
    COMMIT;
   DBMS_SMB.CHECK_SMB_PRIV;
    HS := SYS.SQLPROF_ATTR(HINT_TEXT);
    RET_NAME := DBMS_SQLTUNE_INTERNAL.I_CREATE_SQL_PROFILE(
      SQL_TEXT => SQL_TEXT,
      PROFILE_XML => DBMS_SMB_INTERNAL.VARR_TO_HINTS_XML(HS),
      NAME => NAME,
      DESCRIPTION => DESCRIPTION,
      CATEGORY => CATEGORY,
      CREATOR => SYS_CONTEXT('USERENV', 'SESSION_USER'),
      VALIDATE => VALIDATE,
      TYPE => 'PATCH',
      IS_PATCH => TRUE);
  END;

 
--清空共享池  
alter system flush shared_pool;
--新开会话窗口

--使用绑定变量
--执行下面两个pl/sql,两个绑定变量的数据分布不同
DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'select * from scott.tb_sql_patch where object_id=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 1;
END;


DECLARE
  V_SQL VARCHAR2(3000);
BEGIN
  V_SQL := 'select * from scott.tb_sql_patch where object_id=:1';
  EXECUTE IMMEDIATE V_SQL
    USING 10;
END;

--从下面可以看到,两个绑定变量的数据分布不同,SQL生成了两个不同执行计划,并且使用了上面添加的SQL PATCH
select sql_id,plan_hash_value,a.sql_text,is_bind_sensitive,is_shareable from v$sql a
where sql_text like 'select * from scott.tb_sql_patch where object_id=:1';
/*
SQL_ID   PLAN_HASH_VALUE   SQL_TEXT   IS_BIND_SENSITIVE   IS_BIND_AWARE   SQL_PATCH
djzfp4cy24dx3   815055989   select * from scott.tb_sql_patch where object_id=:1   Y   Y   sql_djzfp4cy24dx3
djzfp4cy24dx3   57853615   select * from scott.tb_sql_patch where object_id=:1   Y   Y   sql_djzfp4cy24dx3
*/

备注:
1.据说在11.2.0.3上使用此方法由于user_bind_peek_mismatch,执行计划不能被共享。我的测试数据库是Oracle 11.2.0.4,没有出现这个问题。
2.上面的测试中,_optim_peek_user_binds=TRUE,如果_optim_peek_user_binds=FALSE,将dbms_sqldiag_internal.i_create_patch中的hint_text值改为 'OPT_PARAM(''_optim_peek_user_binds'' ''true'') BIND_AWARE'即可。
3.如果不再需要SQL PATCH,可通过dbms_sqldiag.drop_sql_patch删除。



    
 
 
 
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术,将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外,均为转载、整理或搜集自网络。欢迎任何形式的转载,转载请注明出处。












  • 相关文章推荐
  • window中oracle环境变量设置方法分享
  • Oracle 系统变量函数介绍
  • oracle环境变量保存问题求教
  • Oracle 系统变量函数用法指南
  • Oracle与JSDK4.0环境变量冲突,如何解决(在线等待)?
  • Oracle 中的表变量(数组变量)
  • 要装oracle,设置好环境变量。之后就啥命令也用不了了
  • oracle客户端环境变量设置的问题
  • 深入ORACLE变量的定义与使用的详解
  • Oracle全局数据库名、环境变量和sid的区别
  • Linux下安装oracle碰到环境变量问题的解决
  • Oracle环境变量引发“Connected to an idle instance.”错误
  • Oracle 中的表变量(数组变量) iis7站长之家
  • 在linux下安装oracle,如何设置让oracle自动启动!也就是让oracle那个服务自动启动,不是手动的
  • oracle 11g最新版官方下载地址
  • 请问su oracle 和su - oracle有什么不同?
  • Oracle 数据库(oracle Database)Select 多表关联查询方式
  • 虚拟机装Oracle R12与Oracle10g
  • Oracle数据库(Oracle Database)体系结构及基本组成介绍
  • Oracle 数据库开发工具 Oracle SQL Developer
  • 如何设置让Oracle SQL Developer显示的时间包含时分秒
  • Oracle EBS R12 支持 Oracle Database 11g
  • Oracle 10g和Oracle 11g网格技术介绍
  • SCO unix下安装oracle,但没有光盘,请大家推荐一个oracle下载站点(unix版本的)。谢谢!!!!
  • oracle中如何把表中具有相同值列的多行数据合并成一行
  • 请问大家用oracle数据库, 用import oracle.*;下的东西么? 还是用标准库?
  • Oracle 数据库(oracle Database)性能调优技术详解
  • Linux /$ORACLE_HOME $ORACLE_HOME
  • ORACLE日期相关操作
  • Linux系统下Oracle的启动与Oracle监听的启动
  • ORACLE数据库常用字段数据类型介绍


  • 站内导航:


    特别声明:169IT网站部分信息来自互联网,如果侵犯您的权利,请及时告知,本站将立即删除!

    ©2012-2021,,E-mail:www_#163.com(请将#改为@)

    浙ICP备11055608号-3