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 Database PL/SQL Packages and Types ReferenceD.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 ofdbms_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... |
---|---|---|---|
|
|
|
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:
|
|
|
|
n/a |
|
|
|
n/a |
|
A range of different values may be seen, such as:
|
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 |
---|---|---|
|
|
Long email addresses are truncated to fit. |
|
|
n/a |
|
(unchanged) |
n/a |
|
(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 GuideSQL Commands (Autocommit = ON)
The following table details how APEX sets session attributes in SQL Commands when Autocommit is enabled.
Session Attribute | Expression |
---|---|
|
|
|
|
|
|
|
|
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 |
---|---|
|
|
|
|
|
|
|
|
SQL Scripts
The following table details how APEX sets session attributes while running SQL Scripts.
Session Attribute | Expression |
---|---|
|
|
|
|
|
|
|
|
|
|
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 |
---|---|---|
|
(unchanged) |
n/a |
|
|
n/a |
|
|
If the schema name is long, it may be truncated so that the module string fits within 48 bytes. |
|
|
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 |
---|---|---|
|
(unchanged) |
n/a |
|
|
n/a |
|
|
n/a |
|
|
Executing initialisation procedure. In all cases, |
|
|
Starting / fetching data. |
|
|
Before action. |
|
|
Executing action. |
|
|
Finished action. |
|
|
Cleanup. |
See Also:
Managing AutomationsParent 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 |
---|---|---|
|
(unchanged) |
n/a |
|
(unchanged) |
n/a |
|
|
|
|
A range of different values may be seen, such as:
|
n/a |
Parent topic: Correlating APEX Sessions to Database Sessions