Oracle数据倾斜导致的问题-有绑定变量

参考整理---<<恩墨年货-SQL与性能优化>>

场景二：

在上一节实验可以知道，没有绑定变量时，数据倾斜问题在特定场景下可以用直方图解决，那么在有绑定变量情况下，数据倾斜问题单凭直方图可以解决吗？

显然是不能的，Oracle绑定变量技术解决了SQL语句硬解析过多的问题，降低了资源的争用。但是绑定变量在引入cursor sharing，增加了软解析的同时，由于SQL文本相同，经常生成相同的执行计划，在数据分布不均匀，数据倾斜严重时，有时会出现性能问题。

在oracle 9i版本，引入了绑定变量窥探Bind Peeking技术，在首次硬解析时，会去探测绑定变量的真实值，从而生成更准确的执行计划，但是从第二次软解析开始，一直会沿用之前的执行计划，而一个执行计划并不会适用所有的绑定值，在过滤列数据分布严重倾斜时，可能会生成低效的执行计划。

为了弥补绑定变量窥探Bind Peeking技术的缺陷，11g引入了自适应游标共享技术(Adaptive Cursor Sharing)，通过自适应游标共享，可以仅针对使用绑定变量的语句智能地共享游标。

一：绑定变量窥探Bind Peeking对执行计划的影响

二：自适应游标共享技术(Adaptive Cursor Sharing)

一：绑定变量窥探Bind Peeking对执行计划的影响

1 查看Bind Peeking和Adaptive Cursor Sharing参数

selectname,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)

wherenamein('_optimizer_adaptive_cursor_sharing',

'_optimizer_extended_cursor_sharing_rel',

'_optimizer_extended_cursor_sharing',

'_optim_peek_user_binds');

2 创建测试数据

SQL> select banner_full from v$version;

BANNER_FULL

--------------------------------------------------------------------------------

Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production

Version 19.3.0.0.0

SQL> show pdbs

CON_ID CON_NAME OPEN MODE RESTRICTED

---------- ------------------------------ ---------- ----------

2 PDB$SEED READ ONLY NO

3 CJCPDB READ WRITE NO

SQL> conn cjc/cjc@cjcpdb

Connected

新建测试表 t1：

SQL> create table t1 as select * from dba_objects;

创建索引：

SQL> create index idx_t1_01 on t1(object_id);

增加数据:

SQL> insert into t1 select * from t1;

/

SQL> update t1 set object_id=rownum;

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

SQL> update t1 set object_id=10 where object_id>10;

290010 rows updated.

SQL> commit;

Commit complete.

SQL> select object_id,count(1) from t1 group by object_id order by 1;

--下面收集字段 OBJECT_ID 的直方图：

SQL>

begin

dbms_stats.gather_table_stats('CJC',

'T1',

method_opt => 'for columns object_id size auto',

cascade => true);

end;

查看直方图信息：

select table_name,

column_name,

histogram,

num_distinct,

density,

last_analyzed

from user_tab_col_statistics

where table_name = 'T1'

and column_name = 'OBJECT_ID';

select*

fromuser_tab_histograms

wheretable_name ='T1'

andcolumn_name ='OBJECT_ID'

orderby5;

3 绑定变量窥探对执行计划的影响

硬解析时绑定变量窥探特性可以根据绑定变量真实值生成高效的执行计划。

SQL> alter system flush shared_pool;

System altered.

SQL> set autotrace traceonly

SQL> set linesize 200

SQL> set timing on

SQL> variable xxx varchar2(100)

SQL> execute :xxx := 10;

SQL> select * from t1 where object_id=:xxx;

SQL>

select sql_id,

child_number,

executions,

loads,

buffer_gets,

is_bind_sensitive as "bind_sensi",

is_bind_aware as "bind_aware",

is_shareable as "bind_share"

from v$sql

where sql_text like 'select * from t1 where object_id%';

