Oracle SQL
  • LICENSE

12c: New SQL PLAN OPERATIONS and HINTS

Posted on July 8, 2015 by Sayan Malakshinov Posted in 12c, CBO, hints, oracle

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

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]
  • 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

-- 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
  • 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

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
  • PX_FAULT_TOLERANCE / NO_PX_FAULT_TOLERANCE hints


See also:

  1. Randolf Geist “12c New Optimizer Features”
  2. 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

See also:
Fuyuncat(Wei Huang) – “Oracle 12c new SQL Hints”

12c cbo
« RESULT_CACHE: run-time dependency tracking
Very simple oracle package for HTTPS and HTTP »
photo Sayan Malakshinov

Oracle ACE Pro Oracle ACE Pro

DEVVYOracle Database Developer Choice Award winner

Oracle performance tuning expert

UK / Cambridge

LinkedIn   Twitter
sayan@orasql.org

Recent Posts

  • CBO and Partial indexing
  • Slow index access “COL=:N” where :N is NULL
  • Where does the commit or rollback happen in PL/SQL code?
  • :1 and SP2-0553: Illegal variable name “1”.
  • ORA exceptions that can’t be caught by exception handler

Recent Comments

  • Oracle SGA 값을 증가 시킬 때 발생 장애 원인 – DBA의 정석 on Example of controlling “direct path reads” decision through SQL profile hints (index_stats/table_stats)
  • Oracle SQL | Oracle diagnostic events — Cheat sheet on Where does the commit or rollback happen in PL/SQL code?
  • Functions & Subqueries | Oracle Scratchpad on Deterministic function vs scalar subquery caching. Part 3
  • Materialized views state turns into compilation_error after refresh - kranar.top - Answering users questions... on Friday prank: select from join join join
  • Exadata Catalogue | Oracle Scratchpad on When bloggers get it wrong – part 1
  • Exadata Catalogue | Oracle Scratchpad on Serial Scans failing to offload
  • lateral join – decorrelation gone wrong – svenweller on Lateral view decorrelation(VW_DCL) causes wrong results with rownum
  • 255 column catalogue | Oracle Scratchpad on Intra-block row chaining optimization in 12.2
  • 255 column catalogue | Oracle Scratchpad on row pieces, 255 columns, intra-block row chaining in details
  • opt_estimate catalogue | Oracle Scratchpad on Correct syntax for the table_stats hint

Blogroll

  • Alex Fatkulin
  • Alexander Anokhin
  • Andrey Nikolaev
  • Charles Hooper
  • Christian Antognini
  • Coskan Gundogar
  • David Fitzjarrell
  • Igor Usoltsev
  • Jonathan Lewis
  • Karl Arao
  • Mark Bobak
  • Martin Bach
  • Martin Berger
  • Neil Chandler
  • Randolf Geist
  • Richard Foote
  • Riyaj Shamsudeen
  • Tanel Poder
  • Timur Akhmadeev
  • Valentin Nikotin

Meta

  • Log in
  • Entries feed
  • Comments feed
  • WordPress.org
©Sayan Malakshinov. Oracle SQL