[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1500
  • Last Modified:


Has anyone used Oracle's FLASHBACK_TRANSACTION_QUERY to view previous db transactions?  I am trying to use it to view transactions made to a schema within the past 24 hours.  I've noticed that querying this view takes an extremely long time.  I'm talking 2 hours or more.  My undo tablespace size is about 1GB.  The version of my database is  The query I have been testing with is show below.

SQL> select xid, to_char(START_TIMESTAMP,'dd-mon-yyyy hh24:mi:ss'), LOGON_USER, OPERATION, TABLE_NAME, TABLE_OWNER from flashback_transaction_query where table_owner='TRAVEL' AND start_timestamp>=TO_TIMESTAMP ('2006/06/01 13:00:00', 'YYYY/MM/DD HH24:MI:SS');

Does anyone know how to make this query more efficient?
1 Solution
slow peformance of the flashback query  might be caused by:
1.query itself
2.undo operations.

for 1: you can use conventional methods to tune your query. In your case, make sure you have indexes on table_owner or start_timestamp.  you can disable flashback and try it to see if it is still slow.

for 2: if you have a lot of transactions occuring the time you go back till now. db needs to restore data from undo tablespace and it might take a long time. If that is the case, I don't think you can do anything about it at this point. to verify this, you can go back only at small amount of time and try it.

Check whether the statistics are uptodate. Flashback Query always uses the cost-based optimizer, which relies on these statistics.
Use index scans as much as possible. If FULL TABLE SCAN is required then use parallel hints also.

Featured Post

NEW Veeam Agent for Microsoft Windows

Backup and recover physical and cloud-based servers and workstations, as well as endpoint devices that belong to remote users. Avoid downtime and data loss quickly and easily for Windows-based physical or public cloud-based workloads!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now