This post is just a compilation of the links to other people’s articles and short descriptions about new SQL PLAN OPERATIONS and HINTS with a couple little additions from me.
| OPERATION_NAME | Description | Links |
|---|---|---|
| JSONTABLE EVALUATION | JSON_TABLE execution | |
| XMLTABLE EVALUATION | This is new name for “COLLECTION ITERATOR PICKLER FETCH [XQSEQUENCEFROMXMLTYPE]”. XPATH EVALUATION still exists. | |
| MATCH RECOGNIZE | New feature “PATTERN MATCHING” | |
| STATISTICS COLLECTOR | Optimizer statistics collector |
|
| OPTIMIZER STATISTICS GATHERING | Automatic Optimizer statistics gathering during the following types of bulk loads:
|
|
| CUBE JOIN | Joining Cubes to Tables and Views |
|
| EXPRESSION EVALUATION | Each parallel slave executes scalar correllated subqueries from SELECT-list. | |
| parallel “FILTER” | Each parallel slave executes own FILTER operation
Example
SQL> explain plan for
2 select--+ parallel
3 owner,object_name
4 from xt_test l
5 where exists(select/*+ no_unnest */ 0 from dual where dummy=object_name);
Explained.
PLAN_TABLE_OUTPUT
-------------------------------------------------------------------------------------------------------
Plan hash value: 2189761709
-------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 2 | 62 | 177K (1)| | | |
| 1 | PX COORDINATOR | | | | | | | |
| 2 | PX SEND QC (RANDOM) | :TQ10000 | 91060 | 2756K| 113 (0)| Q1,00 | P->S | QC (RAND) |
|* 3 | FILTER | | | | | Q1,00 | PCWC | |
| 4 | PX BLOCK ITERATOR | | 91060 | 2756K| 113 (0)| Q1,00 | PCWC | |
| 5 | INDEX FAST FULL SCAN| IX_TEST_1 | 91060 | 2756K| 113 (0)| Q1,00 | PCWP | |
|* 6 | TABLE ACCESS FULL | DUAL | 1 | 2 | 2 (0)| | | |
-------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - filter( EXISTS (SELECT /*+ NO_UNNEST */ 0 FROM "SYS"."DUAL" "DUAL" WHERE "DUMMY"=:B1))
6 - filter("DUMMY"=:B1)
[collapse]
|
|
| PX SELECTOR | Execution of the serial plan parts in the one of the parallel slaves | |
| PX SEND 1 SLAVE | Execution of the serial plan parts in the one of the parallel slaves(single DFO tree) | |
| PX TASK | Parallel access to fixed tables(x$) by each node in RAC | |
| HYBRID HASH DISTRIBUTION | Adaptive parallel data distribution that does not decide the final data distribution(HASH, BROADCAST or SKEW) method until execution time. | |
| PQ_DISTRIBUTE_WINDOW | In addition to “PX SEND” HASH-distribution for WINDOW functions, “PX SEND RANGE” was added
Example
-- TESTPART - list-partitiioned table:
-------------------------------------------------------------------------------------------------
| Operation | Name | Rows | Cost | Pstart| Pstop | TQ |IN-OUT| PQ Distrib |
-------------------------------------------------------------------------------------------------
| SELECT STATEMENT | | 74384 | 102| | | | | |
| PX COORDINATOR | | | | | | | | |
| PX SEND QC (RANDOM) | :TQ10001 | 74384 | 102| | | Q1,01 | P->S | QC (RAND) |
| WINDOW SORT | | 74384 | 102| | | Q1,01 | PCWP | |
| PX RECEIVE | | 74384 | 100| | | Q1,01 | PCWP | |
| PX SEND RANGE | :TQ10000 | 74384 | 100| | | Q1,00 | P->P | RANGE |
| PX BLOCK ITERATOR | | 74384 | 100| 1 | 3 | Q1,00 | PCWC | |
| TABLE ACCESS FULL| TESTPART | 74384 | 100| 1 | 3 | Q1,00 | PCWP | |
-------------------------------------------------------------------------------------------------
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
PQ_DISTRIBUTE_WINDOW(@"SEL$1" 3)
FULL(@"SEL$1" “TESTPART"@"SEL$1")
OUTLINE_LEAF(@"SEL$1")
ALL_ROWS
DB_VERSION('12.1.0.2')
OPTIMIZER_FEATURES_ENABLE('12.1.0.2')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
[collapse]
Hint PQ_DISTRIBUTE_WINDOW(@Query_block N), where N=1 for hash, N=2 for range, N=3 for list |
|
| VECTOR KEY VECTOR |
Inmemory aggregation |
|
| RECURSIVE ITERATION | Unknown | |
| WINDOW CONSOLIDATOR | WINDOW CONSOLIDATOR BUFFER for parallel execution of analyrical WINDOW aggregation functions
Example
SQL> explain plan for select/*+ parallel(t 4) PQ_DISTRIBUTE_WINDOW(2) */ count(*) over(partition by owner) cnt,owner from xt_test t;
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------
Plan hash value: 3410952625
---------------------------------------------------------------------------------------------------
| Id | Operation |Name |Rows |Cost |Pstart|Pstop| TQ |IN-OUT| PQ Distrib |
---------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | |91060| 124| | | | | |
| 1 | PX COORDINATOR | | | | | | | | |
| 2 | PX SEND QC (RANDOM) |:TQ10001|91060| 124| | | Q1,01 | P->S | QC (RAND) |
| 3 | WINDOW CONSOLIDATOR BUFFER| |91060| 124| | | Q1,01 | PCWP | |
| 4 | PX RECEIVE | |91060| 124| | | Q1,01 | PCWP | |
| 5 | PX SEND HASH |:TQ10000|91060| 124| | | Q1,00 | P->P | HASH |
| 6 | WINDOW SORT | |91060| 124| | | Q1,00 | PCWP | |
| 7 | PX BLOCK ITERATOR | |91060| 122| 1 | 4| Q1,00 | PCWC | |
| 8 | TABLE ACCESS FULL |XT_TEST |91060| 122| 1 | 4| Q1,00 | PCWP | |
---------------------------------------------------------------------------------------------------
Note
-----
- Degree of Parallelism is 4 because of table property
[collapse]
|
|
| DETECT END | Unknown | |
| DM EXP MAX AGGR | Unknown | |
| DM EXP MAX PAR | Unknown | |
| FAULT-TOLERANCE BUFFER | The fault-tolerance for parallel statement. Patent #US8572051: Making parallel execution of structured query language statements fault-tolerant |
|
See also:
- Randolf Geist “12c New Optimizer Features”
- Randolf Geist “Parallel Execution 12c New Features Overview”
HINTS:
| PATH | HINT_CLASS | HINT_NAME | VERSION | VERSION_OUTLINE |
|---|---|---|---|---|
| ALL | WITH_PLSQL | WITH_PLSQL | 12.1.0.1 | |
| ALL -> ANSI_REARCH | ANSI_REARCH 1 | ANSI_REARCH NO_ANSI_REARCH |
12.1.0.2 | 12.1.0.2 |
| ALL -> EXECUTION | BATCH_TABLE_ACCESS_BY_ROWID 2 | BATCH_TABLE_ACCESS_BY_ROWID NO_BATCH_TABLE_ACCESS_BY_ROWID |
12.1.0.1 | 12.1.0.1 |
| INMEMORY | INMEMORY NO_INMEMORY |
12.1.0.2 | 12.1.0.2 | |
| INMEMORY_PRUNING | INMEMORY_PRUNING NO_INMEMORY_PRUNING |
12.1.0.2 | 12.1.0.2 | |
| ALL -> COMPILATION -> ZONEMAP | ZONEMAP | ZONEMAP NO_ZONEMAP |
12.1.0.1 | 12.1.0.1 |
| ALL -> COMPILATION -> DATA_SECURITY_REWRITE | DATA_SECURITY_REWRITE_LIMIT | DATA_SECURITY_REWRITE_LIMIT NO_DATA_SECURITY_REWRITE |
12.1.0.1 | 12.1.0.1 |
| ALL -> COMPILATION -> CBO | CLUSTER_BY_ROWID | CLUSTER_BY_ROWID CLUSTER_BY_ROWID |
12.1.0.1(11.2.0.4) | 12.1.0.1 |
| ALL -> COMPILATION -> CBO -> ACCESS_PATH -> BITMAP_TREE | BITMAP_AND | BITMAP_AND | 12.1.0.1 | 12.1.0.1 |
| ALL -> COMPILATION -> CBO -> ADAPTIVE_PLAN | ADAPTIVE_PLAN | ADAPTIVE_PLAN NO_ADAPTIVE_PLAN |
12.1.0.2 | 12.1.0.2 |
| ALL -> COMPILATION -> CBO -> AUTO_REOPT | AUTO_REOPTIMIZE 2 | AUTO_REOPTIMIZE NO_AUTO_REOPTIMIZE |
12.1.0.1 | |
| ALL -> COMPILATION -> CBO -> JOIN_METHOD | ANTIJOIN | CUBE_AJ | 12.1.0.1 | 12.1.0.1 |
| SEMIJOIN | CUBE_SJ | 12.1.0.1 | 12.1.0.1 | |
| ALL -> COMPILATION -> CBO -> JOIN_METHOD -> USE_CUBE | JOIN | USE_CUBE NO_USE_CUBE |
12.1.0.1 | 12.1.0.1 |
| ALL -> COMPILATION -> CBO -> PARTIAL_JOIN | PARTIAL_JOIN | PARTIAL_JOIN NO_PARTIAL_JOIN |
12.1.0.1 | 12.1.0.1 |
| ALL -> COMPILATION -> CBO -> PARTITION | USE_HIDDEN_PARTITIONS | USE_HIDDEN_PARTITIONS | 12.1.0.1 | |
| ALL -> COMPILATION -> CBO -> PQ | PARTIAL_ROLLUP_PUSHDOWN | PARTIAL_ROLLUP_PUSHDOWN NO_PARTIAL_ROLLUP_PUSHDOWN |
12.1.0.1 | 12.1.0.1 |
| PQ_CONCURRENT_UNION | PQ_CONCURRENT_UNION NO_PQ_CONCURRENT_UNION |
12.1.0.1 | 12.1.0.1 | |
| PQ_DISTRIBUTE_WINDOW | PQ_DISTRIBUTE_WINDOW | 12.1.0.1 | 12.1.0.1 | |
| PQ_FILTER | PQ_FILTER | 12.1.0.1 | 12.1.0.1 | |
| PQ_SKEW | PQ_SKEW NO_PQ_SKEW |
12.1.0.1 | 12.1.0.1 | |
| PX_FAULT_TOLERANCE | PX_FAULT_TOLERANCE NO_PX_FAULT_TOLERANCE |
12.1.0.1 | 12.1.0.1 | |
| ALL -> COMPILATION -> CBO -> PQ -> PQ_REPLICATE | PQ_REPLICATE | PQ_REPLICATE NO_PQ_REPLICATE |
12.1.0.1 | 12.1.0.1 |
| ALL -> COMPILATION -> CBO -> STATS -> DBMS_STATS | GATHER_OPTIMIZER_STATISTICS | GATHER_OPTIMIZER_STATISTICS NO_GATHER_OPTIMIZER_STATISTICS |
12.1.0.1 | |
| ALL -> COMPILATION -> TRANSFORMATION | ELIM_GROUPBY ? | ELIM_GROUPBY NO_ELIM_GROUPBY |
||
|
ALL -> COMPILATION -> CBO -> CBQT -> VECTOR_AGG and ALL -> COMPILATION -> TRANSFORMATION -> CBQT -> VECTOR_AGG |
USE_VECTOR_AGGREGATION | USE_VECTOR_AGGREGATION NO_USE_VECTOR_AGGREGATION |
12.1.0.2 | 12.1.0.2 |
| VECTOR_TRANSFORM | VECTOR_TRANSFORM NO_VECTOR_TRANSFORM |
12.1.0.2 | 12.1.0.2 | |
| VECTOR_TRANSFORM_DIMS | VECTOR_TRANSFORM_DIMS NO_VECTOR_TRANSFORM_DIMS |
12.1.0.2 | 12.1.0.2 | |
| VECTOR_TRANSFORM_FACT | VECTOR_TRANSFORM_FACT NO_VECTOR_TRANSFORM_FACT |
12.1.0.2 | 12.1.0.2 | |
| ALL -> COMPILATION -> TRANSFORMATION -> HEURISTIC -> DECORRELATE | DECORRELATE | DECORRELATE NO_DECORRELATE |
12.1.0.1 | 12.1.0.1 |