90-最近优化过的几套Oracle数据库回顾

最近给几个比较重要的客户优化了几套Oracle数据库, 套用一句名言: 性能好的数据库都是相似的, 性能差的数据库各有各的"不幸". 实际上性能好的数据库基本看不到,除非是一些负载非常小的库. 绝大部分数据库都是处于亚健康状态, 很多拿来做优化的库, 要么是濒临瘫痪,要么是业务用户实在是忍无可忍.

对优化不了解的用户, 遇到性能问题很多时候最先想到的处理方法不是优化, 而是更换硬件, 更有甚者干脆再开发一套新的应用.

今天介绍的这几个案例, 就是让大家多了解优化, 每一套"不幸"的数据库, 经过优化之后, 基本上都能够得到新生.

客户1:

数据库升级到12.2版本后, 共享内存(shared pool)一段时间后就增大到了100多G, 而buffer cache被挤压到只剩500多M, 导致SQL执行效率非常低. 客户之前找过专家改过几个隐含参数(如_cursor_obsolete_threshold / _memory_imm_mode_without_autosga等), 还是没有解决:

经过分析AWR发现, 这是一个ges resource dynamic相关的bug, 根据MOS提供的workaround做了调整, 这一步做到了治标. 其实oracle的bug一般不会轻易踩到, 这个bug就是因为应用的一些SQL没有使用绑定变量;还有一些insert values过多的绑定变量, 而绑定变量的长度变化较大, 生成了大量的子游标; 再有就是insert 后面的select 语句是由上千个union 组成, 这些都是诱发bug的原因, 这些问题都需要开发修改代码, 才能从根本上解决问题. 如果把这些问题都解决了, 这个bug应该也就不会出现.

几乎每个数据库都存在一些索引和SQL写法的问题,这个库也不例外,主要问题通过参数调整先临时解决了. 然后把TOP SQL涉及的表增加一些索引, 同时还发现一些SQL的写法需要改进. 索引和SQL写法在两大类性能问题几乎在所有的数据库都存在, 索引可以补建, SQL写法的问题只能通过改代码解决,oracle的优化器虽然很强大, 但是也不能随心所欲, 很多开发写的SQL实现业务逻辑没问题, 性能就惨不忍睹了.

加了索引之后, 这个库的性能又得到了较大的提升. 治本的SQL写法问题还需要开发对代码做调整.

客户2:

这是一个数据仓库, 里面有很多几百G大表, 而且还在持续不断增长. 每天大量的全表扫描(一天的读取量大概是70T), 还好有oracle的一体机Exadata强大的硬件做支撑. 如果不是Oracle原厂的一体机Exadata的硬件, 很难支撑这样的业务系统. Exadata强大的pmem(硬件-持久化内存)和offload(软件), 可以让100多G大表的全表扫描, 只需要1秒左右, 真香.

即便是强大的硬件做支撑, 遇到统计信息收集不准或是参数设置不当的情况, 也是7~8个小时也跑不出结果. 还有在大表中做delete的时候, 即使是一个不删除任何记录的SQL, 也需要执行几十分钟. 而且随着数据量的不断增长, 每天的业务加载时间的持续增长也在意料之中.

这个库的优化手段说起来比较简单, 就是对大表做分区 , 找出合适的字段, 使用合理的分区类型, 不但能实现几百倍的性能提升, 而且不会随着数据量的增长而增加业务加载时间. 分区是数据仓库最有效的的优化方法, 没有之一. 除了分区裁剪可以大幅减少IO读取, 分区还是compress/in-memory等技术的"最佳搭档". 在很多开源数据库, 经常听到一个名词叫"分表", 这是因为分区技术不太成熟; 但是在oracle数据库, 很少听说需要用到"分表", 因为分区就是最好的"分表". 之前遇到某移动客户的账务系统做了分表, 那SQL写起来是相对的尬,如果架构师能多了解一些分区就好了.

同样, 加索引和调整SQL写法也是必不可少的, 这是每个数据库优化都躲不过的, 有时候即使做了分区, SQL写法配合不上也是白扯.

