3 Replies Latest reply on Jan 18, 2005 8:03 PM by Beilstwh

    How Savepoint works

    398884
    398884 Jan 17, 2005 8:57 AM
      Hi All, I would like to know the internal functioning of SAVEPOINTS within oracle architecture, i.e., in conjunction with redo logs, writers etc. And, how the frequent use of SAVEPOINTS can reduce the performance of a program ?
      • 375 Views
      • Tags:
        • 1. Re: How Savepoint works
          The Human Fly
          The Human Fly Jan 17, 2005 9:55 AM (in response to 398884)
          SAVEPOINT gives a name to and marks a point in the processing of your transaction. This marker allows you to ROLLBACK TO that point, erasing any changes and releasing any locks issued after that savepoint, but preserving any changes and locks which occurred before you marked the savepoint.

          The syntax for the SAVEPOINT statement is:

          SAVEPOINT savepoint_name;
          where savepoint_name is an undeclared identifier. This means that it must conform to the rules for an Oracle identifier (up to 30 characters in length, starting with a letter, containing letters, numbers and #, $, or _ ), but that you do not need to (nor can you) declare that identifier.

          Savepoints are not scoped to PL/SQL blocks. If you reuse a savepoint name within the current transaction, that savepoint is "moved" from its original position to the current point in the transaction, regardless of the procedure, function, or anonymous block in which the SAVEPOINT statements are executed. As a corollary, if you issue a SAVEPOINT inside a recursive program, a new SAVEPOINT is executed at each level of recursion, but you can only roll back to the most recently marked savepoint.

          SJH
          OCP DBA
          • 2. Re: How Savepoint works
            398884
            398884 Jan 18, 2005 10:28 AM (in response to The Human Fly)
            Thanks Syed for ur response on the usage of SAVEPOINTs. I would like to know that how SAVEPOINT works internally using Oracle architecture. For eg., following happens when COMMIT is issued for changes made to data :

            1. The server process modifies data in the system global area. The DBWR process writes modified blocks permanently to disk. Because the transaction is committed, the LGWR process immediately records the transaction in the online redo log file.

            2. If the transaction is successful, then the server process sends a message across the network to the application. If it is not successful, then an error message is transmitted.

            I also want to know why frequent use of SAVEPOINT reduces performance.

            Cheers,
            Pratim
            • 3. Re: How Savepoint works
              Beilstwh
              Beilstwh Jan 18, 2005 8:03 PM (in response to 398884)
              Frequent uses of savepoint will not reduce performance. Frequent rollbacks to the savepoint will. A savepoint is nothing more then a pointer to a particular point in the rollback log. If you issue a rollback, everything is rolled back and all savepoints are removed. If you rollback to a savepoint, ONLY those uncommitted changes from the savepoint forward are rolledback. Every thing before the savepoint is still uncommitted. When you commit, all the savepoints are propped and all uncommited changes are committed.