Report that shows the query plan chosen by the optimizer
Any user who runs a query can use the SET EXPLAIN statement or the EXPLAIN directive to display the query plan that the optimizer chooses.
For information about how to specify the directives, see EXPLAIN directives. The user enters the SET
EXPLAIN ON statement or the SET EXPLAIN ON AVOID_EXECUTE statement
before the SQL statement
for the query, as the following example shows.
SET EXPLAIN ON AVOID_EXECUTE;
SELECT * FROM customer, orders
WHERE customer.customer_num = orders.customer_num
AND customer.lname = "Higgins";If a user does not have any access to SQL code source, the Database Administrator can set dynamically the SET EXPLAIN using the onmode -Y command.
After the database server executes the SET EXPLAIN ON statement or sets dynamically the SET EXPLAIN with onmode -Y command, the server writes an explanation of each query plan to a file for subsequent queries that the user enters.