This discussion is archived
1 2 Previous Next 18 Replies Latest reply: Jan 18, 2013 2:57 AM by 985260 RSS

ORA 1555 - snapshot to old when querying over a DB link

985260 Newbie
Currently Being Moderated
Hi Guys,

I have a challenging question for you. I'm not an Oracle expert and I have an issue with a production database when executing a query over a DB Link - we encountered ORA-1555 snapshot to old. The query doesn't take too long, it normally ends in less than a second. The error happen only in week-ends, when the application is not used very much.

From what I read Oracle creates a distributed transaction and reserves an entry in the rollback segment when querying over a DB Link - please see http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

I've reproduced this error on the test server by reducing the UNDO_TABLASPACE size and set UNDO_RETENTION to 5 minutes.

Now I'm trying to fix it. The only solution that I have at the moment is to execute a COMMIT operation after each query that is using a DB_LINK. This doesn't look a very nice fix, it is more a hack.

Do you know a better solution? We don't need the distributed transaction that oracle automatically creates for this queries. We are just querying this database, we never insert/update/delete data.

I've tried SET TRANSACTION READ ONLY, doesn't help, the transaction is created.

Thank you very much!
  • 1. Re: ORA 1555 - snapshot to old when querying over a DB link
    sb92075 Guru
    Currently Being Moderated
    the session that reports ORA-01555 is the victim; not necessarily the culprit.
    does the session reporting ORA-01555 do DML?
    does the session reporting ORA-01555 only issue SELECT?
  • 2. Re: ORA 1555 - snapshot to old when querying over a DB link
    TSharma-Oracle Guru
    Currently Being Moderated
    set undo_retention to 0 should fix the issue.

    Also, Can you confirm the size on undo tablespace?
  • 3. Re: ORA 1555 - snapshot to old when querying over a DB link
    985260 Newbie
    Currently Being Moderated
    @sb92075

    ORA-1555 error is encountered on the server that is queried over the DB_LINK. DML are performed on both servers.
    The sesion that reports ora-1555 is querying the database only (some queries are using NOT EXISTS clause with a local table).

    I've reproduced it using this scenario:

    Session 1: perform a query over a view. This view selects data from a remote table using NOT EXIST clause with a local table; Everything is fine now.
    Session 2: perform many inserts/updates/deletes on a different table than the one that is queried over the DB_LINK
    Session 2: perform few inserts/updades on the table that is queried over the DB_LINK
    Session 1: run the same query - now ORA-1555 is raised

    Session 1 - is the session that is executing the query over a DB_LING
    Session 2 - is a session on the other server

    @TSharma
    I cannot change this value on the production server, I'll run a test on the test environment.
    UNDO_TABLASPECE size is set to 100 GB in production. At the moment is 49% used.

    Thanks!
  • 4. Re: ORA 1555 - snapshot to old when querying over a DB link
    sb92075 Guru
    Currently Being Moderated
    982257 wrote:
    @sb92075

    ORA-1555 error is encountered on the server that is queried over the DB_LINK.
    DML are performed on both servers.
    The problem is not the DML itself; but intervening COMMIT.

    is DML & COMMIT done inside a LOOP?
    if so, that is source of the problem.
  • 5. Re: ORA 1555 - snapshot to old when querying over a DB link
    985260 Newbie
    Currently Being Moderated
    The problem is not the DML itself; but intervening COMMIT.

    is DML & COMMIT done inside a LOOP?
    Yes, it is in the test environment. The purpose of this code is to generate UNDO logs and reproduce the issue.
    if so, that is source of the problem.
    Why this code is the source of the problem? I have a session that is executing the same query two times. The first time is running without errors and the second time is throwing ora-1555. When the query is executed no other operation are performed on both servers. The DML & COMMIT LOOP is performed after the first query is executed. After this operation is finished (the LOOP) the same query is executed and the error is throw.
  • 6. Re: ORA 1555 - snapshot to old when querying over a DB link
    sb92075 Guru
    Currently Being Moderated
    982257 wrote:
    The problem is not the DML itself; but intervening COMMIT.

    is DML & COMMIT done inside a LOOP?
    Yes, it is in the test environment. The purpose of this code is to generate UNDO logs and reproduce the issue.
    if so, that is source of the problem.
    Why this code is the source of the problem? I have a session that is executing the same query two times. The first time is running without errors and the second time is throwing ora-1555. When the query is executed no other operation are performed on both servers. The DML & COMMIT LOOP is performed after the first query is executed. After this operation is finished (the LOOP) the same query is executed and the error is throw.
    Oracle is REQUIRED to provide a Read Consistent View of the data.
    So a long running SELECT starts to read rows from a table.
    Another session does DML against same table.
    Original session does not have a problem since it can get original/Before Image data from UNDO.
    DML session issues COMMIT; which allows the UNDO data to be overwritten.
    Once the UNDO data is overwritten, then original SELECT can no longer obtain Read Consistent data &
    ORA-01555 gets thrown.
  • 7. Re: ORA 1555 - snapshot to old when querying over a DB link
    985260 Newbie
    Currently Being Moderated
    sb92075 wrote:
    982257 wrote:
    The problem is not the DML itself; but intervening COMMIT.

    is DML & COMMIT done inside a LOOP?
    Yes, it is in the test environment. The purpose of this code is to generate UNDO logs and reproduce the issue.
    if so, that is source of the problem.
    Why this code is the source of the problem? I have a session that is executing the same query two times. The first time is running without errors and the second time is throwing ora-1555. When the query is executed no other operation are performed on both servers. The DML & COMMIT LOOP is performed after the first query is executed. After this operation is finished (the LOOP) the same query is executed and the error is throw.
    Oracle is REQUIRED to provide a Read Consistent View of the data.
    So a long running SELECT starts to read rows from a table.
    Another session does DML against same table.
    Original session does not have a problem since it can get original/Before Image data from UNDO.
    DML session issues COMMIT; which allows the UNDO data to be overwritten.
    Once the UNDO data is overwritten, then original SELECT can no longer obtain Read Consistent data &
    ORA-01555 gets thrown.
    This scenario is valid only for long running queries, right? In my case the query is executed in less than a second.

    Why everything is working fine if I execute commit after the query?
  • 8. Re: ORA 1555 - snapshot to old when querying over a DB link
    sb92075 Guru
    Currently Being Moderated
    982257 wrote:
    sb92075 wrote:
    982257 wrote:
    The problem is not the DML itself; but intervening COMMIT.

    is DML & COMMIT done inside a LOOP?
    Yes, it is in the test environment. The purpose of this code is to generate UNDO logs and reproduce the issue.
    if so, that is source of the problem.
    Why this code is the source of the problem? I have a session that is executing the same query two times. The first time is running without errors and the second time is throwing ora-1555. When the query is executed no other operation are performed on both servers. The DML & COMMIT LOOP is performed after the first query is executed. After this operation is finished (the LOOP) the same query is executed and the error is throw.
    Oracle is REQUIRED to provide a Read Consistent View of the data.
    So a long running SELECT starts to read rows from a table.
    Another session does DML against same table.
    Original session does not have a problem since it can get original/Before Image data from UNDO.
    DML session issues COMMIT; which allows the UNDO data to be overwritten.
    Once the UNDO data is overwritten, then original SELECT can no longer obtain Read Consistent data &
    ORA-01555 gets thrown.
    This scenario is valid only for long running queries, right? In my case the query is executed in less than a second.
    any SELECT, fast or slow, that can not obtain Read Consistent data will throw ORA-01555 error.
    Why everything is working fine if I execute commit after the query?
    COMMIT terminates previous transaction & effectively starts a new transaction.
    the data obtained by SELECT will be Read Consistent starting with SCN of the COMMIT.
  • 9. Re: ORA 1555 - snapshot to old when querying over a DB link
    985260 Newbie
    Currently Being Moderated
    sb92075 wrote:
    982257 wrote:
    sb92075 wrote:
    982257 wrote:
    The problem is not the DML itself; but intervening COMMIT.

    is DML & COMMIT done inside a LOOP?
    Yes, it is in the test environment. The purpose of this code is to generate UNDO logs and reproduce the issue.
    if so, that is source of the problem.
    Why this code is the source of the problem? I have a session that is executing the same query two times. The first time is running without errors and the second time is throwing ora-1555. When the query is executed no other operation are performed on both servers. The DML & COMMIT LOOP is performed after the first query is executed. After this operation is finished (the LOOP) the same query is executed and the error is throw.
    Oracle is REQUIRED to provide a Read Consistent View of the data.
    So a long running SELECT starts to read rows from a table.
    Another session does DML against same table.
    Original session does not have a problem since it can get original/Before Image data from UNDO.
    DML session issues COMMIT; which allows the UNDO data to be overwritten.
    Once the UNDO data is overwritten, then original SELECT can no longer obtain Read Consistent data &
    ORA-01555 gets thrown.
    This scenario is valid only for long running queries, right? In my case the query is executed in less than a second.
    any SELECT, fast or slow, that can not obtain Read Consistent data will throw ORA-01555 error.
    Why everything is working fine if I execute commit after the query?
    COMMIT terminates previous transaction & effectively starts a new transaction.
    the data obtained by SELECT will be Read Consistent starting with SCN of the COMMIT.
    So when the query is run for the first time the SCN is retain by Oracle, And when the next query is run Oracle is using the same SCN, even so this is a different run of the query?

    In this case perform a COMMIT after each query that is using the DB_LINK is the only solution?

    I've tried this scenario using two local tables, it never happens. Any ideas why?
  • 10. Re: ORA 1555 - snapshot to old when querying over a DB link
    John Spencer Oracle ACE
    Currently Being Moderated
    982257 wrote:
    Hi Guys,

    I have a challenging question for you. I'm not an Oracle expert and I have an issue with a production database when executing a query over a DB Link - we encountered ORA-1555 snapshot to old. The query doesn't take too long, it normally ends in less than a second. The error happen only in week-ends, when the application is not used very much.

    From what I read Oracle creates a distributed transaction and reserves an entry in the rollback segment when querying over a DB Link - please see http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

    I've reproduced this error on the test server by reducing the UNDO_TABLASPACE size and set UNDO_RETENTION to 5 minutes.

    Now I'm trying to fix it. The only solution that I have at the moment is to execute a COMMIT operation after each query that is using a DB_LINK. This doesn't look a very nice fix, it is more a hack.
    No it is not a hack, it is the correct solution. When you open a db link it starts a transaction on the remote database, and t hat transaction needsto be ended before the db link can be closed. As long as the transaction remains open the entry in the rollback segment (undo segment these days) is required and cannot be released hence the 1555. If you dislike commiting after a select, you could always rollback :-)
    Do you know a better solution? We don't need the distributed transaction that oracle automatically creates for this queries. We are just querying this database, we never insert/update/delete data.

    I've tried SET TRANSACTION READ ONLY, doesn't help, the transaction is created.

    Thank you very much!
    You may not need the distributed transaction, but Oracle does.

    John
  • 11. Re: ORA 1555 - snapshot to old when querying over a DB link
    985260 Newbie
    Currently Being Moderated
    John Spencer wrote:
    982257 wrote:
    Hi Guys,

    I have a challenging question for you. I'm not an Oracle expert and I have an issue with a production database when executing a query over a DB Link - we encountered ORA-1555 snapshot to old. The query doesn't take too long, it normally ends in less than a second. The error happen only in week-ends, when the application is not used very much.

    From what I read Oracle creates a distributed transaction and reserves an entry in the rollback segment when querying over a DB Link - please see http://www.jlcomp.demon.co.uk/faq/dblink_commit.html

    I've reproduced this error on the test server by reducing the UNDO_TABLASPACE size and set UNDO_RETENTION to 5 minutes.

    Now I'm trying to fix it. The only solution that I have at the moment is to execute a COMMIT operation after each query that is using a DB_LINK. This doesn't look a very nice fix, it is more a hack.
    No it is not a hack, it is the correct solution. When you open a db link it starts a transaction on the remote database, and t hat transaction needsto be ended before the db link can be closed. As long as the transaction remains open the entry in the rollback segment (undo segment these days) is required and cannot be released hence the 1555. If you dislike commiting after a select, you could always rollback :-)
    Right, a rollback will also work :)
    >
    Do you know a better solution? We don't need the distributed transaction that oracle automatically creates for this queries. We are just querying this database, we never insert/update/delete data.

    I've tried SET TRANSACTION READ ONLY, doesn't help, the transaction is created.

    Thank you very much!
    You may not need the distributed transaction, but Oracle does.

    John
    We have a lot of java code that is executing queries over a DB link and need to be changed. Do you know a solution to make this commits/rollbacks automatically?
  • 12. Re: ORA 1555 - snapshot to old when querying over a DB link
    John Spencer Oracle ACE
    Currently Being Moderated
    982257 wrote:

    We have a lot of java code that is executing queries over a DB link and need to be changed. Do you know a solution to make this commits/rollbacks automatically?
    I'm not a java person, but I believe that one of the attributes of a connection object controls auto commits. It may even be a statement object attribute.

    John
  • 13. Re: ORA 1555 - snapshot to old when querying over a DB link
    Dave Rabone Journeyer
    Currently Being Moderated
    Hmm .. I'm not sure either John's or sb92075's analysis explains this completely.

    John - I don't see how the retained undo associated with the uncommited distributed transaction could have any influence on the availability of undo for read consistency.

    sb92075 - you almost imply that a distributed transaction is serializable, which is certainly not true.

    This issue has strange echoes for me of something I saw and ignored as "one of those things" just last week. I was selecting through a dblink, but needed to make some changes in the source table which I did with a direct connection to the source database. So it went like this:

    Session 1 - select through dblink
    Session 2 - update (directly) the source table and commit
    Session 1 - repeat select through dblink ... No change ... Did I forget to commit ... No
    Session 1 - repeat select through dblink ... Expected result!

    So what happened at step 3 ... I suspect I got an "old" read consistent image, which is what OP is seeing too, but in his case it causes a 01555 error. Why ... Not sure, but there are some oddities to do with syncing of local and remote SCNs ... documented somewhere which I wasn't able to find quickly tonight.

    It may well be related to the well publicized dblink "DOS" exploit from last year, so the actual detailed behavior is likely to be highly version and patch level dependent on both sides of the dblink.

    To OP ... If your test case is reproducible I'd be intrigued to see what happens if you substitute

    Select * from dual@dblink

    for the suggested commit or rollback between your selects.

    Why ... My strange memory of the doc I couldn't locate ... Without rereading it I couldn't begin to say why.

    Interesting problem to try to think about ...

    Regards,
    Dave
  • 14. Re: ORA 1555 - snapshot to old when querying over a DB link
    985260 Newbie
    Currently Being Moderated
    Thanks for your reply. To be sure that I've fully understood, do you want me to replace the commits with select * from dual@dblink?

    So the steps will be:
    Session 1 - select through dblink
    Session 1 - select from dual@dblink
    Session 2 - perform inserts/updates
    Session 1 - select through dblink
    Session 1 - select from dual@dblink
1 2 Previous Next

Legend

  • Correct Answers - 10 points
  • Helpful Answers - 5 points