Results 1 to 3 of 3
  1. #1
    Join Date
    Dec 2004
    Posts
    2

    Unanswered: reading from undo even after analyze

    Hi all-

    I have a query which is performing poorly since it is reading from UNDO, even though the query starts after the ANALYZE on the source tables, from which the query is pulling data. There is no DML operation on the source tables, while the query starts/running.
    Sometimes the query fails with ora-1555, when it crosses the undo retention of 3 hours.
    The db version is 9.2.0.5.0 - 64bit, on Sun Solaris.

    could any one help me in finding why the query is reading from undo? Any response is appreciated.

    Thanks-

  2. #2
    Join Date
    Aug 2003
    Location
    Where the Surf Meets the Turf @Del Mar, CA
    Posts
    7,776
    Provided Answers: 1
    http://asktom.oracle.com & do a key word search on ORA-01555

    I suspect that some session within Oracle IS changing data while the long running query is working to complete.

    Are you saying that the query in question runs for more than 3 hours?
    My gut says that this SQL would likely benefit from some tuning effort.
    You can lead some folks to knowledge, but you can not make them think.
    The average person thinks he's above average!
    For most folks, they don't know, what they don't know.
    Good judgement comes from experience. Experience comes from bad judgement.

  3. #3
    Join Date
    Dec 2004
    Posts
    2
    thanks for your reply. I did a compute statistics on source tables, instead of estimate, and query came out in 1 hour (which is expected).

    Thanks again-

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •