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

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

数据倾斜即表中某个字段值不均匀,那么什么叫字段值不均匀呢?
如下t1 表的 object_id 字段值就是严重的字段值不均匀, t1 表有 290020 条数据,其中 object_id 值 1 到 9 每个值只有一条记录, object_id=10 的值有 290011 条数据。
SQL> select object_id,count(1) from t1 group by object_id order by 1;

在这种情况下,当以object_id 字段为过滤条件时,在某些场景下可能会出现性能问题。
场景一:未使用绑定变量
1 创建测试数据

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;


当数据分布不均匀的字段做为过滤条件或连接条件时,如果据分布不均匀的字段没有收集直方图可能会有问题,在没有收集直方图的情况下,这个字段的过滤性 DENSITY 都是等于 1/NUM_DISTINCT;
2 对测试表 t1 进行统计信息收集
收集时指定不收集字段object_id 的直方图:

begin
dbms_stats.gather_table_stats ( 'CJC' ,
'T1' ,
method_opt => 'for columns object_id size 1' ,
cascade => true );
end ;

3 查看 T1 表上 Object_id 列没有收集直方图信息

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' ;


4 以 object_id 列为过滤条件,对比结果集相差悬殊的两次查询操作的执行计划
(1) 查看结果集少的执行计划
object_id=1 时结果集只有1 条数据

SQL> set autotrace traceonly
SQL> set linesize 200
SQL> set timing on
SQL> select * from t1 where object_id=1;


(2) 查看结果集多的执行计划
object_id=1 0 时结果集有 290011 条数据
SQL> select * from t1 where object_id=10;
290011 rows selected.

从上图可以看出,两条 SQL 的 PLAN_HASH_VALUE 是一样的,走了相同的执行计划。

select sql_text, sql_id, plan_hash_value
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 PLAN_HASH_VALUE = '964845277' ;


显然在 object_id=1 0 时,结果集有 290011 条数据,占比总表99.99% 的数据量,是不适合走索引范围扫描,全表扫描会更高效些。
5 收集 OBJECT_ID 列直方图信息
在Oracle 中直方图是一种对数据分布质量情况进行描述的工具。
它会按照某一列不同值出现数量多少,以及出现的频率高低来绘制数据的分布情况,以便能够指导优化器根据数据的分布做出正确的选择。正确的直方图,将会对优化器做出正确的选择发挥巨大的作用,使得SQL 语句执行成本最低,从而提升性能。
-- 下面收集字段 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';


6 重新执行 SQL ,查看执行计划
(1) 结果集少的执行计划
SQL> select * from t1 where object_id=1;

(2) 结果集多的执行计划
SQL> select * from t1 where object_id=10;

查看结果集多的SQL 执行计划已经发生了变化,执行了更高效的全表扫描。

select sql_text, sql_id, plan_hash_value, address, hash_value
from v$sql
where sql_text like 'select * from t1 where object_id%';


注意:
有几种情况,在收集直方图后,执行计划不会马上变化
一: SQL CURSOR 没有失效,不会重新生成执行计划,可以通过如下几种方法让 SQL CURSOR 失效。
(1) 在收集统计信息时,指定参数 no_invalidate => false , 使这两条 SQL 的 CURSOR 失效,进行重新解析。
我们通过以下存储过程将这两个 CURSOR 清除,这样再执行就会重新解析了。
--填写ADDRESS和HASH_VALUE值

BEGIN
DBMS_SHARED_POOL.PURGE('000000006EBF2F78,589030732', 'C');
DBMS_SHARED_POOL.PURGE('000000006F2B3660,2332556305', 'C');
END;

(2) 在收集统计时,加 no_invalidate => false 参数

begin
dbms_stats.gather_table_stats('CJC',
'T1',
method_opt => 'for columns object_id size 1',
cascade => true,
no_invalidate => false );
end;

(3) 刷新整个 share pool( 生产环境谨慎使用 )
alter system flush shared_pool;
(4) 对这个表做 ddl 操作或授权或添加改变注释等。
例如:

comment on column C JC . T1 . OBJECT _IDis 'PK_T 1 _ OBJECT_ID ';
comment on column C JC . T1 . OBJECT _IDis '';

二:数据库 cursor_sharing 参数的值是否为 exact ,如果参数的值为 force ,相当于使用绑定变量,收集直方图后,执行计划可能没有变化,解决办法请参考下一节 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 hh24:mi:ss') as created,
status,
force_matching,
description,
substr(sql_text, 1, 50) as sql_text
from dba_sql_patches
order by created;

最后修改:2022 年 02 月 28 日
如果觉得我的文章对你有用,请随意赞赏