Boost your RMAN catalog!

We have a couple of RMAN catalogs holding details for hundreds of databases. The more databases we added to the catalog, the more time RMAN needs to query some of the catalog tables. The CPUs are starving!

For the following statements we have created an index on the corresponding table:

– SQL ID: 9h3pcpkzpr0vc

SELECT NVL(MIN(SCN),POWER(2,64)-1)
FROM (
SELECT MIN(BRL.LOW_SCN) SCN
FROM BRL
WHERE BRL.DBINC_KEY = :B1 UNION
SELECT MIN(AL.LOW_SCN)
FROM AL
WHERE AL.DBINC_KEY = :B1 UNION
SELECT MIN(XAL.LOW_SCN)
FROM XAL
WHERE XAL.DBINC_KEY = :B1 UNION
SELECT MIN(BDF.CKP_SCN)
FROM BDF
WHERE BDF.DBINC_KEY = :B1 UNION
SELECT MIN(CDF.CKP_SCN)
FROM CDF
WHERE CDF.DBINC_KEY = :B1 UNION
SELECT MIN(XDF.CKP_SCN)
FROM XDF
WHERE XDF.DBINC_KEY = :B1 UNION
SELECT MIN(BCF.CKP_SCN)
FROM BCF
WHERE BCF.DBINC_KEY = :B1 UNION
SELECT MIN(CCF.CKP_SCN)
FROM CCF
WHERE CCF.DBINC_KEY = :B1 UNION
SELECT MIN(XCF.CKP_SCN)
FROM XCF
WHERE XCF.DBINC_KEY = :B1 )

-> indexes:

create index bdf_ix1 on bdf (dbinc_key, ckp_scn);
create index bcf_ix1 on bcf (dbinc_key, ckp_scn);
create index brl_ix1 on brl (dbinc_key, low_scn);

– SQL ID: dnqhtfqr9bgj9

DELETE FROM RLH WHERE RLH.DBINC_KEY = :B2 AND NEXT_SCN < :B1

-> index:

create index rlh_ix1 on rlh (dbinc_key, next_scn);

– SQL ID: fav6r1wb207hm

DELETE FROM BS 
WHERE DB_KEY = :B1 
AND ((INPUT_FILE_SCAN_ONLY='YES' AND SYSDATE - COMPLETION_TIME >= 180) 
OR (NVL(INPUT_FILE_SCAN_ONLY,'NO')='NO' AND STATUS='D'))

-> index:

create index bs_ix1 on  bs (db_key, input_file_scan_only, status, completion_time);

Hope it helps!