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.
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.
- Start with configuring the database to save SQL Trace files. See Configure SQL Tracing on Autonomous Database for more information.
- Then, enable SQL tracing. See 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 stop collecting the SQL tracing data, you must disable SQL tracing. See Disable SQL Tracing.
- When you disable SQL tracing, the tracing data collected while the session runs with tracing enabled is written to the
SESSION_CLOUD_TRACE
view in your session and to a trace file in the bucket, which you configure while setting up SQL tracing. You have two options to view the trace data:- View and analyze SQL Trace data in the trace file saved to Cloud Object Store. See View Trace File Saved to Cloud Object Store on Autonomous Database for more information.
- View and analyze SQL Trace data saved to the view
SESSION_CLOUD_TRACE
. See View Trace Data in SESSION_CLOUD_TRACE View on Autonomous Database for more information.
- Configure SQL Tracing on Autonomous Database
- Enable SQL Tracing on Autonomous Database
- Disable SQL Tracing
- View Trace File Saved to Cloud Object Store on Autonomous Database
- View Trace Data in SESSION_CLOUD_TRACE View on Autonomous Database
Parent topic: High Performance
Configure SQL Tracing on Autonomous Database
Enable SQL Tracing on Autonomous Database
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:
Disable SQL Tracing
View Trace File Saved to Cloud Object Store on Autonomous Database
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. ThenumID1
andnumID2
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
andnumID2
values.
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.
View Trace Data in SESSION_CLOUD_TRACE View on Autonomous Database
SESSION_CLOUD_TRACE
view in the session where the tracing was enabled.
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.
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.