select * from table(dbms_xplan.display_cursor('2gr2tazfbjvsa',format => 'advanced'));

第二次执行软解析，绑定变量值换成了1，结果集只有1条，但是沿用了之前的执行计划，走全表扫描，显然是不合理的。

SQL> execute :xxx := 1;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL> select * from t1 where object_id=:xxx;

Elapsed: 00:00:00.05

select sql_id,

child_number,

executions,

loads,

buffer_gets,

is_bind_sensitive as "bind_sensi",

is_bind_aware as "bind_aware",

is_shareable as "bind_share"

from v$sql

where sql_text like 'select * from t1 where object_id%';

二：自适应游标共享技术(Adaptive Cursor Sharing)

在多次执行绑定变量等于1的语句。

SQL> execute :xxx := 1;

PL/SQL procedure successfully completed.

Elapsed: 00:00:00.01

SQL> select * from t1 where object_id=:xxx;

SQL> execute :xxx := 1;

SQL> select * from t1 where object_id=:xxx;

在多次执行绑定变量等于10的语句。

SQL> execute :xxx := 10;

SQL> select * from t1 where object_id=:xxx;

.......

游标自适应生效了

Sql_id相同，但是plan_hash_value不同，表示生成了不同的执行计划

selectsql_id,

plan_hash_value,

child_number,

executions,

loads,

buffer_gets,

is_bind_sensitive as"bind_sensi",

is_bind_aware as"bind_aware",

is_shareable as"bind_share"

fromv$sql

wheresql_text like'select * from t1 where object_id%';

查看生成的执行计划

SELECTSQL_ID,

PLAN_HASH_VALUE,

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

OBJECT_NAME,

CARDINALITY,

BYTES,

COST,

TIME

FROMV$SQL_PLAN

whereSQL_ID ='2gr2tazfbjvsa';

select * from v$sql_cs_histogram where sql_id='2gr2tazfbjvsa';

注意：

游标自适应有时会导致大量SQL执行计划不稳定，在11.2.0.1版本，绑定变量窥探特性可能会导致ORA-03137:TTC protocol internal error:[12333]问题，有时我们会根据情况选择关闭这些特性。

selectname,value,description

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)

wherenamein('_optimizer_adaptive_cursor_sharing',

'_optimizer_extended_cursor_sharing_rel',

'_optimizer_extended_cursor_sharing',

'_optim_peek_user_binds');

--均为动态参数

--bind peeking（绑定变量窥探

---altersystemset"_optim_peek_user_binds"=false;

--acs(adaptive cursor sharing)

altersystemset"_optimizer_extended_cursor_sharing_rel"=NONE;

altersystemset"_optimizer_extended_cursor_sharing"=NONE;

altersystemset"_optimizer_adaptive_cursor_sharing"=false;

数据库级别游标自适应关闭后，可以手动开启语句级别游标自适应，方法如下：

---19C测试失败了，还没找到具体原因。

# 12.2之前版本

DECLARE

V_SQL CLOB;

begin

--取出原SQL的文本

SELECT SQL_FULLTEXT

INTO V_SQL

FROM V$SQL

WHERE SQL_ID = '2gr2tazfbjvsa'

AND ROWNUM = 1;

--增加HINT

sys.dbms_sqldiag_internal.i_create_patch(sql_text => V_SQL,

hint_text => 'BIND_AWARE',

name => 'sql_2gr2tazfbjvsa');

end;

# 12.2及以后版本

#创建sql patch

declare

patch_name varchar2(30);

begin

patch_name := dbms_sqldiag.create_sql_patch(sql_id => '2gr2tazfbjvsa',

hint_text => 'select * from t1 where object_id=:xxx');

end;

/

SQL>

select name,

to_char(created, 'yyyy-mm-dd hh34:mi:ss') as created,

status,

force_matching,

description,

substr(sql_text, 1, 50) as sql_text

from dba_sql_patches

order by created;

欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！