性能优化案例

发表人:dbaoracle

最近这段时间刚做了一项性能优化的工作，根据一周的statspack报告，写了一个优化方案。

一、系统现有的主要性能问题

从最近一周比较典型的STATSPACK报告来看，系统中主要的等待事件如下：

Top 5 Timed Events

~~~~~~~~~~~~~~

Event Waits Time (s) % Total Ela Time

-------------------------------------------- ------------ ----------- ----

CPU time 5,819 46.04

wait for unread message on broadcast channel 3,356 3,374 26.70

db file sequential read 1,100,056 2,454 19.41

51,054 274 2.16

db file scattered read 154,540 220 1.74

-------------------------------------------------------------

数据库中有46％的时间花费在CPU相关的操作上。CPU time可以分为3个部分，即CPU time= parse time CPU + recursive CPU usage +other CPU usage，其中，parse time CPU表示解析SQL语句所花费的CPU时间，recursive CPU usage表示一些递归SQL操作花费的CPU时间，other CPU usage代表逻辑读取花费的CPU时间。

变换一下上述公式，other CPU usage＝CPU time － parse time CPU － recursive CPU usage，将我们的STATSPACK报告的相关数据带入该公式other CPU usage＝5819－584－109＝5126，由此可见，88％的CPU时间花费在了逻辑读取数据的操作上，另外有10％的CPU时间花费在了解析SQL语句上。我们可以从优化SQL语句方面入手，降低逻辑读取。另外，我们也可以在降低SQL解析数方面做些工作。

等待事件wait for unread message on broadcast channel是一个空闲事件，我们无须处理。

db file sequential read等待事件占总时间的19％，表示物理读取数据，减少该等待可以从两个方面来考虑，首先是优化SQL，降低不必要的物理读取；另外也可以适当的增大数据缓冲区，使尽可能多的数据缓存到内存中。

另外一个等待事件buffer busy waits是由大量逻辑读取所导致的内存等待，该事件也可以通过优化SQL语句来消除。

二、解决方案

根据系统中存在的上述问题，提出如下的优化方案：

1） 优化一些逻辑读和物理读较多的SQL，可以有效减少数据的读取，也可以减少内存争用；

2） 对一些执行频繁的SQL使用绑定变量，减少花费在解析SQL语句上的CPU时间和内存栓锁；

3） 适当增大数据缓冲区尺寸，减少物理读。