The ENABLE_OPT_TRACE procedure enables the end-to-end tracing of the optimizer for the current session. After the optimizer tracing is enabled, the generation process of each plan, including the plans of SQL statements in PL, in the current session is traced and a trace file is generated in the log directory of the SQL execution node.
Syntax
DEFAULT_INENTIFIER constant VARCHAR2(20) := '';
DEFAULT_LEVEL constant INT := 1;
PROCEDURE 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 program in a test and trace only a specific SQL statement in a PL function, you can specify sql_id to mark that SQL statement. If you do not specify a sql_id, all SQL statements are traced. |
| identifier | The suffix of the trace file, which helps you easily find the trace file. |
| level | The level of tracing. Valid values:
Note: level is a database keyword and therefore must be enclosed by double quotation marks (" ") in an Oracle tenant and by grave accents (` `) in a MySQL tenant. |
Examples
Enable optimizer tracing for the current session.
obclient> CALL DBMS_XPLAN.ENABLE_OPT_TRACE();
Query OK, 0 rows affected