SQL Optimization

If an SPL routine contains SQL statements, at some point the query optimizer evaluates the possible query plans for SQL in the SPL routine 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.

When you create an SPL routine with the CREATE PROCEDURE statement, the database server attempts to optimize the SQL statements within the SPL routine at that time. If the tables cannot be examined at compile time (because they do not exist or are not 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. In addition, the database server stores information about the SPL routine (such as procedure name and owner) in the sysprocedures system catalog table and an ASCII version of the SPL routine in the sysprocbody system catalog table.

Figure 59 summarizes the information that the database server stores in system catalog tables during the compilation process.

Figure 59. SPL Information Stored in System Catalog Tables

Displaying the Execution Plan

When you execute an SPL routine, it is already optimized. To display the query plan for each SQL statement contained in the SPL routine, execute the SET EXPLAIN ON statement prior to one of the following SQL statements that always tries to optimize the SPL routine:

• CREATE PROCEDURE
• UPDATE STATISTICS FOR PROCEDURE

  For example, use the following statements to display the query plan for an SPL routine:

  SET EXPLAIN ON; 
  UPDATE STATISTICS FOR PROCEDURE procname;

Automatic Reoptimization

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 an SPL routine executes after one of the following situations:

• Execution of any data definition language (DDL) statement (such as ALTER TABLE, DROP INDEX, and CREATE INDEX) that might alter the query plan
• Alteration of a table that is linked to another table with a referential constraint (in either direction)
• Execution of UPDATE STATISTICS FOR TABLE for any table involved in the query

  The UPDATE STATISTICS FOR TABLE statement changes the version number of the specified table in systables.

• Renaming a column, database, or index with the RENAME statement

Whenever the SPL routine is reoptimized, the database server updates the sysprocplan system catalog table with the reoptimized execution plan.

Reoptimizing SPL Routines

If you do not want to incur the cost of automatic reoptimization when you first execute an SPL routine after one of the situations that Automatic Reoptimization lists, execute the UPDATE STATISTICS statement with the FOR PROCEDURE clause immediately after the situation occurs. In this way, the SPL routine is reoptimized before any users execute it. To prevent unnecessary reoptimization of all SPL routines, ensure that you specify a specific procedure name in the FOR PROCEDURE clause.

UPDATE STATISTICS for procedure(myroutine);

For guidelines to run UPDATE STATISTICS, see Updating Statistics.

Optimization Levels for SQL in SPL Routines

The current optimization level set in an SPL routine affects how the SPL routine is optimized.

The algorithm that a SET OPTIMIZATION HIGH statement invokes is 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.

The alternative algorithm that a SET OPTIMIZATION LOW statement invokes 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 and that contain complex SELECT statements, you might want to set the SET OPTIMIZATION statement to HIGH when you create the SPL routine. This optimization level stores the best query plans for the SPL routine. Then set optimization to LOW before you execute the SPL routine. The SPL routine then uses the optimal query plans and runs at the more cost-effective rate if reoptimization occurs.