Hakan Jakobsson (Oracle): Oracle Query Processing and Optimization

• Usual query processing steps, except may enumerate and cost physical plans for several different equivalent queries, i.e., repeat cost-based optimization after certain rewrites. (Ex: materialized view rewrite)

• A "cursor cache" saves compiled plans and checks incoming queries for an exact match. A cached plan is invalidated when new statistics are gathered for data in the query.

• Plan enumeration:

  • Exhaustive join orderings for queries with <= 5 joins

  • Otherwise initial heuristically-generated ordering, look for better one
    • Getting the "leading" (first) table right is most important
    • Best plan is usually one of first few considered
    • Stop based on estimated execution time vs. more optimization time

  • Separate passes with "interesting orders" taken into account (when there's an order-by or aggregation)

  • Considers only left-deep trees except for:
    • "Inherently bushy" plans -- composite plans assembled from query blocks or views; also outerjoins (non-associative)
    • Hash joins

• Major problem: predicted cardinalities and therefore cost estimates can be way off
  • Correlations between attribute values are not captured in single-column statistics (same relation or across joins)
  • Predicate selectivity difficult to predict, e.g., LIKE predicates
  • Values of "bind variables" unknown at compile-time
  • Relevant statistics not gathered
  => Typical culprit in selection of bad plan

• Some solutions:
  • "Bind peeking" -- compile using parameters from first invocation
  • Perform sampling at query optimization time for important statistics (correlation, difficult predicates)
  • Automatic statistics gathering and management ("new stuff coming out")

• Automatic tuning:
  • General-purpose pre-compilation tool for "high load" queries
  • Gathers relevant statistics
  • Partially evaluates query, stores "correction factors" on subresults to be used during full compilation
  • Suggests indexes
  • Suggests query restructuring -- some SQL constructs are better than others
  Flip side: Language for giving strong hints to optimizer

• Another major problem: "Better" optimizer may produce worse plan on 1% of queries. Solutions:
  • Save plans across upgrades
  • Save old optimizer across upgrades

• Changing architectures and relative component speeds invalidating optimizer models and decisions
  • Ex: changed from insertion sort to radial sort, better for L2 cache
  • Cost model from just I/O to {I/O + CPU} to {I/O + CPU + L2 cache}

• Different optimization metrics:
  • Throughout -- least resources
  • Response time -- time to completion (parallelism)
  • First batch of rows (no blocking operators)

Guy Lohman (IBM): The DB2 Universal Database Optimizer

• One code base (UDB) for many different hardware platforms, software platforms, even query languages (SQL, SQL/XML, XQuery)

• Customer database sizes to 100's of terabytes, queries getting more and more complex

• Usual query processing steps, all operate on Query Graph Model ("headquarters for all knowledge about the query")

• Triggers and constraints folded in before rewrite (unusual), similar to views

• Query rewrites:
  • To put queries in canonical form
  • To put queries in more efficient form for cost-based optimizer
  • All heuristics, no cost
  • Idempotent?

• Statistics
  • Used to be manual
  • Recently automated: track updates, recompute stats automatically (sampling)

• LOLEPOPs: Physical operators, query plan is graph of LOLEPOPs, implemented as iterators

• Each LOLEPOP has set of properties, computed from properties of children: columns available, predicates applied, keys, sort order, partitioning, site, cardinality, estimated cost, ...

• Very sophisticated cost model (30,000+ lines of code): I/O, CPU, communication, buffer, prefetching, ...

• Different optimization metrics (same as Oracle):
  • Throughout -- least resources
  • "OPTIMIZE FOR N ROWS" -- time to completion (parallelism)
  • "FETCH FIRST M ROWS ONLY" -- no blocking operators

• Plan enumeration
  • Bottom-up dynamic programming with rules to generate alternatives at each level, considers "interesting properties" (e.g., order, partitioning)
  • Simpler greedy strategy: Pick cheapest join, then cheapest remaining join, and so on
  • Many different "levels" of optmization; chosen level determines which algorithm is used

• System "cloning": For servicing customer databases without looking at the data -- package workload and relevant statistics

• Parallelism
  • Inter-partition: different plan on each machine, depends on partitioning and query
  • Intra-partition: parallelism within single plan

• OLAP
  • Special strategies, e.g., allow early cross-products in star schemas
  • Index manipulations with "Bloom filters", join back to get data

• Comprehensive query plan visualization system, lots of information available about plan and planning

• Ordered nested-loops join: Sort outer to get "nice clean sweep" on inner if inner has clustered index on join column

• "Early out" to handle duplicates issue in subquery-to-join transformation

• Rollup query

Vivek Narasayya (Microsoft): SQL Server Optimizer

• (observation) Example plans favor index operations, sort-merge join

• Parsing and "normalization", then plan enumeration is top-down transformation-based
  • Extensible: easy to add new operators, rules, search strategy (e.g., XML)
  • Fully cost-based, no heuristic rewrites

• Memo: primary data structure on which transformations occur, eventually yields selected execution plan

• Each operator in logical plan becomes a "group" in the initial memo; rules used to expand alternatives (logical and physical) for each group

• Memo maintains properties pertaining to entire group (e.g., keys, cardinalities) and to physical plans within a group (e.g., order, cost)

• Optimization process is highly recursive invocation of:
  • Optimize Group - choose physical plan for group
  • Explore Group - generate alternative logical plans for group
  • Explore Expression - generate alternative logical plans based on operator
  • Apply Rule - to generate alternatives including logical-to-physical; rules ordered and pruned
  • Optimize Inputs - choose physical plans for operands

• Expansion:
  • Avoid cycles
  • No repeated work (memoization)
  • Ttimeout based on number of rules applied

• Actually a phased process:
  1. Trivial query -> trivial plan
  2. Else try small optimizer (few rules); if cheap plan found -> done
  3. Else try medium optimizer (more rules); if cheap plan found -> done
  4. Else use full-blown optimizer (serial or parallel)
  Each phase prunes based on cost of cheapest plan from previous phase

• Sophisticated cardinality estimation: fancy histograms, multi-column densities (MCD), magic numbers (?)

• Automatic management of statistics (sound familiar?); automatic optimization levels and physical tuning

• Different optimization objectives (throughout, response time): change cost metric, change relative "promise" of rules