Return-Path: Delivered-To: 2-oracle-l@orafaq.com Received: (qmail 28262 invoked from network); 9 Sep 2008 15:14:30 -0500 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by air964.startdedicated.com with SMTP; 9 Sep 2008 15:14:04 -0500 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 4E26094A303; Tue, 9 Sep 2008 16:13:56 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 28638-02; Tue, 9 Sep 2008 16:13:56 -0400 (EDT) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id C586A94A2F2; Tue, 9 Sep 2008 16:13:55 -0400 (EDT) Received: with ECARTIS (v1.0.0; list oracle-l); Tue, 09 Sep 2008 16:11:56 -0400 (EDT) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 31A4394A150 for ; Tue, 9 Sep 2008 16:11:56 -0400 (EDT) Received: from turing.freelists.org ([127.0.0.1]) by localhost (turing.freelists.org [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 27702-02 for ; Tue, 9 Sep 2008 16:11:56 -0400 (EDT) Received: from wf-out-1314.google.com (wf-out-1314.google.com [209.85.200.174]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 1DA3C94A1B1 for ; Tue, 9 Sep 2008 16:11:45 -0400 (EDT) Received: by wf-out-1314.google.com with SMTP id 28so2240960wfa.25 for ; Tue, 09 Sep 2008 13:11:37 -0700 (PDT) DKIM-Signature: v=1; a=rsa-sha256; c=relaxed/relaxed; d=gmail.com; s=gamma; h=domainkey-signature:received:received:message-id:date:from:to :subject:cc:in-reply-to:mime-version:content-type:references; bh=e+auhRIrbxisdTP5SEkxwKyxQH/mC8ImYGNie/3mBWA=; b=oNhzETkQj957dZs69Ft+3utNszBvVBhBiHW6Xa8lfh/cEmLLQMa8nJherZ48fc/yOw AmG1xWYbNtejKL4Msi303YYZUnefQblIDxgNMkUifkN2DQo2TgEBput4cnUgJo5A9tan JIGz6wkCupJvXchWjXhhprK5Ye4ipbJZz0v0M= DomainKey-Signature: a=rsa-sha1; c=nofws; d=gmail.com; s=gamma; h=message-id:date:from:to:subject:cc:in-reply-to:mime-version :content-type:references; b=K0GDAuU7YqBqWLnn3BoplHmVK/ufDuvRb1VPEu4PJgZNPf8sq7rmFHm7evr57zhhIs zNpxvb2k+y0B5olXxu/kfFRK7mug8jRvgpw5vt1yxQ8AGf+S7n16mWJ6y192Zk4/wYW6 Q7ZO6nJJ6jZjZyyjgH5m9S+/MgnirY+9hbI/4= Received: by 10.140.157.1 with SMTP id f1mr102788rve.220.1220991097593; Tue, 09 Sep 2008 13:11:37 -0700 (PDT) Received: by 10.141.171.4 with HTTP; Tue, 9 Sep 2008 13:11:37 -0700 (PDT) Message-ID: Date: Tue, 9 Sep 2008 23:11:37 +0300 From: "Michael Elkin" To: sharmakdeep_oracle@yahoo.com Subject: Re: Measuring the impact of Redo Log change Cc: "Roberts, David (GSD - UK)" , oracle-l@freelists.org In-Reply-To: <508476.72774.qm@web52809.mail.re2.yahoo.com> MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_Part_29729_13877173.1220991097579" References: <508476.72774.qm@web52809.mail.re2.yahoo.com> X-archive-position: 10700 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: melkin4u@gmail.com Precedence: normal Reply-to: melkin4u@gmail.com List-help: List-unsubscribe: List-software: Ecartis version 1.0.0 List-Id: oracle-l X-List-ID: oracle-l List-subscribe: List-owner: List-post: List-archive: X-list: oracle-l X-Virus-Scanned: Debian amavisd-new at localhost.localdomain ------=_Part_29729_13877173.1220991097579 Content-Type: text/plain; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline 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 > 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)" > To: sharmakdeep_oracle@yahoo.com; oracle-l@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@freelists.org [mailto: > oracle-l-bounce@freelists.org] *On Behalf Of *Deepak Sharma > *Sent:* 09 September 2008 05:33 > *To:* oracle-l@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 ------=_Part_29729_13877173.1220991097579 Content-Type: text/html; charset=ISO-8859-1 Content-Transfer-Encoding: 7bit Content-Disposition: inline
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@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@logica.com>
To: sharmakdeep_oracle@yahoo.com; oracle-l@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@freelists.org [mailto:oracle-l-bounce@freelists.org] On Behalf Of Deepak Sharma
Sent: 09 September 2008 05:33
To: oracle-l@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
------=_Part_29729_13877173.1220991097579-- -- http://www.freelists.org/webpage/oracle-l