I was working on a piece of code that has to perform a ‘cleanup’ of some tables by deleting data older than x months. Because this might be a long running piece of code I added several DBMS_APPLICATION_INFO calls, mostly to have info show up in V$SESSION (by using the SET_CLIENT_INFO and SET_ACTION subprograms.
The delete statement itself is executed in loops, so I can commit every x rows. Around the delete I use DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS so the progress can be tracked in V$SESSION.
Because I do this via a table that holds al rowid’s of the records to be deleted, I can populate the TOTALWORK-column of V$SESSION_LONGOPS. And because I know how much rows are enabled by one iteration, I can make the progress visible in V$SESSION_LONGOPS.
To update a row in the session_longops-view, the RINDEX-parameter of SET_SESSION_LONGOPS has to be supplied with the same value across calls. I think of it like some sort of rowid. So for every table I execute the delete on, I update the row in V$SESSION_LONGOPS to reflect the progress (x out of y rows deleted).
The mechanism used with DBMS_APPLICATION_INFO is as follows:
-- Get index value for new row in v$session_longops
lv_rowindex := DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT;
<<DELETE_IN_CHUNKS>>
LOOP
EXECUTE IMMEDIATE
Lv_rows_deleted := SQL%ROWCOUNT;
DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS(lv_rowindex, lv_slno, …, lv_rows_deleted, lv_rows_to_delete, …);
EXIT DELETE_IN_CHUNKS WHEN lv_deleted_rows = 0;
END LOOP;
But when I ran the code, I saw that for each table I performed this delete-loop on, I got two entries in V$SESSION_LONGOPS, both with the same counts but with slightly different start- and stop-timestamps. What was going on?
At first, I thought I must have been calling DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS_NOHINT too often, because this function gives a new ‘rowid’, thus creating double rows. But after looking hard at the code and debugging it, I concluded this was not the case.
What was it, then? My Oracle Support (aka Metalink), normally not my first resource, gave the answer after a long search.
Bug 3518980 states that the value of lv_rowindex, which is an IN OUT parameter of SET_SESSION_LONGOPS, will be reset if the total amount of work is reached. It even provides a testcase, which enabled me to reproduce easily.
My own loop always performs an extra loop to make sure all rows are deleted: if the delete-SQL%ROWCOUNT = 0 then we are done. But in the second to last loop all remaining rows were deleted already, thus setting the sofar-rowcount equal to totalwork. This causes a reset of the lv_rowindex IN OUT parameter to 0.
I’m not sure if Oracle considers this a bug or a feature, but it took me a while to figure it out! Be aware when using the total_work value, as soon as it equals the sofar-value you’re done as far as v$session_longops is concerned. So I rewrote my code to stop if the total amount of rows deleted equals the total count of rows to be deleted (aka the count of my rowid-table).
Oh, by the way, no matter what the bug says: I experienced this on Oracle 11.2.0.3.
Ronald's Oracle 