On our automated test system we use flashback all the time to reset our Oracle 11g database to a state before a test run. We noticed some of the scripts involved, started to become slower and slower. In particular the following RMAN statement takes a very long time:
delete archivelog all
Apparently, at that time the following SQL is executing:
SELECT DBINC.RESETLOGS_CHANGE# FROM (SELECT RESETLOGS_CHANGE#, PRIOR RESETLOGS_CHANGE# NEXT_RESETLOGS_CHANGE# FROM V$DATABASE_INCARNATION START WITH RESETLOGS_CHANGE# = :B2 AND RESETLOGS_TIME = :B1 CONNECT BY PRIOR PRIOR_INCARNATION# = INCARNATION# ) DBINC WHERE DBINC.RESETLOGS_CHANGE# <= :B3 AND DBINC.NEXT_RESETLOGS_CHANGE# > :B3
V$DATABASE_INCARNATION on our system already contains 72K entries. Probably because we open the database with the Resetlogs option a lot in our scripts.
What we already tried in RMAN but didn't seem to help much:
sql "alter session set optimizer_mode=RULE";
Any sugestions on how we can make this faster?
Oracle Database version 22.214.171.124.0 on a Windows 2008R2