Previous Next JavaScript must be enabled to correctly display this content

## 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.

- About APEX Sessions and Database Sessions
  Learn about APEX sessions and database sessions.
- Session Attributes
  Learn about session attributes visible to system administrators and developers.
- APEX Processes
  Learn how APEX sets session attributes for an application or background mail session.
- SQL Workshop
  Learn how APEX sets session attributes in SQL Commands and SQL Scripts.
- Data Workshop
  Learn how APEX sets session attributes while importing data using Data Workshop.
- Automations
  Learn how APEX sets session attributes while running Automations.
- Installer
  Learn how APEX installation scripts set session attributes while APEX is being installed or upgraded.

See Also:

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

### D.1 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.

Parent topic: Correlating APEX Sessions to Database Sessions

### D.2 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.

Parent topic: Correlating APEX Sessions to Database Sessions

### D.3 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

Parent topic: Correlating APEX Sessions to Database Sessions

### D.4 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

Parent topic: Correlating APEX Sessions to Database Sessions

### D.5 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

Parent topic: Correlating APEX Sessions to Database Sessions

### D.6 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

Parent topic: Correlating APEX Sessions to Database Sessions

### D.7 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

Parent topic: Correlating APEX Sessions to Database Sessions