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.