一次ORACLE IO性能诊断案例

一 Oracle 诊断案例 -I/O 子系统

1.1 系统软硬件现状

Oracle 数据库运行环境

Unix 主机：               2 * IBM M85

CPU ：        4 个

系统内存：         6GB

磁盘阵列子系统：    IBM SSA RAID-5

Oracle 版本：      8.1.7.1.0 32-bit

OS 版本：     AIX 4.3.3-10

业务系统： ×××××业务管理系统

1.2 Vmstat 数据

kthr     memory             page              faults        cpu    

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

 r  b   avm   fre  re  pi  po  fr   sr  cy  in   sy  cs us sy id wa

 2  4 897649  6717   0   7  23 437  957   0 1011 1184 164 13  7 60 19

12  9 893274  9676   0  30   0   0    0   0 1787 70898 20625 41 23  2 34

 6 15 881613 18578   0 147   0   0    0   0 2035 62910 14427 37 31  1 31

 1 10 880631 17850   0  13   0   0    0   0 1940 38678 10031 27 17  4 51

16 10 886848  9508   0  36   0   0    0   0 2226 48349 7778 26 16  4 54

 2 14 892766   541   0  20   0   0    0   0 2331 88696 14576 43 18  1 38

15 19 895124    34   0  15 382 2007 4143   0 1878 59445 10411 26 16  2 56

 1 12 883561 12427   0  28 270 1288 2735   0 1910 45451 6520 20 14  1 65

22 11 882698 11794   0  29   0   0    0   0 1856 51539 5399 21 25  4 50

 9 19 879662 12982   0  17   0   0    0   0 1914 39960 7045 20 16  3 61

 1 12 878367 12601   0  33   0   0    0   0 2016 30913 5410 18 10  4 68

 2 14 881742  7084   0  51   0   0    0   0 2256 35721 7131 22  7  5 65

 2 11 888809     0   0  25 149 975 1981   0 1893 46405 6496 22 14  6 58

 1 11 877357  9894   0  27 212 631 1314   0 1636 28126 4256 16 26  2 55

 3  9 880033  5315   0  25   0   0    0   0 1714 44692 5093 20 24  3 53

 7 11 879638  3862   0  48   0   0    0   0 2044 62118 8235 27 28  3 42

 1  9 871947  9857   0  28   0   0    0   0 1954 49887 11556 30 22  5 44

 8 12 872208  7408   0  40   0   0    0   0 2132 38014 6991 22 14  4 59

 1 10 879075  3815   0  14 396 2269 4650   0 1775 38587 5830 16 14  6 63

 3 14 884434     4   0  24 235 1632 3575   0 1963 51640 7198 22 15  4 60

。。。 。。。

1.3 Statpack 数据

Top 5 Wait Events

~~~~~~~~~~~~~~~~~                                             Wait     % Total

Event                                               Waits  Time (cs)   Wt Time

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

log file sync                                      95,588    1,675,398   58.21

db file sequential read                           402,294      917,696   31.88

db file parallel write                              7,780      114,321    3.97

db file scattered read                              8,560       70,945    2.46

buffer busy waits                                  12,303       40,058    1.39

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

（详细信息参见《 2.16 如何理解 STATPACK 的报告》 .doc ）

1.4 业务分析

1. 主要业务发生在每天 8 点到 22 点之间

2. 每天的事务量是 60G 左右，要求较高的数据的完整性

3. 每天有较多的 DML （主要是 INSERT 和 UPDATE 操作，几乎没有 DELETE 操作和大量的查询操作。 DML 操作主要是用来更新在 B/S 结构和 C/S 结构中的业务信息， select 操作主要是提供大量的检索功能，业务的操作模式是每个 DML 之后就会 commit ；

4. 目前的备份方式是冷备，目前的归档方式是归档到一个本地磁盘和 RAID 上各一份

5. 目前的 Web 服务，数据库服务， C/S 结构的服务器都跑在 RAID5 上

1.5 数据库性能分析

问题 1 ：

