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

末蓝、 2022-05-30 07:28 396阅读 0赞

数据倾斜即表中某个字段的值分布不均匀，比如有100万条记录，其中字段A中有90万都是相同的值。这种情况下，字段A作为过滤条件时，可能会引起一些性能问题。

本文通过示例分享部分场景的处理方法

1.  未使用绑定变量
2.  使用绑定变量
3.  几种特殊场景

测试环境说明

*数据库版本：ORACLE 11.2.0.4*

新建测试表tb\_test：

create tablescott.tb\_test as select \* from dba\_objects;

创建索引：

create indexscott.idx\_tb\_test\_01 on scott.tb\_test(object\_id);

更新数据,使用数据分布不均匀：

update scott.tb\_testset object\_id=10 where object\_id>10;

commit;

查看数据分布情况：

select object\_id,count(1) from scott.tb\_test groupby object\_id;

![0?wx\_fmt=png][0_wx_fmt_png]

未使用绑定变量

未使用绑定变量的情况下通常数据分布不均匀不会造成问题，但这主要依赖于三个方面：

*  数据分布不均匀的字段是否做为过滤条件或连接条件。

*  数据分布不均匀的字段是否有收集直方图，如果没有收集直方图就可能会有问题。在没有收集直方图的情况下，这个字段的过滤性DENSITY都是等于1/NUM\_DISTINCT;在收集了直方图的情况下，这个字段的过滤性会根据条件值在直方图中的分布比例来计算。

*  数据库cursor\_sharing参数的值是否为exact，如果参数的值为force，相当于使用绑定变量。那就会存在类似使用绑定变量时存在的问题，下节会讲到。

**未收集直方图的情况：**

对测试表tb\_test进行统计信息收集，收集时指定不收集字段object\_id的直方图：

begin

dbms\_stats.gather\_table\_stats('scott','TB\_TEST', method\_opt => 'forcolumns object\_id size 1',cascade=>true);

end;

确认是否收集了直方图：

selecttable\_name,column\_name,histogram from dba\_tab\_col\_statistics

wheretable\_name='TB\_TEST' and column\_name='OBJECT\_ID';

![0?wx\_fmt=png][0_wx_fmt_png 1]

从上图可以看出字段OBJECT\_ID未收集直方图。

**执行测试SQL:**

返回记录比较少的值：

select \* fromscott.TB\_TEST where object\_id=1;

返回记录比较多的值：

select \* fromscott.TB\_TEST where object\_id=10;

查看SQL信息：

selectsql\_text,sql\_id,plan\_hash\_value from v$sql

where sql\_text like'select \* from scott.TB\_TEST where object\_id=%';

![0?wx\_fmt=png][0_wx_fmt_png 2]

从上图可以看出，两条SQL的PLAN\_HASH\_VALUE是一样的，也就是走了相同的执行计划。

再看一下这两条SQL的执行计划：

SELECT SQL\_ID,

PLAN\_HASH\_VALUE,

LPAD(' ', 4 \* DEPTH) || OPERATION ||OPTIONS OPERATION,

OBJECT\_NAME,

CARDINALITY,

BYTES,

COST,

TIME

FROM V$SQL\_PLAN

WHERE sql\_id in('8zqcak67wwh8f','bfag4mr23qht5')

ORDER BY ADDRESS, ID

![0?wx\_fmt=png][0_wx_fmt_png 3]

从上面也可以看出两条SQL的执行计划是相同的。

#### **收集直方图的情况：** ####

下面收集字段OBJECT\_ID的直方图：

begin

dbms\_stats.gather\_table\_stats('scott','TB\_TEST', method\_opt => 'forcolumns object\_id size auto',cascade=>true);

end;

确认是否收集了直方图：

selecttable\_name,column\_name,histogram from dba\_tab\_col\_statistics

wheretable\_name='TB\_TEST' and column\_name='OBJECT\_ID';

