Converse agora com nosso suporte
Chat com o suporte

SQL Optimizer for DB2 LUW 4.3.1 - User Guide

SQL Optimizer for IBM® DB2® LUW
About SQL Optimizer for IBM DB2 LUW Getting Started Options SQL Scanner SQL Optimizer SQL Formatter SQL Inspector Database Explorer SQL Repository Index Impact Analyzer Index Usage Analyzer User-Defined Temp Table Editor Functions SQL Functions SQL Information and Functions Activity Log
Tutorials About us Copyright

Save Optimized SQL

Quest SQL Optimizer for IBM® DB2® LUWmaximizes 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)

SQL Formatter

Database Explorer

SQL Scanner

SQL Inspector

SQL Repository

Index Impact Analyzer

Index Usage Analyzer

 

Save Optimized SQL

Use this procedure to save all the current SQL and index-set alternatives and their test results to a file that you can later reload into a SQL Optimizer session.

To save the current SQL and index-set alternatives

Select SQL | Save Optimized SQL.

Unlike a report, which captures information about the current alternatives in your SQL Optimizer session for viewing purposes, this process captures all the information needed to reload the current alternatives into a new session. See the Open Optimized SQL for more information.

 

Open Optimized SQL

After you have saved the SQL and index-set alternatives to a file, you can load them and their test results back into the SQL Optimizer window.

To reload the saved alternatives

  1. Select SQL | Open Optimized SQL.

  2. Select the file you want to load and click Open. This loads the saved alternative into the SQL Optimizer window.

The Open Optimized SQL Details window displays the following:

Summary Tab

Optimization Information

Displays the original connection and optimization settings information.

Last Saved Access plan Information

Displays the saved and current connection information and whether there are any changes in SQL structure and access plans.

User-Defined Temp Table Tab

If the SQL statement uses a temporary table, the User-Defined Temp Table tab appears in this window. It displays the DDL used to create the temporary table.

Changes Tab

If there are any changes to the SQL structure or the access plans, the Changes tab appears in this window. It displays the SQL text along with the saved and current access plan.

If there are any changes in SQL structure or access plans, you should refresh the access plans so that the reloaded image is a truth reflection of the current environment before any further testing is done.

To refresh the execution plans

Click Refresh Plan from the Open Optimized SQL Details dialog.

If there are changes in the access plan the corresponding SQL statements run time information is deleted. All invalid SQL statements are removed, except for the original SQL statement. You have an option to eliminate SQL statements with duplicate access plans.

After refresh, the Refresh Plan Details dialog can be displayed. This dialog displays the number of access plans refreshed, the total eliminated, and the total invalid plans.

To review the Open Optimized SQL Details dialog anytime

Select View | Show Open Rewrite SQL Details.

To review the Refresh Plans Details dialog anytime

Select View | Refresh Plan Details

 

View Open Optimized SQL Details

ClosedView the Open Optimized SQL Details Window

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

Cost only

The only change to the access plan was a change in the DB2 LUW Cost.

Structure and cost

Both the structure and DB2 LUW cost of the access plan changed.

Structure only

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.

 

Documentos relacionados

The document was helpful.

Selecione a classificação

I easily found the information I needed.

Selecione a classificação