Oracle SQL | 12c: New SQL PLAN OPERATIONS and HINTS

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	JSON in Oracle Database
XMLTABLE EVALUATION	This is new name for “COLLECTION ITERATOR PICKLER FETCH [XQSEQUENCEFROMXMLTYPE]”. XPATH EVALUATION still exists.	XMLTABLE Performing SQL Operations on XMLType Fragments Using XMLTABLE
MATCH RECOGNIZE	New feature “PATTERN MATCHING”	SQL for Pattern Matching
STATISTICS COLLECTOR	Optimizer statistics collector	How Adaptive Plans Work ADAPTIVE_PLAN hint NO_ADAPTIVE_PLAN hint Christian Antognini “STATISTICS COLLECTOR Row Source Operation and LOBs”
OPTIMIZER STATISTICS GATHERING	Automatic Optimizer statistics gathering during the following types of bulk loads: CREATE TABLE … AS SELECT INSERT INTO … SELECT into an empty table using a direct-path insert	GATHER_OPTIMIZER_STATISTICS hint NO_GATHER_OPTIMIZER_STATISTICS hint
CUBE JOIN	Joining Cubes to Tables and Views	USE_CUBE Hint NO_USE_CUBE Hint Joining Cubes to Tables and Views CUBE_AJ/CUBE_SJ hints
EXPRESSION EVALUATION	Each parallel slave executes scalar correllated subqueries from SELECT-list.	Jonathan Lewis “Parallel Query” Randolf Geist “Parallel Execution 12c New Features Overview”
parallel “FILTER”	Each parallel slave executes own FILTER operation Example [sourcecode language=”SQL” highlight=”19″] 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) [/sourcecode] [collapse]	Randolf Geist “Parallel Execution 12c New Features Overview” PQ_FILTER hint
PX SELECTOR	Execution of the serial plan parts in the one of the parallel slaves	Randolf Geist “12c Parallel Execution New Features: PX SELECTOR” Concurrent Execution of Union All PQ_CONCURRENT_UNION Hint NO_PQ_CONCURRENT_UNION Hint
PX SEND 1 SLAVE	Execution of the serial plan parts in the one of the parallel slaves(single DFO tree)	Randolf Geist “12c Parallel Execution New Features: 1 SLAVE distribution”
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.	Adaptive Plans: Parallel Distribution Methods Randolf Geist “Parallel Execution Skew – 12c Hybrid Hash Distribution With Skew Detection” Randolf Geist “12c Hybrid Hash Distribution with Skew Detection / Handling” PQ_SKEW / NO_PQ_SKEW hints
PQ_DISTRIBUTE_WINDOW	In addition to “PX SEND” HASH-distribution for WINDOW functions, “PX SEND RANGE” was added Example [sourcecode language=”SQL” highlight=”7,9,17″] — 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 /[/sourcecode] [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	In-Memory Aggregation V$KEY_VECTOR USE_VECTOR_AGGREGATION / NO_USE_VECTOR_AGGREGATION hints VECTOR_TRANSFORM / NO_VECTOR_TRANSFORM hints VECTOR_TRANSFORM_DIMS / NO_VECTOR_TRANSFORM_DIMS hints VECTOR_TRANSFORM_FACT / NO_VECTOR_TRANSFORM_FACT hints
RECURSIVE ITERATION	Unknown
WINDOW CONSOLIDATOR	WINDOW CONSOLIDATOR BUFFER for parallel execution of analyrical WINDOW aggregation functions Example [sourcecode language=”SQL” highlight=”12″] 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 [/sourcecode] [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	PX_FAULT_TOLERANCE / NO_PX_FAULT_TOLERANCE hints

See also:

HINTS:

PATH	HINT_CLASS	HINT_NAME	VERSION	VERSION_OUTLINE
ALL	WITH_PLSQL	WITH_PLSQL	12.1.0.1
ALL -> ANSI_REARCH	ANSI_REARCH ¹	ANSI_REARCH NO_ANSI_REARCH	12.1.0.2	12.1.0.2
ALL -> EXECUTION	BATCH_TABLE_ACCESS_BY_ROWID ²	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 ²	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
ALL -> COMPILATION -> CBO -> JOIN_METHOD	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