![0?wx\_fmt=png][0_wx_fmt_png 4]

select \* fromdba\_tab\_histograms

wheretable\_name='TB\_TEST' and column\_name='OBJECT\_ID';

![0?wx\_fmt=png][0_wx_fmt_png 5]

从上图可以看出字段OBJECT\_ID有收集直方图。

**重新执行SQL：**

返回记录比较少的值：

select \* from scott.TB\_TEST where object\_id=1;

返回记录比较多的值：

select \* fromscott.TB\_TEST where object\_id=10;

查看SQL信息：

selectsql\_text,sql\_id,plan\_hash\_value,address,hash\_value from v$sql

where sql\_text like'select \* from scott.TB\_TEST where object\_id=%';

![0?wx\_fmt=png][0_wx_fmt_png 6]

从上面可以看出，两条SQL依然使用之前相同的执行计划，执行计划并没有根据数据分布发生改变。

这是因为我们在收集统计信息时，未指定参数no\_invalidate => false，原本这两条SQL的CURSOR未失效，没有进行重新解析。

我们通过以下存储过程将这两个CURSOR清除，这样再执行就会重新解析了。

BEGIN

DBMS\_SHARED\_POOL.PURGE('00000000B34598F8,2412658958', 'C');

DBMS\_SHARED\_POOL.PURGE('00000000B37AEDE8,3292218149', 'C');

END;

确认是否已清除：

selectsql\_text,sql\_id,plan\_hash\_value,address,hash\_value from v$sql

where sql\_text like 'select \*from scott.TB\_TEST where object\_id=%';

![0?wx\_fmt=png][0_wx_fmt_png 7]

从上面可以看出查询无结果，说明已经清除。

**让已存在的CURSOR失效的方法：**

1、在收集统计时，加no\_invalidate => false参数：

begin

dbms\_stats.gather\_table\_stats('scott','TB\_TEST', method\_opt => 'forcolumns object\_id size auto',cascade=>true,no\_invalidate =>false );

end;

2、整个刷新share pool

alter system flushshared\_pool;

3、对这个表做ddl操作或授权都可以。

但以上说的这些方法，因为影响范围的不同，风险也不同。

相对来讲，DBMS\_SHARED\_POOL.PURGE影响是最小的，只对指定的CURSOR做清除。由于我是在个人的测试环境上演示，后面我为了方便操作，直接alter system flush shared\_pool。

**上面通过DBMS\_SHARED\_POOL.PURGE将两个CURSOR清除后，再次执行SQL:**

返回记录比较少的值：

select \* fromscott.TB\_TEST where object\_id=1;

返回记录比较多的值：

select \* fromscott.TB\_TEST where object\_id=10;

查看SQL信息：

selectsql\_text,sql\_id,plan\_hash\_value from v$sql

where sql\_text like'select \* from scott.TB\_TEST where object\_id=%';

![0?wx\_fmt=png][0_wx_fmt_png 8]

从上图的PLAN\_HASH\_VALUE可以看出，两条SQL使用了不同的执行计划。

对于数据分布不均匀是否可使用非绑定变量来解决，主要注意两个方面，SQL执行的频率，数据分布不均匀字段上的NUM\_DISTINCT值的数量。注意这两个方面根本上都是为了防止使用非绑定变量引起的硬解析问题。

使用绑定变量

以下讨论的前提是已经对字段object\_id收集过直方图的情况。

执行下面两个pl/sql,两个绑定变量的数据分布不同：

返回记录比较少的值：

DECLARE

V\_SQL VARCHAR2(3000);

BEGIN

V\_SQL := 'select \* from scott.tb\_test whereobject\_id=:1';

EXECUTE IMMEDIATE V\_SQL

USING 1;

END;

返回记录比较多的值：

DECLARE

V\_SQLVARCHAR2(3000);

BEGIN

V\_SQL := 'select\* from scott.tb\_test where object\_id=:1';

