The ENABLE_OPT_TRACE procedure is used to enable end-to-end tracing for the optimizer. Once enabled, the plan generation process for each statement in the current session, including PL/SQL statements, will be traced, and a trace file is generated in the log directory of the statement 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 | Used to mark the SQL statements to be traced. For example, when you run a PL program for testing and want to trace only specific SQL statements within the PL functions, you can set sql_id marker. Once set, sql_id, only specific SQL statements will be traced; otherwise, all SQL statements will be traced. |
| identifier | A suffix used to identify trace files, making it easier for users to locate their own trace files. |
| level | Specifies the tracing level. Valid values:
Note: level is a database keyword and must be enclosed in backticks ``. |
Examples
Enables 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 file suffix.*/
obclient> CALL DBMS_XPLAN.SET_OPT_TRACE_PARAMETER(identifier=>'trace_test', `level`=>3);
Query OK, 0 rows affected
/* Execute the query */
obclient> SELECT * FROM t1;
Empty set
/* View the trace_test-traced logs 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