由于系统存在大量的 transaction, 造成了 redo log 对磁盘的巨大 io 压力，造成了系统大量的 IO 等待。所以，当前数据库的主要性能瓶颈是磁盘子系统的性能问题，系统的 IO Wait 严重偏高，尤其是 log file sync 事件， 20 分钟内发生 95,588 次，平均每秒发生 80 个左右，占系统所有等待时间的将近 60% 。由于 log file sync 的产生是由于应用程序过度的 Commit 造成的，所以最好的方法是修改应用程序，采用 Batch Commit 技术，来提高系统的性能。如果由于第三方软件的原因，导致应用程序无法进行修改，我们也可以提出一个变通的方案，即优化用户磁盘子系统，提高用户磁盘的性能，以达到减少系统的 IO 资源等待，提高系统性能的目标。

问题 2 ：

还有一个系统的主要问题所在是 Oracle 的配置问题。当前系统有 6GB 的物理内存，但是 Oracle SGA 只是开了 600M 。这是对资源的巨大浪费。如果这个服务器只是运行 Oracle      Database ， 那么 Oracle SGA 可以调节到 3.5-4GB 。 ( 当前系统使用的是 32Bit Oracle ，建议升级到 64Bit Oracle) 。

问题 3 ：

1)         另外的几个小问题是系统地 rollback segment 数目偏少。

2)         系统地 log_buffer 偏大。

3)         系统把 redo log file 作了两个 member, 是高可用性的一个比较好的策略，不过当前的配置是在同一个磁盘上面，似乎意义不大。 Log file switch 过于频繁， logfile 大小偏小。  

1.6 修改建议

1)         如果系统没有配置 hacmp 作为 HA 可用性解决方案，建议把 redolog 从 hdisk2,hdisk3 所在的磁盘移动到 hdisk0,hdisk1 上面（采用 raw device ）。这样，可以大大降低系统地 IO 压力，同时保证 redo log 的安全性。

2)         当前磁盘子系统，主要 IO 集中在 hdisk2, hdisk3 这两个逻辑磁盘上面。为了降低系统地 IO 压力， 建议把一部分 datafile 移到 hdisk4 磁盘上。

3)         如果有可能调节引用程序，建议使用 batch commit 技术，把下面的语句修改一下：同时注意，  

  Buffer Gets    Executions  Gets per Exec  % Total  Hash Value

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

      2,440,675    1,218,841            2.0    18.1   1115576732

update nlc50.z07 set z07_rec_key = :r1:i1,z07_sequence = :r2:i2,

z07_level = :r3:i3,z07_source = :r4:i4,z07_history = :r5:i5 wher

e z07_rec_key = :v1

同时注意：

Executions   Rows Processed    Rows per Exec   Hash Value

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

   1,218,841              355              0.0   1115576732

update nlc50.z07 set z07_rec_key = :r1:i1,z07_sequence = :r2:i2,

z07_level = :r3:i3,z07_source = :r4:i4,z07_history = :r5:i5 wher

e z07_rec_key = :v1

程序的写法有问题。执行那么多次，实际上被处理的行的数据很少，就是实际上很多都是无用功。却找成了系统的那么多的 commit ， 从而赵成了 IO 子系统的很大的压力。

4)         调节下面的 SQL 的索引策略：

  Physical Reads  Executions  Reads per Exec % Total  Hash Value

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

         83,852            5       16,770.4    19.9   3357555463

select max(z30_rec_key_2) from nlc50.z30 where z30_rec_key_2 lik

e :v1

SQL ordered by Executions for DB: ALEPH0  Instance: aleph0  Snaps: 1 -2

-> End Executions Threshold:     100

     497,348           17,371              0.0    695152841

select z98_rec_key,z98_type,z98_direction,z98_compress_type,z98_

total_map_length,z98_map_length,z98_data from nlc09.z98 where z9

8_rec_key >= :v1

     340,815           93,393              0.3   3969206952

select z98_rec_key,z98_type,z98_direction,z98_compress_type,z98_

total_map_length,z98_map_length,z98_data from nlc01.z98 where z9

8_rec_key >= :v1

     141,423          137,021              1.0   3698977705

select * from nlc01.z101 where z101_rec_key >= :v1

1.7 调整后性能的改善

         系统 IO Wait% 降低到 10% 左右，系统 CPU 空闲可以从现在的小于 8% 增加到 30 ％以上。对于用户来讲，系统主要业务响应时间明显变快。