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.
To specify a query optimization
class, follow these steps: