Re: Measuring the impact of Redo Log change

Hi

You definitely would want to compare log file parallel write before and after your changes. (considering

Thanks

--
LSC



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.

>

>


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