Adaptive Query Optimization - Adaptive Plans | Adaptive Statistics - 19c with Example

 

Adaptive Query Optimization Defaults

Adaptive query optimization has two branches 

1)  Adaptive Plans 

2)  Adaptive Statistics

optimizer_adaptive_plans=TRUE

optimizer_adaptive_statistics=FALSE

Note:- Do not conflate "adaptive plans" with adaptive statistics

Adaptive Settings in Oracle Database 19c

No need to explicitly set the following optimizer parameters – the default values are recommended 

• optimizer_adaptive_plans [default TRUE] 

• optimizer_adaptive_statistics [default FALSE] 

• Remove optimizer_adaptive_features from pfile/spfile

The OPTIMIZER_ADAPTIVE_PLANS parameter in Oracle enables the use of adaptive plans, which allow the Oracle Optimizer to modify execution plans dynamically based on the actual runtime statistics gathered during query execution. This feature helps improve the performance of SQL queries by making adjustments mid-execution when certain initial assumptions or estimates (such as row count) are off.

Key Concepts of Adaptive Plans

When OPTIMIZER_ADAPTIVE_PLANS is enabled, Oracle can adjust query operations such as joins, parallelism, and aggregation methods based on adaptive statistics collected during execution. Here’s how it works:

1. Execution Plan Choices: The optimizer generates multiple plan options at compile time. For example, it might consider a nested loop join vs. a hash join.
2. Statistics Feedback: As the query executes, Oracle monitors actual rows processed by each operation.
3. Plan Switch: If runtime statistics significantly differ from optimizer estimates, Oracle may switch to a better execution path (like changing from a nested loop join to a hash join) based on the new data.

Enabling and Disabling OPTIMIZER_ADAPTIVE_PLANS

• Default: In Oracle Database 12c and 19c, OPTIMIZER_ADAPTIVE_PLANS is set to TRUE by default, which enables adaptive plans.

• To Check the Setting:

  SHOW PARAMETER OPTIMIZER_ADAPTIVE_PLANS;

• To Enable/Disable:

  
  ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = TRUE;  -- To enable
  ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = FALSE; -- To disable
  

Benefits of Adaptive Plans

1. Improved Query Performance: Adaptive plans allow the optimizer to adjust execution paths during runtime, which can avoid inefficient operations if initial estimates are incorrect.
2. Better Resource Utilization: Adaptive plans help optimize resource usage by choosing execution strategies (e.g., efficient join methods or skipping unnecessary data accesses) that match actual row counts.
3. Reduced Need for Manual Tuning: By making runtime adjustments, adaptive plans can reduce the need for manual intervention and SQL tuning.

Example Scenario

Suppose a query has a join operation, and Oracle initially decides on a nested loop join. However, as rows are processed, Oracle discovers that the actual number of rows is significantly higher than expected. If OPTIMIZER_ADAPTIVE_PLANS is enabled, Oracle may switch to a hash join mid-execution, which is more efficient for larger row sets.

Limitations and Considerations

• Performance Overhead: In some cases, adaptive plans can introduce minor overhead, as Oracle needs to monitor and gather runtime statistics.
• Complexity in Execution Plans: Adaptive plans can make execution plans harder to interpret, as Oracle may change the plan dynamically.
• Compatibility: Adaptive plans are mainly beneficial in data warehousing and OLAP environments where data patterns are unpredictable.

Using OPTIMIZER_ADAPTIVE_PLANS is especially helpful for unpredictable workloads where data patterns frequently vary. For highly stable environments, it may not yield as significant benefits.

To demonstrate the effect of OPTIMIZER_ADAPTIVE_PLANS, we can create a simple table, insert data, and execute a query to observe how the optimizer adjusts its plan. We’ll walk through setting up the environment, enabling adaptive plans, and analyzing the explain plan.

Step 1: Enable Adaptive Plans

First, ensure that adaptive plans are enabled:

ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = TRUE;

Step 2: Create and Populate Tables

Create two tables: ORDERS (a large table) and CUSTOMERS (a small table), which we’ll use in a join operation. The optimizer might initially choose a nested loop join but may switch to a hash join if the row count increases.

