log file parallel writes question

From: grjohnson <Johnsog123_at_hotmail.com>
Date: 6 Nov 2002 20:45:51 -0800
Message-ID: <32b8a689.0211062045.3b4b968a@posting.google.com>

Hi all,

Just got a query regarding log file parallel writes. I read the following thread with interest

http://groups.google.com/groups?hl=en&lr=&ie=UTF-8&threadm=3d2e60f8%241%40usenetgateway.com&rnum=1&prev=/groups%3Fq%3D%2522log%2Bfile%2Bparallel%2Bwrite%2522%2Bgroup:comp.databases.oracle.server%26hl%3Den%26lr%3D%26ie%3DUTF-8%26selm%3D3d2e60f8%25241%2540usenetgateway.com%26rnum%3D1

but it'd didn't quite satisfy my curiosity. Now I manage a DB with redo logs the size of 512MB. My redologs are located on a separate filesystem which is mirrored (HJR.. I know what you are thinking.. Mirroring at the hardware level isn't a satisfactory multiplexing method… and I agree). Alas, what has me curious from the statistics below, is the number of waits "log file parallel write" equals the number of "redo writes" and very similar to "user commits". My commit rate based on "redo blocks written"/"redo writes" is around 13 blocks a commit. My LOG BUFFER is 4MB. I was just wondering if I could get some opinions on a method to reduce the number of waits.. and why would I'd get a 1:1 ratio of waits to writes?

                Snap Id     Snap Time      Sessions
                ------- ------------------ --------
 Begin Snap:        587 05-Nov-02 10:00:13       34
   End Snap:        588 06-Nov-02 10:00:02       34
    Elapsed:               1,439.82 (mins)

Cache Sizes

db_block_buffers: 40000 log_buffer: 4194304 db_block_size: 16384 shared_pool_size: 200000000

Top 5 Wait Events
~~~~~~~~~~~~~~~~ Wait % Total

Event                               Waits  Time (cs)   Wt Time
---------------------------- ------------ ------------ -------
log file parallel write           359,777      397,722   22.56
log file sync                     335,528      265,254   15.04

Instance Activity Stats
Statistic                             Total   per Second    per Trans
-------------------------- ---------------- ------------ ------------
redo blocks written               4,750,137         55.0         14.1
redo writes                         359,776          4.2          1.1
redo size                     2,230,386,596     25,818.0      6,623.4
user commits                        336,532          3.9          1.0

Log_buffer                    4194304

Thanks,

Greg Johnson
OCP 8i DBA / OCP 9i DBA
Brisbane, Queensland, Australia Received on Wed Nov 06 2002 - 22:45:51 CST