RE: RMAN catalog operations taking 128 seconds per file - where is the bottleneck (and no guessing)
- From: "Crisler, Jon" <Jon.Crisler@xxxxxxx>
- To: <Dave.Herring@xxxxxxxxxx>, <sacrophyte@xxxxxxxxx>, "ORACLE-L" <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 18 Oct 2010 11:22:03 -0400
I can confirm that this workaround usually works. Also collecting statistics
as I posted earlier will address the issue.
EXEC DBMS_STATS.GATHER_FIXED_OBJECTS_STATS(); - gathers stats for dynamic
performance views, helps rman, datapump, export
-----Original Message-----
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On
Behalf Of Herring Dave - dherri
Sent: Monday, October 18, 2010 10:59 AM
To: sacrophyte@xxxxxxxxx; ORACLE-L
Subject: RE: RMAN catalog operations taking 128 seconds per file - where is the
bottleneck (and no guessing)
(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@xxxxxxx
From: oracle-l-bounce@xxxxxxxxxxxxx [mailto:oracle-l-bounce@xxxxxxxxxxxxx] 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.
****************************************************************************
zn{i
Other related posts:
