Query Plan Overview This page last changed on Nov 28, 2008. .stickable, h1, h2, h3, h4, h5 false true Show Sticky notes Hide Sticky notes Save Cancel Oracle Data Service Integrator Documentation > Data Services Developer's Guide # Understanding Query Plans You can obtain a query plan for any function in your data service. Simply select the Query Plan tab and select a function, just as you would in Test View. In addition, as a convenience, you can obtain an ad hoc query plan for XQuery or SQL. ### Using Query Plan View The interface for Query Plan View is quite similar to that used for testing your query functions. You select a function or procedure from a drop down list and then click the Show Query Plan button. A query plan identifies the following query components: Joins Outer join Select statements Data sources Custom function calls Order-bys Remove duplicates There are several ways that a query plan can be viewed: Tree view. A collapsible graphical presentation of the query plan. Text view. Presents the information as text. ##### custOrdersItems Query Plan #### Query Plan Information and Warnings The query plan shows both informational and warning messages. When a section of the plan is flagged with a warning, the plan segment is highlighted in red. If you mouse over the segment, the warning message appears. Informational messages also can appear with plan segments. Such segments are highlighted in yellow. ##### Informational and Warning Messages Associated With Query Plans
Warning Message Type | Informational Message Type
--- | ---
XQuery compiler: Typematch. Typematch issues will be resolved by the compiler (may affect performance) | Audit. Auditing has been set for this particular function (will affect performance).
XQuery compiler: No where clause. There is no predicate associated with the query function (will affect performance). | Cache. Function is cached (may enhance performance).
XQuery compiler: Untyped data. Possible untyped atomic data found in the node constructor. | SQL pushdown generation details.
XQuery compiler: No such element. The element (name provided) is not found in in-scope schemas. |
SQL generation: missing key. Underlying table/view does not have a key. |
SQL generation: cannot generate subquery. isSubquery property is set to false on the data service. (See the "Function Annotations" section of the Understanding Data Services Annotations section of the XQuery Developer's Guide. |
SQL generation: cannot generate SQL for join expression. Unable to translate join condition. |
SQL generation: cannot generate SQL for aggregate expression (named). Function does not operate on a sequence. |
SQL generation: fn:string( ) function encountered. Use xs:string( ) instead since xs:string( ) can be pushed down to the database for processing. |
1. #### Printing or Saving Your Query Plan There are two right-click options associated with query plans: Prints the plan Saves the plan The default file name for the saved file will appear in the form: <dataServiceName_qp> If you right-click on the root element of the plan, Plan A right-mouse option on the root element in the plan allows you to print a query plan to a printer or a file. Right-click on any node in the plan and select either the print or print to a file option. If you print to a file the filename will be of type XML. The name of the file will be the function name followed by the letters _qp, as in: getCustomerView_qp.xml The file can be saved anywhere in your application. #### Loading a Previously Saved Query Plan You can load a previously saved query plan using the following steps: Select
       Load from file...
from the plan drop down box. In the Browse File dialog locate an existing query plan in the current project. Click Open. The selected query plan will be appear. ### Analyzing a Sample Query Assume a query returns data related to order details after it is passed an order ID and a customer ID. The following is a "pseudocode" description of the query: for electronic orders matching CustomerID and OrderID return order information and ship-to information for credit card information matching an AddressID return credit information and bill-to address information for electronic line item information matching the line item in the order return line item information The statements represent mappings or projections in the data service. This can be useful when trying to trace performance issues. The join conditions are identified in the plan as a left-outer join driven by a complex parameter. By definition, joins have left and right sides, each of which can contain additional joins. One of the best uses of the query plan is to see how the query logic works up the various data threads to return results. ### Working With a Query Plan Two options are available in Query Plan: Expand All. This right-click menu option expands the currently selected element and any children. If applied to the top-most element in the plan, all elements are expanded. Match highlighting. When you click on a variable name any elements (open or closed) containing a match for that variable are highlighted. This feature helps you trace variables in the query plan. #### Identifying Problematic Conditions Through the Query Plan When you show a query plan for a particular function, you may notice red or yellow highlighting of particular routines. These correspond to warnings or informational messages from the plan interpreter. For example, if a for statement is missing a where clause (potentially leading to slow performance or retrieval of a massive amount of data) a red warning will appear adjacent to the statement. Simply mouse-over the highlighted section of the plan to view the information or warning.

Document generated by Confluence on Jan 13, 2009 15:57