8i | 9i | 10g | 11g | 12c | 13c | 18c | 19c | 21c | 23c | Misc | PL/SQL | SQL | RAC | WebLogic | Linux

Home » Articles » 12c » Here

Adaptive Plans in Oracle Database 12c Release 1 (12.1)

The cost-based optimizer uses database statistics to determine the optimal execution plan for a SQL statement. If those statistics are not representative of the data, or if the query uses complex predicates, operators or joins the estimated cardinality of the operations may be incorrect and therefore the selected plan is likely to be less than optimal. In previous database releases, once the execution plan was determined there was no possible deviation from it at runtime.

Adaptive Plans in Oracle Database 12c allow runtime changes to execution plans. Rather than selecting a single "best" plan, the optimizer will determine the default plan, and can include alternative subplans for each major join operation in the plan. At runtime the cardinality of operations is checked using statistics collectors and compared to the cardinality estimates used to generate the execution plan. If the cardinality of the operation is not as expected, an alternative subplan can be used. For example, if the statistics suggest two small sets are to be joined, it is likely the optimizer will choose a nested loops join. At runtime, if the fetch operation of the first set returns more than the expected number of rows, the optimizer can switch to a subplan using a hash join instead. This same adaptation can happen for every join operation in the query if an alternative subplan is present. Once the query has run to completion and the optimal plan is determined, the final plan is fixed until it is aged out of the shared pool of reoptimized for some other reason. The statistics collectors can also be used to influence the parallel distribution method used for parallel queries.

Control of the adaptive optimizations has altered in 12.2 and the change is available in 12.1 with the correct patches. See here.

Related articles.

Adaptive Join Method

It is important to remember the adaptive join method functionality is only used during the first execution of the statement, so subsequent executions will follow the final plan determined by the first execution. For adaptive plans, once the final plan is determined, the IS_RESOLVED_ADAPTIVE_PLAN column of the V$SQL view will be marked as "Y".

In this example, the data is created to favour a nested loops join and statistics are gathered.

CONN test/test@pdb1

DROP TABLE tab2 PURGE;
DROP SEQUENCE tab2_seq;
DROP TABLE tab1 PURGE;
DROP SEQUENCE tab1_seq;


CREATE TABLE tab1 (
  id    NUMBER,
  code  VARCHAR2(5),
  data  NUMBER(5),
  CONSTRAINT tab1_pk PRIMARY KEY (id)
);

CREATE INDEX tab1_code ON tab1(code);

CREATE SEQUENCE tab1_seq;

INSERT INTO tab1 VALUES (tab1_seq.nextval, 'ONE', 1);
INSERT INTO tab1 VALUES (tab1_seq.nextval, 'TWO', 2);
INSERT INTO tab1 VALUES (tab1_seq.nextval, 'THREE', 3);
INSERT INTO tab1 VALUES (tab1_seq.nextval, 'FOUR', 4);
INSERT INTO tab1 VALUES (tab1_seq.nextval, 'FIVE', 5);
COMMIT;


CREATE TABLE tab2 (
  id       NUMBER,
  tab1_id  NUMBER,
  data     NUMBER(5),
  CONSTRAINT tab2_pk PRIMARY KEY (id),
  CONSTRAINT tab2_tab1_fk FOREIGN KEY (tab1_id) REFERENCES tab1(id)
);

CREATE SEQUENCE tab2_seq;

CREATE INDEX tab2_tab1_fki ON tab2(tab1_id);

INSERT /*+ APPEND */ INTO tab2
SELECT tab2_seq.nextval,
       TRUNC(DBMS_RANDOM.value(1,5)),
       level
FROM   dual
CONNECT BY level <= 100;
COMMIT;

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB2');

The following query performs a join between the two tables. The query against the DBMS_XPLAN.DISPLAY_CURSOR pipelined table function displays the execution plan used by the statement.

SELECT a.data AS tab1_data,
       b.data AS tab2_data
FROM   tab1 a
       JOIN tab2 b ON b.tab1_id = a.id
WHERE  a.code = 'ONE';

SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor);

PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
SQL_ID  4r3harjun4dvz, child number 0
-------------------------------------
SELECT a.data AS tab1_data,        b.data AS tab2_data FROM   tab1 a
    JOIN tab2 b ON b.tab1_id = a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

