Oracle – for when it was like that when you got there

Main menu

Post navigation

DBMS_APPLICATION_INFO – Are we nearly there yet ?

Deb has come to the conclusion that, when on a long car journey, I’m not a great passenger.
“Are we nearly there yet ?” I enquire politely…usually around five minutes into a four-hour journey.
“No, not yet”, comes the patient reply.
“Are we almost nearly there yet ?”
“No, I’ll let you know when we are”, she responds with iron patience.
A few minutes pass…
“Are we almost nearly almost there ?”
At this point, I’m usually offered the option of walking the rest of the way.
It’s the same with long-running programs on the database. I want to know how far it’s gone and how far it’s got to go.
Oh, what Deb would do for an in-car equivalent of DBMS_APPLICATION_INFO.

Deb would probably have some sympathy with the DBA who gets a call from a user who has a process running and is, essentially, asking “are we nearly there yet ?”
Fortunately, with a bit of foresight and the judicious application of a little DBMS_APPLICATION_INFO magic, the answer to this question is right there in V$SESSION, or even V$SESSION_LONGOPS.

How far have we got ?

DBMS_APPLICATION_INFO provides the facility to set values in the client_info, module and action columns of the V$SESSION view programatically.
If we have a job that’s performing a number of different operations sequentially, we could do something like this :

To monitor this, we need to know which session to look for in V$SESSION and then check the progress in a separate session.
To find out which session we’re looking for, we need to know the audsid of the session we’re monitoring. So, before we kick off the program we need to :

SELECT userenv('sessionid') FROM dual;

This gives us the value of the audsid column in V$SESSION for the session we’re monitoring.
Once we’ve started execution of the program in the session, we then need to switch to the monitoring session and run :

Note to self – once you get over 10000 records processed on this example, the value we’re trying to set for action is too long for the column in V$SESSION. Fortunately, DBMS_SESSION simply truncates the ACTION value to 32 characters, hence the missing s at the end of records.
I think I’ve managed to talk my way out of that one.
Incidentally, the same applies to values you set for module ( 48 characters) and client_info (64). If ever you forget which is which, just do a describe on V$SESSION and note the length of the relevant columns.

Are we nearly there yet ? – The progress bar

Of course, if you just want details of what percentage of the total rows have been processed, you simply need to change the information you pass to the package

Are we almost nearly there yet?

OK, but what if you really, really want to know how much longer Oracle thinks this is going to take ?
Step forward SET_SESSION_LONGOPS. This will write the monitoring information to V$SESSION_LONGOPS and give you an estimate of the execution time remaining.
To demonstrate, here’s the source for an appropriately name procedure :

Obviously, you need to replace the sid and serial# values with those of the session you’re monitoring.

A point to note here – the estimated completion time is always going to be a “best guess”. In the same way that a golfer who has just completed the first 14 holes of a round in par cannot predict that they will play the next four holes in par, Oracle can only base it’s prediction on what has happened so far. The actual time it takes to complete the process will always depend on what else is happening on the database, and the server it’s running on….even if you cheat outrageously and use DBMS_LOCK.SLEEP.

We’re off on holiday next month. Flying across the Atlantic to Canada. Deb’s already started getting some things she claims we need for the trip, including for some reason, a gag. When I questioned this particular purchase, she simply said, archly, that it would help with the headaches she tends to get on long trips with me.
I’ve no idea what she’s on about…