1 2 Previous Next 18 Replies Latest reply: Jan 18, 2013 4:57 AM by 985260 RSS

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

    985260
      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
          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
            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
              @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
                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
                  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
                    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
                      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
                        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
                          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
                            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
                              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
                                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
                                  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
                                    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