-------------------------------------------------------------------------------------------------------
| Id  | Operation                             | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                      |               |       |       |     3 (100)|          |
|   1 |  NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|   2 |   NESTED LOOPS                        |               |    25 |   425 |     3   (0)| 00:00:01 |
|   3 |    TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|*  4 |     INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|*  5 |    INDEX RANGE SCAN                   | TAB2_TAB1_FKI |    25 |       |     0   (0)|          |
|   6 |   TABLE ACCESS BY INDEX ROWID         | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   4 - access("A"."CODE"='ONE')
   5 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan

SQL>

Notice the "this is an adaptive plan" line in the "Note" section of the output. We can display the whole adaptive plan by altering the format of the DBMS_XPLAN.DISPLAY_CURSOR output.

SELECT a.data AS tab1_data,
       b.data AS tab2_data
FROM   tab1 a
       JOIN tab2 b ON b.tab1_id = a.id
WHERE  a.code = 'ONE';

SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive'));

PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------
SQL_ID  4r3harjun4dvz, child number 0
-------------------------------------
SELECT a.data AS tab1_data,        b.data AS tab2_data FROM   tab1 a
    JOIN tab2 b ON b.tab1_id = a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

-----------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name          | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |               |       |       |     3 (100)|          |
|- *  1 |  HASH JOIN                              |               |    25 |   425 |     3   (0)| 00:00:01 |
|     2 |   NESTED LOOPS                          |               |    25 |   425 |     3   (0)| 00:00:01 |
|     3 |    NESTED LOOPS                         |               |    25 |   425 |     3   (0)| 00:00:01 |
|-    4 |     STATISTICS COLLECTOR                |               |       |       |            |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |     1 |    11 |     2   (0)| 00:00:01 |
|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |     1 |       |     1   (0)| 00:00:01 |
|  *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |    25 |       |     0   (0)|          |
|     8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
|-    9 |   TABLE ACCESS FULL                     | TAB2          |    25 |   150 |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."TAB1_ID"="A"."ID")
   6 - access("A"."CODE"='ONE')
   7 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

SQL>

Although we just need to add the "adaptive" format, we will also include the GATHER_PLAN_STATISTICS hint to the query and "allstats last" format so we also get the expected and actual cardinalities.

SELECT /*+ GATHER_PLAN_STATISTICS */
       a.data AS tab1_data,
       b.data AS tab2_data
FROM   tab1 a
       JOIN tab2 b ON b.tab1_id = a.id
WHERE  a.code = 'ONE';

SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'adaptive allstats last'));

PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------------------
SQL_ID  1km5kczcgr0fr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */        a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a        JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

---------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
---------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |               |      1 |        |     25 |00:00:00.01 |       8 |
|- *  1 |  HASH JOIN                              |               |      1 |     25 |     25 |00:00:00.01 |       8 |
|     2 |   NESTED LOOPS                          |               |      1 |     25 |     25 |00:00:00.01 |       8 |
|     3 |    NESTED LOOPS                         |               |      1 |     25 |     25 |00:00:00.01 |       5 |
|-    4 |     STATISTICS COLLECTOR                |               |      1 |        |      1 |00:00:00.01 |       2 |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |      1 |00:00:00.01 |       2 |
|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |      1 |00:00:00.01 |       1 |
|  *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |      1 |     25 |     25 |00:00:00.01 |       3 |
|     8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |     25 |     25 |     25 |00:00:00.01 |       3 |
|-    9 |   TABLE ACCESS FULL                     | TAB2          |      0 |     25 |      0 |00:00:00.01 |       0 |
---------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."TAB1_ID"="A"."ID")
   6 - access("A"."CODE"='ONE')
   7 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

SQL>

As described in the "Note" section, the symbol "-" in the "Id" column indicates the lines that were not used in the plan.

Now let's alter the data to make the existing plan not so attractive.

INSERT /*+ APPEND */ INTO tab1
SELECT tab1_seq.nextval,
       'ONE',
       level
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

INSERT /*+ APPEND */ INTO tab2
SELECT tab2_seq.nextval,
       TRUNC(DBMS_RANDOM.value(11,10005)),
       level
FROM   dual
CONNECT BY level <= 10000;
COMMIT;

We now have a lot more rows that match the initial filter criteria, so we've gone from a driving set of 1 row to a driving set of 10001 rows. All of a sudden the nested loop doesn't sound so attractive. Notice, we've not updated the statistics, so the optimizer doesn't know that things have changed.

SELECT num_rows FROM user_tables WHERE table_name = 'TAB1';

  NUM_ROWS
----------
         5

SQL>

SELECT num_rows FROM user_tables WHERE table_name = 'TAB2';

  NUM_ROWS
----------
       100

SQL>

Notice that the plan does not change, since the final plan was determined during the first run of the statement.

