Perform SQL Tracing on Autonomous AI Database

Use SQL tracing to help identify the source of an excessive database workload, such as a high-load SQL statement in your application. Autonomous AI Database supports session-based tracing with ALTER SESSION and targeted tracing with the DBMS_USERDIAG package.

SQL tracing is disabled by default. To collect trace data, enable a tracing rule, run the workload that you want to investigate, and then disable the rule by using the same identifying attributes. When tracing is disabled, the database uploads the collected data to Cloud Object Storage. You can review the data in the generated trace file or in the SESSION_CLOUD_TRACE view.

The tracing method determines how the database identifies the sessions to trace. Client identifier and service, module, and action tracing can identify matching sessions across database instances, whereas session-based tracing targets a specific session on a specific instance. Client identifier and service, module, and action tracing support bind variable and wait event information. Because basic tracing is not available for these tracing methods, wait tracing is enabled by default when you do not explicitly specify bind or wait tracing. Session-based tracing supports basic, bind, and wait trace levels.

Configure SQL Tracing on Autonomous AI Database

Configure the database to save SQL trace files to Cloud Object Storage before you enable SQL tracing.

Note: If you enable SQL tracing, application performance for the session might be degraded while trace collection is enabled. This expected impact is caused by the overhead of collecting and saving trace data.

Prerequisites for SQL Tracing

The following configuration is required for both ALTER SESSION tracing and DBMS_USERDIAG tracing. Complete these steps before you enable either tracing method.

To configure your database for SQL tracing, do the following:

Create a bucket to store trace files in Cloud Object Storage.

To save SQL trace files, you can use any Cloud Object Storage service supported by Autonomous AI Database.

For example, to create a bucket in Oracle Cloud Infrastructure Object Storage:
1. Open the Oracle Cloud Infrastructure Console.
2. Select Storage from the menu.
3. Under Storage, select Object Storage and Archive Storage.
4. Click Create Bucket.
5. On the Create Bucket page, enter the bucket name and click Create.
If you use Oracle Cloud Infrastructure Object Storage, SQL tracing files are supported only in buckets created in the standard storage tier. Select Standard as the storage tier. See Overview of Object Storage for information about the Standard Object Storage tier.
Create a credential for your Cloud Object Storage account using DBMS_CLOUD.CREATE_CREDENTIAL.

For example:
```
BEGIN
  DBMS_CLOUD.CREATE_CREDENTIAL(
    credential_name => 'DEF_CRED_NAME',
    username        => 'adb_user@example.com',
    password        => 'password');
END;
/
```
Note: If your user account is in the default identity domain, specifying only the user name is sufficient. If your user account belongs to a nondefault identity domain, prefix the user name with the identity domain name followed by /. For example, identity_domain_name/adb_user@example.com.

For Oracle Cloud Infrastructure, specify your Oracle Cloud Infrastructure user name as username and your Oracle Cloud Infrastructure authentication token as password. See Working with Auth Tokens for more information.

See CREATE_CREDENTIAL Procedure for information about the parameters and their values for the Cloud Object Storage service you use.
Set database properties to specify the Cloud Object Storage location for SQL trace files and the credential used to access it.
1. Set the DEFAULT_LOGGING_BUCKET database property to specify the logging bucket.
  
  For example, if you created the bucket in Oracle Cloud Infrastructure Object Storage:
```
SET DEFINE OFF;
ALTER DATABASE PROPERTY SET
   DEFAULT_LOGGING_BUCKET = 'https://objectstorage.us-ashburn-1.oraclecloud.com/n/namespace-string/b/bucket_name/o/';
```
  In this example, namespace-string is the Oracle Cloud Infrastructure Object Storage namespace and bucket_name is the name of the bucket you created. See Understanding Object Storage Namespaces for more information.
  
  See Regions and Availability Domains for a list of regions.
2. Set the DEFAULT_CREDENTIAL database property to the credential you created in Step 2. Include the schema name with the credential. In this example, the schema is ADMIN.
```
ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';
```

DBMS_USERDIAG-Specific Prerequisites

The common prerequisites above apply to DBMS_USERDIAG tracing. In addition, complete the following prerequisites before you use this package:

You must be logged in as the ADMIN user or have EXECUTE privilege on DBMS_USERDIAG.
For session-based tracing, obtain the target session ID and serial number. You can query V$SESSION to identify these values.
For client identifier tracing, ensure that your application sets the client identifier, for example, SALES_APP.
For service, module, and action tracing, identify the application attributes used to match the target sessions.
Use bind and wait tracing only when you need this additional diagnostic information. These options can generate large trace files, affect application performance, and increase the PGA memory used by the session while trace data is collected.

After you complete the common and DBMS_USERDIAG-specific prerequisites, choose the tracing method that best matches your diagnostic goal. Use ALTER SESSION to trace activity in the current session, or use DBMS_USERDIAG to target a specific workload or group of sessions.

