The SET_OPT_TRACE_PARAMETER procedure is used to modify the parameters for optimizer full-link tracing in 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 the SQL statements to be traced. For example, if you want to trace only specific SQL statements within PL/SQL functions during a test, you can set the sql_id parameter. Once set, only the specified SQL statements will be traced; otherwise, all SQL statements will be traced. |
| identifier | Used to mark the suffix of the trace file, making it easier for users to locate their trace files. |
| level | Used to set the tracing level:
Note: 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
Set the level and the suffix for the trace log file.
obclient> CALL DBMS_XPLAN.SET_OPT_TRACE_PARAMETER(identifier=>'trace_test', `level`=>2);
Query OK, 0 rows affected