The ENABLE_OPT_TRACE procedure enables full-trace of the optimizer. After this procedure is executed, the optimizer will trace the plan generation process for each SQL statement executed in the current session, including those generated in PL/SQL blocks, and generate a trace file in the execution node log directory.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only MySQL 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 | The SQL ID of the SQL statement to be traced. For example, if you want to trace only specific SQL statements in PL/SQL blocks during the test, you can set the sql_id parameter. Once set, only the specified SQL statements will be traced. |
| identifier | The suffix of the trace file, which helps users locate their trace files. |
| level | The trace level.
Notice: level is a database keyword. In an Oracle tenant, you must enclose it in double quotation marks ("). In a MySQL tenant, you must enclose it in backticks (`). |
Examples
/* 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 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 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