Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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 11.2.0.1.0 on a windows 2008R2

share|improve this question
    
Have you tried to re-create the control file? That would clean up things. –  fnds Jan 14 at 22:24
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Browse other questions tagged or ask your own question.