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
_ID is 'PK_T
1
_
OBJECT_ID
';
comment on column C
JC
.
T1
.
OBJECT
_ID is '';
二：数据库 cursor_sharing
参数的值是否为
exact
，如果参数的值为
force
，相当于使用绑定变量，收集直方图后，执行计划可能没有变化，解决办法请参考下一节
Oracle
数据倾斜导致的问题
-
有绑定变量
欢迎关注我的微信公众号"IT小Chen"，共同学习，共同成长！！！
