The ENABLE_OPT_TRACE procedure is used to enable full-link tracing of the optimizer. After it is enabled, the tracing process will be applied to the plan generation of each SQL statement in the current session, including those generated within PL/SQL blocks. A trace file will be generated in the execution node log directory of the SQL statement.
Applicability
Community Edition V4.2.5 and later support this feature.
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 | Specifies the SQL statement to be traced. For example, if you want to trace only specific SQL statements within PL/SQL blocks, you can set the sql_id parameter. Once set, only the specified SQL statements will be traced; otherwise, all SQL statements will be traced. |
| identifier | Specifies the suffix of the trace file, making it easier for users to locate their trace files. |
| level | Specifies the tracing level. The options are as follows:
Note: level is a database keyword. In Oracle tenants, it must be enclosed in double quotation marks (" "). In MySQL tenants, it must be enclosed in backticks ( |
Examples
/* Connect to the database and enable optimizer tracing for the current session. */
obclient> CALL DBMS_XPLAN.ENABLE_OPT_TRACE();
Query OK, 0 rows affected
/* Set the tracing level and the suffix for the trace 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 file with the suffix "trace_test" in the log directory of the OBServer node. */
vi /data/1/observer/log/optimizer_trace_BkkGn1_trace_test.trac
/* Disable optimizer tracing for the current session. */
obclient> CALL DBMS_XPLAN.DISABLE_OPT_TRACE();;
Query OK, 0 rows affected