客户3:

这是一个ETL加报表输出库, 很多SQL执行时间在1~2小时以上,还有一些执行7~8个小时以上最后报ora-01555错误,相当于消耗了大量的系统资源, 最后得到了一个寂寞.

这个数据库最大的问题是修改了一个重要的优化器参数optimizer_mode, 将这个参数恢复到默认值后, 再加上其他的一些优化手段, 原来执行几个小时的SQL, 执行时间都降到了1分钟以下. 有的数据库参数可以根据实际情况做一些调整, 但是像optimizer_mode这种参数, 千万不要动, 这是在做"劣化", 而不是做优化.

介绍完3个优化案例, 再谈谈优化相关的一些理论, 索引是SQL优化最基本的技术, 也是OLTP系统最主要的优化手段, 很多人都觉得简单, 但就是这项技术, 我花了整整两天的时间给学员做培训.

下面是我看到的几个关于索引的优化案例, 看完之后感觉优化人员对索引的理解可能存在一些偏差, 举两个例子:

1. 下面3个sql, 创建索引应该能够带来很大的性能提升, 但是把常量1加到索引做联合索引, 是完全没有必要的, 这个做法暴露了对索引理解的不足:

SELECT ... FROM xxx WHERE col1 = :B1 OR col2 = :B1 ;

create index .. on xxx(col1,1);

create index .. on xxx(col2,1);

select ... from xxx WHERE col1 || col2= :B1;

create index .. on xxx(col1||col2,1);

SELECT ... FROM xxx WHERE col1= TRIM(:B1 );

create index .. on xxx(col1,1);

把常量加到联合索引, 某些特殊情况确实需要, 但都不是上面这些情况.

2. 下面这个SQL, 给出的优化建议存在多个问题:

select ... from xxx

where c1=:b1 and c2=:b2 and c3=:b3 and c4=:b4 and c5=:b5;

当前已经存在(c1,c2,c3,c4,c5) 5 字段联合索引 , 5个字段的NDV(number of distinct value) 分别是 97, 1, 1, 264, 572519 (表上记录数 650万)

优化人员给出的建议是新建(c5,c4,c1,c2,c3) 5字段联合索引.

这个新建索引对当前SQL起到的优化作用可以说是微乎其微.因为都是等值条件, 联合索引字段先后顺序基本上没有影响. 并不是说NDV大的字段就一定要放到联合索引的最前面, 没有这样的理论.

上面的建议涉及到几个索引的基本理论:

不需要把where 后面所有的条件都加到联合索引(肥大的索引有很多弊端, 这里不一一列举), 这个索引只用c4,c5 两个字段联合基本上就足够了.

如果是等值条件, 联合索引字段先后顺序差别很小,基本可以忽略不计(c5,c4也是一样的)

把NDV=1的c2和c3加到联合索引, 意义不大; ndv=1 字段加入到索引,只在某些特殊情况才有意义, 比如大部分是null值/查找不存在的值/加了rownum<=xx等.

结语:

很多人认为数据库优化很简单, 几乎每个DBA的简历上都写着精通数据库优化. 而实际情况却是大部分DBA遇到数据库性能问题时, 还是希望能通过修改数据库的初始化参数得到解决, 最多也只是加几个简单索引, 固定一些SQL的执行计划而已. 很少从业务和开发的角度提出解决性能问题的根本原因.

而开发人员对越来越慢的系统, 有的会抱怨DBA维护水平差, 有的会认为硬件资源不足, 较少从架构设计/SQL写法等自身原因分析问题, 而这部分恰恰是导致性能恶化问题的主要根源.

当DBA和开发把知道的优化方法都使出来之后, 数据库性能还是不能得到明显的改善, 就只能寄希望于高级硬件上了, 高级硬件能解决一大部分性能问题, 但是无法解决诸如SQL写法低效/少索引/统计信息过旧/参数设置不当/优化器缺陷等其他诸多问题.