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.
- Configure SQL Tracing on Autonomous Database
Shows the steps to configure SQL tracing on Autonomous Database. - Enable SQL Tracing on Autonomous Database
Shows the steps to enable SQL tracing for the database session. - Disable SQL Tracing on Autonomous Database
Shows the steps to disable SQL tracing on Autonomous Database. - 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 useTKPROF
to organize and view trace file data. - 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 theSESSION_CLOUD_TRACE
view in the session where the tracing was enabled.
Parent topic: Monitor and Manage Performance
Configure SQL Tracing on Autonomous Database
Shows the steps to configure SQL tracing on Autonomous Database.
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:
Parent topic: Perform SQL Tracing on Autonomous Database
Enable SQL Tracing on Autonomous Database
Shows the steps to enable SQL tracing for the database session.
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:
Parent topic: Perform SQL Tracing on Autonomous Database
Disable SQL Tracing on Autonomous Database
Shows the steps to disable SQL tracing on Autonomous Database.
To disable SQL tracing, do the following:
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 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.
Parent topic: Perform SQL Tracing on Autonomous Database
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. 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.
See "Tools for End-to-End Application Tracing" in Oracle Database SQL Tuning
Guide for information about using the TKPROF
utility.
Parent topic: Perform SQL Tracing on Autonomous Database
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.
Parent topic: Perform SQL Tracing on Autonomous Database