Displaying Results of Queries (Revision #0)

The Query Analyzer provides several different query results tools: Text Results, Oracle DBMS_OUTPUT, Text History Results, Grid Results, Pivot Grid Results, Form Results, Execution Plan, Client Statistics, and Script Details. Once a query has been executed within the Query Analyzer, the results display in a pane at the bottom of the Query Window with tabs for each result type (when enabled). The various results tabs are hidden or dsiplayed by clicking the results icons in the Main Application Toolbar or selecting them underneath the Main Application Query Menu. The Results Tabs in the Results Pane can be rearranged by dragging them into new positions.

Contents

• Text Results
• Oracle DBMS_OUTPUT
• Text History Results
• Grid Results
• Pivot Grid Results
• Form Results
• Execution Plan
• Client Statistics
• Script Details
• Pinning Grid Results
• Printing Grid and Text results

Text Results -Text Results displays formated text, which is cleared for every query executed. Text Results most resemble the output viewed within a commandline interface to a database. On the top right of the text results is a box to indicate errors. If the box is red there are warnings or errors in the results. If the box is green, the query completed successfully without warnings or errors. An annotation bar runs on the right side of the results. Colored marks highlight where the errors or warnings occur. Clicking on them sends the cursor to that point in the results. A black marker with centered dot indicates the current cursor position within the Text Results.

To vastly reduce the amount of memory used by Aqua Data Studio on large results sets (more than 100,000 rows), disable Text Results, and enable ONLY Grid Results in the Query Analyzer Toolbar BEFORE executing. Text Results is very memory intensive on large results sets.

Oracle DBMS_OUTPUT -Oracle allows a user to print out messages from a query, PL/SQL block or stored procedure. ADS will display this output if it is used by the user. An annotation bar runs on the right side of the results. Colored marks highlight where the errors or warnings occur. Clicking on them sends the cursor to that point in the results. A black marker with centered dot indicates the current cursor position within the DBMS_OUTPUT in the Text Results Pane.

Text History Results -The Text History is formated text and is appended to the previous text. On the top right of the  text results is a box to indicate errors. If the box is red there are warnings or errors in the results. If the box is green, the query completed successfully without warnings or errors. An annotation bar runs on the right side of the results. Colored marks highlight where the errors or warnings occur. Clicking on them sends the cursor to that point in the results. A black marker with centered dot indicates the current cursor position.

Grid Results -Results are displayed in a grid which can be sorted by clicking on column headings. One grid per statement is displayed. The Grid Results offers an expansive set of features for sorting, searching, saving, printing and other functionality. The column headers in the grid can be rearranged by dragging them, and sorted by CTRL + Click for multiple columns. Aggregate Functions display statistics on selected data from the grid. The Quickfilter at the top right of the grid rapidly filters what results are currently displayed. When there are multiple statements separated by GO or /, the SQL Preview icon at the top left of each Grid Result assists in identifying what statement generated each Grid Result. Grid Results can also be Pinned

Read more about Grid Results here

Enabling ONLY Grid Results BEFORE querying a large results sets (100,000 rows or more) vastly increases Aqua Data Studio's speed, and dramatically lowers its memory footprint. Grid Results are the preferred method of viewing query results in the Query Analyzer.

Pivot Grid Results - The Pivot Grid presents the results as a set of user-configurable tables with data sorting, the equivalent of a Pivot Table in Microsoft Excel. One pivot grid is displayed per result set. The Pivot Grid Results offer an expansive set of features, including scriptable charts and the ability to save to Excel spreadsheets with charting.

Read more about Pivot Grid Results here

Read more on Aqua Data Studio's Pivot Grid Charts here.

They are particularly useful with large amounts of data, helping a user to classify data by categories and obtaining summaries without the time consuming search that it would normally take to look through all of the data. Pivot grids, once configured, can be refreshed to show changes in the data without having to organize the pivot structure and columns each time.

Form Results - Form Results displays database results as records with column names on the left and value on the right, as if the information was appearing in a form. Next and Previous buttons allow cycling through the records. Records can also be viewed by entering a record number.

Execution Plan - Execution Plans display detailed statistics on the execution cost of the statement or script. A color coded visual execution plan will be generated for each query executed in the Query Analyzer. Subtree Cost, Node Cost, CPU cost, Estimated Input/Output, Estimated Rows, Executes, Argument, Type, Average Row Size, Defined Values, Output List, Warnings, Parallel, Estimated Executions, Physical Operations, Logical Operations and Statement Text are presented in a table. The contents of an Execution Plan can be added to a Visual Explain Whiteboard so that comparisons can be made to query variations. Execution Plans are extremely useful in query tuning for speed optimization.

Read more about Execution Plans and the Visual Explain Whiteboard here.

Client Statistics - Displays detailed statistics on the execution of the statement or script.

Total Processing: This is the amount of time it takes from the second you click Execute until ADS finishes displaying all of the results on the screen. This is the Script Execution time plus the UI time to process the results and display them.

Client Processing: This is the Total Processing time minus the server wait time.

Server wait time: Server wait time: This is the amount of time ADS spends waiting on the server during server-side methods calls to execute and retrieve results. For example, the executing of a SELECT query, ADS might wait 3 seconds for the query to execute on the server (CPU Server Cycles), then another 5 seconds retrieving results (Network Transfer Cycles). So, this is the best estimate ADS can make on how much time the server spends executing and sending the data to the client.

Script Parse Time: This is the amount of time it takes to parse the script. This is usually zero, unless you have a very large file.

Script Execution Time: This is the total time it takes from the beginning to the end of the execution of the script. This is the raw execution time which doesn’t include any time ADS spends formatting the results to be displayed on the screen.

Script Details - The Script Detail Results lists each statement executed in a script with detail indicating success, failure and/or warnings. Double-clicking on an item highlights the associated lines in the script.

Pinning Grid Results - A Grid Result can be pinned so that if a query is reexecuted the pinned results do not get cleared. Clicking the horizontal pin icon in the Query Analyzer Grid Results toolbar tab (so that a vertical pin appears) pins the result. Pinned Results get their own tab within their Result Pane. Pinned and unpinned results tabs can be navigated just like Query Analyzer tabs with right click context menus or the tab navigator to the right side of the pinned and unpinned tabs. The orientation of the pin icon helps distinguish which Results Pane tabs are pinned (vertical pin) and which are not pinned (horizontal pin).

See File > Options > Results > Grid Results for Pinning Options including Auto Pinning

Printing Grid and Text results - Grid, Text, and Execution Plan results can be printed using a physical or software printer. The printing can be configured using options presented in the Print dialog such as: document header and footer, paper size, margins and document orientation. The Print Preview displays the layout of the item selected for printing. Once printing begins, Aqua Data Studio provides a popup status dialog that both shows what page is currently printing and provides the ability to cancel the print job.