1. Changes in Statistics

Statistics are vital for the Oracle optimizer to determine the most efficient execution plan for a query. Why? – Because they are the ones providing insights into the distribution and characteristics of data within tables and indexes. When statistics change—due to activities such as loading new data, deleting existing records, or performing scheduled updates—the optimizer’s perspective on the data changes as well. This can prompt Oracle to re-evaluate the current execution plan, this can potentially lead to the situations when Oracle changes the execution plan.

Why does this matter? In practice, frequent changes in statistics can lead to significant variability in query performance. For database administrators and developers, this calls for a keen eye on when and how statistics are updated. But it has to be a thoughtful process, enabling them to maintain stable and predictable query performance. Regular monitoring and adjustments may be necessary, especially after large data loads or deletions. That is if one wants to prevent performance degradation and ensure that the execution plans remain efficient.

2. Changes in Data Distribution

Even when the statistics remain unchanged, the actual distribution of data within the tables can subtly change over time. This can be due to various factors such as updates to existing records or the addition of new, yet unaccounted for, data types. These affect the cardinality estimates – predictions about how many rows of data the query will likely affect. The predictions are critical for the Oracle optimizer when deciding on the most efficient execution plan.

Why is this significant? Cardinality estimates influence decisions about which indexes to use, whether to perform full table scans, and how to join tables in multi-table queries. If the optimizer’s assumptions about data distribution are off, it may choose a plan that is no longer optimal. 

Periodical review and adjustment of the optimizer’s assumptions is how one could handle it. This could involve more frequent statistics updates or using features such as dynamic sampling – This allows Oracle to better assess the actual data distribution at runtime. Keeping the optimizer informed about the true state of data distribution can help ensure that it makes decisions based on the most accurate and current information.

3. Changes in Database Parameters

Parameters such as optimizer_mode dictate how the optimizer approaches execution plans. This mode can be set to values like ALL_ROWS for optimizing query performance across all returned rows, or FIRST_ROWS for prioritizing speed in returning the initial rows. Altering these parameters can change how queries are processed. This, in turn, affects the chosen execution plans.

The modification of other parameters, such as optimizer_index_cost_adj, which adjusts the cost estimation of index accesses, or optimizer_index_caching, which influences the assumed percentage of index blocks in the cache, can also drastically alter the execution plan. These changes recalibrate how the optimizer weighs different execution strategies. Sometimes, that’s when Oracle changes the execution plan.

4. Changes in Schema Objects

• Index Changes: When new indexes are added, they provide the optimizer with additional pathways – usually more efficient than those previously available. As expected, dropping an index works the other way around. It may force the optimizer to revert to less efficient table scans. Modifying an index, such as changing its columns or type (e.g., from a b-tree to a bitmap index), can also alter its suitability for certain query types, prompting a reevaluation of the best execution strategy.

• Role of Constraints: Constraints such as primary keys, foreign keys, and unique constraints provide the optimizer with information about data uniqueness and integrity. These have the potential to influence join methods and the use of indexes during query optimization. Changes to constraints might lead the optimizer to adjust its assumptions about data distribution, which in turn affects the execution plan.

5. Bind variable peeking

Bind variable peeking is a feature where Oracle’s optimizer looks at the actual values of bind variables during the first execution of a SQL statement. The goal is to determine the most efficient execution plan and allow the optimizer to create a tailored plan. A plan, that takes into account the specific characteristics of the data it will operate on.

Typically, the use of bind variables in SQL queries promotes plan stability and reusability by avoiding hard parsing for each execution. However, the initial execution allows the optimizer to “peek” at the actual bind values, which can influence the chosen plan. For example, a query that filters on a column with highly deformed data distribution might lead to different execution paths. It all depends on the value used at parse time.

Impact of Literal Values

Replacing bind variables with literal values changes this dynamic. The optimizer generates a plan for each unique set of literal values, which can lead to multiple plans for what is essentially the same query. While this might optimize performance for specific cases, it can also increase the parsing overhead and reduce the overall efficiency due to the lack of plan sharing.

6. Adaptive Cursor Sharing

When a query is first executed, Oracle creates a default execution plan based on the current statistics and the initial bind variable values. When the query is executed repeatedly with different bind variable values, the optimizer collects performance data. At the same time, it evaluates whether the initial plan is still effective or if adjustments are needed. If significant differences in performance are detected, the optimizer may generate multiple execution plans for the same query, and each of them will be optimized for a specific range of bind variable values.

7. Bug in the Optimizer

It happens rarely but it does happen – Oracle’s optimizer is not immune to the occasional bug. Obviously enough, one of them can be a cause of unexpected inefficiencies in execution plans. When they occur, either due to errors in the optimizer’s codebase or unforeseen interactions between different parts of the database system, they may generate suboptimal plans. Such plans do not effectively use the available indexes or execute more costly operations than necessary.

There is no solution other than to identify the bug. This requires thorough testing and observation, especially if a query that previously ran efficiently suddenly exhibits performance degradation without apparent cause. 

8. Oracle Changes the Execution Plan in the Presence of Hints

Hints in Oracle SQL queries serve as instructions that guide the optimizer’s choices, often overriding its default decision-making process. By embedding hints directly in an SQL statement, developers can influence the path the optimizer takes, such as specifying which indexes to use, dictating join methods, or directing the order of table joins.

Useful as they are, changes to the hints themselves can also lead to changes in the execution plans. For example, removing a previously included hint might revert the optimizer back to its original plan, which could be less efficient based on the current data environment. Similarly, modifying hints to reflect new indexing strategies or different join approaches can radically alter how a query is executed.

9. Small Sample Sizes When Gathering Statistics

Without sufficient statistics, the optimizer may not be able to make informed decisions about the best execution plans for queries. When the sample size is too small or not representative of the full dataset, the resulting statistics may not accurately reflect the true nature of the data. 

This can lead to inaccurate cardinality estimates. Misjudged cardinality can, in turn, mislead the optimizer, resulting in it choosing suboptimal execution plans. For example, the optimizer might select a full table scan when an index scan would be more efficient, or it might improperly arrange joins due to incorrect assumptions about data size and distribution.

10. Volatile Data Values

In Oracle databases, volatile data values are those that are temporary or experience high rates of change. 

When data values in a column are volatile, the optimizer’s statistics might not accurately reflect the current state of the data at the time of query execution. This discrepancy can lead to a mismatch between the expected and actual data distribution, causing the optimizer to select suboptimal execution plans. A plan optimized for a specific range of values may perform poorly if those values change rapidly and unpredictably.

The optimizer faces additional challenges with short-lived data, where values may only exist for a brief period before being updated or deleted. In such cases, the optimizer’s decision-making process, based on statistics gathered during routine intervals, may lag behind the actual data scenario, leading to moments when Oracle changes the execution plan. The reason is that the optimizer attempts to recalibrate its strategy to align with the latest data characteristics.

11. Asynchronous statistics gathering

In Oracle databases, asynchronous statistics gathering is a method used to collect data distribution statistics without interrupting the normal operation of the database. This approach is particularly useful in high-availability environments where minimizing downtime is the primary concern. But, there is always a ‘but’.

The delay in statistics updates caused by asynchronous gathering means that the optimizer may not have the most current data insights when determining the best execution plan. This can result in the selection of plans that are not optimized for the current data distribution. That’s how we get inefficient query performance.