Trace Sessions with ALTER SESSION

Use ALTER SESSION to enable SQL tracing for the current database session. This method captures trace information for the work performed in that session.

Before you enable SQL tracing, configure the database to save SQL trace files. See Configure SQL Tracing on Autonomous AI Database for more information.

To enable SQL tracing for the current session:

(Optional) Set a client identifier for the application. This identifier is used as a component of the trace file name.

For example:
```
BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
```
DBMS_SESSION.SET_IDENTIFIER sets the client identifier for the current session.
(Optional) Set a module name for the application. The module name is also used as a component of the trace file name.

For example:
```
BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', NULL);
END;
/
```
Enable the SQL trace facility.

For example:
```
ALTER SESSION SET SQL_TRACE = TRUE;
```
ALTER SESSION SET SQL_TRACE = TRUE enables tracing for the current session.
Run the workload that you want to analyze. SQL tracing data is collected while the workload runs in the session.
Disable SQL tracing.

Disable SQL Tracing on Autonomous AI Database

Disable SQL tracing after the workload finishes. Disabling tracing writes the collected data to the session and to a trace file in the Cloud Object Storage bucket configured for SQL tracing.

To disable SQL tracing for the current session:

Disable the SQL trace facility.

For example:
```
ALTER SESSION SET SQL_TRACE = FALSE;
```
ALTER SESSION SET SQL_TRACE = FALSE disables tracing for the current session.
(Optional) Reset the DEFAULT_LOGGING_BUCKET database property to clear the logging bucket value.

For example:
```
ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
```
This statement clears the logging bucket configured for SQL tracing.

After you disable SQL tracing, you can view the trace data in either of the following ways:

View and analyze the SQL trace data in the trace file saved to Cloud Object Storage. See View Trace File Saved to Cloud Object Storage on Autonomous AI Database.
View and analyze the SQL trace data in the SESSION_CLOUD_TRACE view. See View Trace Data in SESSION_CLOUD_TRACE View on Autonomous AI Database.

Trace Workloads with DBMS_USERDIAG

Use the DBMS_USERDIAG package to enable and disable SQL tracing for a specific SQL statement, session, client identifier, or application service, module, and action. This approach helps you collect targeted diagnostic information for the specific SQL workload under investigation, while minimizing unrelated database activity in the analysis.

Benefits of DBMS_USERDIAG SQL Tracing

DBMS_USERDIAG provides the following capabilities:

Scope tracing to a specific SQL statement by using its SQL_ID.
Trace another active session by providing its Session ID and Serial number.
Trace application activity by client identifier.
Trace application activity by service, module, and action.
Include bind variable values and wait event details when deeper diagnostics are required.
Collect execution plan statistics for the first execution, every execution, or an adaptive set of executions based on the investigation need.

Enable SQL Tracing for a Specific SQL ID

Use a SQL_ID to trace only the matching SQL statement. In this example, bind variable and wait event information are collected, and adaptive plan statistics are enabled.

The sys parameter is not operational in Autonomous AI Database and is not used in the examples in this topic.

For example:

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

The sql_id parameter limits tracing to the specified SQL statement. The binds and waits parameters include bind variable and wait event information, and plan_stat specifies the plan statistics collection option.

Run the workload that uses SQL ID 5yka4rvxpbjc6, then disable tracing:

For example:

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

The sql_id parameter identifies the tracing rule to disable. Use the same identifying value that you specified when enabling tracing.

The sql_id value must identify the statement you want to trace. The valid plan_stat values are FIRST_EXECUTION, NEVER, ALL_EXECUTIONS, and ADAPTIVE.

Enable SQL Tracing for a Different Session

Use the target session ID and serial number to trace a different session. In this example, 39 is the session ID and 15593 is the serial number.

For example:

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

The sid and ser parameters identify the target session. The binds and waits parameters enable collection of bind variable and wait event information.

Run the workload in the target session, then disable tracing from a session with the required privileges:

For example:

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

The sid and ser parameters identify the tracing rule associated with the target session.

If the target session exits before tracing is disabled, the database uploads the trace file when the session exits.

Enable SQL Tracing by Client Identifier

Use a client identifier to trace matching sessions across database instances. In this example, the application sets the client identifier to SALES_APP.

For example:

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    client_id => 'SALES_APP',
    binds     => 1,
    waits     => 1);
END;
/

The client_id parameter identifies the application sessions to trace. The binds and waits parameters enable collection of additional diagnostic information.

Run the application workload, then disable tracing by using the same client identifier:

For example:

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    client_id => 'SALES_APP');
END;
/

The client_id parameter identifies the tracing rule to disable. Use the same client identifier that you specified when enabling tracing.

