Originally posted by blguy

New mount points added, and we moved the REDO's from the R5 array to the new drives. But no change in performance from dedicated drives (same wait times, etc).

It turns out that SUN do 36GB 15K RPM drives for the 3510, instead of the 146GB 10K RPM drives. The drive buffer caches are different too (it seems SUN do small fast drives specifically).
Hi,

I just looked closer to the log writer write performance:

Statistic Total per Second per Trans
--------------------------------- ---------------- ------------ ------------
redo blocks written 314,161 510.0 5.6
redo buffer allocation retries 5 0.0 0.0
redo entries 222,696 361.5 3.9
redo log space requests 5 0.0 0.0
redo log space wait time 75 0.1 0.0
redo ordering marks 0 0.0 0.0
redo size 145,666,592 236,471.7 2,580.2
redo synch time 225,625 366.3 4.0
redo synch writes 56,541 91.8 1.0
redo wastage 9,989,864 16,217.3 177.0
redo write time 115,578 187.6 2.1
redo writer latching time 2 0.0 0.0
redo writes 39,158 63.6 0.7

user commits 56,456 91.7 1.0

236 kb/sec redo size / 63 writes = 3.75 kb/write (7 or 8 blocks a 512 bytes)

510 redo blocks written/sec * 512 bytes = 255 kb/sec (about the same as 236 kb/sec redo size), OK
(Just to check if mathematic works... :-)

91.7 user commits / 63.6 redo writes = 1.44 commits/second
(You are in "batch commiting" - one write serves more than 1 commit; that is good)

Avg
Total Wait wait Waits
Event Waits Timeouts Time (cs) (ms) /txn
---------------------------- ------------ ---------- ----------- ------ ------
log file sync 56,757 0 225,427 40 1.0
db file sequential read 1,729,916 0 93,853 1 30.6


(1) your "db file sequential read" comes from a cache - either UNIX Filesystem Cache or Cache from Storage Array. (real physical read is 6 ms - 12 ms)

(2) The 40 ms for "log file sync" are low - as mentioned on many places.

On an Application whith high commit-rate (800 commits/second) we used the old Sun A1000 arrays for data and redo. 2 Arrays, mirroring was across the arrays.

iostat showed less than 1 ms per write,
Oracle Statspack of course more (but below 4 ms) - the Oracle "log file sync" includes in my understanding both write calls - one per redo member.

Although the A1000 are not very expensive they provided a 128 kb battery buffered write back cache. And once I noticed that "log file sync" increased (more than 10 ms) - and system performance decreased - and that was because the old battery was not working any more and the Storage array disabled automatically the write back cache.

Conclusion: Even a VERY SMALL (128 kb - yes, kb not MB) SIGNIFICANTLY increases the performance of the logwriter in case that you have small writes.

I guess the Sun 3510 replaced the old A1000 arrays.

(The log file sync on that Sun A1000 were faster than on our billing system using an EMC Symmetrix with 32 GB write back cache....)

Now I am really curious about your log file sync on this new arrays and waiting for new statspack report (at least after you got your new controller)

But this will help only for the "normal" operation with many small commits.

Your huge CTAS in logging mode will definitely have severe impact!
(How big is that table and how long does that take?)

Good Luck,

Telco_DBA
http://www.Mercury-Consulting-Ltd.com