The ENABLE_OPT_TRACE procedure enables end-to-end tracing for the optimizer of the current session. After end-to-end tracing is enabled, all plan generation procedures in the current session, including the SQL plan generation procedures in PL, are traced. In addition, a trace folder is generated in the log directory of the SQL execution node.
Applicability
This topic applies only to OceanBase Database Enterprise Edition. OceanBase Database Community Edition provides only the 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 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
/* Connect to the database and enable end-to-end tracing for the optimizer of the current session. */
obclient> CALL DBMS_XPLAN.ENABLE_OPT_TRACE();
Query OK, 0 rows affected
/* Specify the tracing level and suffix of the trace log file. */
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 file that is suffixed with trace_test in the log directory on the OBServer node. */
vi /data/1/observer/log/optimizer_trace_BkkGn1_trace_test.trac
/* Disable end-to-end tracing for the optimizer of the current session. */
obclient> CALL DBMS_XPLAN.DISABLE_OPT_TRACE();;
Query OK, 0 rows affected