SELECT /*+ GATHER_PLAN_STATISTICS */
       a.data AS tab1_data,
       b.data AS tab2_data
FROM   tab1 a
       JOIN tab2 b ON b.tab1_id = a.id
WHERE  a.code = 'ONE';

SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------
SQL_ID	1km5kczcgr0fr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */	    a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a	 JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 2672205743

------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |               |      1 |        |  10025 |00:00:00.40 |   12623 |     30 |
|- *  1 |  HASH JOIN                              |               |      1 |     25 |  10025 |00:00:00.40 |   12623 |     30 |
|     2 |   NESTED LOOPS                          |               |      1 |     25 |  10025 |00:00:00.31 |   12623 |     30 |
|     3 |    NESTED LOOPS                         |               |      1 |     25 |  10025 |00:00:00.17 |    3042 |     30 |
|-    4 |     STATISTICS COLLECTOR                |               |      1 |        |  10001 |00:00:00.14 |    1431 |     30 |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |  10001 |00:00:00.09 |    1431 |     30 |
|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |  10001 |00:00:00.03 |     739 |      0 |
|  *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |  10001 |     25 |  10025 |00:00:00.07 |    1611 |      0 |
|     8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |  10025 |     25 |  10025 |00:00:00.07 |    9581 |      0 |
|-    9 |   TABLE ACCESS FULL                     | TAB2          |      0 |     25 |      0 |00:00:00.01 |       0 |      0 |
------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."TAB1_ID"="A"."ID")
   6 - access("A"."CODE"='ONE')
   7 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

SQL>

If we flush the shared pool to force a hard parse of the statement, we will see the same adaptive plan is created based on the cardinality estimates from the statistics, but the final plan takes advantage of the hash join because the incorrect cardinality estimates were detected at runtime and hash join subplan was used in place of the nested loops join.

CONN sys@pdb1 AS SYSDBA
ALTER SYSTEM FLUSH SHARED_POOL;

CONN test/test@pdb1

SELECT /*+ GATHER_PLAN_STATISTICS */
       a.data AS tab1_data,
       b.data AS tab2_data
FROM   tab1 a
       JOIN tab2 b ON b.tab1_id = a.id
WHERE  a.code = 'ONE';

SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID	1km5kczcgr0fr, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS */	    a.data AS tab1_data,
b.data AS tab2_data FROM   tab1 a	 JOIN tab2 b ON b.tab1_id =
a.id WHERE  a.code = 'ONE'

Plan hash value: 1599395313

------------------------------------------------------------------------------------------------------------------------------------------------
|   Id  | Operation                               | Name          | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|     0 | SELECT STATEMENT                        |               |      1 |        |  10025 |00:00:00.04 |     760 |       |       |          |
|  *  1 |  HASH JOIN                              |               |      1 |     25 |  10025 |00:00:00.04 |     760 |  1899K|  1899K| 1944K (0)|
|-    2 |   NESTED LOOPS                          |               |      1 |     25 |  10001 |00:00:00.16 |      63 |       |       |          |
|-    3 |    NESTED LOOPS                         |               |      1 |     25 |  10001 |00:00:00.12 |      63 |       |       |          |
|-    4 |     STATISTICS COLLECTOR                |               |      1 |        |  10001 |00:00:00.09 |      63 |       |       |          |
|     5 |      TABLE ACCESS BY INDEX ROWID BATCHED| TAB1          |      1 |      1 |  10001 |00:00:00.06 |      63 |       |       |          |
|  *  6 |       INDEX RANGE SCAN                  | TAB1_CODE     |      1 |      1 |  10001 |00:00:00.02 |      37 |       |       |          |
|- *  7 |     INDEX RANGE SCAN                    | TAB2_TAB1_FKI |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |
|-    8 |    TABLE ACCESS BY INDEX ROWID          | TAB2          |      0 |     25 |      0 |00:00:00.01 |       0 |       |       |          |
|     9 |   TABLE ACCESS FULL                     | TAB2          |      1 |     25 |  10100 |00:00:00.03 |     697 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - access("B"."TAB1_ID"="A"."ID")
   6 - access("A"."CODE"='ONE')
   7 - access("B"."TAB1_ID"="A"."ID")

Note
-----
   - this is an adaptive plan (rows marked '-' are inactive)

SQL>

Adaptive Parallel Distribution Method

