You can use Visual Explain to view many types of information.
The information includes:
- Information about each operation (icon) in the query graph
- Highlight expensive icons
- The statistics and index advisor
- The predicate implementation of the query
- Basic and detailed information in the graph
Information about each operation (icon) in the query graph
As stated before, the icons in the graph represent operations that occur during the implementation of the query. The order of operations is shown by the arrows connecting the icons. If parallelism was used to process an operation, the arrows are doubled. Occasionally, the optimizer "shares" hash tables with different operations in a query, causing the lines of the query to cross.
You can view information about an operation by selecting the icon. Information is displayed in the Attributes table in the right pane. To view information about the environment, click an icon and then select Display query environment from the Action menu. Finally, you can view more information about the icon by right-clicking the icon and selecting Help.
Highlight expensive icons
You can highlight problem areas (expensive icons) in your query using Visual Explain. Visual Explain offers you two types of expensive icons to highlight: by processing time or number of rows. You can highlight icons by selecting Highlight expensive icons from the View menu.
The statistics and index advisor
During the implementation of a query, the optimizer can determine if statistics need to be created or refreshed, or if an index might make the query run faster. You can view these recommendations using the Statistics and Index Advisor from Visual Explain. Start the advisor by selecting Advisor from the Action menu. Additionally, you can begin collecting statistics or create an index directly from the advisor.
The predicate implementation of the query
Visual explain allows you to view the implementation of query predicates. Predicate implementation is represented by a blue plus sign next to an icon. You can expand this view by right-clicking the icon and selecting Expand. or open it into another window. Click an icon to view attributes about the operation. To collapse the view, right-click anywhere in the window and select Collapse. This function is only available on V5R3 or later systems.
The optimizer can also use the Look Ahead Predicate Generation to minimize the random the I/O costs of a join. To highlight predicates that used this method, select Highlight LPG from the View menu.
Basic and full information in the graph
Visual Explain also presents information in two different views: basic and full. The basic view only shows those icons that are necessary to understand the implementation of the SQL statement, thus excluding some preliminary or intermediate operations that are not essential for understanding the main flow of query implementation. The full view may show more icons that further depict the flow of the execution tree. You can change the graph detail by select Graph Detail from the Options menu and selecting either Basic or Full. The default view is Basic. Note that in order to see all of the detail for a Full view, you will need to change the Graph Detail to Full, close out Visual Explain, and run the query again. The setting for Graph Detail will persist.
For more information about Visual Explain and the different options that are available, see the Visual Explain online help.
Refresh the Visual Explain diagram
For long running queries, you can refresh the visual explain graph with runtime statistical information before the query is complete. Refresh also updates the appropriate information in the attributes section of the icon shown on the right of the screen. In order to use the Refresh option, you need to select Explain while Running from the Run SQL Scripts window.
To refresh the diagram, select Refresh from the View menu. Or click the Refresh button in the toolbar.
