Written by: ilmarkerm

Category: Blog entry

Published: July 21, 2009

For every Oracle database session it is always good to set MODULE, ACTION and CLIENT_ID values for instrumentation. This allows DBA to see and debug in database level what the session is/was doing in detail. Oracle diagnostic tools are all powered up for using these values and a lot of diagnostic power is just lost, when the application is not instrumented.

For JDBC one can use end-to-end metrics support in JDBC driver
For PL/SQL one can use DBMS_APPLICATION_INFO subprograms

All this is just great, but what about APEX? I think it’s the best database application development tool in the market today, but has it got this instrumentation already built in?
Yes it has!

SELECT   module,
         action,
         client_id
  FROM   v$active_session_history
 WHERE   module LIKE 'APEX%';

MODULE                  ACTION          CLIENT_ID
----------------------- --------------- ------------------------
APEX:APPLICATION 109    PAGE 7          ILMAR:2697049844839191
APEX:APPLICATION 109    PAGE 12         ILMAR:2697049844839191
APEX:APPLICATION 109    PAGE 6          ILMAR:2697049844839191

MODULE is set to the application number
ACTION contains the page number
CLIENT_ID constains username and session id

This example is from APEX 3.2 and Oracle 11.1.0.7 database.

You can read more about using Oracle diagnostic tools and session tracing from Doug Burns blog:
Session Level ASH Reports