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?
