Skip to content

Load Controlling for Rollback Segments

Rollback segments hold the data to undo/rollback a transaction throughout its' lifetime. Lifetime of a transaction is identified/delimited by either a COMMIT or complete ROLLBACK (not ROLLBACK TO SAVEPOINT). Even if a transaction is computationally intensive and consequently, take a long time to complete, it will not load the rollback segments if the amount of data modifications done by it is not considerable. Albeit this may be the case for ordinary business logic, when it comes to upgrades it turn out to be different. That is, in an upgrade, data is changed in large volumes. In such cases rollback segments have to store all the undo information throughout this long transaction which account to mass amounts in space as well as time. As a result, rollback segments will enlarge uncontrollably and extend towards virtual memory. This affects the execution times of all the processes at an upgrade.

Remedy for this is to execute COMMITs from time to time in a long-running data intensive process, instead of waiting until the whole process to be finished to COMMIT.

Example:

Most widely accepted technique for issuing intermediate commits is to use bulk fetches with limits.

DECLARE
   TYPE serviceline_t IS TABLE OF get_line_rec%ROWTYPE INDEX BY BINARY_INTEGER;

   service_invoice_line_collection_ serviceline_t;

   CURSOR get_line_rec IS
      SELECT service_invoice_id, service_invoice_line
      FROM   SERVICE_INVOICE_LINE_TAB
      WHERE  zero_invoice_amount IS NULL;      
BEGIN  
   OPEN get_line_rec;

   LOOP
      FETCH get_line_rec BULK COLLECT INTO service_invoice_line_collection_ LIMIT 100;
      EXIT WHEN service_invoice_line_collection_.COUNT = 0;

      FORALL i IN service_invoice_line_collection_.FIRST..service_invoice_line_collection_.LAST
         UPDATE SERVICE_INVOICE_LINE_TAB
         SET    ZERO_INVOICE_AMOUNT  = 'YES'
         WHERE  service_invoice_id   = service_invoice_line_collection_(i).service_invoice_id 
         AND    service_invoice_line = service_invoice_line_collection_(i).service_invoice_line;

      COMMIT;
   END LOOP;

   CLOSE get_line_rec;
END;

Notes:

  • Committing a transaction also has an overhead of its' own and, doing so too frequently may also cause performance to suffer.
  • Do not make any assumptions (or hopes) about the data modifications that follows a specific commit when issuing that COMMIT command. Those data may be lost without a subsequent COMMIT due to numerous reasons.
  • Often the solutions for problems in rollback segments will add an additional overhead to code complexity and detrimental to readability, maintainability etc. Therefore, spare those techniques to places where there is an actual requirement, instead of applying them as a common practice.
  • Consider the need of clearing/deleting/purging the data structures that are used in bulk operations at the end.