Adaptive Query Optimization is a term used in Oracle Database 12c to describe a collection of features that work together to allow the cost based optimizer (CBO) to improve the accuracy of execution plans. Some of the features are renamed versions of functionality from previous releases, while others are new to Oracle Database 12c.
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".
the data is created to favour a nested loops join and statistics are gathered.
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 13 14:04:40 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL>
SQL> show user;
USER is "SYS"
SQL>
SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL>
SQL> show user;
USER is "MAXWELLPAN"
SQL>
SQL> Drop table tab2 purge;
Drop table tab2 purge
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> Drop SEQUENCE tab2_seq;
Drop SEQUENCE tab2_seq
*
ERROR at line 1:
ORA-02289: sequence does not exist
SQL> DROP TABLE tab1 PURGE;
DROP TABLE tab1 PURGE
*
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> DROP SEQUENCE tab1_seq;
DROP SEQUENCE tab1_seq
*
ERROR at line 1:
ORA-02289: sequence does not exist
SQL>
SQL> CREATE TABLE tab1 (
2 id NUMBER,
3 code VARCHAR2(5),
4 data NUMBER(5),
5 CONSTRAINT tab1_pk PRIMARY KEY (id)
6 );
Table created.
SQL> CREATE INDEX tab1_code ON tab1(code);
Index created.
SQL> CREATE SEQUENCE tab1_seq;
Sequence created.
SQL> 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);
1 row created.
SQL>
1 row created.
SQL> INSERT INTO tab1 VALUES (tab1_seq.nextval, 'FOUR', 4);
1 row created.
SQL>
1 row created.
SQL> INSERT INTO tab1 VALUES (tab1_seq.nextval, 'FIVE', 5);
1 row created.
SQL> COMMIT;
Commit complete.
SQL> CREATE TABLE tab2 (
2 id NUMBER,
3 tab1_id NUMBER,
4 data NUMBER(5),
5 CONSTRAINT tab2_pk PRIMARY KEY (id),
6 CONSTRAINT tab2_tab1_fk FOREIGN KEY (tab1_id) REFERENCES tab1(id)
7 );
Table created.
SQL> CREATE SEQUENCE tab2_seq;
Sequence created.
SQL> CREATE INDEX tab2_tab1_fki ON tab2(tab1_id);
Index created.
SQL> INSERT /*+ APPEND */ INTO tab2
2 SELECT tab2_seq.nextval,
3 TRUNC(DBMS_RANDOM.value(1,5)),
4 level
5 FROM dual
6 CONNECT BY level <= 100;
100 rows created.
SQL> COMMIT;
Commit complete.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB2');
PL/SQL procedure successfully completed.
SQL> 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.
SQL> SELECT a.data AS tab1_data,
2 b.data AS tab2_data
3 FROM tab1 a
4 JOIN tab2 b ON b.tab1_id = a.id
5 WHERE a.code = 'ONE';
TAB1_DATA TAB2_DATA
---------- ----------
1 1
1 2
1 6
1 21
1 22
1 26
1 34
1 36
1 41
1 42
1 43
1 47
1 50
1 51
1 52
1 63
1 64
1 66
1 68
1 71
1 77
1 80
1 97
1 100
24 rows selected.
SQL> set pagesize 200 linesize 200
SQL>
SQL> 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
29 rows selected.
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.
SQL>
SQL> SELECT a.data AS tab1_data,
2 b.data AS tab2_data
3 FROM tab1 a
4 JOIN tab2 b ON b.tab1_id = a.id
5 WHERE a.code = 'ONE';
TAB1_DATA TAB2_DATA
---------- ----------
1 1
1 2
1 6
1 21
1 22
1 26
1 34
1 36
1 41
1 42
1 43
1 47
1 50
1 51
1 52
1 63
1 64
1 66
1 68
1 71
1 77
1 80
1 97
1 100
24 rows selected.
SQL> SET LINESIZE 200 PAGESIZE 100
SQL>
SQL> 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)
33 rows selected.
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.
SQL>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
2 a.data AS tab1_data,
3 b.data AS tab2_data
4 FROM tab1 a
5 JOIN tab2 b ON b.tab1_id = a.id
6 WHERE a.code = 'ONE';
TAB1_DATA TAB2_DATA
---------- ----------
1 1
1 2
1 6
1 21
1 22
1 26
1 34
1 36
1 41
1 42
1 43
1 47
1 50
1 51
1 52
1 63
1 64
1 66
1 68
1 71
1 77
1 80
1 97
1 100
24 rows selected.
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> 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 | | 24 |00:00:00.01 | 8 |
|- * 1 | HASH JOIN | | 1 | 25 | 24 |00:00:00.01 | 8 |
| 2 | NESTED LOOPS | | 1 | 25 | 24 |00:00:00.01 | 8 |
| 3 | NESTED LOOPS | | 1 | 25 | 24 |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 | 24 |00:00:00.01 | 3 |
| 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 24 | 25 | 24 |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)
34 rows selected.
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.
SQL>
SQL>
SQL> INSERT /*+ APPEND */ INTO tab1
2 SELECT tab1_seq.nextval,
3 'ONE',
4 level
5 FROM dual
6 CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
Commit complete.
SQL> INSERT /*+ APPEND */ INTO tab2
2 SELECT tab2_seq.nextval,
3 TRUNC(DBMS_RANDOM.value(11,10005)),
4 level
5 FROM dual
6 CONNECT BY level <= 10000;
10000 rows created.
SQL> COMMIT;
Commit complete.
SQL>
SQL> 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.
SQL>
SQL>
SQL> 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> show user;
USER is "MAXWELLPAN"
SQL> Notice that the plan does not change, since the final plan was determined during the first run of the statement.
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
2 a.data AS tab1_data,
3 b.data AS tab2_data
4 FROM tab1 a
5 JOIN tab2 b ON b.tab1_id = a.id
6 WHERE a.code = 'ONE';
TAB1_DATA TAB2_DATA
---------- ----------
1 2
1 7
1 8
1 11
1 12
1 19
1 21
1 30
1 33
1 42
1 46
1 53
1 56
1 57
1 66
1 68
1 69
1 70
1 77
1 82
1 83
1 91
1 93
1 95
1 97
1 98
3156 1
5453 2
8676 3
8029 4
10026 rows selected.
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> 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 | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10026 |00:00:00.01 | 797 | 24 | | | |
| * 1 | HASH JOIN | | 1 | 25 | 10026 |00:00:00.01 | 797 | 24 | 2011K| 2011K| 2035K (0)|
|- 2 | NESTED LOOPS | | 1 | 25 | 10001 |00:00:00.01 | 99 | 24 | | | |
|- 3 | NESTED LOOPS | | 1 | 25 | 10001 |00:00:00.01 | 99 | 24 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | 10001 |00:00:00.01 | 99 | 24 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 10001 |00:00:00.02 | 99 | 24 | | | |
| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 10001 |00:00:00.01 | 73 | 0 | | | |
|- * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 0 | 25 | 0 |00:00:00.01 | 0 | 0 | | | |
|- 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 0 | 25 | 0 |00:00:00.01 | 0 | 0 | | | |
| 9 | TABLE ACCESS FULL | TAB2 | 1 | 25 | 10100 |00:00:00.01 | 698 | 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
-----
- dynamic statistics used: statistics for conventional DML
- this is an adaptive plan (rows marked '-' are inactive)
35 rows selected.
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.
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 13 14:56:57 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> alter session set container=PDB1;
Session altered.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS */
2 a.data AS tab1_data,
3 b.data AS tab2_data
4 FROM tab1 a
5 JOIN tab2 b ON b.tab1_id = a.id
6 WHERE a.code = 'ONE';
TAB1_DATA TAB2_DATA
---------- ----------
1 2
1 7
1 8
1 11
1 12
1 19
1 21
1 30
1 33
1 42
1 46
TAB1_DATA TAB2_DATA
---------- ----------
4956 9996
8911 9997
9981 9998
1892 9999
9235 10000
10026 rows selected.
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> 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 | | 10026 |00:00:00.01 | 761 | | | |
| * 1 | HASH JOIN | | 1 | 2525 | 10026 |00:00:00.01 | 761 | 2011K| 2011K| 2183K (0)|
|- 2 | NESTED LOOPS | | 1 | 2525 | 10001 |00:00:00.01 | 63 | | | |
|- 3 | NESTED LOOPS | | 1 | 2525 | 10001 |00:00:00.01 | 63 | | | |
|- 4 | STATISTICS COLLECTOR | | 1 | | 10001 |00:00:00.01 | 63 | | | |
| 5 | TABLE ACCESS BY INDEX ROWID BATCHED| TAB1 | 1 | 1 | 10001 |00:00:00.01 | 63 | | | |
| * 6 | INDEX RANGE SCAN | TAB1_CODE | 1 | 1 | 10001 |00:00:00.01 | 37 | | | |
|- * 7 | INDEX RANGE SCAN | TAB2_TAB1_FKI | 0 | 100 | 0 |00:00:00.01 | 0 | | | |
|- 8 | TABLE ACCESS BY INDEX ROWID | TAB2 | 0 | 2525 | 0 |00:00:00.01 | 0 | | | |
| 9 | TABLE ACCESS FULL | TAB2 | 1 | 2525 | 10100 |00:00:00.01 | 698 | | | |
------------------------------------------------------------------------------------------------------------------------------------------------
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
-----
- dynamic statistics used: statistics for conventional DML
- this is an adaptive plan (rows marked '-' are inactive)
35 rows selected.
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.
[oracle@oracle-db-19c ~]$ sqlplus / as sysdba
SQL*Plus: Release 19.0.0.0.0 - Production on Mon Feb 13 15:03:40 2023
Version 19.3.0.0.0
Copyright (c) 1982, 2019, Oracle. All rights reserved.
Connected to:
Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production
Version 19.3.0.0.0
SQL> show user;
USER is "SYS"
SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL>
SQL> show user;
USER is "MAXWELLPAN"
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB1');
PL/SQL procedure successfully completed.
SQL> EXEC DBMS_STATS.gather_table_stats(USER, 'TAB2');
PL/SQL procedure successfully completed.
SQL> CONN sys@pdb1 AS SYSDBA
Enter password:
Connected.
SQL> ALTER SYSTEM FLUSH SHARED_POOL;
System altered.
SQL> conn maxwellpan/maxwellpan@PDB1;
Connected.
SQL>
SQL> SELECT /*+ GATHER_PLAN_STATISTICS
2 PARALLEL(16) */
3 a.data AS tab1_data,
4 b.data AS tab2_data
5 FROM tab1 a
6 JOIN tab2 b ON b.tab1_id = a.id
7 WHERE a.code = 'ONE';
SQL> SET LINESIZE 200 PAGESIZE 100
SQL> 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: 1086986504
--------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows | A-Rows | A-Time | Buffers | OMem | 1Mem | Used-Mem | Used-Tmp|
--------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | 10026 |00:00:00.76 | 10 | | | | |
| 1 | PX COORDINATOR | | 1 | | 10026 |00:00:00.76 | 10 | 73728 | 73728 | | |
| 2 | PX SEND QC (RANDOM) | :TQ10002 | 0 | 10100 | 0 |00:00:00.01 | 0 | | | | |
|* 3 | HASH JOIN BUFFERED | | 0 | 10100 | 0 |00:00:00.01 | 0 | 3987K| 3323K| 2278K (1)| 1024K|
| 4 | PX RECEIVE | | 0 | 10001 | 0 |00:00:00.01 | 0 | | | | |
| 5 | PX SEND HYBRID HASH | :TQ10000 | 0 | 10001 | 0 |00:00:00.01 | 0 | | | | |
| 6 | STATISTICS COLLECTOR | | 0 | | 0 |00:00:00.01 | 0 | | | | |
| 7 | PX BLOCK ITERATOR | | 0 | 10001 | 0 |00:00:00.01 | 0 | | | | |
|* 8 | TABLE ACCESS FULL | TAB1 | 0 | 10001 | 0 |00:00:00.01 | 0 | | | | |
| 9 | PX RECEIVE | | 0 | 10100 | 0 |00:00:00.01 | 0 | | | | |
| 10 | PX SEND HYBRID HASH | :TQ10001 | 0 | 10100 | 0 |00:00:00.01 | 0 | | | | |
| 11 | PX BLOCK ITERATOR | | 0 | 10100 | 0 |00:00:00.01 | 0 | | | | |
|* 12 | 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")
8 - access(:Z>=:Z AND :Z<=:Z)
filter("A"."CODE"='ONE')
12 - access(:Z>=:Z AND :Z<=:Z)
Note
-----
- Degree of Parallelism is 16 because of hint
38 rows selected.
SQL> In Oracle 12.1 the adaptive optimizer features are controlled by the OPTIMIZER_ADAPTIVE_FEATURES parameter, which was set to TRUE by default.
-- Session-level.
ALTER SESSION SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE;
ALTER SESSION SET OPTIMIZER_ADAPTIVE_FEATURES = TRUE;
-- System-level.
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = FALSE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_FEATURES = TRUE SCOPE=BOTH;
In Oracle 12.2 the OPTIMIZER_ADAPTIVE_FEATURES parameter has been removed and replace by two new parameters.
OPTIMIZER_ADAPTIVE_PLANS: Default (TRUE). Enables/disables adaptive plans, star transformation bitmap pruning and the adaptive parallel distribution method.OPTIMIZER_ADAPTIVE_STATISTICS: Default (FALSE). Enables/disables SQL plan directives, statistics feedback for joins, performance feedback and adaptive dynamic sampling for parallel execution.
Both parameters are modifiable at both the session and system level. Notice most of the new adaptive functionality is turned off by default in 12.2. The reasoning for this is many of these optimizations are more appropriate for data warehousing, where there optimization time is is a small proportion of the query runtime. In OLTP environments, where SQL runtime is typically smaller, the additional optimization time may become a significant part of the elapsed time, for little extra benefit.
-- Session-level.
ALTER SESSION SET OPTIMIZER_ADAPTIVE_PLANS = FALSE;
ALTER SESSION SET OPTIMIZER_ADAPTIVE_PLANS = TRUE;
ALTER SESSION SET OPTIMIZER_ADAPTIVE_STATISTICS = TRUE;
ALTER SESSION SET OPTIMIZER_ADAPTIVE_STATISTICS = FALSE;
-- System-level.
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = FALSE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_PLANS = TRUE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = TRUE SCOPE=BOTH;
ALTER SYSTEM SET OPTIMIZER_ADAPTIVE_STATISTICS = FALSE SCOPE=BOTH;
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
