PURPOSE
-------
Give an overview of known performance problems with RMAN Backups
and Restores
SCOPE & APPLICATION
-------------------
This document is intended for DBA's who are facing performance issues
during the backup or restore via RMAN.
Its intention is to give an overview of the know performance
issues with RMAN and not to give any diagnosis. The diagnosis and
investigation is handled more in depth in other documents as
referenced at the bottom of this document.
KNOW RMAN PERFORMANCE ISSUES
-----------------------------
Note :
Generic Recommendation for Oracle 10G
Generate statistics on the fixed objects, using
DBMS_STATS.GATHER_FIXED_OBJECTS_STATS.
This should be done when the database has been running
for awhile with generic workload, so the information
in the fixed objects reflect a resonable state of the database load.
Gather the statistics with :
SQL> exec dbms_stats.gather_fixed_objects_stats
Alert Note 463227.1 Oracle10g RMAN Recovery Catalog Known Performance Issues
Bug 6051693 SLOW RESYNC AND SHOW ALL IS TAKING AROUND 2 MINUTES
Duplicate of Bug 5620640
Note 820856.1 High Waits On "Control File Sequential Read" During Rman Backup
Version affected: 10.2.0.1 to 10.2.0.3
Version fixed : some one-off backports for 10.2.0.3 , fixed in 10.2.0.4
Affects : Target database
Workaround : none
Unpublished Bug 8239313 Duplicate database for a very large database of around 3000 datafiles is very slow.
Version affected: 10.2.0.3 and higher
Version fixed : 11.2
Affects : Target database
Workaround : none
Bug 5251842 Poor plan against V$DATAFILE
Version affected: 10.2.0.3 and higher
Version fixed : 11.1.0.7 and 11.2 and some one-off for 10.2.0.3 | 4
Affects : Target database
Workaround : none
Bug 7023147: V$LOG_HISTORY queries can be slow
Version affected: 10.2.0.2 and higher
Version fixed : 10.2.0.5 and 11.2
Affects : Target database
Workaround : Recreate the controlfile
Note : 882154.1 RMAN LIST BACKUP command takes a long time querying
v$log_history
Bug 7595777 RMAN TAKES A LONG TIME BEFORE STARTING THE BACKUP
Version affected: 10.2.0.3 and higher
Version fixed : Unknown yet (FEBR-2009)
Affects : Target database
Workaround : Replace wasresynced function ?/rdbms/admin/recover.bsq with the following:
---------------------------------
function wasresynced(until_stamp IN number
,high_stamp IN number) return number is
nodups number; -- number of duplicates
high number;
low number;
resyncstamp number;
begin
high := high_stamp;
low := until_stamp;
nodups := 0;
resyncstamp := 0;
deb('resync', 'wasresynced high_stamp=' || high_stamp ||
' high_date=' || stamp2date(high_stamp), dbtype);
for duprec in duprec_c(low, high) loop
if (dbms_rcvcat.isDuplicateRecord(recid => duprec.recid
,stamp => duprec.stamp
,type => duprec.type)) then
if (resyncstamp = 0) then
resyncstamp := duprec.stamp;
end if;
nodups := nodups + 1;
if (nodups >= maxdups) then
deb('resync', 'wasresynced resyncstamp=' || resyncstamp ||
' resyncdate=' || stamp2date(resyncstamp), dbtype);
return resyncstamp;
end if;
else -- couldn't find 16 consecutive duplicate records.
deb('resync', 'wasresynced could not find record recid=' ||
duprec.recid || ' stamp=' || duprec.stamp || ' type=' ||
duprec.type || ' maxdups=' || nodups, dbtype);
return 0;
end if;
end loop;
-- Timestamp range not enough to satisfy the number of duplicates.
-- Retry using a higher timestamp
deb('resync', 'timestamp range not enough - nodups=' || nodups, dbtype);
return -1;
end;
-------------------------------------
Bug 7206538 PERFORMANCE ISSUE WITH RMAN BACKUP
Bug 6412947 DRA PERFORMANCE ISSUES
Version affected: 11.1.0.6
Version fixed : 11.1.0.7
Affects : Target database
Workaround : set parameter "_dra_enable_offline_dictionary"=false
Reference : Note 605557.1 RMAN BACKUPS CAN TAKE A LONG TIME IN 11.1.0.6
Bug 7173341 - CLEANUPRSR IS TAKING VERY LONG TIME CAUSING SLOW RESYNC
Version affected: 10.2.0.4
Version fixed : 11.2
Affects : Catalog Schema
Workaround : Change the 2 following SQL-statements in the recover.bsq
Replace the 'SELECT max(rsr_key)' statement in cleanupROUT:
SELECT max(rsr_key) into high_session_key
FROM rsr, dbinc
WHERE dbinc.db_key = this_db_key
AND rsr.dbinc_key = dbinc.dbinc_key
AND rsr.rsr_stamp < high_stamp;
Replace the 'DELETE FROM rsr' statement in cleanupRSR:
DELETE FROM rsr
WHERE rsr_end < nowTime-60
AND rsr.dbinc_key IN
(select dbinc_key from dbinc
where dbinc.db_key = this_db_key);
Upgrade the catalog to pick up the change in recover.bsq file.
Bug 6476935 - RMAN Resyncs may take a long time to complete
Version affected : 10.2.0.3
Version fixed : 11.2
Affects : Catalog Schema
Workaround : create index rsr_i_stamp on rsr(rsr_sstamp, rsr_srecid);
Note 433335.1 - RMAN Restore Of Incremental Backups Will Not Parallelise
and Uses Only One Channel
Version affected : 10.1.0.0 to 10.2.0.4
Versions fixed : Expected behaviour - media managers can influence how we do
Backups @10g
Affects : Target database
Workaround : SET PARALLELMEDIARESTORE OFF;
See Note 433335.1
Note 413098.1 - Extremely Poor RMAN Backup Performance to NFS After Upgrade
to 10.2 on Solaris
Version affected : 10.2.0.0 to 10.2.0.4
Version fixed : 10.2.0.3 for NON-RAC applications only:
Affects : Target database
Diagnosis : 10.2 forces the use of directio when writing to NFS in a
RAC implementation, this is done regardless of whether or
Not you use the forcedirectio nfs mount option: this
results in very poor performance
Workaround : None - do not write backups to NFS at 10.2 when using RAC
See Note 413098.1
Bug 5219484 - CATALOG RESYNCS ARE VERY SLOW - ROUT TABLE HAS 6 MILLION ROWS +
Version affected : 10.1
Version fixed : 10.2.0.4
Affects : Catalog Schema
Diagnosis : Many rows in ROUT-table.
Workaround : See Note 378234.1 Rman Catalog Resync Operation is Very slow at 10G
Bug 5047758 - RMAN SLOW PERFORMANCE ON EXIT (UPDATING STATUS STATISTICS)
Version affected : 10.1
Version fixed : 10.2 due to base bug 3325292 OPTIMIZER NOT ABLE TO USE INDEXES IN QUERY USING V$SQL_PLAN
Affects : Target database
Diagnosis : Expensive statement =
select round(sum(MBYTES_PROCESSED)) ,round(sum(INPUT_BYTES)),
round(sum(OUTPUT_BYTES))
from V$RMAN_STATUS
start with (RECID=:b1 and STAMP=:b2)
connect by prior RECID=parent_recid
Workaround : RMAN>sql "alter session set optimizer_mode=RULE";
Remark : It might be that bug 5247609 is a followup / new occurence of this bug
Bug 5247609 RMAN SLOW PERFORMANCE DURING REGISTER DATABASE/OPEN RESETLOGS
Version affected : 10.2
Version fixed : 10.4
Affects : Target database
Diagnosis : Expensive statement =
select round(sum(MBYTES_PROCESSED)) ,round(sum(INPUT_BYTES)),
round(sum(OUTPUT_BYTES))
from V$RMAN_STATUS
start with (RECID=:b1 and STAMP=:b2)
connect by prior RECID=parent_recid
Workaround : RMAN>sql "alter session set optimizer_mode=RULE";
Bug 1551773 RMAN RESTORE RUNS VERY SLOWLY WHEN THERE ARE MANY ROWS IN THE CKP TABLE
Version affected : 8.1
Version fixed : 9i
Affects : Catalog Schema
Diagnosis : The table CKP in the RMAN-catalog has many rows.
Workaround : Call Oracle Support to supply a cleanup-script.
Reference : Note.209780.1 RMAN Hangs During Restore if CKP Table is Excessively Large
Bug 2803823 RESYNC CATALOG AND MAINTENANCE COMMANDS ARE SLOW
Version affected : 9.2
Version fixed : 9205, 10g
Affects : Catalog Schema
Diagnosis : Bad execution plan for SQL-statement : DELETE FROM CKP
Reference : Note 248361.1 RMAN Command 'Resync Catalog' is Very Slow
Bug 4013855 - RMAN CROSSCHECK IS TAKING A LONG TIME TO COMPLETE
Version affected : 9i
Version fixed : 9207, 10.1.0.5, 10gRelease2
Affects : Catalog Schema
Diagnosis : Large number of backupsets.
LIST/CROSSCHECK commands(in 9iR2 and above) taking more time.
REPORT/DELETE obsolete (in 9iR2) takes more time.
Note 339964.1 RMAN 8.1.7 Crosscheck Command Is Very Slow
Version affected : 8i
Version fixed : No fix available.
Affects : Catalog Schema
Diagnosis : See this note for details on how to confirm if you have hit this problem.
Workaround : RMAN> sql "alter session set optimizer_mode=RULE";
Cleanup backup history - see this note for hints on how to do this at 8i.
Bug 4548861 RMAN RESTORE ARCHIVELOG UNTIL SEQUENCE TAKES A LONG TIME TO COMPLETE
Version affected : 9i and 10G
Version fixed : 10.2.0.2, 11G
Affects : RMAN executable, Target database, Catalog Schema
Diagnosis : Restore archivelog takes long compilation time either during
RESTORE command or during RECOVER command.
Workaround : None
Note 342999.1 First Resync on Production Host After Running RMAN on
Backup Server Takes a Long Time
Version affected : 8i
Version fixed : No fix available.
Affects : RMAN executable, Target database, Catalog Schema
Diagnosis : See this note for details on how to confirm if you have hit this problem.
Workaround : Recreate the controlfile.
Bug 2876717 RMAN backup starts SLOW if READ-ONLY device is used
Version affected : 9.2
Version fixed : 9204, 10g
Affects : Target database
Diagnosis : READ_ONLY devices, like CD-rom, are used.
Bug 1887868 RMAN RESYNC takes a long time with many tablespaces
Version affected : 8.1
Version fixed : 9201, 10g
Affects : Catalog Schema
Diagnosis : RMAN full resync (either implicit resync, or explicit
resync requested by RESYNC CATALOG command) takes a
very long time when there are a lot of tablespaces
(probably 500 or more) in the database)
Internal Bug 1712720 RMAN MAINTENANCE COMMANDS ARE VERY SLOW
Version affected : 8.1, 9.0.1
Version fixed : 9201
Affects : Catalog Schema
Workaround : create index brl_i_dts on brl(dbinc_key, thread#, sequence#)
Bug 2174697 RMAN incremental backup performance degrades over time in NOCATALOG mode
Version affected : 8.1 9.0.1
Version fixed : 8174 9014 9201
Affects : Target database
Diagnosis : Many entries in X$KSFQP
Workaround : SQL> alter system set events 'immediate trace name ksfqp_limit level 1';
Bug 2385857 RMAN backup performance degrades over time for large DB_FILES
Version affected : 8.1 9.0.1
Version fixed : 9202 10g
Affects : Target database
Diagnosis : Contention of ksfqpl latch
Workaround : SQL> alter system set events 'immediate trace name ksfqp_limit level 1';
or in the init.ora :
event = "logon trace name ksfqp_limit level 1"
Bug 2484250 RMAN CUMULATIVE BACKUP / MAINTENENCE may appear to hang (spin)
Version affected : 8.1 9.0.1 9.2
Version fixed : 9203 10g
Affects : Catalog Schema
Workaround : dbms_utility.analyze_schema() on RMAN- catalog schema.
Bug 2610596 Huge CPU usage from RMAN server when TAPE not mounted with BACKUP_TAPE_IO_SLAVE=TRUE
Version affected : 8.1 9.0.1 9.2
Version fixed : 9203 10g
Affects : Target database
Workaround : Insert a tape in the tape drive, or
Set init.ora param BACKUP_TAPE_IO_SLAVES to FALSE
Bug 2968394 RESYNC TAKES A LONG TIME TO COMPLETE : LARGE BP TABLE
Version affected : 8.1 9i
Version fixed : 10g
Affects : Catalog Schema
Diagnosis : Many rows in tables BS and BP
Workaround : Create 2 additional indexes in the RMAN-catalog schema :
SQL> create index bs_i_1 on bs (db_key, bs_recid, bs_stamp);
create index db_i_2 on bp (db_key, bp_recid, bp_stamp);
Bug 1407413 NO INDEX TO ON RI CONSTRAINT BETWEEN BS AND BDF
Version affected : 8.1
Version fixed : 9i
Affects : Catalog Schema
Diagnosis : Many rows in tables BDF
Workaround : Create 1 additional index in the RMAN-catalog schema :
SQL> create index bdf_i_bs_key on bdf(bs_key);
Bug 4110489 RMAN SKIP READ ONLY TABLESPACE CLAUSE TAKING TOO MUCH TIME TO SKIP
Version affected : 9i
Version fixed : 10G Release2
Affects : RMAN Executable
Diagnosis :
1) SKIP READ ONLY or SKIP OFFLINE option takes too much
when NOT BACKED UP option is used in catalog mode.
2) SKIP READ ONLY or SKIP OFFLINE option takes too much when
BACKUP OPTIMIZATION is turned ON in catalog mode
Workaround : Run the backup in NOCATALOG mode and
prefrom a RESYNC CATALOG afterwards
Bug 3966722 CONCURRENT RMAN BACKUPS WAIT ON TM ENQUEUE ON CKP TABLE
Version affected : 9i, 10G
Version fixed : 9207, 10105, 10G Release2
Affects : Catalog Schema
Diagnosis :
Multiple resyncs are done in parallel for same/different databases.
There is no error signaled, but the other backup job will not continue
until the resync in first job is completed.
Workaround : Create indexes in recovery catalog:
For 9i and 10G:
SQL> CREATE INDEX dfatt_i_sck on dfatt(start_ckp_key);
CREATE INDEX dfatt_i_eck on dfatt(end_ckp_key);
CREATE INDEX tsatt_i_sck on tsatt(start_ckp_key);
CREATE INDEX tsatt_i_eck on tsatt(end_ckp_key);
CREATE INDEX ckp_i_dbinc on ckp(dbinc_key);
For 10G Release 1 and 2:
SQL> CREATE INDEX rsr_i_dbinc on rsr(dbinc_key);
For 10G Release 2:
SQL> CREATE INDEX rout_i_db on rout(db_key);
CREATE INDEX rout_i_rsr on rout(rsr_key);
Generic Performance Issues which effect RMAN :
----------------------------------------------
Bug 2710321 X$KCCFN / V$DATAFILE much slower in 9i than 8i with many datafiles
Version affected : 9
Version fixed : 9203
Affects : Target database
Diagnosis : Many datafiles
RELATED DOCUMENTS
-----------------
Note 145624.1 : RMAN: Resolving an RMAN Hung Job
Note 228838.1 : RMAN Diagnostic Checklist
Note 605557.1 : RMAN BACKUPS CAN TAKE A LONG TIME IN 11.1.0.6
No comments:
Post a Comment