View Open Optimized SQL Details
The Open Optimized SQL Details window displays information about the SQL Rewrite and Generate Indexes processes and the resulting access plans. The Open Optimized SQL Details window appears after the SQL and index-set alternatives are reloaded into the SQL Optimizer window.
To review the Open Optimized SQL Details window
Select View | Show Open Optimized SQL Details when the SQL Optimizer window is active.
Note: The Optimized SQL Details cannot be viewed if the SQL Text Pane is the active pane in the SQL Optimizer window.
The Open Optimized SQL Details window has the following tabs:
Summary Tab
The Summary tab provides detailed information about each SQL and index-set alternative and changes that have occurred in any of their access plans since the last SQL Rewrite or Generate Indexes was run.
Optimization Information
The optimization information includes the date and time of the last SQL Rewrite or Generate Indexes execution, how many alternatives were created, the connection information, and the optimization settings from the Options window at the time of the rewrite or index generation.
Last Saved Access Plan Information
The access plan information includes the date and time that the alternatives were saved, the number of alternatives created, connection information, and whether any of the access plans changed since the save. The changes are noted in the Current column as follows:
|
Current column |
Explanation |
|
The only change to the access plan was a change in the DB2 LUW Cost. | |
|
Both the structure and DB2 LUW cost of the access plan changed. | |
|
The structure of the access plan changed but the DB2 LUW cost remained the same. | |
|
No |
The current access plan is the same as the access plan that was saved with the SQL statement. |
Refresh Plan button
The Refresh Plan button enables you to replace old access plans with the current plans. This process retrieves the current access plans, deletes any alternative that is now invalid, and removes any run time information. You can select to eliminate alternatives that now have duplicate access plans. After the access plans are refreshed, the Refresh Plan Details window is displayed.
Note: The Refresh Plan button is only enabled when there is a difference between the current access plan and the saved plan.
Changes Tab
The Changes tab displays the text of the SQL statement in the top pane. The bottom pane displays the saved access plan and the current access plan side by side for comparison.
User-Defined Temp Table Tab
The User-Defined Temp Table tab is available only if the SQL statements use a temporary table. When the alternatives were saved, the DDL for the temporary table is also saved. When the alternatives are reloaded, the DDL is displayed.
Refresh Plan Details Window
The Refresh Plan Details window tells how many alternatives were refreshed and whether any of the alternatives were eliminated due to duplicate access plans or invalid SQL statements.
This window is only available after the saved access plans are replaced with the current access plans by clicking Refresh Plan on the Open Optimized SQL Details window.
SQL Formatter
Quest SQL Optimizer for IBM® DB2® LUW maximizes SQL performance by automating the manual, time-intensive and uncertain process of ensuring that SQL statements are performing as fast as possible. SQL Optimizer analyzes, rewrites, and evaluates SQL statements within multiple database objects, files, or SQL statements captured by the DB2 Event Monitor. With SQL Optimizer, you can analyze and optimize all your problem SQL from multiple sources. SQL Optimizer also provides you a complete index optimization and plan change analysis solution, from index recommendations to simulated index impact analysis, through comparison of multiple SQL access plans.
SQL Optimizer provides you with the following main modules.
SQL Optimizer (including SQL Rewrite and Generate Indexes functions)
About SQL Formatter
The SQL Formatter transforms a SQL statement into a more readable format by automatically indenting and aligning the text, keeping a consistent SQL layout throughout the source code, and hence making the SQL statement easier to understand and read. The SQL Formatter not only formats the SQL statement but also checks its syntax, and highlights variables, DB2 optimizer SQL options, and comments.