A query plan contains detailed, functional-level information about an XQuery. Reviewing the Query Plan is the first step in troubleshooting a data service function's performance bottlenecks, as it lets you view the query's construction.
Objectives
After completing this lesson, you will be able to:
Examine a query plan in three different views: tree, XML, and text.
Locate the SQL statement created to retrieve data from the underlying database.
Locate XML elements.
Overview
The most common reason for viewing a query plan is to review the SQL statement generated by the ALDSP query engine. However, the query plan also displays the following information for the physical data sources to be called during the query:
Physical Data Source
Information Provided
Relational
Data source name, actual SQL calls, and join parameters.
Web Services
Data source name, operation(s) called, and join parameters.
Custom Functions
Function name and join parameters.
XML and Delimited Files .
Filename
In addition, the following information is displayed for all functions:
Number of invocations.
Order in which the data source calls are made.
Compilation time.
Areas where calls are made in parallel.
Areas where there are Cartesian joins.
Areas where join algorithms are used, including parameter passing and index joins.
Any calls to a middle-tier cache.
26.1 Viewing the Query Plan
A query plan is generated for each data service function, when a ALDSP project is built.
Objectives
In this exercise, you will:
Get the query plan for the getCustomerProfile() function.
View the results in tree, XML, and text views.
Instructions
Open XQueries.ds in Query Plan View.
Select getTop10Customers() from the function drop down list.
Click Show Query Plan. The query plan opens in tree view, as displayed in Figure 26-1.
Figure 26-1 Query Plan as a Tree Structure
Click the XML button to view the Query Plan as an XML document.
Figure 26-2 Query Plan as an XML Document
Click the Text button to view the Query Plan as a text document.
Figure 26-3 Query Plan as a Text Document
26.2 Locating the SQL Statement in a Query Plan
SQL statements are generated for functions that call relational databases.
Objectives
In this exercise, you will:
Locate an SQL statement within the query.
Review the contents of the SQL statement.
Instructions
Open the Query Plan as an XML document.
Expand the FLWOR nodes until you see the #cdata-section. This is the SQL statement for the query.
Figure 26-4 Query Plan View of SQL Statements
As a reminder, this function retrieves customer and order amount information. In addition, the result set is ordered in descending order by order amount.
26.3 Locating XML Elements
XML elements identify the data that will be returned by the query function. Each XML element is identified with a QName.
Objectives
In this exercise, you will:
Locate all XML elements within the query.
Review the contents of the XML element lines.
Instructions
In Query Plan View, expand the return node.
Notice all the XML elements that will be returned when the function is executed.
Figure 26-5 Query Plan View of XML Elements
Lesson Summary
In this lesson, you learned how to:
Examine a query plan as tree, XML, and text documents.
Locate the SQL statement that was created to retrieve data from the underlying database.
