Using the Query Plan Facility

From DBArtisan
Jump to: navigation, search

Go Up to Execution and Execution-related SQL Editor options

Each RDBMS platform lets you view the execution path that your SQL follows. For details, see the following topics:

Viewing a Tree-based Query Plan (IBM DB2 for LUW, SQL Server, Sybase ASE)

For IBM DB2 for Windows, Unix, and Linux, Microsoft SQL Server, and Sybase ASE, you can view a tree-based representation of a query plan. The Query Plan toolbar button is a toggle. Set it to enable the Show Plan mode.

The Query Plan window displays data for the estimated costs, number of rows, and bytes returned by each plan step.

Note: For IBM DB2 for Linux, Unix, and Windows, a tree view of statements and associated costs is included.

To view a tree-based representation of a query plan

  1. Open a script.
  2. On the Query menu, select Query Plan.
    The Show Plan mode starts.
  3. To generate the Show Plan in a separate result window, click Execute.

Viewing a Tree-based, Graphical, or DBMS_XPLAN Query Plan (Oracle)

For Oracle, you can view a tree-based view, a graphical representation of a query plan, or view using the DBMS_XPLAN format, a format easily recognized by Oracle users. In each view, you can view details for each step and work with a number of column viewing options.

Note: The Options editor has a Default Query Plan setting that controls whether the default query plan display is graphical, tree-based, or DBMS_XPLAN. For details, see ISQL Options.

The Query Plan toolbar button is a toggle. Set it to enable the Show Plan mode.

To view a graphical representation of a query plan

  1. Open a script.
  2. On the Query menu, select Query Plan.
    The Show Plan mode starts.
  3. To generate the Show Plan in a separate result window, click Execute.

Options when working with the graphical Query Plan view include:

  • Hovering the mouse over an execution step node to display detailed cost details for that step
  • Right-clicking and choosing Find Node or Find Next Node to search large plans for nodes whose label contains a specified text string
  • Right-clicking and choosing Zoom In or Zoom Out
  • Right-clicking and choosing an Orientation sub-menu command to change the orientation of the view
  • Right-clicking and choosing Overview Window to open a small window showing the entire plan
  • Right-clicking and choosing Save to File to open a dialog that lets you save the graphical plan as a graphics file
  • Clicking the Query Plan button to toggle between the graphical view and a tree-based view
  • For multiple plans created from the same script/ISQL window, using the dropdown at the top of the plan to change the plan displayed

To view a DBMS_XPLAN representation of a query plan

  1. On the ISQL page, type the Query, and then click the Query Plan button on the toolbar.
  2. Once executed, right-click in the plan, and then select View As DBMS_XPLAN.

Options when working with the DBMS_XPLAN include:

  • Displaying a Level of Detail
    • BASIC displays the operation ID, name, and its option
    • TYPICAL (Default) displays the operation ID, name, option, number of rows, number of bytes, and the optimizer cost. Note that PRUNING, PARALLEL, and PREDICATE information appears only when it applies. This option also excludes PROJECTION, ALIAS, and REMOTE SQL information. If that information is desired, use the ALL option.
    • SERIAL is similar to the TYPICAL option, but it does not include PARALLEL information event when the plan executes in parallel.
    • ALL displays the same information as TYPICAL but also includes PROJECTION, ALIAS, and -- if the operation is distributed -- REMOTE SQL.
  • Displaying selected statistics if the target plan table also stores plan statistics columns.

Managing Explain Plan Columns

An easy-to-use interface allows you to manage how columns appear in the Explain Plan. You can hide/show columns, change the order in which the columns appear, and save the settings so that you can share with orders who are interested in using the same format.

Note that you cannot make changes to the Operation, Options, or Cost columns because those appear by default and are always available even when you scroll.

To change column settings

You can change the column settings by right-clicking on the Query Plan, and then selecting Adjust Columns.

  • In the Adjust Execution Plan Columns dialog box, checkboxes next to a Column Name allow you to toggle whether a column appears in the view.
  • Click a column name, and then use the arrows next to the scroll bar to move the column up or down in the display order. Use the double arrows as a shortcut to move to the top or bottom of the list. The move options also are available by right-clicking on the selected column name.
  • Be sure to click Save to retain your changes.
  • The Load button allows others to load the settings you configured, saved, and then sent to them. This allows you to collaborate over data appearing in the same format.

Viewing as a Graph

When you use the View As Graph option (right-click Query Plan, and then select View As Graph), note that the order in which items appear is reflective of the order in the Adjust Execution Plan Columns dialog box. Any changes made in that dialog box affect the appearance and order of the content in the graph.

Retrieved from "https://docwiki.embarcadero.com/DBArtisan/188/e/index.php?title=Using_the_Query_Plan_Facility&oldid=6219"