During SPL optimization, the query optimizer evaluates the possible query plans and selects the query plan with the lowest cost. The database server puts the selected query plan for each SQL statement in an execution plan for the SPL routine. The database server optimizes each SQL statement within the SPL routine and includes the selected query plan in the execution plan.
The current optimization level set in an SPL routine affects how the SPL routine is optimized. The SQL statement, SET OPTIMIZATION, sets the optimization level, which in turn determines the algorithm that the query optimizer uses, as follows.
| SET OPTIMIZATION Statement | Algorithm Used |
|---|---|
| SET OPTIMIZATION HIGH | Invokes a sophisticated, cost-based strategy that examines all reasonable query plans and selects the best overall alternative For large joins, this algorithm can incur more overhead than desired. In extreme cases, you can run out of memory. |
| SET OPTIMIZATION LOW | Invokes a strategy that eliminates unlikely join strategies during the early stages, which reduces the time and resources spent during optimization However, when you specify a low level of optimization, the optimal strategy might not be selected because it was eliminated from consideration during early stages of the algorithm. |
For SPL routines that remain unchanged or change only slightly, you might want to set the SET OPTIMIZATION statement to HIGH when you create the routine. This optimization level stores the best query plans for the routine. Then set optimization to LOW before you execute the routine. The routine then uses the optimal query plans and runs at the more cost-effective rate if reoptimization occurs.
When you create an SPL routine, the database server attempts to optimize the SQL statements within the routine at that time. If the tables cannot be examined at compile time (they might not exist or might not be available), the creation does not fail. In this case, the database server optimizes the SQL statements the first time that the SPL routine executes. The database server stores the optimized execution plan in the sysprocplan system catalog table for use by other processes.
The database server uses the dependency list to keep track of changes that would cause reoptimization the next time that an SPL routine executes. The database server reoptimizes an SQL statement the next time that an SPL routine executes after one of the following situations:
The UPDATE STATISTICS FOR TABLE statement changes the version number of the specified table in systables.
The database server updates the sysprocplan system catalog table with the reoptimized execution plan.
Home | [ Top of Page | Previous Page | Next Page | Contents | Index ]