Session Longops

One area that PL/SQL is commonly used for is large, batch-style data processing. Afterall, why pull large volumes of data out of the database, manipulate it and then push it back into the database when the process can be accomplished within the database by a language that is tailored for that type of activity?

Not only do bulk data processes manipulate large volumes of data but they are often long running. As with any long running process the question of “how long before it is finished?” is raised, particularly when the process is vitally important for the organisation or if other processes are waiting for it to complete. While historical statistics will give run time averages, the question of how far through an executing process has progressed is difficult to determine if the process hasn’t been designed to reveal that information.

One obvious strategy to obtain current execution details is for the process to log its progress, and I would highly recommend this strategy. However, this post is concerned with another mechanism for monitoring processes; session longops.

The supplied package DBMS_APPLICATION_INFO exposes the SET_SESSION_LONGOPS procedure. This routine is used to create an entry in the v$session_longops view and record processing progress. The details recorded are simply “processed X many widgets out of Y” so it is ideal for tracking progress through a collection of items. It is the responsibility of the process to invoke SET_SESSION_LONGOPS to update the progress statistics.

Let’s look at a simple scenario; we have a process that works its way through 120 “things” and “does stuff” for each one (feel free to substitute something relevant to you for “things” and “does stuff”). The code for this process may vaguely resemble:

SET ECHO ON

-- display the session SID so we can query
-- v$session_longops for this session
SELECT SYS_CONTEXT('userenv','sid') AS session_sid
FROM   dual
/

PAUSE

-- simply loop through an array of 120 entry, pausing for one
-- second and updating session longops per iteration
DECLARE
   -- create a associative array to store the "things" we process
   TYPE t_number_tab IS TABLE OF NUMBER INDEX BY BINARY_INTEGER;
   l_number_tab  t_number_tab;

   -- longops variables
   l_rindex             BINARY_INTEGER;
   l_slno               BINARY_INTEGER;
   l_sofar              NUMBER;
   l_totalwork          NUMBER;
   
   PROCEDURE do_stuff (p_number IN NUMBER)
   AS
   BEGIN
      -- we'll simulate activity by sleeping for a while...
      -- (and I'm sure there's a joke lurking in that statement somewhere...)
      dbms_lock.sleep(1);
   END do_stuff;

BEGIN   

   -- populate the array with 120 values
   FOR i IN 1..120
   LOOP
      l_number_tab(i) := i;
   END LOOP;
   
   -- create a new entry in the session longops view
   l_rindex    := dbms_application_info.set_session_longops_nohint;
   -- set the total amount of processing we expect to do
   l_totalwork := l_number_tab.COUNT; 
   -- set the current progress
   l_sofar := 0;
   -- initialise the session longops monitoring
   dbms_application_info.set_session_longops
     (rindex      => l_rindex
     ,slno        => l_slno
     ,op_name     => 'Doing stuff'
     ,target      => 0
     ,context     => 0
     ,sofar       => l_sofar
     ,totalwork   => l_totalwork
     ,target_desc => 'Array of things'
     ,units       => 'Things'); 
   
   -- simply loop through the elements in the array
   FOR i IN l_number_tab.FIRST..l_number_tab.LAST
   LOOP
      --"do stuff" for each array element
      do_stuff (l_number_tab(i));
	  
      -- increament the activity counter
      l_sofar := l_sofar + 1;
      -- update the session longops
      dbms_application_info.set_session_longops
         (rindex      => l_rindex
         ,slno        => l_slno
         ,totalwork   => l_totalwork
         ,sofar       => l_sofar); 
   END LOOP;

END;
/

The above script will display the session’s SID before continuing with the processing. We need the SID to feed into the following query, which we will run from a separate session to see how our process is going:

SET ECHO ON
SET DEFINE ON
SET LINESIZE 132

COLUMN opname FORMAT A15
COLUMN target FORMAT A15
COLUMN units  FORMAT A10

SELECT sid
,      opname
,      target
,      sofar
,      totalwork
,      units
,      start_time
,      time_remaining
FROM   v$session_longops
WHERE  sid = &sid
/

Running the above query whilst our processing script is execute we get (after 10 seconds since the our process commenced):

       SID OPNAME          TARGET               SOFAR  TOTALWORK UNITS      START_TIM TIME_REMAINING
---------- --------------- --------------- ---------- ---------- ---------- --------- --------------
       144 Doing stuff                             10        120 Things     03-AUG-11            110

The above output tells us that our processing has got through 10 “things” out of the 120 that will be processed. You’ll also note that the time remaining has been correctly calculated, which is a very nice touch to session longops. Obviously if the time per “thing” varies then Oracle may not be quite the “Oracle” that its name may imply…

If the “things” to process are being taken from a cursor then the number of entries may be unknown. Session longops does not require that the total work attribute be populated so this parameter may be left as NULL. Obviously in this scenario Oracle will be unable to calculate the time remaining.

---

Download scripts shown in this post.

Advertisement