For a parallel query, the optimizer must decide how rows are distributed (broadcast or hash for the left input, round-robin or hash for the right input) to slaves between one operation and the next. Picking the wrong distribution method can have an impact on the performance of the parallel query. Oracle Database 12c introduces an adaptive parallel distribution method called hybrid hash, where the decision on the distribution method is delayed until execution time, based on the results of statistics collectors. Unlike the adaptive join method, which is limited to the first execution, the adaptive parallel distribution method is used for each execution of the statement.

The hybrid hash adaptive distribution method assumes a hash distribution is required. If the number of rows returned on the producer side of the parallel operation is less than a threshold value, the distribution method is switched to broadcast. The threshold value is twice the degree of parallelism (DOP) for the query.

We can use the data from the previous example to show the presence of the hybrid hash in a parallel query. All we need to do is gather statistics again, but we will also flush the shared pool to keep things clean.

CONN test/test@pdb1

EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
EXEC DBMS_STATS.gather_table_stats(USER, 'TAB2');

CONN sys@pdb1 AS SYSDBA
ALTER SYSTEM FLUSH SHARED_POOL;

CONN test/test@pdb1

If we repeat the query from the previous section, but add a parallel hint, we can see the new PX SEND HYBRID HASH distribution method is used, allowing the decision on the distribution method to be delayed until execution time. The relevant parts of the plan are shown in bold.

SELECT /*+ GATHER_PLAN_STATISTICS 
           PARALLEL(16) */
       a.data AS tab1_data,
       b.data AS tab2_data
FROM   tab1 a
       JOIN tab2 b ON b.tab1_id = a.id
WHERE  a.code = 'ONE';

SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => 'allstats last adaptive'));


PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------
SQL_ID  axw4khkastaz7, child number 0
-------------------------------------
SELECT /*+ GATHER_PLAN_STATISTICS            PARALLEL(16) */
a.data AS tab1_data,        b.data AS tab2_data FROM   tab1 a
JOIN tab2 b ON b.tab1_id = a.id WHERE  a.code = 'ONE'

Plan hash value: 1613349611

-----------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |  OMem |  1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |  10029 |00:00:00.08 |      10 |       |       |          |
|   1 |  PX COORDINATOR             |          |      1 |        |  10029 |00:00:00.08 |      10 |       |       |          |
|   2 |   PX SEND QC (RANDOM)       | :TQ10002 |      0 |   3178 |      0 |00:00:00.01 |       0 |       |       |          |
|*  3 |    HASH JOIN BUFFERED       |          |      0 |   3178 |      0 |00:00:00.01 |       0 |  2517K|  2517K| 2398K (0)|
|   4 |     JOIN FILTER CREATE      | :BF0000  |      0 |   2001 |      0 |00:00:00.01 |       0 |       |       |          |
|   5 |      PX RECEIVE             |          |      0 |   2001 |      0 |00:00:00.01 |       0 |       |       |          |
|   6 |       PX SEND HYBRID HASH   | :TQ10000 |      0 |   2001 |      0 |00:00:00.01 |       0 |       |       |          |
|   7 |        STATISTICS COLLECTOR |          |      0 |        |      0 |00:00:00.01 |       0 |       |       |          |
|   8 |         PX BLOCK ITERATOR   |          |      0 |   2001 |      0 |00:00:00.01 |       0 |       |       |          |
|*  9 |          TABLE ACCESS FULL  | TAB1     |      0 |   2001 |      0 |00:00:00.01 |       0 |       |       |          |
|  10 |     PX RECEIVE              |          |      0 |  10100 |      0 |00:00:00.01 |       0 |       |       |          |
|  11 |      PX SEND HYBRID HASH    | :TQ10001 |      0 |  10100 |      0 |00:00:00.01 |       0 |       |       |          |
|  12 |       JOIN FILTER USE       | :BF0000  |      0 |  10100 |      0 |00:00:00.01 |       0 |       |       |          |
|  13 |        PX BLOCK ITERATOR    |          |      0 |  10100 |      0 |00:00:00.01 |       0 |       |       |          |
|* 14 |         TABLE ACCESS FULL   | TAB2     |      0 |  10100 |      0 |00:00:00.01 |       0 |       |       |          |
-----------------------------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   3 - access("B"."TAB1_ID"="A"."ID")
   9 - access(:Z>=:Z AND :Z<=:Z)
       filter("A"."CODE"='ONE')
  14 - access(:Z>=:Z AND :Z<=:Z)
       filter(SYS_OP_BLOOM_FILTER(:BF0000,"B"."TAB1_ID"))

Note
-----
   - Degree of Parallelism is 16 because of hint

SQL>

Miscellaneous Points

For more information see:

Hope this helps. Regards Tim...

Back to the Top.