The ENABLE_OPT_TRACE procedure is used to enable full-link tracing for the optimizer. Once enabled, the tracing will capture the plan generation process for every SQL statement executed in the current session, including those generated within PL/SQL blocks, and generate a trace file in the execution node log directory.
Applicability
This content applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition only provides MySQL-compatible mode.
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 | Specifies the SQL statement to be traced. For example, if you want to trace only specific SQL statements within a PL/SQL block, you can set the sql_id parameter. Once set, only the specified SQL statement will be traced; otherwise, all SQL statements will be traced. |
| identifier | Specifies the suffix for the trace file, making it easier for users to locate their trace files. |
| level | Specifies the tracing level:
Notice: level is a database keyword. In Oracle-compatible tenants, it must be enclosed in double quotation marks (" "), while in MySQL-compatible tenants, it must be enclosed in backticks ( |
Examples
/* 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 level and 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 file 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