Appendix D: Correlating APEX Sessions to Database Sessions

Learn about best practices that enable administrators and developers to correlate the database session with a specific APEX application in order to troubleshoot application performance issues.

See Also: SET_IDENTIFIER Procedure and DBMS_APPLICATION_INFO in Oracle AI Database PL/SQL Packages and Types Reference

About APEX Sessions and Database Sessions

Learn about APEX sessions and database sessions.

Oracle APEX includes a database account called APEX_PUBLIC_USER. All database sessions in the database session pool of Oracle REST Data Services (ORDS) use a single database user such as ORDS_PUBLIC_USER, APEX_PUBLIC_USER, or some other user depending how the instance was configured. There is no permanent, fixed correlation between an APEX session and a database session. Because the assignment of a session in the session pool to service an APEX request is essentially unpredictable and not constant, this correlation can only be done for active database sessions, which are actively servicing APEX requests.

Session Attributes

Learn about session attributes visible to system administrators and developers.

While one or more APEX database processes are active, they may set the following session attributes which are visible to system administrators from v$session, v$sqlarea, v$session_longops, and from various audit and SQL tracing tools. Developers can set these session attributes using PL/SQL.

client_id

client_id is a 64 byte string that is generally used to identify the user/client session. Since it is possible for the processing for a single APEX session to execute in different database sessions, you can correlate the workload across all database sessions to each APEX session. client_id is also often used in conjunction with Virtual Private Database (VPD) using an application context. In PL/SQL, you can set client_id for a session by calling dbms_session.set_identifier and retrieve it with sys_context('USERENV','CLIENT_IDENTIFIER'). You can view client_id for active sessions in the client_identifier column in the v$session view.

Within an APEX session, setting this attribute is not necessary and generally should be left unchanged. If custom PL/SQL code executed within an APEX session changes client_id, this change will not cause problems for Oracle APEX. However, it may affect the ability for some monitoring tools to effectively identify the APEX session.

client_info

client_info is a 64 byte string that is usually used by APEX to identify the workspace and user. In PL/SQL, you can set client_info for the session by calling dbms_application_info.set_client_info and retrieve it using dbms_application_info.read_client_info or with sys_context('USERENV','CLIENT_INFO'). You can view client_info for active sessions in the client_info column in the v$session view.

Within an APEX session, setting this attribute is not necessary and generally should be left unchanged. If custom PL/SQL code executed within an APEX session changes client_info, this change will not cause problems for Oracle APEX. However, it may affect the ability for some monitoring tools to effectively identify the APEX session.

module

module is a 48 byte string that is usually used to identify the currently running module or process. In PL/SQL, you can set module by calling dbms_application_info.set_module and retrieve it using dbms_application_info.read_module or with sys_context('USERENV','MODULE'). You can view module for active sessions in the module column in the v$session view.

Within an APEX session, Oracle recommends setting this attribute (along with action ) as a way to allow monitoring tools to identify what is currently being processed.

action

action is a 32 byte string that is usually used to identify the current step being performed within a module or process. In PL/SQL, you can set action for the session by calling dbms_application_info.set_module or set_action and retrieve it using dbms_application_info.read_module or sys_context('USERENV','ACTION'). You can view action for active sessions in the action column in the v$session view.

Within an APEX session, Oracle recommends setting this attribute (along with module) as a way to allow monitoring tools to identify what is currently being processed.

Session long ops

Session longops (long operations) is a record structure that describes the number of units processed so far versus the total number of units expected to be processed. You can use session longops to estimate completion time for long-running processes. In PL/SQL, you can set session longops for the session by calling dbms_application_info.set_session_longops. You can monitor long operations by querying the v$session_longops view.

Within an APEX session, Oracle recommends setting this attribute for processes that are expected to take a long time, if the workload can be reasonably broken up into discrete units of work (for example, “number of records processed”). Note: Oracle APEX calls the SYS version of dbms_application_info; even if a wrapper for the package exists, it will not be used.

APEX Processes

Learn how APEX sets session attributes for an application or background mail session.

Tip: Most APEX processes reset the session values to NULL when they finish. They do not restore prior values.

The following table details how APEX sets session attributes for an application session.

Session Attribute	Expression	Example.	Notes More…
`client_id`	`app_user \|\| ':' \|\| session-id`	`jane.doe@example.com:12161645673208`	If app_user is too long, it will be truncated to fit (for example, the first 50 or 49 bytes). Before authentication is completed, will be: `'nobody:' \|\| session-id`
`client_info`	`workspace-id \|\| ':' \|\| app_user`	`3574091691765823934:jane.doe@example.com`	n/a
`module`	`schema \|\| '/APEX:APP ' \|\| app-id \|\| ':' \|\| page-id`	`MY_DB/APEX:APP 17251:4`	n/a
`action`	A range of different values may be seen, such as: `'start_page_request' 'PAGE ' \|\| page-id 'computation and processes' 'fetch meta data' 'Processes - point: ' \|\| process-point 'Validations' 'branching' 'import app ' \|\| app-id`	n/a	n/a

