The ENABLE_OPT_TRACE procedure enables full-trace of the optimizer. After this procedure is called, the optimizer traces the plan generation process of every SQL statement in the current session, including the plan generation process of SQL statements in PL/SQL programs. The trace file is 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 | The ID of the SQL statement to be traced. For example, if you want to run a PL/SQL program and trace only specific SQL statements in the PL/SQL program, you can set the sql_id parameter. Once you set the sql_id parameter, only the specified SQL statement is traced. Otherwise, all SQL statements are traced. |
| identifier | The suffix of the trace file. This parameter is used to help you locate your trace file. |
| level | The trace level.
Note: level is a database keyword. In an Oracle tenant, you must enclose it with double quotation marks ("). In a MySQL tenant, you must enclose it with backticks (`). |
Examples
Enable the optimizer trace for the current session.
/* Connect to the database and enable the optimizer trace for the current session. */
obclient> CALL DBMS_XPLAN.ENABLE_OPT_TRACE();
Query OK, 0 rows affected
/* Set the trace level and the suffix of 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> EXPLAIN SELECT * FROM t1;
Empty set
/* View the trace file with the trace_test suffix in the log directory of the OBServer node. */
vi /data/1/observer/log/optimizer_trace_BkkGn1_trace_test.trac
/* Disable the optimizer trace for the current session. */
obclient> CALL DBMS_XPLAN.DISABLE_OPT_TRACE();;
Query OK, 0 rows affected