Setting the optimization class

When you specify an optimization level, consider whether a query uses static or dynamic SQL and XQuery statements, and whether the same dynamic query is repeatedly executed.

About this task

For static SQL and XQuery statements, the query compilation time and resources are expended only once, and the resulting plan can be used many times. In general, static SQL and XQuery statements should always use the default query optimization class (5). Because dynamic statements are bound and executed at run time, consider whether the overhead of additional optimization for dynamic statements improves overall performance. However, if the same dynamic SQL or XQuery statement is executed repeatedly, the selected access plan is cached. Such statements can use the same optimization levels as static SQL and XQuery statements.

If you are not sure whether a query might benefit from additional optimization, or you are concerned about compilation time and resource consumption, consider benchmark testing.

Procedure

To specify a query optimization class:

Analyze performance factors.
- For a dynamic query statement, tests should compare the average run time for the statement. Use the following formula to estimate the average run time:
```
   compilation time + sum of execution times for all iterations
   ------------------------------------------------------------
                       number of iterations
```
  The number of iterations represents the number of times that you expect the statement might be executed each time that it is compiled.
  
  Note: After initial compilation, dynamic SQL and XQuery statements are recompiled whenever a change to the environment requires it. If the environment does not change after a statement is cached, subsequent PREPARE statements reuse the cached statement.
- For static SQL and XQuery statements, compare the statement run times.
  Although you might also be interested in the compilation time of static SQL and XQuery statements, the total compilation and execution time for a static statement is difficult to assess in any meaningful context. Comparing the total times does not recognize the fact that a static statement can be executed many times whenever it is bound, and that such a statement is generally not bound during run time.
Specify the optimization class.
- Dynamic SQL and XQuery statements use the optimization class that is specified by the CURRENT QUERY OPTIMIZATION special register. For example, the following statement sets the optimization class to 1:
```
   SET CURRENT QUERY OPTIMIZATION = 1
```
  To ensure that a dynamic SQL or XQuery statement always uses the same optimization class, include a SET statement in the application program.
  
  If the CURRENT QUERY OPTIMIZATION special register has not been set, dynamic statements are bound using the default query optimization class. The default value for both dynamic and static queries is determined by the value of the dft_queryopt database configuration parameter, whose default value is 5. The default values for the bind option and the special register are also read from the dft_queryopt database configuration parameter.
- Static SQL and XQuery statements use the optimization class that is specified on the PREP and BIND commands. The QUERYOPT column in the SYSCAT.PACKAGES catalog view records the optimization class that is used to bind a package. If the package is rebound, either implicitly or by using the REBIND PACKAGE command, this same optimization class is used for static statements. To change the optimization class for such static SQL and XQuery statements, use the BIND command. If you do not specify the optimization class, the data server uses the default optimization class, as specified by the dft_queryopt database configuration parameter.