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

以下测试：

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删除。