The ENABLE_OPT_TRACE procedure enables optimizer full-link tracing. After this procedure is executed, the optimizer traces the plan generation process of each statement in the current session, including the plan generation process of SQL statements in PL/SQL programs, and generates a trace file 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 | The SQL ID of the SQL statement to be traced. For example, if you want to run a PL program and trace only specific SQL statements in the PL function, you can set the sql_id parameter. Once sql_id is set, only the specified SQL statements are traced. If sql_id is not set, all SQL statements are traced. |
| identifier | The suffix of the trace file, which helps users locate their trace files. |
| level | The tracing level.
Note: level is a database keyword. In an Oracle tenant, you must enclose it in double quotation marks (" "). In a MySQL tenant, you must enclose it in backticks ( |
Examples
Enable optimizer tracing for the current session.
/* 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 trace log level and the trace 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 logs with the suffix "trace_test" in the OBServer node log directory. */
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