The ENABLE_OPT_TRACE procedure enables end-to-end tracing for the optimizer of the current session. After end-to-end tracing is enabled, all plan generation procedures in the current session, including the SQL plan generation procedures in PL, are traced. In addition, a trace folder is generated in the log directory of the SQL execution node.
Syntax
DEFAULT_INENTIFIER constant VARCHAR2(20) := '';
DEFAULT_LEVEL constant INT := 1;
DBMS_XPLAN.ENABLE_OPT_TRACE(
sql_id IN VARCHAR2 DEFAULT '',
identifier IN VARCHAR2 DEFAULT DEFAULT_INENTIFIER,
level IN INT DEFAULT DEFAULT_LEVEL
);
Parameters
Parameter |
Description |
|---|---|
| sql_id | The ID of the SQL statement to be traced. For example, if you want to run a PL program in a test and trace only a specific SQL statement in a PL function, you can specify sql_id to mark that SQL statement. If you do not specify a sql_id, all SQL statements are traced. |
| identifier | The suffix of the trace file, which helps you easily find the trace file. |
| level | The level of tracing. Valid values:
Note: level is a database keyword and therefore must be enclosed by double quotation marks (" ") in an Oracle tenant and by grave accents (` `) in a MySQL tenant. |
Examples
Enable end-to-end tracing for the optimizer of the current session.
/* Connect to the database and enable end-to-end tracing for the optimizer of the current session. */
obclient> CALL DBMS_XPLAN.ENABLE_OPT_TRACE();
Query OK, 0 rows affected
/* Specify the tracing level and suffix of the trace log file. */
obclient> CALL DBMS_XPLAN.SET_OPT_TRACE_PARAMETER(identifier=>'trace_test', `level`=>3);
Query OK, 0 rows affected
/* Execute a query. */
obclient> SELECT * FROM t1;
Empty set
/* View the trace log file that is suffixed with trace_test in the log directory on the OBServer node. */
vi /data/1/observer/log/optimizer_trace_BkkGn1_trace_test.trac
/* Disable end-to-end tracing for the optimizer of the current session. */
obclient> CALL DBMS_XPLAN.DISABLE_OPT_TRACE();;
Query OK, 0 rows affected