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.
It is important to remember the adaptive join method
functionality is only used during the first execution (Hard parsing) of the
statement, so subsequent executions (soft parsing) 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 favor a nested loops
join and statistics are gathered
rajesh@PDB1> create table t1
2 (id number,
3 code varchar2(10),
4 others char(100) default 'x',
5 constraint t1_pk primary key(id) );
Table created.
rajesh@PDB1>
rajesh@PDB1> insert into t1(id,code)
values(s1.nextval,'ONE');
1 row created.
rajesh@PDB1> insert into t1(id,code)
values(s1.nextval,'TWO');
1 row created.
rajesh@PDB1> insert into t1(id,code)
values(s1.nextval,'THREE');
1 row created.
rajesh@PDB1> insert into t1(id,code)
values(s1.nextval,'FOUR');
1 row created.
rajesh@PDB1> insert into t1(id,code)
values(s1.nextval,'FIVE');
1 row created.
rajesh@PDB1> commit;
Commit complete.
rajesh@PDB1>
rajesh@PDB1> create index t1_idx on t1(code);
Index created.
rajesh@PDB1> create table t2
2 (id number ,
3 t1_id references t1,
4 data char(100) default 'x',
5 constraint t2_pk primary key(id) ) ;
Table created.
rajesh@PDB1>
rajesh@PDB1> insert into t2(id,t1_id)
2 select s2.nextval , trunc(
dbms_random.value(1,5))
3 from dual
4 connect by level <=20;
20 rows created.
rajesh@PDB1> commit;
Commit complete.
rajesh@PDB1>
rajesh@PDB1> create index t2_idx on t2(t1_id);
Index created.
rajesh@PDB1> exec dbms_stats.gather_table_stats(user,'T1');
PL/SQL procedure successfully completed.
rajesh@PDB1> exec dbms_stats.gather_table_stats(user,'T2');
PL/SQL procedure successfully completed.
rajesh@PDB1>
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.
rajesh@PDB1> column others noprint
rajesh@PDB1> column data noprint
rajesh@PDB1> set serveroutput off
rajesh@PDB1> alter session set statistics_level=all;
Session altered.
rajesh@PDB1> @d:\script.sql
rajesh@PDB1> /*
rajesh@PDB1> set termout off
rajesh@PDB1> select *
rajesh@PDB1> from t1, t2
rajesh@PDB1> where t1.id = t2.t1_id
rajesh@PDB1> and t1.code ='ONE';
rajesh@PDB1> set termout on
rajesh@PDB1> */
rajesh@PDB1>
rajesh@PDB1> set termout off
rajesh@PDB1>
rajesh@PDB1> select * from
table(dbms_xplan.display_cursor(format=>'adaptive allstats last'));
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------------------
SQL_ID 1tdvk58uq6a1a,
child number 0
-------------------------------------
select * from t1, t2 where t1.id = t2.t1_id and t1.code ='ONE'
Plan hash value: 3573579377
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
| Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | | 7 |00:00:00.01 | 6 |
|- * 1 | HASH JOIN |
| 1 | 5 |
7 |00:00:00.01 | 6 |
| 2 | NESTED LOOPS | |
1 | 5 | 7 |00:00:00.01 | 6 |
| 3 | NESTED LOOPS | |
1 | 5 | 7
|00:00:00.01 | 4 |
|- 4 | STATISTICS COLLECTOR | |
1 | | 1 |00:00:00.01 | 2 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED|
T1 | 1 |
1 | 1 |00:00:00.01 | 2 |
| * 6 |
INDEX RANGE SCAN | T1_IDX | 1 |
1 | 1 |00:00:00.01 | 1 |
| * 7 |
INDEX RANGE SCAN
| T2_IDX | 1 | 5 |
7 |00:00:00.01 | 2 |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 |
7 | 5 | 7 |00:00:00.01 | 2 |
|- 9 | TABLE ACCESS FULL | T2 |
0 | 5 | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
access("T1"."ID"="T2"."T1_ID")
6 -
access("T1"."CODE"='ONE')
7 -
access("T1"."ID"="T2"."T1_ID")
Note
-----
- this is an adaptive plan (rows marked '-' are inactive)
32 rows selected.
rajesh@PDB1>
rajesh@PDB1> select substr(sql_text,1,25),is_resolved_adaptive_plan
2 from v$sql
3 where sql_id ='1tdvk58uq6a1a' ;
SUBSTR(SQL_TEXT,1,25)
I
------------------------- -
select * from t1, t2 wher Y
1 row selected.
rajesh@PDB1>
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.
rajesh@PDB1>
rajesh@PDB1> insert into t1(id,code)
2 select s1.nextval, 'ONE'
3 from dual
4 connect by level <=10000;
10000 rows created.
rajesh@PDB1> commit;
Commit complete.
rajesh@PDB1>
rajesh@PDB1> insert into t2(id,t1_id)
2 select s2.nextval,
trunc(dbms_random.value(6,10000))
3 from dual
4 connect by level <=10000;
10000 rows created.
rajesh@PDB1> commit;
rajesh@PDB1>
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 many 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.
rajesh@PDB1> select table_name,num_rows
2 from user_tables
3 where table_name in ('T1','T2');
TABLE_NAME NUM_ROWS
---------- ----------
T2 20
T1 5
2 rows selected.
rajesh@PDB1>
Notice that the plan does not change, since the final plan was
determined during the first run (Hard parsing) of the statement.
rajesh@PDB1> @d:\script.sql
rajesh@PDB1> /*
rajesh@PDB1> set termout off
rajesh@PDB1> select *
rajesh@PDB1> from t1, t2
rajesh@PDB1> where t1.id = t2.t1_id
rajesh@PDB1> and t1.code ='ONE';
rajesh@PDB1> set termout on
rajesh@PDB1> */
rajesh@PDB1>
rajesh@PDB1> set termout off
rajesh@PDB1>
rajesh@PDB1> select * from
table(dbms_xplan.display_cursor(format=>'adaptive allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 1tdvk58uq6a1a,
child number 0
-------------------------------------
select * from t1, t2 where t1.id = t2.t1_id and t1.code ='ONE'
Plan hash value: 3573579377
--------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
| Buffers |
--------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | | 10007 |00:00:00.16 | 11148 |
|- * 1 | HASH JOIN
| |
1 | 5 | 10007 |00:00:00.16 | 11148 |
| 2 | NESTED LOOPS | |
1 | 5 | 10007 |00:00:00.14 | 11148 |
| 3 | NESTED LOOPS | |
1 | 5 | 10007 |00:00:00.08 | 1205 |
|- 4 | STATISTICS COLLECTOR | |
1 | | 10001 |00:00:00.03 | 363 |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED|
T1 | 1 |
1 | 10001 |00:00:00.02 | 363 |
| * 6 |
INDEX RANGE SCAN
| T1_IDX | 1 | 1 |
10001 |00:00:00.01 | 137 |
| * 7 |
INDEX RANGE SCAN
| T2_IDX | 10001 | 5 |
10007 |00:00:00.04 | 842 |
| 8 | TABLE ACCESS BY INDEX ROWID | T2 |
10007 | 5 | 10007 |00:00:00.04 | 9943 |
|- 9 | TABLE ACCESS FULL | T2 |
0 | 5 | 0 |00:00:00.01 | 0 |
--------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 -
access("T1"."ID"="T2"."T1_ID")
6 -
access("T1"."CODE"='ONE')
7 -
access("T1"."ID"="T2"."T1_ID")
Note
-----
- this is an adaptive
plan (rows marked '-' are inactive)
32 rows selected.
rajesh@PDB1>
If we influence the 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.
rajesh@PDB1> set echo off
rajesh@PDB1> @d:\script1.sql
rajesh@PDB1> /*
rajesh@PDB1> set termout off
rajesh@PDB1> select *
rajesh@PDB1> from t1, t2 t
rajesh@PDB1> where t1.id = t.t1_id
rajesh@PDB1> and t1.code ='ONE';
rajesh@PDB1> set termout on
rajesh@PDB1>
rajesh@PDB1> */
rajesh@PDB1>
rajesh@PDB1> set termout off
rajesh@PDB1>
rajesh@PDB1> select * from
table(dbms_xplan.display_cursor(format=>'adaptive allstats last'));
PLAN_TABLE_OUTPUT
-----------------------------------------------------------------------------------------------------------------------------------
SQL_ID 2t0pgvvas1g4j,
child number 0
-------------------------------------
select * from t1, t2 t where t1.id = t.t1_id and t1.code ='ONE'
Plan hash value: 451563210
-----------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time
| Buffers | OMem | 1Mem | Used-Mem |
-----------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT
STATEMENT | |
1 | | 10007 |00:00:00.07 | 452 |
| | |
| * 1 |
HASH JOIN
| | 1 |
5 | 10007 |00:00:00.07 | 452 |
2210K| 1106K| 2554K (0)|
|- 2 | NESTED LOOPS | |
1 | 5 | 10001 |00:00:00.03 | 194 |
| | |
|- 3 | NESTED LOOPS | |
1 | 5 | 10001 |00:00:00.03 | 194 |
| | |
|- 4 | STATISTICS COLLECTOR | |
1 | | 10001 |00:00:00.02 | 194 |
| | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED|
T1 | 1 |
1 | 10001 |00:00:00.01 | 194 |
| | |
| * 6 |
INDEX RANGE SCAN
| T1_IDX | 1 | 1 |
10001 |00:00:00.01 | 35 | | |
|
|- * 7 | INDEX RANGE SCAN | T2_IDX | 0 |
5 | 0 |00:00:00.01 | 0 |
| | |
|- 8 | TABLE ACCESS BY INDEX ROWID | T2 |
0 | 5 | 0 |00:00:00.01 | 0 |
| | |
| 9 | TABLE ACCESS FULL | T2 |
1 | 5 | 10020 |00:00:00.01 | 258 |
| | |
-----------------------------------------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - access("T1"."ID"="T"."T1_ID")
6 -
access("T1"."CODE"='ONE')
7 -
access("T1"."ID"="T"."T1_ID")
Note
-----
- this is an adaptive
plan (rows marked '-' are inactive)
32 rows selected.
rajesh@PDB1>
rajesh@PDB1> set echo off
rajesh@PDB1>
rajesh@PDB1> select
substr(sql_text,1,25),is_resolved_adaptive_plan
2 from v$sql
3 where sql_id ='2t0pgvvas1g4j' ;
SUBSTR(SQL_TEXT,1,25)
I
------------------------- -
select * from t1, t2 t wh Y
1 row selected.
rajesh@PDB1>
