Viewing the Query Execution Plan and Cost

The execution plan shows how the table(s) referenced by the SQL statement will be scanned (plain sequential scan and index scan).

The SQL statement execution cost is the estimate at how long it will take to run the statement (measured in cost units that are arbitrary, but conventionally mean disk page fetches).

Follow the steps below to view the plan and cost for a required SQL query:

  1. Enter the query or use an existing query in the SQL Terminal and click on the SQL Terminal toolbar to view explain plan.

    To view explain plan with analyze, click the drop-down from , select Include Analyze, and click .

    The Execution Plan opens in tree view format as a new tab at the bottom by default. The display mode has a tree shape and text style.

    The data shown in tree explain plan and visual explain may vary, since the execution parameters considered by both are not the same.

    Following are the parameters selected for explain plan with/without analyze and the columns displayed:

    Table 1 Explain plan options

    Explain Plan Type

    Parameters Selected

    Columns

    Include Analyze unchecked (default setting)

    Verbose, Costs

    Node type, startup cost, total cost, rows, width, and additional Info

    Include Analyze checked

    Analyze, Verbose, Costs, Buffers, Timing

    Node type, startup cost, total cost, rows, width, Actual startup time, Actual total time, Actual Rows, Actual loops, and Additional Info

    Additional Info column includes, predicate information (filter predicate, hash condition), distribution key and output information along with the node type information.

    The tree view of plan categorizes nodes into 16 types. In tree view, each node will be preceded with corresponding type of icon. Following is the list of node categories with icons:

    Table 2 Node categories with Icon

    Node Category

    Icon

    Aggregate

    Group Aggregate

    Function

    Hash

    Hash Join

    Nested Loop

    Nested Loop Join

    Modify Table

    Partition Iterator

    Row Adapter

    Seq Scan on

    Set Operator

    Sort

    Stream

    Union

    Unknown

    Hover over the highlighted cells to identify the heaviest, costliest, and slowest node. Cells will be highlighted only for tree view.

    If multiple queries are selected, explain plan with/without analyze will be displayed only for last query selected.

    Each time execution plan is executed, the plan opens in a new tab.

    If the connection is lost and the database is still connected in Object Browser, then Connection Error dialog box is displayed:

    • Yes - The connection is reestablished and retrieves explain plan and cost.
    • No - Disconnects database in Object Browser.

    Toolbar menu in the Execution Plan window:

    Toolbar Name

    Toolbar Icon

    Description

    Tree Format

    This icon is used view explain plan in tree format.

    Text Format

    This icon is used view explain plan in text format.

    Copy

    This icon is used to copy selected content from result window to clipboard. Shortcut key - Ctrl+C.

    Save

    This icon is used to save the explain plan in text format.

    Refer to Executing SQL Queries for information refresh, SQL preview, and search bar.

    Refresh operation re-executes the explain/analyze query and refreshes the plan in the existing tab.

    The result is displayed in the Messages tab.

Parent topic: SQL Terminal