The SET_OPT_TRACE_PARAMETER procedure is used to modify the end-to-end tracing parameters of the optimizer for 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 | Used to mark SQL statements for tracing. For example, if you want to run a PL program for testing but only trace specific SQL statements within the PL function, you can set sql_id marker. Once set, sql_id, only specific SQL statements will be traced; otherwise, all SQL statements will be traced. |
| identifier | A suffix used to identify trace files, making it easier for users to locate their own trace files. |
| level | Used to set the tracing level. Valid values:
Note: level is a database keyword and must be enclosed in backticks (`). |
Examples
Set the level for trace logs and the log file suffix.
obclient> CALL DBMS_XPLAN.SET_OPT_TRACE_PARAMETER(identifier=>'trace_test', `level`=>2);
Query OK, 0 rows affected