📋Steps in this guide1/3

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

See how the 12c optimizer uses adaptive plans to adjust executiuon plans on the fly.

oracle 12cconfigurationintermediate

by OracleDba

11 views

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 column of the view will be marked as "Y". In this example, the data is created to favour a nested loops join and statistics are gathered. The following query performs a join between the two tables. The query against the pipelined table function displays the execution plan used by the statement. 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. Although we just need to add the "adaptive" format, we will also include the hint to the query and "allstats last" format so we also get the expected and actual cardinalities. 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. 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. Notice that the plan does not change, since the final plan was determined during the first run of the statement. 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.

Code/Command (click line numbers to comment):

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');

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>

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>

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>

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;

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>

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>

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. If we repeat the query from the previous section, but add a parallel hint, we can see the new 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.

Code/Command (click line numbers to comment):

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

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

- The following initialization parameters must be set for adaptive plans to work. The parameter must be set to 12.1.0.1 or higher. The following initialization parameters must be set for adaptive plans to work. The parameter must be set to 12.1.0.1 or higher. - The final plan can be used with SQL Plan Management . The final plan can be used with SQL Plan Management . - Adaptive plans do not allow the join order to be altered, so even if a better join method or parallel distribution method is used, the execution plan may still be sub-optimal. In this case the SQL statement may be flagged for reoptimization, as indicated by the column in the view. Adaptive plans do not allow the join order to be altered, so even if a better join method or parallel distribution method is used, the execution plan may still be sub-optimal. In this case the SQL statement may be flagged for reoptimization, as indicated by the column in the view. For more information see: Hope this helps. Regards Tim...

Code/Command (click line numbers to comment):

1
2
3

optimizer_adaptive_features	     boolean	 TRUE
optimizer_adaptive_reporting_only    boolean	 FALSE
optimizer_features_enable	     string	 12.1.0.1

Comments (0)

Please to add comments

No comments yet. Be the first to comment!