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 := 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不同，表示生成了不同的执行计划
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;
