Re: Measuring the impact of Redo Log change

  • From: Deepak Sharma <sharmakdeep_oracle@xxxxxxxxx>
  • To: "Roberts, David \(GSD - UK\)" <david.h.roberts@xxxxxxxxxx>
  • Date: Tue, 9 Sep 2008 08:43:55 -0700 (PDT)

Hi Dave,

Using your idea, we did see a drop in the "seconds to archive" - almost half 
the prior values.

We also see a drop in 'log file sequential read' wait event based on AWR data. 
I think that the 'log file sequential read' waits are when the Arc process is 
trying to read the Redo logs and creating an archived file on the disk.

I also wanted to measure the throughput of the redo logs itself, basically - 
are we now writing faster to the redo logs.

Anyone:  Should we be looking at 'log file parallel write' and 'log file single 
write' waits, or something in addition to these?

Thanks,
Deepak




----- Original Message ----
From: "Roberts, David (GSD - UK)" <david.h.roberts@xxxxxxxxxx>
To: sharmakdeep_oracle@xxxxxxxxx; oracle-l@xxxxxxxxxxxxx
Sent: Tuesday, September 9, 2008 3:47:03 AM
Subject: RE: Measuring the impact of Redo Log change

 
While the following SQL isnʼt going
to be able to tell you anything about the performance of the application you are
running, it may give an indication of increased stability, and depending on the
size of your control file may give you data from both before and after the 
change
for you to compare.
 
One of the problems I have encountered is when
redo logs are all on a single disk device, there will be contention between the
archive log process and the log writer, which will be seen in an increasing
amount of time taken to archive logs (Iʼm assuming youʼre in
archive log mode) when the system is under duress.
 
SELECT recid,
       To_Char(
next_time, 'YYYY-MON-DD HH24:MI:SS' ),
      
(completion_time - next_time) * ( 24 * 60 * 60 ) "Seconds to archive"
  FROM v$archived_log
 ORDER BY recid
 
If the peek or average time to archive
logs has dropped, it would be possible to argue that you have increased the
theoretical throughput!
 
Obviously if you had the opportunity,
placing redo log groups on alternating physical devices would obviously be a
better way to reduce this form of contention!
 
 
Dave
 
 
David Roberts
 _________________________________________ 
Logica- Releasing your
potential
www.logica.com
 
Logica UK Limited
Registered in Englandand Wales(registered number 947968)
Registered office: Stephenson House, 75 Hampstead Road, LondonNW1 2PL, United 
Kingdom
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx[mailto:oracle-l-bounce@xxxxxxxxxxxxx] On 
Behalf Of Deepak Sharma
Sent: 09 September 200805:33
To: oracle-l@xxxxxxxxxxxxx
Subject: Measuring the impact of
Redo Log change
 
We recently (today) moved the redo logs from RAID5
to RAID1+0.

The obvious reason of doing this is to make things better in terms of
performance, as RAID1+0 is better suited for read/writes. Someone pls correct
me if that's not true.

What are different ways to measure the impact of this change? 

The platform is AIX, DB is 10.2.0.3 and DB size is 55TB, and 2TB redo is
generated each day. There are plans underway to reduce the redo generation
using direct path etc. (so let's not get diverted by that). 

We can try measuring the wait event stats using AWR, session stats etc., but
what exactly to look for?  'log file switch completion', 'log file sync',
are some things that come to mind - what else?

Thanks,
Deepak
 
This e-mail and any attachment is for authorised use by the intended 
recipient(s) only. It may contain proprietary material, confidential 
information and/or be subject to legal privilege. It should not be copied, 
disclosed to, retained or used by, any other party. If you are not an intended 
recipient then please promptly delete this e-mail and any attachment and all 
copies and inform the sender. Thank you. 


      

Other related posts: