共计 5378 个字符，预计需要花费 14 分钟才能阅读完成。
绑定变量在 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
本文永久更新链接地址 ：http://www.linuxidc.com/Linux/2017-01/139394.htm
