The SET_OPT_TRACE_PARAMETER procedure sets end-to-end tracing parameters for the optimizer of the current session.
Syntax
DBMS_XPLAN.SET_OPT_TRACE_PARAMETER(
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
Specify level and the log file suffix for trace logs.
obclient> CALL DBMS_XPLAN.SET_OPT_TRACE_PARAMETER(identifier=>'trace_test', `level`=>2);
Query OK, 0 rows affected