Posted on 2006-06-02
Last Modified: 2007-11-27
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?
Question by:biff999
    LVL 19

    Accepted 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.

    LVL 16

    Expert Comment

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Note: this article covers simple compression. Oracle introduced in version 11g release 2 a new feature called Advanced Compression which is not covered here. General principle of Oracle compression Oracle compression is a way of reducing the d…
    How to Unravel a Tricky Query Introduction If you browse through the Oracle zones or any of the other database-related zones you'll come across some complicated solutions and sometimes you'll just have to wonder how anyone came up with them.  …
    This video explains at a high level with the mandatory Oracle Memory processes are as well as touching on some of the more common optional ones.
    This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.

    779 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    13 Experts available now in Live!

    Get 1:1 Help Now