In Oracle 12c Adaptive Execution Plans allows optimizer to make Janision on SQL at runtime and in mid execution. It will actually make sub-plans and collect statistics to make final Janision on execution. Currently release only supports adaptive execution plans for join and parallel operations.
This article will demonstrate the use of Adaptive Execution Plan against a set test tables.
- Logon server as the oracle software owner and logon to SQLPLUS as the user scott.
[root@linux2 ~]# su – oracle
Last login: Thu Jan 15 13:05:52 EST 2016 on pts/1
Enter database to use:
1 – ORCL
2 – CDB1
Option >
1
[oracle@linux2 schema]$ sqlplus scott/password
SQL*Plus: Release 12.1.0.2.0 Production on Fri Jan 23 08:12:04 2016
Copyright (c) 1982, 2014, Oracle. All rights reserved.
Last Successful login time: Fri Jan 23 2016 08:10:57 -05:00
Connected to:
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 – 64bit Production
With the Partitioning, OLAP, Advanced Analytics, Real Application Testing
and Unified Auditing options
SQL>
- Create the user test with dba privileges.
SQL> create user test identified by test
default tablespace users quota unlimited on users; 2
User created.
SQL> grant dba to test;
Grant succeeded.
- Create a test table test_tab1 and 200 insert values.
SQL> create table test.test_tab1(
col_a number,
col_b varchar2(10),
constraint tab1_pk primary key(col_a)); 2 3 4
Table created.
SQL> Janlare
2 val_a number:=1;
3 val_b varchar2(10):=’a’;
4
5 begin
6
7 while val_a<200
8 loop
9 insert into test.test_tab1(col_a, col_b)
10 values(val_a, val_b);
11
12 val_a:=val_a+1;
13 if val_b=’z’
14 then
15 val_b:=’a’;
16 else
17 val_b:=chr(ascii(val_b) + 1);
18 end if;
19 end loop;
20 commit;
21 end;
22 /
PL/SQL procedure successfully completed.
SQL> commit;
Commit complete.
SQL>
- Create test table test_tab2 with foreign key back to test_tab1. NOTE: Adaptive Execution only work with join operations.
SQL> create table test.test_tab2(
tab1_col_a number,
col_a number,
col_b varchar2(10),
constraint tab2_tab1_fk foreign key (tab1_col_a) references test.test_tab1(col_a)); 2 3 4 5
Table created.
SQL>
- Populate test table test_tab2.
SQL> insert into test.test_tab2(tab1_col_a, col_a, col_b)
select col_a, col_a+1, ‘testing’
from test.test_tab1 where 1=mod(col_a,2); 2 3
100 rows created.
SQL> commit;
Commit complete.
SQL>
- Perform an explain for a select join operation on the two tables.
SQL> explain plan for select * from test.test_tab2 b, test.test_tab1 a
where a.col_a=tab1_col_a 2
3 ;
Explained.
SQL>
- Now examine the execution plan and not the adaptive remark at the bottom.
SQL> select * from table(dbms_xplan.display);
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
Plan hash value: 1368181317
——————————————————————————–
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
——————————————————————————–
| 0 | SELECT STATEMENT | | 100 | 5300 | 6 (0)| 00:00:01 |
|* 1 | HASH JOIN | | 100 | 5300 | 6 (0)| 00:00:01 |
| 2 | TABLE ACCESS FULL| TEST_TAB2 | 82 | 2706 | 3 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL| TEST_TAB1 | 82 | 1640 | 3 (0)| 00:00:01 |
——————————————————————————–
Predicate Information (identified by operation id):
—————————————————
1 – access(“A”.”COL_A”=”TAB1_COL_A”)
Note
—–
– dynamic statistics used: dynamic sampling (level=AUTO)
– this is an adaptive plan
20 rows selected.
SQL>
- Now use the format => adaptive reference in the dbms_xplan.display function to get the adaptive plan.
SQL> SET LINESIZE 200 PAGESIZE 100
SELECT * FROM TABLE(DBMS_XPLAN.display_cursor(format => ‘adaptive’));SQL>
PLAN_TABLE_OUTPUT
——————————————————————————————————————————————————————————————————–
SQL_ID ff5spha9pjuyu, child number 0
————————————-
select * from table(dbms_xplan.display)
Plan hash value: 2137789089
———————————————————————————————
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
———————————————————————————————
| 0 | SELECT STATEMENT | | | | 29 (100)| |
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY | 8168 | 16336 | 29 (0)| 00:00:01 |
———————————————————————————————
13 rows selected.
SQL>
- This completes testing of adaptive execution plan.
Larry Catt
OCP