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

SET EXPLAIN { { OFF | ON [ AVOID_EXECUTE ] } | FILE TO { expression | 'file_name' | file_name_var } }


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;

The following example suspends writing query plans to a file in the current session:

SET EXPLAIN OFF;