Oracle Cloud Infrastructure Documentation

DBMS_USERDIAG Package

The DBMS_USERDIAG package provides subprograms to enable and disable SQL tracing for a specific SQL statement, session, client identifier, or application service, module, and action. You can also collect bind variable values, wait event information, and execution plan statistics for more detailed diagnostics.

SESSION_SQL_TRACE_ENABLE Procedure

Use the DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE procedure to enable SQL tracing for the current session or for sessions that match the specified tracing attributes.

Syntax

DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE (
    level         IN BINARY_INTEGER DEFAULT 1,
    sid           IN BINARY_INTEGER DEFAULT 0,
    ser           IN BINARY_INTEGER DEFAULT 0,
    instance_name IN VARCHAR2       DEFAULT NULL,
    binds         IN BINARY_INTEGER DEFAULT 0,
    waits         IN BINARY_INTEGER DEFAULT 0,
    client_id     IN VARCHAR2       DEFAULT NULL,
    service       IN VARCHAR2       DEFAULT NULL,
    module        IN VARCHAR2       DEFAULT NULL,
    action        IN VARCHAR2       DEFAULT NULL,
    plan_stat     IN VARCHAR2       DEFAULT NULL,
    sql_id        IN VARCHAR2       DEFAULT NULL
);

Parameters

Parameter Description
level Specifies the SQL trace level. Level 1 collects basic trace data. Level 4 includes bind variable information, and level 8 includes wait event information. The values can be combined. This parameter is optional and defaults to 1.
sid Specifies the session identifier of the target session. The default value, 0, identifies the current session.
ser Specifies the serial number of the target session. The default value, 0, identifies the current session. Use sid and ser together to identify another session.
instance_name Specifies the database instance that contains the target session.
binds Specifies whether to include bind variable information. A nonzero value includes bind variable information and adds level 4 tracing. The default value is 0.
waits Specifies whether to include wait event information. A nonzero value includes wait event information and adds level 8 tracing. The default value is 0.
client_id Specifies the client identifier used to match sessions across database instances.
service Specifies the database service used to match sessions.
module Specifies the application module used with service to match sessions.
action Specifies the application action used with service and module to match sessions.
plan_stat Specifies the execution plan statistics option. Valid values are NEVER, FIRST_EXECUTION, ALL_EXECUTIONS, and ADAPTIVE. The default value is FIRST_EXECUTION.
sql_id Specifies the SQL ID. When specified, tracing is limited to the SQL statement with that SQL ID.

Example

The following example enables SQL tracing for a specific SQL statement and includes bind variable and wait event information:

BEGIN
    DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
        sql_id    => '5yka4rvxpbjc6',
        binds     => 1,
        waits     => 1,
        plan_stat => 'ADAPTIVE');
END;
/

The following example enables SQL tracing for a different session by using its session ID and serial number:

BEGIN
    DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
        sid   => 39,
        ser   => 15593,
        binds => 1,
        waits => 1);
END;
/

Usage Notes

  • You must be logged in as the ADMIN user or have EXECUTE privilege on the DBMS_USERDIAG package to run this procedure.
  • SQL tracing is disabled by default. Enable one tracing method for a tracing rule, run the workload, and disable tracing by using the same identifying attributes.
  • Specify one tracing method for a tracing rule. You can identify tracing targets by SQL ID, session, client identifier, service, module, and action.
  • Higher trace levels, including bind variable and wait event tracing, can generate large trace files and affect application performance.
  • A local user cannot trace a session that belongs to a common user.
  • DBMS_USERDIAG.ENABLE_SQL_TRACE_EVENT is deprecated. Use SESSION_SQL_TRACE_ENABLE and SESSION_SQL_TRACE_DISABLE instead.

See SESSION_SQL_TRACE_DISABLE Procedure for more information.

SESSION_SQL_TRACE_DISABLE Procedure

Use the DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE procedure to disable SQL tracing for the current session or for sessions that match the specified tracing attributes.

Disable tracing after the workload finishes. When tracing is disabled, the database uploads the collected trace data to the Cloud Object Storage bucket configured for SQL tracing. You can review the trace data in the generated trace file or in the SESSION_CLOUD_TRACE view.

Syntax

DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE (
    level         IN BINARY_INTEGER DEFAULT 1,
    sid           IN BINARY_INTEGER DEFAULT 0,
    ser           IN BINARY_INTEGER DEFAULT 0,
    instance_name IN VARCHAR2       DEFAULT NULL,
    binds         IN BINARY_INTEGER DEFAULT 0,
    waits         IN BINARY_INTEGER DEFAULT 0,
    client_id     IN VARCHAR2       DEFAULT NULL,
    service       IN VARCHAR2       DEFAULT NULL,
    module        IN VARCHAR2       DEFAULT NULL,
    action        IN VARCHAR2       DEFAULT NULL,
    plan_stat     IN VARCHAR2       DEFAULT NULL,
    sql_id        IN VARCHAR2       DEFAULT NULL
);

Parameters

Parameter Description
level Specifies the SQL trace level. Level 1 collects basic trace data. Level 4 includes bind variable information, and level 8 includes wait event information. The values can be combined. This parameter is optional and defaults to 1.
sid Specifies the session identifier of the target session. The default value, 0, identifies the current session.
ser Specifies the serial number of the target session. The default value, 0, identifies the current session. Use sid and ser together to identify another session.
instance_name Specifies the database instance that contains the target session.
binds Specifies whether to include bind variable information. A nonzero value includes bind variable information and adds level 4 tracing. The default value is 0.
waits Specifies whether to include wait event information. A nonzero value includes wait event information and adds level 8 tracing. The default value is 0.
client_id Specifies the client identifier used to match sessions across database instances.
service Specifies the database service used to match sessions.
module Specifies the application module used with service to match sessions.
action Specifies the application action used with service and module to match sessions.
plan_stat Specifies the execution plan statistics option. Valid values are NEVER, FIRST_EXECUTION, ALL_EXECUTIONS, and ADAPTIVE. The default value is FIRST_EXECUTION.
sql_id Specifies the SQL ID. When specified, tracing is limited to the SQL statement with that SQL ID.

Specify the same identifying attributes that you used when you enabled tracing.

Example

The following example disables SQL tracing for the SQL statement identified by 5yka4rvxpbjc6:

BEGIN
    DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
        sql_id => '5yka4rvxpbjc6');
END;
/

The following example disables SQL tracing for the session identified by session ID 39 and serial number 15593:

BEGIN
    DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
        sid => 39,
        ser => 15593);
END;
/

Usage Notes

  • Use the same identifying attributes in SESSION_SQL_TRACE_DISABLE that you used in SESSION_SQL_TRACE_ENABLE.
  • If the target session exits before tracing is disabled, the database uploads the trace file when the session exits.
  • Trace data can contain application data. Protect the Cloud Object Storage bucket and credentials appropriately.
  • The trace data is available in the SESSION_CLOUD_TRACE view for the duration of the session in which tracing was enabled. After the session ends, the data is no longer available in the view.

See SESSION_SQL_TRACE_ENABLE Procedure for more information.