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.

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

Table 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 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 '14 at 22:24

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.