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

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 参数

select name , value

from ( select nam.ksppinm name ,

val.KSPPSTVL value ,

--nam.ksppdesc description,

val.ksppstdf isdefault

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

where nam.inst_id = val.inst_id

and nam.indx = val.indx )

where name in ( '_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 *

from user_tab_histograms

where table_name = 'T1'

and column_name = 'OBJECT_ID'

order by 5 ;

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 := 1 0 ;

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 := 1 0 ;

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

.......

游标自适应生效了

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

select sql_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"

from v$sql

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

查看生成的执行计划

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 = '2gr2tazfbjvsa' ;

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

注意：

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

select name , value , description

from ( select nam.ksppinm name ,

val.KSPPSTVL value ,

nam.ksppdesc description ,

val.ksppstdf isdefault

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

where nam.inst_id = val.inst_id

and nam.indx = val.indx )

where name in ( '_optimizer_adaptive_cursor_sharing' ,

'_optimizer_extended_cursor_sharing_rel' ,

'_optimizer_extended_cursor_sharing' ,

'_optim_peek_user_binds' );

--均为动态参数

--bind peeking（绑定变量窥探

--- alter system set "_optim_peek_user_binds" = false ;

--acs(adaptive cursor sharing)

alter system set "_optimizer_extended_cursor_sharing_rel" = NONE ;

alter system set "_optimizer_extended_cursor_sharing" = NONE ;

alter system set "_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"，共同学习，共同成长！！！