RE: RMAN catalog operations taking 128 seconds per file - where is the bottleneck (and no guessing)

From: Herring Dave - dherri <Dave.Herring_at_acxiom.com>
Date: Mon, 18 Oct 2010 09:59:11 -0500
Message-ID: <7ED53A68952D3B4C9540B4EFA5C76E360866BF7D_at_CWYMSX04.Corp.Acxiom.net>



(resending)

Charles,

We're on 10gr2 and have rather poor performance with catalog operations.  We had Oracle advanced services helping us with a separate issue and they noted the same problem.  They shared that the problem was related to tuning problems with some of Oracle's internal catalog queries and the best work-around was to change your optimizer_mode to RULE against the catalog:

SQL 'ALTER SESSION SET optimizer_mode = RULE';

Seems to work for us.  Again this is for catalog operations and as many before me pointed out, the problem is within your RMAN catalog database and performance of those internal queries.  And Oracle noted this problem was fixed in 11g.

HTH. Dave Herring  | DBA
Acxiom Global Technology Solutions   

630-944-4762 office | 630-430-5988 cell | 630-944-4989 fax 1501 Opus Pl | Downers Grove, IL, 60515 | U.S.A. | www.acxiom.com Service Desk: 888-243-4566, https://servicedesk.acxiom.com, GSCA_at_DNB.com

From: oracle-l-bounce_at_freelists.org [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Charles Schultz Sent: Wednesday, October 13, 2010 2:05 PM To: ORACLE-L
Subject: RMAN catalog operations taking 128 seconds per file - where is the bottleneck (and no guessing)

Good day, list,

What would cause an RMAN catalog operation to last a relatively eternal 128 seconds?

The stats I have collected so far:

• Sun T5440
• 256 virtual processors at 1.414MHz
• 128gb ram (76gb swap - I know... I didn't set it up)
• 65 databases
• Truss shows a lot of parking and sleeping, but not much else.
• Trace event 10046 is similarly not giving me much help.
• Running an RMAN 10.2.0.2 Duplicate command on a database with 164 datafiles.
• Controlfile is 22mb with a keep time of 7 days.

As I file a case with Oracle, where else can I look to find the TRUTH of why this is running so slow*? Sysadmins are telling me there are no host bottlenecks evidenced by OS tools.

Fortunately, this is not a rush. At this point, I am more curious than anything else, and I am concerned that this significant slowdown is symptomatic of a deeper issue. So, no guessing allowed (*nod to Alex Gorbachev*). I am asking you all to help me find what the real problem is. =)

*slow: means I ran this operation on different databases (same host) three times in the past two days, not to mention countless times prior to that. I expect catalog operations to be fairly quick, fast enough that cataloging a list of files usually scrolls off the screen before I can read them all.

--
Charles Schultz



The information contained in this communication is confidential, is intended only for the use of the recipient named above, and may be legally privileged.

If the reader of this message is not the intended recipient, you are hereby notified that any dissemination, distribution or copying of this communication is strictly prohibited.

If you have received this communication in error, please resend this communication to the sender and delete the original message or any copy of it from your computer system.

Thank You.



†Ûiÿü0ÁúÞzX¬¶Ê+ƒün– {ú+iÉ^ Received on Mon Oct 18 2010 - 09:59:11 CDT

Original text of this message