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

桃扇骨 2023-01-16 09:54 66阅读 0赞

以下测试：

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)

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)

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

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

发表评论取消回复

还没有评论，来说两句吧...

相关阅读

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

相关数据倾斜

相关 Hive数据倾斜问题

相关 oracle 优化之按图索骥：SQL中数据倾斜问题

相关 spark 数据倾斜解决方案

相关 Hive数据倾斜解决办法

相关数据倾斜

相关 Spark数据倾斜解决方案

相关 Flink 数据倾斜解决方法

相关 MapReduce解决数据倾斜