EXECUTE IMMEDIATEV\_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\_test where object\_id=:1';

![0?wx\_fmt=png][0_wx_fmt_png 9]

从上面可以看出虽然字段OBJECT\_ID上有使用直方图，但因为使用了绑定变量，ORACLE只硬解析了一次。Oracle 9i就开始引入的BIND PEEK不能解决这个问题，因为BIND PEEK只是发生在第一次硬解析。

**解决方法：**

#### **方法1：通过在应用代码中判断** ####

为了避免非绑定变量的解析问题，并且可以在逻辑上将倾斜的值区分出来，则可以在应用代码中根据值的不同让其它走不同的执行计划。

伪代码：

if variable =10then

execute‘select /\*+full(TB\_TEST)\*/ \* from scott.TB\_TEST where object\_id=:1’using variable;

else

execute‘select /\*+index(TB\_TEST IDX\_TB\_TEST\_01)\*/\* from scott.TB\_TEST whereobject\_id=:1’using variable;

…

end if;

#### **方法2：通过HINT:bindaware** ####

上面刚才讲到Oracle 9i就开始引入的BIND PEEK不能解决这个问题，因为只会在第一次硬解析的时候去窥视绑定变量的值。*从ORACLE11G开始引入了ACS的特性，即AdaptiveCursor Sharing自适应游标，它可以共享监视候选查询的执行统计信息，并使相同的查询能够生成和使用不同的绑定值集合的不同执行计划。*例如，优化器可能会选择绑定值1的一个执行计划和绑定值10的一个执行计划。

自适应游标的主要依赖于bind\_sensitive游标的绑定敏感性和bind\_aware游标的绑定感知性。大概的作用就是在数据库第一次执行一条SQL语句时，做一次硬解析，优化器发现使用绑定变量并在过滤条件上有直方图，它将存储游标的执行统计信息。在下一次使用不同绑定值执行相同SQL进行软解析时，把执行统计信息和存储在游标中的执行统计信息进行比较，来决定是否产生新的执行计划。这些执行统计信息可以在V$SQL\_CS\_\*相关的视图查看。

> V$SQL\_CS\_HISTOGRAM：在执行历史直方图上显示执行统计的分布。
> 
> V$SQL\_CS\_SELECTIVITY：对带绑定变量的过滤条件显示存储在游标中的选择性区域或范围。
> 
> V$SQL\_CS\_STATISTICS：包含数据库收集的执行信息，用来确定是否应该使用BIND\_AWARE的游标共享。

另外在V$SQL中增加了IS\_BIND\_SENSITIVE和IS\_BIND\_AWARE列，来标识一个游标是否为绑定敏感和是否感知游标共享。

自适应游标的概念文档可参考：*Adaptive Cursor Sharing: Overview(Doc ID 740052.1)*。

默认自适应游标特性是开启的，默认参数为：

\--\_optim\_peek\_user\_binds=TRUE

\_optimizer\_adaptive\_cursor\_sharing=TRUE

\_optimizer\_extended\_cursor\_sharing=UDO

\_optimizer\_extended\_cursor\_sharing\_rel=SIMPLE

但根据我们的最佳实践是不建议开启的，防止大量SQL执行计划的可变性引起的不稳定和新特性带来的Bug，但我们可以针对指定的SQL语句使用。

下面演示通过SQL\_PATCH对SQL加BIND\_AWARE的HINT,解决数据倾斜的问题。

同样是上面测试的SQL，我们对SQL增加BIND\_AWARE的HINT：

DECLARE

V\_SQL CLOB;

begin

--取出原SQL的文本

SELECTSQL\_FULLTEXT INTO V\_SQL FROM V$SQL WHERE SQL\_ID = 'fgagrcttxvq2a' AND ROWNUM =1;

--增加HINT

sys.dbms\_sqldiag\_internal.i\_create\_patch(sql\_text  => V\_SQL,

hint\_text => 'BIND\_AWARE',

name      => 'sql\_fgagrcttxvq2a');

