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

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

FLASHBACK_TRANSACTION_QUERY Performance

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 10.1.0.4.  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?
0
biff999
Asked:
biff999
1 Solution
 
actonwangCommented:
slow peformance of the flashback query  might be caused by:
1.query itself
or
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.

acton
0
 
MohanKNairCommented:
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.
0

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