The great thing about concurrent programs is that they run in the background, busily getting their job done without pestering you. There is a price you pay for this apparent hassle free life, and you may no even know you’re paying for it.
Like all other programs, system resources are needed to satisfy concurrent programs; and as we all know, system resources are the limited and valuable. The price you, or rather you and your DBA, are paying for background processes is the lack of visibility of the use, and potential abuse, of these system resources.
It is primarily the role of the DBA to protect the system, and ensure that no rogue processes eat up CPU or hog the disks, so why as a developer should you care? Well, many DBAs have what developers call ‘a personality defect’; this is when a DBA asks loudly in the middle of the office the equivalent of “who’s been trip-trapping over my bridge?”, with the corresponding ugly troll look and tact. This can be mitigated, and if done right the DBA can owe you one, by judicious use of the DBMS_APPLICATION_INFO package.
This Oracle built-in package is there to allow the DBA to have more detail of a running process; the value of ‘module’ is automatically set by Apps to the name of the concurrent request and the value of ‘action’ is automatically set by Apps to ‘Concurrent Request’. This can be seen in TOAD using DBA > Session Browser, or can be seen using:
SELECT s.SID, s.SERIAL#, s.MODULE, s.ACTION
FROM V$SESSION s
The value that is not set automatically by Apps is the running state of the process, because Apps has no idea what the process is attempting to do. It is this that we need to update in our concurrent programs. Long running operations have visibility to the DBA via the view V$SESSION_LONGOPS; the SQL engine posts messages to this view for known long running SQL operations such as table scans, which is useful in itself, but this view can also be updated from within a concurrent program using:
This call should be made throughout the concurrent program, preferably via a liberal scattering of a function call, to show the progress that is being made by the current operation. The following are a few notes that should make it easier to use.
1. The initial value of rindex should be set to the package constant dbms_application_info.set_session_longops_nohint;
2. The value of slno should be null and never updated by you.
3. The value of op_name is set once per row and isn’t updated for the same row on subsequent calls.
4. The value of target should be null, or the object_id (from all_objects) of the object being worked on. For this to be correctly translated in to an object name, the parameter target_desc must explicitly be set to NULL. This is set once per row and isn’t updated for the same row on subsequent calls.
5. The value of sofar is the value that you would change on each call, usually the percentage done.
6. The value of totalwork is set once per row and isn’t updated for the same row on subsequent calls. Calls with the same parameters for everything else, but a different value for totalwork force a new row to be written.
7. The value of target_desc is set once per row and isn’t updated for the same row on subsequent calls.
8. The value of units is set once per row and isn’t updated for the same row on subsequent calls.
Here is a little test program illustrating its use.
IF r IS NULL THEN
r := dbms_application_info.set_session_longops_nohint;
FOR a IN 1 .. 100 LOOP
(rindex => r
,slno => s
,op_name => ‘TestOpName’
,target => t
,sofar => a
,totalwork => 100
,target_desc => ‘Description Msg’
,units => ‘TestUnits’
— ADD DELAY HERE IF REQD TO SEE IN ACTION
In a different session, run the following query to view the running program:
WHERE opname = ‘TestOpName’
Ok, so background processes aren’t really Elves, and checking a view that you have purposefully written to isn’t exactly spying, but hey… use your imagination!