RE: Average size of reads from online redo logs by ARCH process

Hi Alex,
To clarify my previous response, the v$ references refer to LGWR and not ARCH (I don't believe arch will write to the redo ).  

We did some experimentation with EMC redo logs and stripe sizes and found a couple of things for our application mix (high OLTP with commit frequency extremely high). We found:  

1. raw devices performed better ('log file parallel write') than solaris ufs by between 10 and 20% on average time over long periods. I think this is notion is supported by steve adams notes.
2. the drives were mirrored in the EMC array and we were told by EMC that the array will perform sequential reads (ARCH copies) from the 'mirror' as to not introduce measurable contention. We did not confirm this technically but were unable to see any contention when ARCH was active reading. so this may not be a critical issue for you. Are you spending a lot of time in log file sync? There may be other reasons why...
3. the array provides a degree of isolation from physical writes to a point by memory caching, but there is a limit of a number of writes that can be pending on any one drive in the array. because of this behavior, our emc tech specialist advised us to stripe the redo rather than but it on a separate disk pair in the array. I can't recall the stripe size off hand...

You could talk to EMC about any best practice stuff they might have...  

By the way,
select *

    from v$session_event
    where event = 'log file sequential read' will show which sessions have read the redo since database startup and the average time waited...  

cheers.
-----Original Message-----

From: Alexandre Gorbatchev [mailto:agorbatchev_at_amadeus.net] Sent: Tuesday, November 16, 2004 11:19 AM To: McCartney, Bruce
Cc: oracle-l_at_freelists.org
Subject: RE: Average size of reads from online redo logs by ARCH process

Hi Bruce,

On Steve's site I didn't find anything about determining size of read by ARCH. I know how to determine avg. writes size by LGWR. Now I want to know what is size of ARCH IOs (reading) in order to figure out what striping we need to implement to reduce possible IO contention. Ideally, we need to put every redo log on a separate disk but it's an expensive solution, so I'm looking to possibility of stripe it. I think if both reads and writes are small, we can have stripe size to set so the one single IO affects most of the time one single physical disk. Another complication is that we are on EMC and I'm not sure yet if caching will play significant role in writes performance.

You and KGopal. are right - I could use 10046 to figure that out.

Thanks,
Alex

From: "McCartney, Bruce" <BMcCartney_at_talisman-energy.com> on 16-11-2004 09:59 MST

To: <agorbatchev_at_amadeus.net>
<oracle-l_at_freelists.org>         

cc:                                           

Subject: RE: Average size of reads from online redo logs by ARCH process                 

Alex,
you could strace the arch process to get detailed numbers. The read size is dependent on the underlying file system setup (i.e. raw devices, veritas file system or unix/windows file system). There is not direct relationship to the buffer_size for read (or write). check out steve adams site for some detailed info on redo i/o http://www.ixora.com.au/notes/

just curious, why do you care about the *read* size? the average *write* size can be calculated from :

select value from v$sysstat where name='redo blocks written'; /
select value from v$sysstat where name='redo writes';

a log block size is os dependent as shown in http://www.ixora.com.au/notes/log_block_size.htm

-----Original Message-----

From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Alexandre Gorbatchev Sent: Tuesday, November 16, 2004 9:12 AM To: oracle-l_at_freelists.org
Subject: Average size of reads from online redo logs by ARCH process

Hi all,
How do I figure out what is the average (or constant?) size of disk reads from online logs by ARCH process?
Is there any relation to _log_archive_buffer_size? It is 2048 log blocks by default, as far as I know, so on my platform it's 2Mb.

Thanks in advance,
Alex

--

http://www.freelists.org/webpage/oracle-l

--

http://www.freelists.org/webpage/oracle-l Received on Tue Nov 16 2004 - 15:05:29 CST