onmode and Y arguments: Change query plan measurements for a session (SQL administration API)

Use the onmode and Y arguments with the admin() or task() function to change the output of query plan measurements for an individual session.

Syntax

Read syntax diagramSkip visual syntax diagram
>>-EXECUTE FUNCTION--+-admin-+--(------------------------------->
                     '-task--'      

>--"onmode"--,--"Y"--"session_id"--,---------------------------->

>--+-0-----------------------+--)--;---------------------------><
   '-+-2-+--+--------------+-'         
     '-1-'  '-,--file_name-'           

Element Description Key Considerations
file_name The explain output file name. If the file's absolute path is not included, the example output file is created in the default example output file location. If the file already exists, explain output is appended to it. If a file already exists from the SET EXPLAIN statement, that file is not used until dynamic explain is turned off.
session_id Identifies the specific session. None.
-Y Dynamically change the value of the SET EXPLAIN statement. None.

Usage

You can use this function to emulate the SET EXPLAIN statement.

The last argument determines if record query measurements, including the plan of the query optimizer, an estimate of the number of rows returned, and the relative cost of the query.

Use the 2 argument to enable the database server to send the query plan to the explain output file.

Use the 1 argument to enable the database server to send the query plan and statistics, to the explain output file. This setting is equivalent to the SET EXPLAIN ON statement for a specific session.

Use the 0 argument to disable the output of query measurements to the explain output file for the current session. This setting is equivalent to the SET EXPLAIN OFF statement.

This function is equivalent to the onmode -Y command.

Example

The following example disables the output of query measurements for user session with an ID of 32:
EXECUTE FUNCTION task("onmode","Y","32","0");

Copyright© 2018 HCL Technologies Limited