In the first two parts of our series on DB2 optimization we took a look at indexed access versus data scanning, and DB2 join methods. In this 3rd and final part of the series, we’ll review the optimization class parameter and its impact on access path formulation.
For DB2 for Linux, Unix and Windows, the choice of join method also will depend on the optimization class being used. DB2 for z/OS does not have an optimization class, so this material only applies to DB2 for LUW.
Optimization classes specify the different search strategies that should be used by the optimizer when compiling and optimizing SQL statements. So, not every access path technique described above is always available to the optimizer. Instead, based on the optimization class, different techniques will be made available to the optimizer.
The purpose of the optimization class is to guide DB2 as to how thorough it should be when considering search strategies and optimization techniques to use. In general, the more search strategies considered by the optimizer the better the access plan will be for the query. However, when the optimizer is guided to consider more search strategies it will take longer to compile the SQL into an executable access path.
Fortunately, you can set the optimization class to limit the number of techniques applied when optimizing your query. This can be quite useful for simpler queries, resource-constrained systems, and dynamic SQL. The optimization classes are outlined in Table 1.
Directs the optimizer to use a minimal amount of optimization to generate an access plan. Only nested loop join and index scan access methods are available. Limited use of statistics (e.g. does not consider non-uniform distribution statistics).
Similar to class 0, but adds merge joins, table scans, and very basic query rewrite (plus a few additional features).
Significantly improves on class 1 but with significantly lower compilation cost than class 3. This class makes use of all available statistics, most query rewrite rules, list prefetch, and summary table routing. Similar to class 5 except that it uses greedy join enumeration rather than dynamic programming.
This class is the closest to the query optimization used by DB2 for z/OS. It provides a moderate amount of optimization and a requires moderate amount of resources to compile.
This is the default optimization class. It provides a significant amount of optimization and requires more resources to compile than class 3. The optimizer intelligently determines when additional resources are not warranted for dynamic SQL. Class 5 is a good choice for a mixed environment of complex and simpler queries.
Similar to class 5 but it will not determine when additional resources are not warranted for dynamic SQL.
Uses all available optimization techniques, including all available statistics, all query rewrite rules, all possibilities for join enumeration (including Cartesian products and unlimited composite inners) and all access methods.
Although you may select any query optimization class as described in the table, classes 0 and 9 should be used only on rare occasions. Classes 0, 1, and 2 use the Greedy join enumeration algorithm; for complex queries this algorithm considers far fewer alternative plans, and incurs significantly less compilation time, than classes 3 and above. Classes 3 and above use the Dynamic Programming join enumeration algorithm; this algorithm considers far more alternative plans, and can incur significantly more compilation time, than classes 0, 1, and 2 as the number of tables increases.
The way you set a specific query optimization class depends on whether you are using static or dynamic SQL. For static SQL statements the optimization class is specified on the PREP and BIND commands. The QUERYOPT column in the SYSCAT.PACKAGES catalog table records the optimization class that was used to bind the package. Dynamic SQL statements use the optimization class specified by the CURRENT QUERY OPTIMIZATION special register that is set using the SQL SET statement.
Finally, let’s quickly define the two types of search strategies and their characteristics. The first type, used by classes 0, 1, and 2, is greedy join enumeration. With greedy join enumeration, once a join method is selected for two tables, it will not be changed during further optimization. Therefore, it may not choose the absolute best access plan when joining many tables. Queries joining only a few tables will likely have the same access plan chosen by greedy join enumeration as by the other search strategy, dynamic programming join enumeration. Dynamic programming join enumeration will require more time and resources as the number of tables being joined increases. It is more likely choose the best access plan possible than greedy join enumeration.
OK, then, which optimization class should you choose? Most SQL statements can be adequately optimized with a reasonable amount of resource by using optimization class 5, which is the default. Generally speaking, the higher the optimization class and the more complex the query, the longer it will take to optimize the query and the more resources DB2 will consume to do so.
You should consider class 0 only for very simple statements. Class 9 should be considered only for queries that are very complex and/or long-running that use large tables. For most users, optimization classes 1, 2, 3, 5, and 7 will suffice for most of your queries.
Of course, there is much more to DB2 optimization and access path formulation than we have covered in this three-part series. That said, the material here can be used as a primer on query optimization as it provides the basics that everybody needs to know before they can start to tune and manage DB2 performance. Use these articles to start you on your journey, or to bolster your current knowledge, and keep on digging and learning more to power you on your way to becoming a DB2 tuning expert.