Use SQL Tracing on Autonomous Database on Dedicated Exadata Infrastructure

You can use SQL Tracing with Autonomous Database on Dedicated Exadata Infrastructure to help you identify the source of an excessive database workload, such as a high-load SQL statement in your application.

Note:

You can use SQL Tracing only with Autonomous Database versions 19.18 or later.

About SQL Tracing

When an application operation takes longer than expected, getting a trace of all the SQL statements executed as part of this operation with details such as time spent by that SQL statement in the parse, execution, and fetch phases will help you identify and resolve the cause of the performance issue. You can use SQL tracing on an Autonomous Database to achieve this.

SQL tracing is disabled by default in Autonomous Database. You must enable it to start collecting the SQL tracing data. To trace SQL statements, implement the following tasks as an ADMIN user:

Configure SQL Tracing on Autonomous Database

To configure your Autonomous Database for SQL tracing, do the following:
  1. Create a bucket to store trace files in your Cloud Object Storage.
    To save the SQL tracing files, the bucket can be in any Cloud Object Store that Autonomous Database supports. For example, to create a bucket in Oracle Cloud Infrastructure Object Storage, refer to Creating a Bucket.

    Tip:

    Ensure to pick Standard as the storage tier when creating your bucket in Oracle Cloud Infrastructure Object Storage because SQL tracing files are only supported with buckets created in the standard storage tier. For information on the Standard Object Storage Tier, see Understanding Storage Tiers.
  2. 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;
/

    See CREATE_CREDENTIAL Procedure for details on the arguments for the username and password parameters for different object storage services.

  3. Set initialization parameters to specify the Cloud Object Storage URL for a bucket for SQL trace files and to specify the credentials to access the Cloud Object Storage.
    1. Set database property DEFAULT_LOGGING_BUCKET to specify the logging bucket on Cloud Object Storage.
      For example, if you create the bucket with Oracle Cloud Infrastructure (OCI) 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/';

      Where namespace-string is the OCI Object Storage namespace and bucket_name is the name of the bucket you previously created. See Understanding Object Storage Namespaces for more information.

      See Regions and Availability Domains for a list of regions.

      The Cloud Object Store you use for SQL Tracing files can be any Cloud Object Store that Autonomous Database supports.

    2. Set the database property DEFAULT_CREDENTIAL to the credential you created in Step 2.
      For example:
      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

      Including the schema name with the credential is required. In this example the schema is ADMIN.

Enable SQL Tracing on Autonomous Database

Note:

Enabling SQL tracing may degrade your application performance for the session while the trace collection is enabled. This performance impact is expected due to the overhead of collecting and saving trace data.

To enable SQL tracing for a database session, , do the following:

  1. Optionally, set a client identifier for the application. This step is optional but is recommended. SQL tracing uses the client identifier as a component of the trace file name when the trace file is written to Cloud Object Store.
    For example:
    BEGIN
  DBMS_SESSION.SET_IDENTIFIER('sqlt_test');
END;
/
  2. Optionally, set a module name for the application. This step is optional but is recommended. SQL tracing uses the module name as a component of the trace file name when the trace file is written to Cloud Object Store.

    For example:

    BEGIN
  DBMS_APPLICATION_INFO.SET_MODULE('modname', null);
END;
/
  3. Enable the SQL Trace facility.
    ALTER SESSION SET SQL_TRACE = TRUE;
  4. Run your workload.
    This step involves running the entire application or specific parts of the application. While you run your workload in the database session, SQL tracing data is collected.
  5. Disable SQL Tracing.
    When you disable SQL tracing the collected data for the session is written to a table in your session and to a trace file in the bucket you configure when you set up SQL tracing.

Disable SQL Tracing

To disable SQL tracing, do the following:
  1. Disable the SQL Trace facility.
    ALTER SESSION SET SQL_TRACE = FALSE;
  2. Optionally, as needed for your environment, you may want to reset the database property DEFAULT_LOGGING_BUCKET to clear the value for the logging bucket on Cloud Object Storage.
    For example:
    ALTER DATABASE PROPERTY SET DEFAULT_LOGGING_BUCKET = '';
When you disable SQL tracing, the tracing data collected while the session runs with tracing enabled is copied to a table and sent to a trace file on Cloud Object Store.

View Trace File Saved to Cloud Object Store on Autonomous Database

You use SQL trace file data to analyze application performance on Autonomous Database. When you disable SQL trace in your database session, data is written to the Cloud Object Store bucket configured with DEFAULT_LOGGING_BUCKET.

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

default_logging_bucket/sqltrace/clientID/moduleName/sqltrace_numID1_numID2.trc

The components of the file name are:

  • default_logging_bucket: is the value of the DEFAULT_LOGGING_BUCKET database property. See Configure SQL Tracing on Autonomous Database for more information.

  • clientID: is the client identifier. See Enable SQL Tracing on Autonomous Database for more information.

  • moduleName: is the module name. See Enable SQL Tracing on Autonomous Database for more information.

  • numID1_numID2: are two identifiers that the SQL Trace facility provides. The numID1 and numID2 numeric values uniquely distinguish each trace file name from other sessions using tracing and creating trace files in the same bucket in the Cloud Object Storage.

    When the database service supports parallelism and a session runs a parallel query, the SQL Trace facility can produce multiple trace files with different numID1 and numID2 values.

Note:

When SQL tracing is enabled and disabled multiple times within the same session, each trace iteration generates a separate trace file in Cloud Object Store. To avoid overwriting previous traces that were generated in the session, subsequently generated files follow the same naming convention and add a numeric suffix to the trace file name. This numeric suffix starts with the number 1 and is incremented by 1 for each tracing iteration thereafter.

For example, the following is a sample generated trace file name when you set the client identifier to "sql_test" and the module name to "modname":

sqltrace/sqlt_test/modname/sqltrace_5415_56432.trc

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

  1. Copy the trace file from Object Store to your local system.
  2. Navigate to the directory in which the trace file is saved.
  3. 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.

  4. To view online Help, invoke TKPROF without arguments.
    See Tools for End-to-End Application Tracing in Oracle Database SQL Tuning Guide for information about using the TKPROF utility.

View Trace Data in SESSION_CLOUD_TRACE View on Autonomous Database

When you enable SQL Tracing, the same trace information that is saved to the trace file on Cloud Object Store is available in the SESSION_CLOUD_TRACE view in the session where the tracing was enabled.
While you are still in the database session you can view SQL tracing data in the SESSION_CLOUD_TRACE view. The SESSION_CLOUD_TRACE view includes two columns: ROW_NUMBER and TRACE.
DESC SESSION_CLOUD_TRACE

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

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

After you disable SQL tracing for the session, you can run queries on the SESSION_CLOUD_TRACE view.

For example:
SELECT trace FROM SESSION_CLOUD_TRACE ORDERBY 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 Trace is enabled and disabled multiple times within the same session, SESSION_CLOUD_TRACE shows the trace data for all the iterations cumulatively. Thus, re-enabling tracing in a session after previously disabling tracing does not remove the trace data produced by the earlier iteration.