RE: RMAN Restore Archivelog Files -- Very Slow
Date: Fri, 6 Feb 2009 18:41:44 -0500
Message-ID: <56211FD5795F8346A0719FEBC0DB067503CC5C45_at_mds3aex08.USIEXCHANGE.COM>
Another common fix for RMAN performance problems is to run execute dbms_stats.gather_fixed_object_stats
This helps for RMAN performance problems, Export performance problems and some Data Pump performance problems. The cause is poor execution plans when using the CBO.
I think if you are looking at OEM or AWR, you wind up seeing a lot of SQL statements with excessive cpu and high CBO cost that reference views v$hist_head and/or v$obj ( I am saying this from memory). Setting optimizer_mode=RULE addresses the same issue.
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Jared Still
Sent: Thursday, February 05, 2009 12:33 PM
To: rama.ari_at_accenture.com
Cc: oracle-l_at_freelists.org
Subject: Re: RMAN Restore Archivelog Files -- Very Slow
You may want to search the Oracle-L archives at freelists.org for RMAN performance problems.
This has appeared fairly frequently of late.
One of the posts lists a MetaLink note that is actually a master list pointing to other notes, all relating to RMAN performance problems.
BTW, the 'fix' for many RMAN performance problems:
SQL 'ALTER SESSION SET OPTIMIZER_MODE=RULE'
Jared Still
Certifiable Oracle DBA and Part Time Perl Evangelist
On Wed, Feb 4, 2009 at 9:42 PM, <rama.ari_at_accenture.com> wrote:
Thanks for reading this email & Thanks in Advance for your help.
We are facing performance issue when trying to restore archive log files using RMAN catalog.
Environment:
Oracle Database: 10g (10.2.0.3) RAC
Database Size: ~350 GB
Archive Log Files per day: ~30 - 45
Archive Log File Size: 200MB
NetBackup: 6.5
Backups: RMAN to VTL (Weekly Full, Daily Incremental)
We restored 350 GB of database using RMAN catalog and it took around 6 Hrs which is very good. Most of the datafile sizes are around 30GB and some of the files are couple of GB.
When we try to restore archive log (200MB) files using RMAN Catalog and it is taking approximately 15 Minutes for each file. If we bypass RMAN catalog by using backup information from controlfile, it takes approximately 2.5 Minutes to restore each archive log file. After working with backup team, we found out that approximately 12 Minutes time was spent for the hand shake between RMAN Catalog and Netbackup catalog.
Did anyone of you have seen similar situation and what might be the problem? Have you done any kind of database maintenance work for RMAN repository database to improve performance?
Thanks,
Rama
This message is for the designated recipient only and may contain privileged, proprietary, or otherwise private information. If you have received it in error, please notify the sender immediately and delete the original. Any other use of the email by you is prohibited.
-- http://www.freelists.org/webpage/oracle-lReceived on Fri Feb 06 2009 - 17:41:44 CST