Query Plan Viewer Tasks
This section discusses the following tasks:
•
To create a query plan
•
To start Query Plan Viewer
•
To view a query plan
•
To export a query plan to an XML file
•
To adjust the display size of a query plan in the Plan Viewer
•
To scroll through a query plan in the Plan Viewer
•
To reload a changed query plan
•
To view details of a query plan node
•
To view a subquery on a query plan
►
To create a query plan
1
Execute SET QRYPLAN = on to turn on the creation of a query plan.
2
Execute the SET QRYPLANOUTPUT statement and specify the location and name of the query plan file.
See Query Plan Settings.
3
Execute an SQL SELECT, INSERT, UPDATE, or DELETE statement (which creates its corresponding query plan).
4
Execute SET QRYPLAN = off to turn off the creation of query plans.
►
To start Query Plan Viewer
1
Perform one of the following actions:
a.
In Pervasive PSQL Control Center, click ToolsQuery Plan Viewer.
b.
Execute the file w3sqlqpv.exe located in the PSQL\Bin directory.
►
To view a query plan
1
In the Query Viewer, click FileOpen.
2
Navigate to the location of the desired query plan file, click the file then click Open.
The title bar of the Query Viewer informs you how many query plans are open and which plan you are viewing.
3
If you have more than one query plan open, use the View menu commands to navigate among the plans:
•
First or Ctrl + F. Displays the earliest loaded query plan.
•
Last or Ctrl + L. Displays the latest loaded query plan.
•
Next or Ctrl + N. Displays the next latest query plan.
•
Prev(ious) or Ctrl + P. Displays the next earliest query plan.
•
Goto or Ctrl + G. Displays the query plan based on the ordinal number of the loaded plans.
►
To export a query plan to an XML file
1
In the Query Viewer, click FileExport XML.
2
Navigate to the location of the desired query plan XML file, click the file then click Open.
*Note: You can specify the name of a new XML file in the same dialog box.
*Tip: The Export XML menu item is enabled only if the query plan is loaded into the viewer.
The following table explains the schema of an XML file derived from an SQL query.
Element and Attributes
Explanation
Parent Element
Child Elements
<QPF filename=filename>
filename: path and name of QPF file
QPF file on which XML file is based; one per XML file
Header Information
<Query>
<Query number=number>
number: query number displayed in Query Plan Viewer; first is 1, second is 2, and so forth.
Query in the <QPF> file; at least one per XML file
<QPF>
<SQL>
<TreeRoot>
<SQL>
SQL statement used to generate plan
<Query>
 
<TreeRoot name=name>
name: Root Query or Subquery X
Indicates root query or subquery
<Query>
All Node Elements:
<Join>
<Filter>
<Base>
<Distinct>
<Set>
<FCalc>
<Group>
<GroupBreak>
<OrderedTempTable>
<Union>
<Subquery>
Node Elements
Each is a node in the query plan tree (a part of the SQL statement)
<TreeRoot> or the <Child>, <LeftChild>, or <RightChild> of another node element
 
<Join>
 
<TreeRoot>
<Text>
<Properties>
<LeftChild>
<RightChild>
<Filter>
 
<TreeRoot>
<Text>
<Properties>
<Child>
<Base>
Represents a leaf in diagram tree
<TreeRoot>
<Text>
<Properties>
<Distinct>
 
<TreeRoot>
<Properties>
<Child>
<Set>
 
<TreeRoot>
<Text>
<Properties>
<SetString>
<Child>
<FCalc>
 
<TreeRoot>
<Properties>
<LeftChild>
<RightChild>
<Group>
 
<TreeRoot>
<Properties>
<Child>
<GroupBreak>
 
<TreeRoot>
<Properties>
<Child>
<OrderedTempTable>
 
<TreeRoot>
<Properties>
<Child>
<Union>
 
<TreeRoot>
<Properties>
<Child>
<Subquery>
 
<TreeRoot>
<Properties>
<Child>
Node Element Children
Varies; provide additional information about node or link to child of node tag
Varies
 
<Text>
 
<Join>
<Filter>
<Set>
Optionally, <Base>
 
<Properties>
 
All node elements
 
<SetString>
 
<Set>
 
<Child>
 
<Filter>
<Distinct>
<Set>
<Group>
<GroupBreak>
<OrderedTempTable>
<Union>
<Subquery>
 
<LeftChild >
 
<Join>
<FCalc>
 
<RightChild>
 
<Join>
optionally, <FCalc>
 
►
To adjust the display size of a query plan in the Plan Viewer
Click View then a desired sizing command:
•
Autofit. Sizes the query plan so that the entire plan is viewable in the Plan Viewer. The view resizes if you resize the window. Autofit is the default when you view a query plan.
•
100%, 50%, or 25%. Sizes the query plan to the specified percentage.
•
Percent. Sizes the query plan to the percent you specify.
•
Zoom In (-) or Zoom Out (+). Enlarges the size of the query plan (zoom in) or reduces the size of the query plan (zoom out). You can zoom between 5% and 500%.
►
To scroll through a query plan in the Plan Viewer
Click View then a desired scroll command:
•
Scroll Right or Right Arrow. Scrolls toward the right side of the pane.
•
Scroll Left or Left Arrow. Scrolls toward the left side of the pane.
•
Scroll Up or Up Arrow. Scrolls toward the top of the pane.
•
Scroll Down or Down Arrow. Scrolls toward the bottom of the pane.
►
To reload a changed query plan
In the Query Viewer, click FileRefresh.
This re-reads the currently loaded query plan file.
►
To view details of a query plan node
In the Query Viewer, double-click one of the following nodes:
•
Table
•
Filter
•
Subquery
•
Ordered temporary table
See Node Details.
►
To view a subquery on a query plan
In the Plan Viewer, click Subquery then the number of the subquery (the first subquery in the main query corresponds to "Subquery 1," the second subquery corresponds to "Subquery 2" and so forth). A query plan can contain any number of subqueries, or none. All subqueries for a query plan appear on the Subquery menu. When you select a subquery, its name appears in parentheses in the Plan Viewer title.