end;

执行成功后，可在dba\_sql\_patches视图中查看相关信息

select \* from dba\_sql\_patches

where name='sql\_fgagrcttxvq2a';

![0?wx\_fmt=png][0_wx_fmt_png 10]

顺便说一下，下面是dbms\_sqldiag\_internal.i\_create\_patch的存储过程，可以看出它其实也是调用了I\_CREATE\_SQL\_PROFILE这个过程，和使用SQL\_PROFILE在底层是一样的。只是当前的场景使用sql\_patch要比使用SQL\_PROFILE要方便。

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;

下面看一下使用BIND\_AWARE这个HINT后的效果：执行下面两个pl/sql,两个绑定变量的数据分布不同

返回记录比较少的值：

DECLARE

V\_SQLVARCHAR2(3000);

BEGIN

V\_SQL := 'select\* from scott.tb\_test where object\_id=:1';

EXECUTE IMMEDIATEV\_SQL

USING 1;

END;

返回记录比较多的值：

DECLARE

V\_SQLVARCHAR2(3000);

BEGIN

V\_SQL := 'select\* from scott.tb\_test where object\_id=:1';

EXECUTE IMMEDIATEV\_SQL

USING 10;

END;

查看SQL信息：

select sql\_id,plan\_hash\_value,a.sql\_text,is\_bind\_sensitive,is\_bind\_aware,is\_shareable,sql\_patchfrom v$sql a

where sql\_text like 'select \* from scott.tb\_test whereobject\_id=:1';

![0?wx\_fmt=png][0_wx_fmt_png 11]

从上面可以看出，ORACLE根据数据分布选择了不同的执行计划，并且都有使用到这个SQL\_PATCH。

查看ACS(adaptive cursor sharing)和bind peek相关参数：

select name,value

from (selectnam.ksppinm name,

val.KSPPSTVL value,

--nam.ksppdesc description,

val.ksppstdf isdefault

fromsys.x$ksppi nam, sys.x$ksppcv val

wherenam.inst\_id = val.inst\_id

andnam.indx = val.indx)

where name in('\_optimizer\_adaptive\_cursor\_sharing',

'\_optimizer\_extended\_cursor\_sharing\_rel',

'\_optimizer\_extended\_cursor\_sharing',

'\_optim\_peek\_user\_binds')

![0?wx\_fmt=png][0_wx_fmt_png 12]

从上面可以看出ACS是关闭的，说明这种方法在ACS关闭的情况下也是可以生效的。

上面的测试中，\_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'即可。

如果不再需要SQLPATCH，可通过dbms\_sqldiag.drop\_sql\_patch删除。

#### **方法3：通过SPM** ####

通过DBMS\_SPM.evolve\_sql\_plan\_baseline演化基线的方式。此方法不再演示，可参考文档：*How to Evolve a SQL Plan Baseline and Adjust the AcceptanceThreshold (Doc ID 1617790.1)。*

其它特殊情况

#### **单字段分布不均匀，多字段分布均匀** ####

举个简单的例子：

select \* from tb where a=:1 and b=:2

字段a和字段b都是数据分布不均匀的字段，但业务逻辑上，在同一行记录中，字段a或者字段b，会有一个是过滤性强的。之前用户分别在字段a和字段b上建了两个索引。这样在绑定变量的情况下，就会出现这条SQL一直选择其中一个索引做索引范围扫描，当遇到倾斜的值时就会出现性能问题。最后通过将字段a和字段b建复合索引解决了此问题，当创建复合索引后，字段a或字段b其中一个值是倾斜时不会影响索引扫描的性能。

####     ####

#### **Null分布问题** ####

举个简单的例子：

select \* from tb where a isnull;

