Oracle Cloud Infrastructure Documentation

Perform SQL Tracing on Autonomous Database

Use SQL tracing to help you identify the source of an excessive database workload, such as a high load SQL statement in your application.

Parent topic: Monitor and Manage Performance

Configure SQL Tracing on Autonomous Database

Shows the steps to configure SQL tracing on Autonomous Database.

Note

If you enable SQL Tracing your application performance for the session may be degraded while the trace collection is enabled. This negative performance impact is expected due to the overhead of collecting and saving trace data.

To configure your 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, do the following

    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. In the Create Bucket page, enter the Bucket Name and click Create.

    If you are using an Oracle Cloud Infrastructure Object Storage, note that SQL tracing files are only supported with buckets created in the standard storage tier, make sure you pick Standard as the storage tier when creating your bucket. See Overview of Object Storage for information on the Standard Object Storage Tier.

  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;
/

    For Oracle Cloud Infrastructure the username is your Oracle Cloud Infrastructure user name. The password is your Oracle Cloud Infrastructure auth token. See Working with Auth Tokens for more information.

    See CREATE_CREDENTIAL Procedure for details on the parameters and their values, depending on the Cloud Object Storage.

  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 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 Oracle Cloud Infrastructure 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.

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

      For example:

      ALTER DATABASE PROPERTY SET DEFAULT_CREDENTIAL = 'ADMIN.DEF_CRED_NAME';

Enable SQL Tracing on Autonomous Database

Shows the steps to enable SQL tracing for the database session.

Note

If you enable SQL tracing your application performance for the session may be degraded while the trace collection is enabled. This negative performance impact is expected due to the overhead of collecting and saving trace data.

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

To enable SQL tracing, do the following:

  1. (Optional) 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. (Optional) 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. See Disable SQL Tracing on Autonomous Database for details.

Disable SQL Tracing on Autonomous Database

Shows the steps to disable SQL tracing on Autonomous Database.

To disable SQL tracing, do the following:

  1. Disable the SQL Trace facility.
    ALTER SESSION SET SQL_TRACE = FALSE;
  2. (Optional) 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. You have two options to view trace data:

View Trace File Saved to Cloud Object Store on Autonomous Database

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

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