FW: rman backup slow
Date: Mon, 2 Feb 2009 02:19:10 -0500
Message-ID: <F4E41F17BAD043D980C61B38988266DA_at_rsiz.com>
oops, forgot to snip small enough for the list.
From: Mark W. Farnham [mailto:mwf_at_rsiz.com]
Sent: Sunday, February 01, 2009 8:31 AM
To: 'robertgfreeman_at_yahoo.com'; 'p4cldba_at_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-<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>
-- http://www.freelists.org/webpage/oracle-lReceived on Mon Feb 02 2009 - 01:19:10 CST