Using DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS to see "Are we there yet?"

Oracle has actually made it pretty easy to track things.  The DBMS_APPLICATION_INFO package has some really simple to use procedures to help us see what is happening in our code.

Something that is likely under used is the SET_SESSION_LONGOPS procedure.

Using this in your code and you can really know "Are we there yet?" and more importantly, how close to "there" you are.

The procedure does look a tad confusing when you look at its description and the Oracle docs are a bit thin on it as well.  (In defense of the doc writers, they tend to write more robustly for features that are well used.  Obscure features don't get much press and hence the doc writing will not have the time to focus on writing a verbose description.)

The basic idea with this procedure is to "publish" or "register" your running application into the view V$SESSION_LONGOPS where you can then query that view to see how it's going.

Here is the description of the procedure.

DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS (

   rindex      IN OUT BINARY_INTEGER,

   slno        IN OUT BINARY_INTEGER,

   op_name     IN     VARCHAR2       DEFAULT NULL,

   target      IN     BINARY_INTEGER DEFAULT 0,

   context     IN     BINARY_INTEGER DEFAULT 0,

   sofar       IN     NUMBER         DEFAULT 0,

   totalwork   IN     NUMBER         DEFAULT 0,

   target_desc IN     VARCHAR2       DEFAULT 'unknown target',

   units       IN     VARCHAR2       DEFAULT NULL) 

set_session_longops_nohint constant BINARY_INTEGER := -1;

So what does all that mean?  The rindex and slno values are (as far as we’re concerned) combined into a key used to identify the row we are working with in v$session_longops.  We get a new row by setting the rindex to set_session_longops_nohint, and then the returned values for both will be used in subsequent calls.  

The rest of the parameters are very much up to us to decide what they are.  The op_name is a key field we could use to see the information we want. This corresponds to the column of the same name in v$session_longops.  Of course you could also use other columns like sid and serial# form the view as well.

Let take a look at an example.  This code is just setup to do some mindless work just so it takes some time to complete so we can see something going on. 

In one session I’ll run this code block:

DROP TABLE SSL_DEMO;

CREATE TABLE SSL_DEMO
  (SSL_KEY    NUMBER(8),
   SSL_STRING VARCHAR2(10),
   SSL_DATE   DATE);  

DECLARE
  rindex    BINARY_INTEGER;
  slno      BINARY_INTEGER;
  totalwork number;
  sofar     number;
  obj       BINARY_INTEGER; 
begin
  -- This will get a new row in v$session_long_ops
  rindex := dbms_application_info.set_session_longops_nohint;
  -- Haven't processed any rows sofar
  sofar := 0;
  -- We will process 200,000 when were done
  totalwork := 200000;
  -- Puting the object id into the context parameter
  select object_id into obj from user_objects where object_name = 'SSL_DEMO';
  for i1 in 1..1000 loop
    for i2 in 1..100 loop
      insert into ssl_demo
        values (i2, DBMS_RANDOM.STRING('a', 10), sysdate);
      sofar := sofar + 1;
    end loop;
    for i3 in 1 .. 100 loop
      delete from ssl_demo where ssl_key = i3;
      sofar := sofar + 1;
    end loop;
    -- Here is where we first get a row then update it in v$session_longops
    dbms_application_info.set_session_longops(rindex, slno,
    'RPM SSL DEMO', obj, 0, sofar, totalwork, 'Table Work', 'Rows Processed');
  end loop;
end;
/

Notice that in the block I keep a simple count of the rows as they are inserted and deleted by just incrementing sofar.  Then at the end of each set up inserts and deletes I use set_session_longops to update the view.  While that runs I’ll run this in another session.

select opname, target_desc, elapsed_seconds, time_remaining, (sofar/totalwork)*100 PCT
from V$SESSION_LONGOPS
where opname = 'RPM SSL DEMO';

The output from this query would look something like this (each line is a re-run of the query):

OPNAME         TARGET_DESC    ELAPSED_SECONDS TIME_REMAINING PCT Done
-------------- -------------- --------------- -------------- --------
RPM SSL DEMO   Table Work                4.00          45.00     8.10
RPM SSL DEMO   Table Work               50.00          31.00    61.40
RPM SSL DEMO   Table Work               71.00          18.00    79.50
RPM SSL DEMO   Table Work               88.00           7.00    92.60
RPM SSL DEMO   Table Work               98.00            .00   100.00

The time remaining column isn’t perfect but it’s a pretty good indication of how long it will take to complete.

Selecting the message column from v$session_longops while the block is running would look like this:

select message from V$SESSION_LONGOPS where opname = 'RPM SSL DEMO';
RPM SSL DEMO: Table Work 95705: 73200 out of 200000 Rows Processed done
RPM SSL DEMO: Table Work 95705: 135400 out of 200000 Rows Processed done
RPM SSL DEMO: Table Work 95705: 200000 out of 200000 Rows Processed done

Kinda cool eh?