"log file sync" 等待事件(2)

显然log file sync在等待db file parallel write的完成.

这里磁盘IO肯定存在了瓶颈，实际用户的redo和数据文件同时存放在Raid的磁盘上，存在性能问题.
需要调整.

3.统计信息

 
Statistic                                    Total   per Second    per Trans
--------------------------------- ---------------- ------------ ------------
....
redo blocks written                         93,853         63.9          6.4
redo buffer allocation retries                   1          0.0          0.0
redo entries                               135,837         92.5          9.3
redo log space requests                          1          0.0          0.0
redo log space wait time                         3          0.0          0.0
redo ordering marks                              0          0.0          0.0
redo size                               41,776,508     28,458.1      2,852.0
redo synch time                              4,174          2.8          0.3
redo synch writes                           14,198          9.7          1.0
redo wastage                             4,769,200      3,248.8        325.6
redo write time                              3,698          2.5          0.3
redo writer latching time                        0          0.0          0.0
redo writes                                 14,572          9.9          1.0
....
sorts (disk)                                     4          0.0          0.0
sorts (memory)                             179,856        122.5         12.3
sorts (rows)                             2,750,980      1,874.0        187.8
....
transaction rollbacks                           36          0.0          0.0
transaction tables consistent rea                0          0.0          0.0
transaction tables consistent rea                0          0.0          0.0
user calls                               1,390,718        947.4         94.9
user commits                                14,136          9.6          1.0
user rollbacks                                 512          0.4          0.0
write clones created in backgroun                0          0.0          0.0
write clones created in foregroun               11          0.0          0.0
          -------------------------------------------------------------


avg.redo write size = (Redo block written/redo writes)*512 bytes
      = ( 93,853 / 14,572 )*512 
      = 3K

这个平均过小了，说明系统的提交过于频繁.

 
Latch Sleep breakdown for DB: DPSHDB  Instance: dpshdb  Snaps: 3473 -3475
-> ordered by misses desc

                                Get                                  Spin &
Latch Name                    Requests         Misses      Sleeps Sleeps 1->4
-------------------------- -------------- ----------- ----------- ------------
row cache objects              12,257,850     113,299          64 113235/64/0/
                                                                  0/0
shared pool                     3,690,715      60,279      15,857 52484/588/65
                                                                  46/661/0
library cache                   4,912,465      29,454       8,876 23823/2682/2 
                                                                  733/216/0
cache buffers chains           10,314,526       2,856          33 2823/33/0/0/
                                                                  0
redo writing                       76,550         937           1 936/1/0/0/0
session idle bit                2,871,949         225           1 224/1/0/0/0
messages                          107,950         159           2 157/2/0/0/0
session allocation                184,386          44           6 38/6/0/0/0
checkpoint queue latch             96,583           1           1 0/1/0/0/0
          -------------------------------------------------------------