A central concept of Adaptive Server 15.0's query processing engine is the “optimization goal”, which provides an indication of the nature of the query being optimized. The Adaptive Server query optimizer determines how best to optimize a query based on optimization goals.
For example, a typical OLTP (online transaction processing) query and a typical DSS (decision-support system) query result in very different query plans due to the different data access patterns used by these queries. OLTP queries generally affect only one or a few rows and join only a few well-indexed tables. However, DSS queries typically affect many rows, return a few rows, and may join many tables.Because of their different access patterns, OLTP queries often run most efficiently using a classic “nested-loop join”, whereas DSS queries are more likely to run faster with a “hash join”. If you indicate that a query is for OLTP or DSS purposes, the optimizer uses that information to generate a query plan that may save time, memory, and CPU usage.
Adaptive Server 15.0 provides three optimization goals, ordered from “narrow” to “wide,” which correspond to the number of options and strategies that they allow the optimizer to consider:
allrows_oltp – is best for OLTP queries. allrows_oltp offers the narrowest selection of join methods: the query optimizer considers only nested-loop joins.
allrows_mix – is the default after upgrading to Adaptive Server 15.0. allrows_mix allows the optimizer to consider merge joins as well as parallel plans (if the Adaptive Server is configured for parallelism).
allrows_dss – is best for DSS queries. allrows_dss offers the widest selection of join methods. The optimizer considers hash joins, as well as nested-loop joins, merge joins, and parallel plans.
If you use allrows_mix and allrows_dss, additional low-level processing algorithms are enabled for SQL operations; these algorithms are disabled if you use allrows_oltp.When you widen the optimization goal, the query optimizer might use significantly more resources (time and procedure cache) to generate a query plan. If the optimizer generates the same query plan, with only nested-loop joins, under allrows_dss and allrows_oltp, you may expect the optimization under allrows_dss to take more time and procedure cache than under allrows_oltp.
The choice of optimization goal can have a significant impact on query performance. If you know that a certain application has different workload characteristics than the rest of your system, you may want to set an appropriate session-level optimization goal for that application. Either use the QPTune utility, or manually experiment with different optimization goals, and select one that provides the best overall performance for your particular set of applications and queries. See Chapter 2, “QPTune” for more details.
You can define the optimization goal at the server-, session- or individual- query level:
Server-wide default:
sp_configure 'optimization goal', 0, 'allrows_dss'
Session-level setting (overrides server-wide setting):
set plan optgoal allrows_dss
Query-level setting (overrides server-wide and session-level settings):
select * from T1, T2 where T1.a = T2.b plan '(use optgoal allrows_dss)'
You can also use a login trigger to
set the session-level optimization goal.