表tb中大部分记录中字段a的值都为非空，经常要查询字段a为 空的记录。单独在字段a上建索引，由于此索引中不存null值，所以where条件a is null无法走索引。可通过建(a,1)的复合索引将字段a的NULL值也存进去，使a is null使用索引。

#### **！=分布问题** ####

举个简单的例子：

select \* from tb where a !=1;

表tb中大部分记录中字段a的值都为1，经常要查询字段a!=1的记录，字段a为not null。单独在字段a上建索引，通常这样的SQL是会走全表扫描，如果强制走索引会走index full scan效率也不高。对于这种情况，如果想提高此SQL的性能，当字段a中!=1的值种类固定且不多时，可以将where条件a！=1改写为a in (x,y,z) 的形式--X/Y/Z为！=1的值;当字段a中!=1的值种类不固定，可以建函数索引decode(a,1,null,'2')，并将where条件a!=1改写为decode(a,1,null,'2')='2'，使其走索引范围扫描，提高SQL性能

[0_wx_fmt_png]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnVic8ObH0RvKia0rVLS696jpian9IibO2xvULcdQNYyMs7xgSCF0Rz8bicARw/0?wx_fmt=png
[0_wx_fmt_png 1]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnVIibNRLhLOyYF6NOpMkfibREdbKdHlXCJJicqKB41kBGmEiciam8JyLFGe8A/0?wx_fmt=png
[0_wx_fmt_png 2]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnVwPUXqvU4coicfIuIYqnVQSdaERtAbaohrHI6iaL1uDydDlD2Qu0ST1Zw/0?wx_fmt=png
[0_wx_fmt_png 3]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnVibgJDksSIBGQXdtEwPP9FCNQ3TEkJKjeEBRvkZYIzRJn1cZE2Lg045Q/0?wx_fmt=png
[0_wx_fmt_png 4]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnVPibjDC3KHK80wia7Cr1mh0pRgd9R0Ymia4wCIt1L0U3FSQgKrAIAs0hvw/0?wx_fmt=png
[0_wx_fmt_png 5]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnVia0aKdRTFmJ75DP9NzXibfibS7QvZvXG2kaw9mvGxkE0H89uwHSE5zvOg/0?wx_fmt=png
[0_wx_fmt_png 6]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnVPCZ5I6Cpx0giaoYicS4Okv8YKOoXCUqhnQsoCZYlPtYzMESHxvYg1L3A/0?wx_fmt=png
[0_wx_fmt_png 7]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnVeDCgp3876XBRMlkvbfuDa4rbhkIoMib9lQ8WBL0ibOjv8IQQQD7icdqyA/0?wx_fmt=png
[0_wx_fmt_png 8]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSGjlZUmhNWelVSwRcooZnV2VHiaHNgVguJyytLp9OPib77kKtZlbnNsrUPQk7Nxichq06RCavZWic3rA/0?wx_fmt=png
[0_wx_fmt_png 9]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSVuBYyibXCfxQW0OZXaFibf50UQXJAsFAxOTuDtXyTS3ml2nps1o6D1Sz6eaECya0PqIBjgqbUVmDg/0?wx_fmt=png
[0_wx_fmt_png 10]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSVuBYyibXCfxQW0OZXaFibf5OsjjHGJkNbVGJATX8I1fHY9v0nICn6VpNSxW5lj6pHfUaVRsp2Ag3w/0?wx_fmt=png
[0_wx_fmt_png 11]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSVuBYyibXCfxQW0OZXaFibf5gwiaQQic3Dr7xd5nnBskvCJmxYmE9wrOQVSBPHddLttbaXFJuPSty6Cw/0?wx_fmt=png
[0_wx_fmt_png 12]: https://ss.csdn.net/p?http://mmbiz.qpic.cn/mmbiz_png/bURPjgFpGMSVuBYyibXCfxQW0OZXaFibf5RLmMibhACpWgkNaYLDrRO2pj5gqFsCEQFDQnHvF1IDicPgsicYe0w4h2Q/0?wx_fmt=png