Mail Background Session

The following table details how APEX sets session attributes for a mail background session.

Session Attribute	Expression	Notes
`client_id`	`mail-from \|\| ':' \|\| message-guid`	Long email addresses are truncated to fit.
`client_info`	`workspace-id \|\| ':' \|\| mail-from`	n/a
`module`	(unchanged)	n/a
`action`	(unchanged)	n/a

SQL Workshop

Learn how APEX sets session attributes in SQL Commands and SQL Scripts.

Note: The Autocommit check box displays in SQL Commands if Enable Transactional SQL Commands is enabled in Administration Services, Feature Configuration. To learn more, see Configuring SQL Workshop in Oracle APEX Administration Guide

SQL Commands (Autocommit = ON)

The following table details how APEX sets session attributes in SQL Commands when Autocommit is enabled.

Session Attribute	Expression
`client_id`	`app_user \|\| ':' \|\| session-id`
`client_info`	`workspace-id \|\| ':' \|\| app_user`
`module`	`'APEX:SQL Workshop'`
`action`	`sql-statement`

SQL Commands (Autocommit = OFF)

The following table details how APEX sets session attributes in SQL Commands when Autocommit is disabled. When Autocommit is disabled, SQL Commands are executed by a separate database job. While awaiting a command from the SQL Commands window, the job is in an “idle” state.

Session Attribute	Expression
`client_id`	`app_user \|\| ':' \|\| session-id`
`client_info`	`workspace-id \|\| ':' \|\| app_user`
`module`	`'APEX:SQL Workshop-No Autocommit'`
`action`	`Working sql-statement Idle, awaiting work Stopping`

See Also: Using SQL Commands in Oracle APEX SQL Workshop Guide

SQL Scripts

The following table details how APEX sets session attributes while running SQL Scripts.

Session Attribute	Expression
`client_id`	`app_user \|\| ':' \|\| session-id`
`client_info`	`workspace-id \|\| ':' \|\| app_user`
`module`	`'APEX:SQL Workshop:Script'`
`action`	`script-name`
`longops`	`SQL statement N of M`

See Also: Using SQL Scripts in Oracle APEX SQL Workshop Guide

Data Workshop

Learn how APEX sets session attributes while importing data using Data Workshop.

The following table details how APEX sets session attributes while importing data using Data Workshop.

Session Attribute	Expression	Notes
`client_id`	(unchanged)	n/a
`client_info`	`workspace-id \|\| ':' \|\| app-user`	n/a
`module`	`schema \|\| '/APEX:APP' \|\| app-id \|\| ':DATA_LOADING' or schema \|\| '/APEX:APP' \|\| app-id \|\| ':DATA_LOADING_SODA'`	If the schema name is long, it may be truncated so that the module string fits within 48 bytes.
`action`	`Data Loading Job`	n/a

Automations

Learn how APEX sets session attributes while running Automations.

The following table details how APEX sets session attributes while running Automations.

Session Attribute	Expression	Notes
`client_id`	(unchanged)	n/a
`client_info`	`workspace-id \|\| ':'`	n/a
`module`	`automation-module`	n/a
`action`	`automation-static-id \|\| ':I'`	Executing initialisation procedure. In all cases, `automation-static-ID` is truncated to 16 characters.
`action`	`automation-static-id`	Starting / fetching data.
`action`	`automation-static-id \|\| ':R' \|\| row-num \|\| ':B'`	Before action.
`action`	`automation-static-id \|\| ':R' \|\| row-num \|\| ':A' \|\| action-sequence`	Executing action.
`action`	`automation-static-id \|\| ':R' \|\| row-num`	Finished action.
`action`	`automation-static-id \|\| ':T'`	Cleanup.

See Also: Managing Automations

Installer

Learn how APEX installation scripts set session attributes while APEX is being installed or upgraded.

The following table details how APEX installation scripts set session attributes while installing or upgrading APEX.

Session Attribute	Expression	Notes
`client_id`	(unchanged)	n/a
`client_info`	(unchanged)	n/a
`module`	`'APEX ' \|\| install-type \|\| ' PHASE ' \|\| phase`	`install-type` is something like `'FULLINS'` , `'RUNTIME'` , `'PATCH'` , and so on.
`action`	A range of different values may be seen, such as: `'upgrade' 'recompiling'`	n/a