3 Replies Latest reply on Jun 23, 2012 1:36 PM by Aman....

    Need for Read Consistency

    706771
      Dear Members :

      [1] In later versions of Oracle (10g/11) the error "ora-01555 snapshot-too-old" almost never occurs - is this true ?

      [2] Why in the first place need for read-consistency, if that's misleading ?

      This is just to convince myself - although I never encountered.

      Thanks in advance.
      Atanu
        • 1. Re: Need for Read Consistency
          sb92075
          user11184124 wrote:
          Dear Members :

          [1] In later versions of Oracle (10g/11) the error "ora-01555 snapshot-too-old" almost never occurs - is this true ?
          false
          ORA-01555 is an application induced error.
          With proper implementation is can be avoided.
          [2] Why in the first place need for read-consistency, if that's misleading ?
          HUH?
          Oracle provides Read Consistent view of data when any/every transaction starts.
          • 2. Re: Need for Read Consistency
            Bobby Durrett
            The newer versions have the automatic undo management that helps with snapshot too old, but doesn't eliminate it. If you have a big enough undo tablespace and set undo retention high enough some types of ora-01555 errors will go away.

            One case where you can still get them is the "fetch across commit" case.

            This Oracle support document was really helpful for me:

            ORA-01555 "Snapshot too old" in Very Large Databases (if using Rollback Segments) [ID 45895.1]

            See case 4. Fetch across commit wont cause an ORA-01555 if you fetch from a temporary segment. So, if you add an order by on an unindexed column or insert the results of your query into a global temporary table and then fetch from it across the commit you can work around it. It is a slick trick.

            - Bobby
            • 3. Re: Need for Read Consistency
              Aman....
              user11184124 wrote:
              Dear Members :

              [1] In later versions of Oracle (10g/11) the error "ora-01555 snapshot-too-old" almost never occurs - is this true ?
              The important word here is almost which comes along with many things. If you have a sufficiently sized Undo tablespace and a properly configured Undo Retention period, its possible that you won't hit the 1555 error any time soon but that doesn't mean its gone for good. Its very much there and can definitely hit you.
              [2] Why in the first place need for read-consistency, if that's misleading ?

              This is just to convince myself - although I never encountered.
              I have given a reply for this in the below thread which also asked the same question. Have a read,
              about read consistency

              HTH
              Aman....