-- Create a large table
CREATE TABLE ORDERS (
    ORDER_ID NUMBER,
    CUSTOMER_ID NUMBER,
    ORDER_DATE DATE,
    ORDER_AMOUNT NUMBER
);

-- Insert sample data into the ORDERS table

BEGIN
    FOR i IN 1..100000 LOOP
        INSERT INTO ORDERS VALUES (i, MOD(i, 1000), SYSDATE - DBMS_RANDOM.VALUE(0, 365), DBMS_RANDOM.VALUE(100, 1000));
    END LOOP;
    COMMIT;
END;
/

-- Create a smaller table
CREATE TABLE CUSTOMERS (
    CUSTOMER_ID NUMBER PRIMARY KEY,
    CUSTOMER_NAME VARCHAR2(50)
);

-- Insert sample data into the CUSTOMERS table
BEGIN
    FOR i IN 1..1000 LOOP
        INSERT INTO CUSTOMERS VALUES (i, 'Customer ' || i);
    END LOOP;
    COMMIT;
END;
/

Step 3: Collect Statistics

Collect statistics on the tables so that the optimizer has baseline data. However, leave some statistics intentionally stale by inserting more data afterward to allow dynamic sampling and adaptive plan adjustments.

-- Gather statistics
EXEC DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA', 'ORDERS');
EXEC DBMS_STATS.GATHER_TABLE_STATS('YOUR_SCHEMA', 'CUSTOMERS');

Step 4: Execute a Query with an Adaptive Plan

Now, let’s run a join query between ORDERS and CUSTOMERS. Due to the data distribution and volume, Oracle might consider adaptive join methods.

EXPLAIN PLAN FOR
SELECT C.CUSTOMER_NAME, SUM(O.ORDER_AMOUNT) AS TOTAL_AMOUNT
FROM CUSTOMERS C
JOIN ORDERS O ON C.CUSTOMER_ID = O.CUSTOMER_ID
GROUP BY C.CUSTOMER_NAME;

Step 5: Review the Execution Plan

Check the execution plan to see if Oracle has chosen an adaptive plan. In Oracle, adaptive plans are indicated by the ADAPTIVE keyword in the OPERATION column of the execution plan.

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Interpreting the Output

• If adaptive plans are in effect, you should see ADAPTIVE STATISTICS or ADAPTIVE JOIN in the execution plan output.
• The Join Method (e.g., NESTED LOOPS, HASH JOIN) may also be dynamic and could indicate an initial method with potential switching to a different join type based on row estimates.
• Look for statistics collector or filter operations where Oracle collects runtime statistics for mid-execution optimization.

Example Output Explanation

The output could look something like this:

plaintext

---------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name      | Rows  | Bytes | Cost (%CPU)| Time     | ADAPTIVE |
---------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |           |       |       |   450 (100)|          |          |
|   1 |  HASH GROUP BY               |           |  1000 | 30000 |   450   (2)| 00:00:05 |          |
|*  2 |   HASH JOIN                  |           |  1000 | 30000 |   440   (1)| 00:00:05 | YES      |
|*  3 |    TABLE ACCESS FULL         | CUSTOMERS |  1000 | 15000 |     3   (0)| 00:00:01 |          |
|*  4 |    TABLE ACCESS FULL         | ORDERS    | 50000 | 20000 |   440   (1)| 00:00:05 |          |
---------------------------------------------------------------------------------------------------

• The HASH JOIN operation in this example shows YES under ADAPTIVE, indicating that this part of the plan is adaptive.
• The optimizer initially planned for a NESTED LOOPS JOIN (not shown directly here) but has chosen a HASH JOIN due to the higher row estimates detected during runtime.

Adaptive Plan Adjustments

If the actual row count or workload changes, Oracle might adjust the join method dynamically. This helps prevent inefficient execution paths, ensuring optimal performance.

This demonstrates how OPTIMIZER_ADAPTIVE_PLANS allows Oracle to choose the best plan based on actual runtime conditions, avoiding potential performance issues caused by incorrect row count or join method estimates.