Displaying the Plan of a Currently Executing Query

A new feature of version 8.51 of Extended Parallel Server supports the immediate command-line option to the onmode -q explain session_id command to read and send as output to a file the query optimizer plan for a currently executing DML statement.

The following diagram shows the syntax of onmode that supports this feature:

                                    .-on--------.
>>-onmode -q explain--session_id--+-+-immediate-+--+--------------------------+-+-><
                                  |                '-+-file-------+--filename-' |
                                  |                  '-fileappend-'             |
                                  '-+-off-----------+---------------------------'
                                    '-avoid_execute-'
 

Here session_id and filename have the same syntax and semantics that the previous section described for XPS 8.32.

The immediate command-line option can have the following effects:

• Append the query optimizer plan for the currently executing DML statement to the default sqexplain.out file in current working directory:

  onmode -q explain session_id immediate
  

• Write the query optimizer plan for the currently executing DML statement to a specified filename in current working directory, or at a specified pathname from that directory, overwriting any contents of that file:

  onmode -q explain session_id immediate file filename
  

• Append the query optimizer plan for the currently executing DML statement to a specified filename in current working directory, or at a specified pathname from that directory:

  onmode -q explain session_id immediate fileappend filename
  

If the DBA is not aware of the current working directory of the user, or wishes to save the output in some fixed directory, the full pathname can be specified in the filename as a parameter to file or fileappend. For example, the following command writes the query plan of the current query to the file /tmp/mysqexplain.out:

onmode -q explain 1.16 immediate file /tmp/mysqexplain.out

If neither on nor immediate are passed to the file and fileappend parameters, the default is on.

The behavior of other options of onmode -q explain is not changed by this feature.