User friendly / supported monitoring of concurrent processes

Yes, I know everyone else is having a great time at OOW, but some of us are back in the real world still.

I've asked a question on OTN (under EBS General Discussion) Best way to execute / monitor long running custom conc request with slave.

Can anyone help me with suggestions for an EBS-supported API (11.5.10 on Solaris 10 / Oracle 9iR2) that would enable the professional user who launched a (PL/SQL) concurrent process to monitor its progress over several hours from his/her application UI? To add to the fun, the process is going to spawn some slaves to make use of all the spare CPUs / cores / threads we have lying around.

As a developer, I would normally start with the DBMS_APPLICATION_INFO.SET_SESSION_LONGOPS procedure (and I'll build that in anyhow) - but in this case I'm struggling to find any documentation or ML notes. to point me at something that would actually appear on the apps UI.

Answers here - or better still, on the OTN thread. Thanks in advance!

SYS_CONTEXT versus V$ views for getting session information

A thread on Oracle-L today sidetracked into the use of V$ views for getting hold of session information such as username, SID and module. The poster had a logon trigger that was supposed to record these items for each logon from a particular client.

I don't approve of granting access to V$ views willy nilly; best practice is always to grant the minimum privileges necessary to achieve an objective.

Another poster raised the issue of performance. In the past, SYS_CONTEXT was considered slower than direct access to the views.

So here is a test to compare the two:

---

set echo off feedback off
set timing on

set termout off

variable v_loops number;
exec :v_loops := 1000000;

set termout on

prompt Testing sys_context

declare
 l_user varchar2(30);
 l_action varchar2(32);
 l_module varchar2(48);
 l_sid number;
        l_loopcount pls_integer := :v_loops;
begin
    for i in 1..l_loopcount loop
 dbms_application_info.read_module(l_module, l_action);
 l_user := sys_context('userenv', 'session_user');
 l_sid := sys_context('userenv', 'sessionid');
    end loop;
end;
/


prompt Testing mystat

declare
 l_user varchar2(30);
 l_action varchar2(32);
 l_module varchar2(48);
 l_sid number;
        l_loopcount pls_integer := :v_loops;
begin
    -- note this only gets one of the three pieces of information
    for i in 1..l_loopcount loop
 select sid
 into   l_sid
 from   v$mystat
 where rownum = 1;
    end loop;
end;
/

---

And here are the results:

C:\sql>sqlplus testuser/testuser

SQL*Plus: Release 10.2.0.1.0 - Production on Tue Feb 26 22:23:10 2008

Copyright (c) 1982, 2005, Oracle.  All rights reserved.


Connected to:
Oracle Database 10g Express Edition Release 10.2.0.1.0 - Production

SQL> @sys_context_test
Testing sys_context
Elapsed: 00:00:07.31
Testing mystat
Elapsed: 00:00:38.57
SQL>

I suspect that in the past SYS_CONTEXT issued recursive SQL under the covers (just as the SYSDATE pl/sql functions used to, and the USER function and the 11g assignment from a sequence still do).

Now I assume SYS_CONTEXT gets its information directly.