16.9. The DB2 Optimizer The DB2 optimizer, as stated in previous chapters, is the brain of DB2. The optimizer is a sophisticated and complex set of algorithms whose main objective is to calculate the fastest way to retrieve your data based on your database statistics. The DB2 optimizer performs a number of tasks during the creation of the internal-compiled form of your SQL statements. Parse the query. The optimizer's first task is to analyze the SQL query to validate the syntax. If it detects any syntax errors, the optimizer stops processing, and the appropriate SQL error is returned to the application attempting to compile the SQL statement. When parsing is complete, an internal representation of the query is created. Check the query semantics. The second task of the optimizer is to further validate the SQL statement by checking to ensure that the parts of the statement make sense given the other parts, for example, ensuring that the data types of the columns input into scalar functions are correct for those functions. Also during this stage, the optimizer adds the behavioral semantics to the query graph model, such as the effects of referential constraints, table check constraints, triggers, and views. Rewrite the query. The optimizer uses global semantics provided in the query graph model to transform the query into a form that can be optimized more easily. For example, the compiler might move a predicate, altering the level at which it is applied, in an attempt to improve query performance. This particular process is called general predicatepushdown . Any changes made to the query are rewritten back to the query graph model. Optimize the access plan. The SQL optimizer portion of the optimizer uses the query graph model as input and generates many alternative execution plans for satisfying the user's request. It estimates the execution cost of each alternative plan using the statistics for tables, indexes, columns, and functions, and chooses the plan with the smallest estimated execution cost. The optimizer uses the query graph model to analyze the query semantics and to obtain information about a wide variety of factors, including indexes, base tables, derived tables, subqueries, correlation, and recursion. The output from this step of the optimizer is an access plan, which provides the basis for the information captured in the explain tables. The information used to generate the access plan can be captured with an explain snapshot. Generate the executable code. The optimizer's final step uses the access plan and the query graph model to create an executable access plan, or section, for the query. This code generation step uses information from the query graph model to avoid repetitive execution of expressions that only need to be computed once for a query. Examples for which this optimization is possible include code page conversions and the use of host variables. Information about access plans for static SQL is stored in the system catalog tables. When the package is executed, DB2 UDB will use the information stored in the system catalog tables to determine how to access the data and provide results for the query. It is this information that is used by the db2expln tool.
It is recommended that you run the RUNSTATS command periodically on the tables used in queries where you need optimal performance. The optimizer will then be better equipped with relevant statistical information on the nature of the data. If the RUNSTATS command is not run, or the optimizer determines that RUNSTATS was run on empty or near-empty tables, the optimizer may either use defaults or attempt to derive certain statistics based upon the number of file pages used to store the table on disk. |
