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 hh24: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"，共同学习，共同成长！！！