Enable SQL Tracing by Service, Module, and Action

Use application attributes to trace sessions that use a specified service, module, and action. In this example, tracing is limited to the HIGH service, the ORDER_PROCESSING module, and the CREATE_ORDER action.

For example:

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_ENABLE(
    service => 'HIGH',
    module  => 'ORDER_PROCESSING',
    action  => 'CREATE_ORDER',
    binds   => 1,
    waits   => 1);
END;
/

The service, module, and action parameters identify the application sessions to trace. The binds and waits parameters enable collection of additional diagnostic information.

Run the application workload, then disable tracing by using the same service, module, and action values:

For example:

BEGIN
  DBMS_USERDIAG.SESSION_SQL_TRACE_DISABLE(
    service => 'HIGH',
    module  => 'ORDER_PROCESSING',
    action  => 'CREATE_ORDER');
END;
/

The service, module, and action parameters identify the tracing rule to disable. Use the same values that you specified when enabling tracing.

The application must set the service, module, and action attributes for its sessions. You can use DBMS_APPLICATION_INFO.SET_MODULE to set the module and action. Matching is performed across database instances when the application attributes are the same.

See the following for more information:

Verify SQL Tracing

You can query the following views to verify that tracing is enabled and to review the tracing rules:

SELECT sid, serial#, sql_trace
FROM   V$SESSION
WHERE  sid = 39;

SELECT *
FROM   DBA_ENABLED_TRACES;

The TRACE_TYPE column in DBA_ENABLED_TRACES identifies the tracing method, such as SQL ID, session, client identifier, or service, module, and action.

View Trace File Saved to Cloud Object Storage on Autonomous AI Database

Describes the output file naming for SQL trace files and shows the commands to use TKPROF to organize and view trace file data.

When you disable SQL tracing, the trace data collected in the session is written to the Cloud Object Storage bucket configured with DEFAULT_LOGGING_BUCKET.

The SQL Trace facility writes the trace data collected in the session to Cloud Object Storage in the following format:

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

The components of the file name are:

default_logging_bucket: The value of the DEFAULT_LOGGING_BUCKET database property. See Configure SQL Tracing on Autonomous AI Database.
clientID: The client identifier. See Trace Sessions with ALTER SESSION.
moduleName: The module name. See Trace Sessions with ALTER SESSION.
numID1_numID2: Two identifiers that uniquely distinguish the trace file from trace files generated by other sessions using the same bucket. When a session runs a parallel query, the SQL Trace facility can produce multiple trace files with different values.

Note: When SQL tracing is enabled and disabled multiple times in the same session, each tracing iteration generates a separate trace file. Subsequent files include a numeric suffix to avoid overwriting earlier trace files. The suffix starts at 1 and increments for each subsequent tracing iteration.

For example, the following is a sample generated trace file name when the client identifier is sqlt_test and the module name is modname:

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

You can run TKPROF to translate the trace file into a readable output file:

Copy the trace file from Object Storage to your local system.
Navigate to the directory in which the trace file is saved.

Run the TKPROF utility from the operating system prompt using the following syntax:

tkprof filename1 filename2 [waits=yes|no] [sort=option] [print=n]
    [aggregate=yes|no] [insert=filename3] [sys=yes|no] [table=schema.table]
    [explain=user/password] [record=filename4] [width=n]

The input and output files are the only required arguments.

To view online help, invoke TKPROF without arguments.

See “Tools for End-to-End Application Tracing” in Oracle AI Database SQL Tuning Guide for information about using the TKPROF utility.

View Trace Data in SESSION_CLOUD_TRACE View on Autonomous AI Database

When you enable SQL tracing, the same trace information that is saved to the Cloud Object Storage trace file is available in the SESSION_CLOUD_TRACE view in the session where tracing was enabled.

While you are still in the database session, you can view SQL tracing data in the SESSION_CLOUD_TRACE view. The view includes two columns: ROW_NUMBER and TRACE.

DESC SESSION_CLOUD_TRACE

Name       Null? Type
---------- ----- ------------------------------
ROW_NUMBER       NUMBER
TRACE            VARCHAR2(32767)

ROW_NUMBER specifies the ordering for trace data in the TRACE column. Each line of trace output written to a trace file becomes a row in the view and is available in the TRACE column.

After you disable SQL tracing, query the SESSION_CLOUD_TRACE view. For example:

SELECT trace
FROM   SESSION_CLOUD_TRACE
ORDER BY row_number;

The data in SESSION_CLOUD_TRACE persists for the duration of the session. After you log out or close the session, the data is no longer available.

If SQL tracing is enabled and disabled multiple times in the same session, SESSION_CLOUD_TRACE shows the trace data for all iterations cumulatively. Re-enabling tracing in a session after previously disabling it does not remove the trace data produced by an earlier iteration.