On our automated test system we use flashback all the time to reset our Oracle 11g database to a state before a testrun. 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
apperently at the 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 database with the resetlogs option a lot in our scripts.
what we already tried in rman and didn't see 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