Statement-level output

The first section of showplan output for each query plan presents statement-level information, including the statement and line number in the batch or stored procedure of the query for which the query plan was generated:

QUERY PLAN FOR STATEMENT N (at line N).

This message may be followed by a series of messages that apply to the statement’s entire query plan. If the query plan was generated using an abstract plan about how the abstract plan was forced:

• If an explicit abstract plan was given by a plan clause in the SQL statement, the message is:

  Optimized using the Abstract Plan in the PLAN clause.
  

• If an abstract plan has been internally generated (that is, for alter table and reorg commands that are executed in parallel), the message is:

  Optimized using the forced options (internally generated Abstract Plan).
  

• If a new statement is cached, the output includes:

  STEP 1
  
  The type of query is EXECUTE.
  Executing a newly cached statement.
  

• If a cached statement is reused, the output includes:

  STEP 1
  
  The type of query is EXECUTE.
  Executing a previously cached statement.
  

• If the query recompiles the statement, the output includes:

  QUERY PLAN IS RECOMPILED DUE TO SCHEMACT.
  
  THE RECOMPILED QUERY PLAN IS:
  
  . . .
  
  QUERY PLAN FOR STATEMENT 1 (at line 1)
  
  . . . 
  

• If an abstract plan has been retrieved from sysqueryplans because automatic abstract plan usage is enabled, the message is:

  Optimized using an Abstract Plan (ID : N).
  

• If the query plan is a parallel query plan, the following message shows the number of processes (coordinator plus worker) that are required to execute the query plan:

  Executed in parallel by coordinating process and N worker processes.
  

• If the query plan was optimized using simulated statistics, this message appears next:

  Optimized using simulated statistics.
  

• The output includes VA= which indicates the virtual address for the operator, and the order in which each operator is executed. The query processor starts at VA=0. Generally, scan nodes (leaf nodes) are executed first.

  The VA= in the showplan output is available for Adaptive Server version 15.0.2 ESD #2 and later. You will not see VA= in earlier versions of Adaptive Server.

• Adaptive Server uses a scan descriptor for each database object that is accessed during query execution. By default, each connection (or each worker process for parallel query plans) has 28 scan descriptors. If the query plan requires access to more than 28 database objects, auxiliary scan descriptors are allocated from a global pool. If the query plan uses auxiliary scan descriptors, this message is printed, showing the total number required:

  Auxiliary scan descriptors required: N
  

• This message shows the total number of operators appearing in the query plan:

  N operator(s) under root
  

• The next message shows the type of query for the query plan. For query plans, the query type is select, insert, delete, or update:

  The type of query is SELECT.
  

• A final statement-level message is printed at the end of showplan output if Adaptive Server is configured to enable resource limits. The message displays the optimizer’s total estimated cost of logical and physical I/O:

  Total estimated I/O cost for statement N (at line M): X.
  

The following query, with showplan output, shows some of these messages:

use pubs2
go
set showplan on
go
select stores.stor_name, sales.ord_num
from stores, sales, salesdetail
where salesdetail.stor_id = sales.stor_id
and stores.stor_id = sales.stor_id
plan " ( m_join ( i_scan salesdetailind salesdetail)
( m_join ( i_scan salesind sales ) ( sort ( t_scan stores ) ) ) )"

QUERY PLAN FOR STATEMENT 1 (at line 1).
Optimized using the Abstract Plan in the PLAN clause.

STEP 1
The type of query is SELECT.

6 operator(s) under root

ROOT:EMIT Operator (VA = 6)

    |MERGE JOIN Operator (Join Type: Inner Join) (VA = 5)
    | Using Worktable3 for internal storage.
    |  Key Count: 1
    |  Key Ordering: ASC
    |
    |   |SCAN Operator (VA = 0)
    |   |  FROM TABLE
    |   |  salesdetail
    |   |  Index : salesdetailind
    |   |  Forward Scan.
    |   |  Positioning at index start.
    |   |  Index contains all needed columns. Base table will not be read.
    |   |  Using I/O Size 2 Kbytes for index leaf pages.
    |   |  With LRU Buffer Replacement Strategy for index leaf pages.
    |
    |   |MERGE JOIN Operator (Join Type: Inner Join) (VA = 4)
    |   | Using Worktable2 for internal storage.
    |   |  Key Count: 1
    |   |  Key Ordering: ASC
    |   |
    |   |   |SCAN Operator (VA = 1)
    |   |   |  FROM TABLE
    |   |   |  sales
    |   |   |  Table Scan.
    |   |   |  Forward Scan.
    |   |   |  Positioning at start of table.
    |   |   |  Using I/O Size 2 Kbytes for data pages.
    |   |   |  With LRU Buffer Replacement Strategy for data pages.
    |   |
    |   |   |SORT Operator (VA = 3)
    |   |   | Using Worktable1 for internal storage.
    |   |   |
    |   |   |   |SCAN Operator (VA = 2)
    |   |   |   |  FROM TABLE
    |   |   |   |  stores
    |   |   |   |  Table Scan.
    |   |   |   |  Forward Scan.
    |   |   |   |  Positioning at start of table.
    |   |   |   |  Using I/O Size 2 Kbytes for data pages.
    |   |   |   |  With LRU Buffer Replacement Strategy for data pages.

After the statement level output, the query plan appears. The showplan output of the query plan consists of two components:

• The names of the operators (some provide additional information) to show which operations are being executed in the query plan.

• Vertical bars (the “|” symbol) with indentation to show the shape of the query plan operator tree.