Query Optimization Overview

Oracle’s Optimizer is fundamentally cost-based: it compares candidate plans using a cost model that approximates resource usage (I/O, CPU, memory) and chooses the plan with the lowest cost.

Two concepts drive most decisions:

·        Selectivity: the estimate of how much a WHERE-clause predicate filters down the number of rows returned.

·        Cardinality: how many rows are predicted to flow through each operation performed by the SQL statement.

Those estimates, combined with object definitions (indexes, partitions), system metadata, and optimizer features, determine choices such as:

·        Which table becomes the driving row source.

·        What types of joins to use, for example, nested loops, hash joins, or sort-merge joins.

·        How to access data, for example, via a full table scan, index scan, or partition pruning.

·        When to apply parallel execution and how to distribute work.

The optimizer’s strength comes from its ability to deal with a huge plan search space, followed by the use of costing and estimation to target the best plan it can find.

Before and during plan selection, Oracle applies query transformations. These are rewrites that preserve correctness but change the internal shape of the query so it can be executed more efficiently.

Categories you’ll find in the documentation are:

·        Subquery transformations (e.g., unnesting)

·        View merging and predicate pushing

·        Join transformations

·        Aggregation transformations

·        Set-to-join and related rewrites

The optimizer doesn’t just pick access paths (see below). It may restructure queries, too. This is one of the reasons why Oracle can execute complex SQL efficiently even when the original text has not been optimized by hand.

The optimizer chooses among multiple access paths, influenced by schema design and data organization. At a high level, it considers:

·        Index and table access options

o   Index-based access (various scan styles)

o   Full scans when a large portion of rows is needed or when scanning is cheaper than many random I/Os

·        Partitioning-aware optimization

When tables are partitioned, the optimizer can take advantage of:

·        Partition pruning (limit work to relevant partitions)

·        Partition-wise strategies (in certain join/grouping scenarios)

·        Local vs. global index implications

Partitioning and indexing don’t just change storage; they expand the optimizer’s set of viable strategies.

For multi-table SQL statements, the optimizer has two major degrees of freedom:

Join order

The optimizer determines an order in which row sources are combined, based heavily on estimated cardinalities and selectivities.

Join method

Oracle commonly chooses among:

·        Nested loops (often strong for selective lookups).

·        Hash joins (often strong for larger joins, especially where hashing fits memory).

·        Sort-merge joins (useful in some ordered/mergeable cases).

At a conceptual level: join order decides what drives, join method decides how to combine.

The optimizer’s cost model is only as good as the information it has. Optimizer statistics (stats, for short) provide that foundation.

Statistics include:

·        Table stats: row counts, block counts, average row length.

·        Index stats: levels, leaf blocks, clustering factor, distinct keys.

·        Column stats: distinct values, null counts, distribution information.

·        Histograms (when appropriate): represent skewed distributions so selectivity can be estimated more accurately.

·        Extended statistics (e.g., column groups): help with correlated columns and multi-column selectivity.

Up-to-date optimizer statistics are essential for predictable performance at scale.

The optimizer uses runtime information to improve performance if compile-time assumptions are imperfect. These capabilities allow certain plan choices to be refined using runtime observations, helping Oracle avoid committing too early when estimates are uncertain or learning from previous executions.

·        Adaptive plans (improving the current execution)

·        Adaptive statistics (improving future executions).

Oracle can capture additional information that helps improve subsequent optimization decisions, particularly for patterns where SQL is complex or conventional statistics don’t fully describe reality.

·        Statistics feedback.

·        Adaptive cursor sharing (ACS).

Enterprises increasingly run mixed workloads with variability (different bind values, seasonal data shapes, rapidly changing data). Adaptive capabilities are designed to reduce the “penalty” for uncertainty.

SQL Plan Management addresses a different enterprise need: plan stability.

Rather than letting the optimizer freely switch plans whenever stats change or environment conditions differ, SPM provides a managed lifecycle:

·        Capture and maintain a history of plans for repeatable SQL.

·        Define baselines of known-good plans.

·        Enable controlled evolution to new plans (adopt better plans without unexpected regressions).

SPM is often part of broader change-management strategy, especially around upgrades and large application rollouts.

Even without diving into troubleshooting, it’s worth calling out that Oracle provides multiple “control planes” over optimization behavior:

·        Optimizer hints: SQL-level directives that influence transformations or plan choices.

·        Session/system parameters: influence optimizer features and defaults.

·        SQL plan baselines (SPM): governance of plan selection across executions

In well-governed environments, teams typically prefer data-driven optimization (schema design, stats and SPM) over widespread hinting, reserving hints for exceptional cases.