Re: Measuring the impact of Redo Log change

From: Michael Elkin <melkin4u_at_gmail.com>
Date: Tue, 9 Sep 2008 23:11:37 +0300
Message-ID: <b37755ee0809091311j4319d3c9qc303e767eb678ee0@mail.gmail.com>


You can find very useful Oracle statistics measurements instead of events wait times.
Please correct me if i am wrong but you actually want to measure the improvement in write throughput to the redo logs after change from RAD5 to RAID 1/0. You can simply use "redo writes"/"redo write time". The "redo write time" stats is in microseconds.

In addition you can use OS monitoring commands like sar or nmon on Linux.

Michael Elkin

2008/9/9 Deepak Sharma <sharmakdeep_oracle_at_yahoo.com>

> 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_at_logica.com>
> To: sharmakdeep_oracle_at_yahoo.com; oracle-l_at_freelists.org
> 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 England and Wales (registered number *947968*)
> Registered office: Stephenson House, 75 Hampstead Road, London NW1 2PL, United
> Kingdom
>
> -----Original Message-----
> *From:* oracle-l-bounce_at_freelists.org [mailto:
> oracle-l-bounce_at_freelists.org] *On Behalf Of *Deepak Sharma
> *Sent:* 09 September 2008 05:33
> *To:* oracle-l_at_freelists.org
> *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.
>
>

-- 
Best Regards
Michael Elkin

--
http://www.freelists.org/webpage/oracle-l
Received on Tue Sep 09 2008 - 15:11:37 CDT

Original text of this message