SET EXPLAIN statement
Use the SET EXPLAIN statement to enable or disable the recording measurements of queries in the current session, including the plan of the query optimizer, an estimate of the number of rows returned, and the relative cost of the query.
Syntax
| Element | Description | Restrictions | Syntax |
|---|---|---|---|
| expression | Expression that returns a file name specification | Must return a string satisfying the restrictions on the file name | Expression |
| file_name | The explain output file name. If the file's absolute path is not included, the explain output file will be created in the default explain output file location | Must conform to operating-system rules. If the file already exists, explain output will be appended to it. | Quoted String |
| file_name_var | Host variable that stores a file name | Must be a character data type | Language specific |
Usage
Output from a SET EXPLAIN ON statement is directed to the appropriate file until you issue a SET EXPLAIN OFF statement or until the program ends. If you do not enter a SET EXPLAIN statement, then the default behavior is OFF, and the database server does not generate measurements for queries.
The SET EXPLAIN statement executes during the database server optimization phase, which occurs when you initiate a query. For queries that are associated with a cursor, if the query is prepared and does not have host variables, optimization occurs when you prepare it. Otherwise, optimization occurs when you open the cursor.
The SET EXPLAIN statement provides various measurements of the work involved in performing a query.
- Option
- Effect
- ON
- Generates measurements for each subsequent query and writes the results to an output file in the current directory. If the file already exists, new output is appended to the existing file.
- AVOID_EXECUTE
- Prevents a SELECT, INSERT, MERGE, UPDATE, or DELETE statement from executing. The database server prints the query plan to an output file
- OFF
- Terminates activity of the SET EXPLAIN statement, so that measurements for subsequent queries are no longer generated or written to the output file
- FILE TO
- Generates measurements for each subsequent query and allows you to specify the location for the explain output file.
The following example writes the query plan in the explain output file for subsequent queries in the current session:
SET EXPLAIN ON;
SET EXPLAIN OFF;