You can save the Optimized SQL statements. Unlike a report, this file is used to reload the SQL back to the SQL Optimizer window using the Open Optimized SQL function.
To save the original SQL and the SQL alternatives
Select SQL | Save Optimized SQL.
After you have saved the optimized SQL statements to a file, you can load them back through the SQL Optimizer window:
To load a saved optimized SQL statements and alternatives
Select SQL | Open Optimized SQL.
Displays the original connection and optimization settings information.
Last Saved Query plan Information
Displays the saved and current connection information and whether there are any changes in SQL structure and query plans.
If the SQL statement uses a temporary table, the User-Defined Temp Table tab displays in this window. It displays the DDL used to create the temporary table.
If there are any changes to the SQL structure or the access plans, the Changes tab displays inI this window. It displays the SQL text along with the saved and current query plan.
If there are any changes in either SQL structure or query plans it is advisable that you refresh the query plans so that the reloaded image is a truth reflection of the current environment before any further testing is done. Click Refresh Plan from the Open Optimized SQL Details window. If there are changes in the query plan the corresponding SQL statements run time and statistics information are deleted. All invalid SQL statements are removed, except for the original SQL statement. You have an option to eliminate SQL statements with duplicate query plans.
After refresh, the Refresh Plan Details window can be displayed. This window displays the number of query plans refreshed, the total eliminated, and invalid plans.
The Open Optimized SQL Details and Refresh Plan Details windows can be reviewed at a later stage.
To view the details
Select View | Show Open Optimized SQL Details and View | Refresh Plan Details.
The Open Optimized SQL Details window displays information about the optimization process and the resulting query plans. The Open Optimized SQL Details window displays after the SQL statements are reloaded into the SQL Optimizer window. You can review the Open Optimized SQL Details window once it has been closed by selecting View | Show Open Optimized SQL Details when the SQL Optimizer window is active.
Note: The Optimized SQL Details cannot be viewed if the SQL Editor pane is the active pane in the SQL Optimizer window.
The Open Optimized SQL Details window has the following tabs:
The Summary tab provides detailed information about the optimization of the SQL statements and changes that may have occurred in the query plans of the original SQL or alternative SQL statements since the optimization process was done.
The optimization information includes the date and time of the optimization, how many alternatives were created, the connection information, and the optimization settings from the Preferences window at the time of this optimization.
The query plan information includes the date and time of the SQL statements that were saved, how many alternatives were created, the connection information, and if any of the query plans have changed since the time they were saved.
During the process of opening the Save Optimized SQL, the current query plan is retrieved and compared to the saved query plan. The changes are noted in the Current column as follows:
|Current column item||Description|
The only change to the query plan was a change in the Estimated I/O cost.
Structure and cost
Both the structure and Estimated I/O cost of the query plan changed.
The structure of the query plan changed but the Estimated I/O cost remained the same.
The current query plan is the same as the query plan that was saved with the SQL statement.
The Refresh Plan button enables you to replace old query plans with the current plans. This process retrieves the current query plans, deletes any SQL statement that is now invalid, removes any run time information. You can select to eliminate SQL alternatives that now have duplicate query plans. After the query plans are refreshed, the Refresh Plan Details window displays.
Note: The Refresh Plan button is only enabled when there is a difference between the current query plan and the saved plan.
The Changes tab displays the text of the SQL statement in the top pane. The bottom pane displays the saved query plan and the current query plan side by side for comparison.
The User-Defined Temp Table tab is available only if the SQL statements use a temporary table. When the Optimized SQL statements were saved, the DDL for creating the temporary table is saved with them and when the SQL statements are reloaded the DDL displays.