oracle sql 调优

  oracle数据库sql调优学习记录笔记。

核心思想

只有大表才会产生性能

基本概念

基数

  某个列唯一键的数量叫做基数。主键列的基数等于行数。性别列的基数等于2。

  基数的高低影响列的数据分布。

  当查询结果返回表中5%以内的数据时,应该走索引;当查询结果返回表中超过5%的数据时,应该走全表扫描。

  • 查询性别列的基数
1
select count(distinct t.sex) from user t

选择性

  基数/表的总行数*100%。

  什么样的列必须创建索引?当一个列出现在 where 条件中,该列没有创建索引并且选择性大于20%,那么该列就必须创建索引。

1
select count(distinct t.sex) / count(*) from user t

直方图

  当某个列的基数很低,该列的数据分布就会不均衡。数据分布不均衡,就会导致在查询该列时要么走索引,要么走全表扫描,这时候很容易走错执行计划。

  如果没有对基数低的列收集直方图统计信息,基于成本的优化器(CBO)会认为该列的数据分布是均衡的。

  直方图是用来帮助CBO在对基数很低、数据分布不均衡的列进行Rows估算的时候,可以得到更精确的Rows。

  什么样的列应该收集直方图?该列出现在where条件中,列的选择性小于1%并且该列没有收集过直方图。

回表

  当对一个列创建索引之后,索引会包含该列的键值以及键值对应行所在的rowid。

  通过索引中记录的rowid访问表中的数据就叫回表。回表一般是单块读(一个rowid对应一个数据块),回表次数太多会严重影响SQL性能。如果回表次数太多,就不应该走索引,应该直接走全表扫描。

  arraysize参数:表示Oracle服务器每次传输多少行数据到客户端。假设arraysize=15,如果一个数据块有150行数据,那么每次传输15行,需要读取这个数据块10次才能读完,此时逻辑读被放大了。为了消除arraysize对逻辑读的影响,可以将参数调大。

全自动优化脚本

抓住必须创建索引的列

  • 刷行数据库监控信息
1
2
3
begin
dbms_stats.flush_database_monitoring_info;
end;
  • 查询指定用户指定表出现在where条件中的列信息
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
select r.name as owner,
o.name as table_name,
c.name as column_name,
u.equality_preds, -- 等值过滤
u.equijoin_preds, -- 等值join,ex: where a.id=b.id
u.nonequijoin_preds, -- 不等于join
u.range_preds, -- 返回过滤次数
u.like_preds, -- like过滤
u.null_preds, -- null过滤
u.timestamp
from sys.col_usage$ u,
sys.obj$ o,
sys.col$ c,
sys.user$ r
where u.obj# = o.obj#
and u.obj# = c.obj#
and u.intcol# = c.col#
and r.name = 'RETAIL'
and o.name = 'CON_CONTRACT'
  • 查询指定用户指定表的索引信息
1
2
3
4
select c.table_owner, c.table_name, c.column_name, c.index_name
from dba_ind_columns c
where c.table_owner = 'RETAIL'
and c.table_name = 'CON_CONTRACT';
  • 指定用户指定表的选择性大于20%的列
1
2
3
4
5
6
7
8
select a.owner, a.table_name, a.column_name, round(a.num_distinct / b.num_rows * 100, 2) selectivity
from dba_tab_col_statistics a,
dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'RETAIL'
and a.table_name = 'CON_CONTRACT'
and a.num_distinct / b.num_rows >= 0.2;
  • 最终的脚本
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
select t1.owner, t1.table_name, t1.column_name, t1.num_rows, t1.Cardinality, t1.selectivity, 'Need index' as notice
from (select b.owner,
a.table_name,
a.column_name,
b.num_rows,
a.num_distinct Cardinality,
round(a.num_distinct / b.num_rows * 100, 2) selectivity
from dba_tab_col_statistics a,
dba_tables b
where a.owner = b.owner
and a.table_name = b.table_name
and a.owner = 'RETAIL'
and a.table_name = 'CON_CONTRACT') t1
where t1.selectivity >= 20
and not exists(select 1
from dba_ind_columns t2
where t2.table_owner = t1.owner
and t2.table_name = t1.table_name
and t2.column_name = t1.column_name)
and exists(select 1
from sys.col_usage$ u,
sys.obj$ o,
sys.col$ c,
sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = t1.owner
and o.name = t1.table_name
and c.name = t1.column_name);

抓住必须创建直方图的列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
select a.owner,
a.table_name,
a.column_name,
b.num_rows,
a.num_distinct,
trunc(a.num_distinct / b.num_rows * 100, 2) selectivity,
'Need Gather Histogram' notice
from dba_tab_col_statistics a,
dba_tables b
where a.owner = 'RETAIL'
and a.table_name = 'CON_CONTRACT'
and a.owner = b.owner
and a.table_name = b.table_name
and a.num_distinct / b.num_rows < 0.01
and (a.owner, a.table_name, a.column_name) in
(select r.name owner, o.name table_name, c.name column_name
from sys.col_usage$ u,
sys.obj$ o,
sys.col$ c,
sys.user$ r
where o.obj# = u.obj#
and c.obj# = u.obj#
and c.col# = u.intcol#
and r.name = a.owner
and o.name = a.table_name)
and a.histogram = 'NONE';
-------------完-------------
大爷常来玩啊。。