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's SQL log directory.
Applicability
This feature is only applicable to OceanBase Database Enterprise Edition. OceanBase Database Community Edition does not support this feature.
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 specify the SQL statement to be traced. For example, if you are running a PL/SQL program and want to trace only specific SQL statements within the PL/SQL function, 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 | Used to specify the suffix of the trace file, making it easier for users to locate their trace files. |
| level | Used to set the tracing level:
Notice: level is a database keyword. In Oracle tenants, it must be enclosed in double quotation marks (" "), and in MySQL 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 log 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 log with the suffix "trace_test" in the OBServer node's 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