The ENABLE_OPT_TRACE procedure is used to enable full-trace of the optimizer. After it is enabled, the plan generation process of each query in the current session will be traced, including the plan generation process of SQL statements in PL/SQL procedures, and a trace file will be generated in the execution node log directory of the SQL statement.
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 | Used to mark the SQL statement to be traced. For example, if you want to run a PL/SQL procedure and trace only specific SQL statements within the PL/SQL function, you can set the sql_id parameter. Once sql_id is set, only the specified SQL statements will be traced; otherwise, all SQL statements will be traced. |
| identifier | Used to mark the suffix of the trace file, making it easier for users to locate their trace files. |
| level | Used to set the trace level:
Note: level is a database keyword. In an Oracle tenant, it must be enclosed in double quotation marks (""). In a MySQL tenant, it must be enclosed in backticks (`). |
Examples
Enable the optimizer trace feature for the current session.
/* Connect to the database and enable the optimizer trace feature for the current session */
obclient> CALL DBMS_XPLAN.ENABLE_OPT_TRACE();
Query OK, 0 rows affected
/* Set the trace level and log file suffix */
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 with the suffix "trace_test" in the OBServer node log directory */
vi /data/1/observer/log/optimizer_trace_BkkGn1_trace_test.trac
/* Disable the optimizer trace feature for the current session */
obclient> CALL DBMS_XPLAN.DISABLE_OPT_TRACE();;
Query OK, 0 rows affected