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

绑定变量在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删除。
更多Oracle相关信息见Oracle 专题页面 http://www.linuxidc.com/topicnews.aspx？tid=12本文永久更新链接地址