Received: (qmail 11990 invoked from network); 2 Feb 2009 01:21:43 -0600 Received: from freelists-180.iquest.net (HELO turing.freelists.org) (206.53.239.180) by static-ip-85-25-126-90.inaddr.intergenia.de with SMTP; 2 Feb 2009 01:21:43 -0600 Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 61947B8ED01; Mon, 2 Feb 2009 02:21:43 -0500 (EST) 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 18106-09; Mon, 2 Feb 2009 02:21:43 -0500 (EST) Received: from turing.freelists.org (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id CD0A8B8ED85; Mon, 2 Feb 2009 02:21:42 -0500 (EST) Received: with ECARTIS (v1.0.0; list oracle-l); Mon, 02 Feb 2009 02:19:35 -0500 (EST) Received: from localhost (localhost [127.0.0.1]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id E8510B8EAEC for ; Mon, 2 Feb 2009 02:19:34 -0500 (EST) 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 17415-06 for ; Mon, 2 Feb 2009 02:19:34 -0500 (EST) Received: from troll.tpk.net (mail.tpk.net [216.107.198.11]) by turing.freelists.org (Avenir Technologies Mail Multiplex) with ESMTP id 84B7DB8E440 for ; Mon, 2 Feb 2009 02:19:34 -0500 (EST) Received: from MWF600XL (216-107-223-2.RightSizing.tpk.static.cust.seg.net [216.107.223.2]) by troll.tpk.net (8.14.2/8.12.11) with ESMTP id n127JS57011412 for ; Mon, 2 Feb 2009 02:19:33 -0500 From: "Mark W. Farnham" To: "'ORACLE-L'" Subject: FW: rman backup slow Date: Mon, 2 Feb 2009 02:19:10 -0500 Message-ID: MIME-Version: 1.0 Content-Type: multipart/alternative; boundary="----=_NextPart_000_006B_01C984DC.A30364F0" X-MimeOLE: Produced By Microsoft MimeOLE V6.00.2900.5579 X-archive-position: 14381 X-ecartis-version: Ecartis v1.0.0 Sender: oracle-l-bounce@freelists.org Errors-to: oracle-l-bounce@freelists.org X-original-sender: mwf@rsiz.com Precedence: normal Reply-to: mwf@rsiz.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 ------=_NextPart_000_006B_01C984DC.A30364F0 Content-Type: text/plain; charset="us-ascii" Content-Transfer-Encoding: 7bit oops, forgot to snip small enough for the list. _____ From: Mark W. Farnham [mailto:mwf@rsiz.com] Sent: Sunday, February 01, 2009 8:31 AM To: 'robertgfreeman@yahoo.com'; 'p4cldba@gmail.com' Cc: 'ORACLE-L' Subject: RE: rman backup slow Aside from all the diagnostic questions, did I miss the part of this thread where waiting on x$dual was going on? SELECT TO_CHAR(SYSDATE,:"SYS_B_0",:"SYS_B_1"),TO_CHAR(SYSDATE,:"SYS_B_2",:"SYS_B_3" ), TO_CHAR(SYSDATE,:"SYS_B_4",:"SYS_B_5") FROM X$DUAL Appears to depend only on bind variables, and nothing *should* be locking dual. So either this was a red herring (where it is not really waiting on the x$dual query, it is just that is the current query) or else something pretty horrible is going on. So, could you verify right directly on the instance you are using that that you are sitting waiting on the x$dual query? If it is not that, then we need to separate out the time to copy at the OS level from the time to copy via RMAN. First, just time dd <8GB datafile> /dev/null. That is about the fastest you can read the 8GB, leaving Oracle out of it altogether. If that looks "bad" check whether something else besides Oracle is using the pieces of your disk farm where that file or device is. Many mysterious i/o problems in "Oracle" are solved by the discovery that something non-Oracle was using the "Oracle" media chain. Things like some administrator noticing that the half of all the disks you "destroked" for performance leaving half the drives empty was a neat place to put other kinds of backups often come to light, or deciding that the disk acreage you "didn't want" could be used by someone else, and hey, why would you want to know? You told me to just stripe and mirror it all, so you must not care. This can happen without affecting daytime performance and only backups if the activity is only when you're also doing backups. The other big "invisible to Oracle" thing that can happen is broken RAID-, as properly reference by a BAARF member. If the source media complex's performance seems "good," then repeat, but this time to a place on your mediaplex. If that slows it down, you've identified your non-Oracle performance problem with Oracle, and do a similar search for the cause of that. If all that shows reasonable performance, then it would appear you indeed have a problem with the way Oracle is interacting with your media in the RMAN pathway. Since that uses the database read technology on the source side, and you have not reported problems with non-backup performance, I would then move on to Robert's usual fine advice. But I'm still really wondering if the wait time is actually in the x$dual query. To me that is the elephant in the room we've ignored, and if it is not that elephant I'd move on to looking for a rat, because you've reported the problem in a way that makes me think it is a large increase in time required on a previously satifactorily performing system. Regards, mwf _____ ------=_NextPart_000_006B_01C984DC.A30364F0 Content-Type: text/html; charset="us-ascii" Content-Transfer-Encoding: quoted-printable

oops, forgot = to snip small enough for the list.

 

From: Mark = W. Farnham [mailto:mwf@rsiz.com]
Sent: Sunday, February = 01, 2009 8:31 AM
To: = 'robertgfreeman@yahoo.com'; 'p4cldba@gmail.com'
Cc: 'ORACLE-L'
Subject: RE: rman backup = slow

 

Aside from all the diagnostic = questions, did I miss the part of this thread where waiting on x$dual was going = on?

 

SELECT TO_CHAR(SYSDATE,:"SYS_B_0",:"SYS_B_1"),TO_CHAR(SYSDAT= E,:"SYS_B_2",:"SYS_B_3"),
TO_CHAR(SYSDATE,:"SYS_B_4",:"SYS_B_5") FROM = X$DUAL

Appears to depend only on bind variables, and nothing *should* be locking dual. So either = this was a red herring (where it is not really waiting on the x$dual query, it is = just that is the current query) or else something pretty horrible is going = on. So, could you verify right directly on the instance you are using that that you = are sitting waiting on the x$dual query?

 

If it is not that, then we need to separate out the time to copy = at the OS level from the time to copy via RMAN. First, just time dd <8GB = datafile> /dev/null. That is about the fastest you can read the 8GB, leaving = Oracle out of it altogether. If that looks “bad” check whether = something else besides Oracle is using the pieces of your disk farm where that file or = device is. Many mysterious i/o problems in “Oracle” are solved by = the discovery that something non-Oracle was using the “Oracle” = media chain. Things like some administrator noticing that the half of all the = disks you “destroked” for performance leaving half the drives = empty was a neat place to put other kinds of backups often come to light, or = deciding that the disk acreage you “didn’t want” could be used by = someone else, and hey, why would you want to know? You told me to just stripe = and mirror it all, so you must not care. This can happen without affecting = daytime performance and only backups if the activity is only when you’re = also doing backups. The other big “invisible to Oracle” thing = that can happen is broken RAID-<letter between E and G>, as properly = reference by a BAARF member. If the source media complex’s performance seems “good,” then repeat, but this time to a place on your = mediaplex. If that slows it down, you’ve identified your non-Oracle performance = problem with Oracle, and do a similar search for the cause of that. If all that = shows reasonable performance, then it would appear you indeed have a problem = with the way Oracle is interacting with your media in the RMAN pathway. Since = that uses the database read technology on the source side, and you have not = reported problems with non-backup performance, I would then move on to = Robert’s usual fine advice.

 

But I’m still really wondering if the wait time is = actually in the x$dual query. To me that is the elephant in the room we’ve = ignored, and if it is not that elephant I’d move on to looking for a rat, = because you’ve reported the problem in a way that makes me think it is a = large increase in time required on a previously satifactorily performing = system.

 

Regards,

 

mwf

 

<SNIP>

 

------=_NextPart_000_006B_01C984DC.A30364F0-- -- http://www.freelists.org/webpage/oracle-l