Search notes:

Oracle: SQL Statement Execution Plan Operations

An SQL execution plan consists of a series of tree-like nested operations (aka row sources).
There are two types of operations:
Operation Option nof inputs Comment
? AND-EQUAL
BITMAP AND 2 Compare with BITMAP OR
BITMAP CONSTRUCTION
BITMAP CONVERSION TO ROWIDS 1
BITMAP CONVERSION FROM ROWIDS ?
BITMAP CONVERSION COUNT ? Count bits in a bitmap index (select count(*) from tab;, tab having a bitmap index).
BITMAP INDEX FAST FULL SCAN 1 Same as FULL SCAN?
BITMAP INDEX RANGE SCAN ?
BITMAP INDEX SINGLE VALUE 1
BITMAP MERGE 1
BITMAP KEY ITERATION
BITMAP OR 2 - n Compare with BITMAP AND
BUFFER SORT 1 - 2 In spite of the SORT in the name, this operation does not necessarily sort its input, rather it only uses the buffering functionality of a «traditional» sort to store data in the PGA or UGA to reduce full table scans. Compare with SORT (…). See also Sadique's blog
BUFFER SORT (REUSE)
COLLECTION ITERATOR PICKLER FETCH 1 Column object_type in plan_table often set to PROCEDURE and object_owner and object_type set to, for example, dbms_xplan and display.
CONCATENATION 3 - 3 (?) The CONCATENATION operator is functionally equivalent to the UNION-ALL operator and was used in versions prior to 12c for or expansions. See also the use_concat hint.
CONNECT BY NO FILTERING WITH START-WITH 1
CONNECT BY WITH FILTERING 2
CONNECT BY WITH FILTERING (UNIQUE) 2
CONNECT BY WITHOUT FILTERING 1 - 2
CONNECT BY PUMP 1 Compare with RECURSIVE WITH PUMP
COUNT 1 Created by rownum.
COUNT STOPKEY 1 rownum plus criterion (in outer query?)
CREATE TABLE STATEMENT 1 - (?) One of the root operations.
CREATE INDEX STATEMENT 1 One of the root operations. For non-parallel execution, the (only) child operator is INDEX BUILD.
DATA LINK FULL FULL 1 Compare with EXTENDED DATA LINK FULL.
DELETE 1
DELETE STATEMENT 1 One of the root operations. The (only) child operation is always DELETE.
DOMAIN INDEX
EXPRESSION EVALUATION
EXTENDED DATA LINK FULL 1 Compare with DATA LINK
EXTERNAL TABLE ACCESS FULL ? Used to select from an external table.
FAST DUAL 1 Used when selecting an expression that does not involve DUMMY from DUAL.
FILTER 1 - n Only pass on rows that match criteria. Compare with the plan operation VIEW and the plan table column filter_predicates of the plan_table.
FIRST ROW 1
FIXED TABLE FIXED INDEX 1
FIXED TABLE FULL 1
FOR UPDATE 1
HASH GROUP BY 1
HASH GROUP BY PIVOT 1
HASH UNIQUE 1
HASH JOIN ANTI 2
HASH JOIN ANTI NA 2
HASH JOIN ANTI SNA 2
HASH JOIN BUFFERED 2
HASH JOIN FULL OUTER 2
HASH JOIN OUTER 2
HASH JOIN OUTER BUFFERED 2
HASH JOIN RIGHT ANTI 2
HASH JOIN RIGHT ANTI NA 2
HASH JOIN RIGHT OUTER 2
HASH JOIN RIGHT OUTER BUFFERED 2
HASH JOIN RIGHT SEMI 2
HASH JOIN RIGHT SEMI BUFFERED 2
HASH JOIN SEMI 2 HASH JOIN SEMI seems to be the (left) counterpart to HASH JOIN RIGHT SEMI (so there is no HASH JOIN LEFT SEMI).
HASH JOIN 2 - 3(?)
INDEX FAST FULL SCAN 1 Similar to TABLE ACCESS (FULL) but get requested values (attributes/columns) from an index rather than the table. Thus, the index is not used to locate rows. Compare with INDEX FULL SCAN.
INDEX FULL SCAN 1 Compare with INDEX FAST FULL SCAN
INDEX FULL SCAN DESCENDING 1
INDEX FULL SCAN (MIN/MAX) 1
INDEX RANGE SCAN 1 - 2
INDEX RANGE SCAN DESCENDING 1
INDEX RANGE SCAN (MIN/MAX) 1
INDEX SAMPLE FAST FULL SCAN ?
INDEX SKIP SCAN 1
INDEX UNIQUE SCAN 1
INDEX MAINTENANCE 1
INLIST ITERATOR 1 Iterate over each element in a where x in (elem_1, elem_2, …, elem_n) predicate and execute the child plan operation
INSERT STATEMENT 1 One of the root operations.
INTO ? Used in conjuction with the MULTI-TABLE INSERT operator for insert all (multi table insert) statements
JOIN FILTER CREATE 1 Compare with PART JOIN FILTER (CREATE)
JOIN FILTER USE 1
JSONTABLE EVALUATION ? Apparently used to execute statements with json_table() expression. Compare with XMLTABLE EVALUATION
LOAD AS SELECT 1
LOAD AS SELECT (CURSOR DURATION MEMORY) 1 - ? Always a child of TEMP TABLE TRANSFORMATION. Related to the star transformation query transformation?
LOAD AS SELECT (HYBRID TSM/HWMB) 1 TSM = Temp Segment Merge, HWMB = High Water Mark Brokering
LOAD AS SELECT (TEMP SEGMENT MERGE) ?
LOAD TABLE CONVENTIONAL 1 - n
MATCH RECOGNIZE BUFFER DETERMINISTIC FINITE AUTOMATON
MATCH RECOGNIZE SORT DETERMINISTIC FINITE AUTOMATON
MAT_VIEW ACCESS FULL
MAT_VIEW ACCESS BY INDEX ROWID
MAT_VIEW ACCESS BY INDEX ROWID BATCHED
MAT_VIEW ACCESS BY USER ROWID
MAT_VIEW ACCESS FULL
MAT_VIEW REWRITE ACCESS FULL 1 See Connor McDonald's youtube video Become a Materialized View Super Hero!
MERGE 1
MERGE JOIN ANTI 2
MERGE JOIN ANTI NA 2
MERGE JOIN CARTESIAN 2
MERGE JOIN OUTER 2
MERGE JOIN SEMI 2
MERGE JOIN 2
MERGE STATEMENT 1 One of the root operations. For non-parallel execution, the only child operator is MERGE.
MINUS 2
MULTI-TABLE INSERT ? Used in conjuction with the INTO operator for insert all (multi table insert) statements
NESTED LOOPS ANTI 2
NESTED LOOPS ANTI SNA 2
NESTED LOOPS OUTER 2
NESTED LOOPS SEMI 2
NESTED LOOPS 1 - 3(?)
OPTIMIZER STATISTICS GATHERING 1 See Online statistics gathering. Compare with STATISTICS COLLECTOR
PARTITION COMBINED ITERATOR ?
PARTITION LIST ALL 1
PARTITION LIST INLIST ?
PARTITION LIST ITERATOR ?
PARTITION LIST SINGLE 1 Access one partition only, see this example.
PARTITION RANGE ALL 1
PARTITION RANGE EMPTY 1 (?)
PARTITION RANGE INLIST 1
PARTITION RANGE ITERATOR 1
PARTITION RANGE JOIN-FILTER 1
PARTITION RANGE OR 1
PARTITION RANGE SINGLE 1
PARTITION REFERENCE ALL 1
PARTITION REFERENCE INLIST 1
PART JOIN FILTER CREATE 1 Compare with the JOIN FILTER CREATE row source.
PX BLOCK ITERATOR 1
PX BLOCK ITERATOR ADAPTIVE 1
PX COORDINATOR 1
PX COORDINATOR FORCED SERIAL ? Statement execution was started parallel, but Oracle then detected during optimization phase that the statement cannot be executed in parallel (for example because of involving a PL/SQL function that is not parallel enabled).
PX PARTITION LIST ALL 1
PX PARTITION RANGE
PX PARTITION RANGE ALL 1
PX PARTITION RANGE ITERATOR 1
PX RECEIVE 1
PX SELECTOR 1
PX SEND 1 SLAVE 1
PX SEND BROADCAST 1
PX SEND HASH 1
PX SEND HASH (BLOCK ADDRESS) 1
PX SEND HASH (NULL RANDOM) 1
PX SEND HASH (RANDOM) 1
PX SEND HYBRID (ROWID PKEY) 1
PX SEND HYBRID HASH 1
PX SEND HYBRID HASH (SKEW) 1
PX SEND QC (ORDER) 1
PX SEND QC (RANDOM) 1
PX SEND RANDOM LOCAL 1
PX SEND RANGE 1
PX SEND ROUND-ROBIN 1
RECURSIVE WITH PUMP ? ? Compare with CONNECT BY PUMP
REMOTE ? ?
RESULT CACHE 1
SELECT STATEMENT 1 - n One of the root operations.
SELECT STATEMENT REMOTE
SEQUENCE 1
SORT AGGREGATE 1 - 2 Used when aggregate functions are applied to an entire table/subquery without group by. Compare with BUFFER (SORT).
SORT AGGREGATE APPROX 1 AGGREGATE APPROX is used, for example, in approx_count_distinct.
SORT CREATE INDEX ?
SORT GROUP BY 1
SORT GROUP BY NOSORT 1
SORT GROUP BY PIVOT 1
SORT JOIN 1 Sort this operation's input before passing it to a MERGE JOIN operation.
SORT ORDER BY 1
SORT ORDER BY STOPKEY 1
SORT UNIQUE 1
SORT UNIQUE NOSORT 1 Removes duplicates from a sorted «stream of data».
SQL MODEL ORDERED
SQL MODEL ORDERED FAST
STATISTICS COLLECTOR 1 Used in conjunction with adaptive query optimization when the optimizer creates an adaptive plan. Compare with OPTIMIZER STATISTICS GATHERING
TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED 1
TABLE ACCESS BY INDEX ROWID 1 - 2
TABLE ACCESS BY INDEX ROWID BATCHED 1 - 2 BATCHED indicates that the step retrieves a few rowids from an index and then tries to access the corresponding rows in order of the blocks so that the number of times that a block is read is minimized.
TABLE ACCESS BY LOCAL INDEX ROWID 1
TABLE ACCESS BY LOCAL INDEX ROWID BATCHED 1
TABLE ACCESS BY USER ROWID 1
TABLE ACCESS CLUSTER 1
TABLE ACCESS FULL 1
TABLE ACCESS HASH 1(?)
TABLE ACCESS SAMPLE 1 SAMPLE select statement.
TABLE ACCESS SAMPLE BY ROWID RANGE 1
TEMP TABLE TRANSFORMATION 3 Indicates that a «cursor-duration» temp table was used to execute a query
TRANSPOSE n
UNION-ALL 1 - n
UNION ALL PARTITION
UNION ALL (RECURSIVE WITH) BREADTH FIRST ?
UNION ALL PUSHED PREDICATE 2(?) - n
UNPIVOT
UPDATE 1 - n
UPDATE STATEMENT 1 One of the root operations. For non-parallel execution, the only child operator is UPDATE.
VIEW 1 A passive operation: it passes its resultset to the parent operation. Jonathan Lewis says the VIEW operator identifies a separately optimized query block (in a possibly transformed SQL statement) whose result set might be completely built in memory before passing it to the parent operators. Compare with the plan operation FILTER.
VIEW PUSHED PREDICATE 1 Compare with with VIEW
WINDOW BUFFER 1 Used in over() clauses in an analytic function when the clause does not contain a partition by or order by.
WINDOW CHILD PUSHED RANK
WINDOW NOSORT ?
WINDOW NOSORT STOPKEY ?
WINDOW SORT 1 Used in over() in an analytic function clauses when the clause contains a partition by or order by.
WINDOW SORT PUSHED RANK 1
WINDOW CONSOLIDATOR BUFFER ?
WINDOW (IN SQL MODEL) ORDERED
XMLTABLE EVALUATION 1 Related to the SQL function XMLTable? Compare with JSONTABLE EVALUATION.
XPATH EVALUATION
This table was produced with raw data that was selected with the following select statement: 
with sp as (
   select
      p.operation  p_operation,
      p.options    p_options,
      c.operation  c_operation,
      c.options    c_options,
      p.id         p_id,
      p.sql_id,
      p.child_number,
      c.sql_id c_sql_id, c.child_number c_child_number, c.parent_id c_parent_id, 
      rank() over (partition by c.sql_id, c.child_number, c.parent_id order by c.position) r_child
   from
      v$sql_plan p                                           left join
      v$sql_plan c   on p.sql_id       = c.sql_id       and
                        p.child_number = c.child_number and
                        p.id           = c.parent_id
   /*                     
   where
     not (
        ( p.operation = 'FILTER'                     and p.options is null) or
        ( p.operation = 'UNION-ALL'                  and p.options is null) or
        ( p.operation = 'UNION ALL PUSHED PREDICATE' and p.options is null) or
        ( p.operation = 'SELECT STATEMENT'           and p.options is null) or
        ( p.operation = 'LOAD AS SELECT'             and p.options = '(CURSOR DURATION MEMORY)') or
        ( p.operation = 'LOAD TABLE CONVENTIONAL'    and p.options is null) or
        ( p.operation = 'CREATE TABLE STATEMENT'     and p.options is null)
     )
   */
),
gr as (
   select
      p_operation,
      p_options,
   -- p_id,
   -- sql_id,
      child_number,
      max(r_child) max_r_child
   from
      sp
   group by
      p_operation,
      p_options,
      p_id,
      sql_id,
      child_number
)
select
   count(*) over (partition by p_operation, p_options) cnt,
   p_operation,
   p_options,
   max_r_child
from
   gr
group by
   p_operation,
   p_options,
   max_r_child
order by
   p_operation,
   p_options;

Root operators

There are seven root operators for execution plans
Usually, these operators have no options (but see also SELECT STATEMENT REMOTE).

Matrix Operation/Object type

The following matrix show which object types are accessed by which plan operations.
TABLE CLUSTER TABLE (TEMP) INDEX (CLUSTER) INDEX INDEX (UNIQUE) INDEX (BITMAP) VIEW VIEW (FIXED) PROCEDURE TABLE (FIXED) SEQUENCE REMOTE
TABLE ACCESS FULL X X X
TABLE ACCESS BY GLOBAL INDEX ROWID X
TABLE ACCESS BY GLOBAL INDEX ROWID BATCHED X
TABLE ACCESS BY INDEX ROWID X X X
TABLE ACCESS BY INDEX ROWID BATCHED X X X
TABLE ACCESS BY LOCAL INDEX ROWID X
TABLE ACCESS BY LOCAL INDEX ROWID BATCHED X
TABLE ACCESS BY USER ROWID X X
TABLE ACCESS SAMPLE X
TABLE ACCESS SAMPLE BY ROWID RANGE X
TABLE ACCESS CLUSTER X
INDEX FAST FULL SCAN X X
INDEX FULL SCAN X X X
INDEX FULL SCAN (MIN/MAX) X X
INDEX FULL SCAN DESCENDING X
INDEX RANGE SCAN X X X
INDEX RANGE SCAN (MIN/MAX) X X
INDEX RANGE SCAN DESCENDING X X
INDEX SAMPLE FAST FULL SCAN X X
INDEX SKIP SCAN X X X
INDEX UNIQUE SCAN X X
BITMAP INDEX FAST FULL SCAN X
BITMAP INDEX FULL SCAN X
BITMAP INDEX RANGE SCAN X
BITMAP INDEX SAMPLE FAST FULL SCAN X
BITMAP INDEX SINGLE VALUE X
VIEW PUSHED PREDICATE X
VIEW X X
COLLECTION ITERATOR PICKLER FETCH X
FIXED TABLE FIXED INDEX X
FIXED TABLE FULL X
REMOTE X
SEQUENCE X
This matrix was created on a Oracle version 19.22 database with the following statement: 
select * from (
    select
       case when count(*) > 0 then 'X' end flg,
       p.operation,
       p.options,  
       p.object_type
    from
       ( select operation, options, object_type from  dba_hist_sql_plan union all
         select operation, options, object_type from         v$sql_plan
       ) p
    where
       p.object_type is not null
    group by
       p.operation,
       p.options,  
       p.object_type
)  
pivot (
    any_value(flg) flg    for
    object_type in (
       'TABLE'           as "TABLE",
       'CLUSTER'         as "CLUSTER",
       'TABLE (TEMP)'    as "TABLE (TEMP)",
       'INDEX (CLUSTER)' as "INDEX (CLUSTER)",
       'INDEX'           as "INDEX",
       'INDEX (UNIQUE)'  as "INDEX (UNIQUE)",
       'INDEX (BITMAP)'  as "INDEX (BITMAP)",
       'VIEW'            as "VIEW",
       'VIEW (FIXED)'    as "VIEW (FIXED)",
       'PROCEDURE'       as "PROCEDURE",
       'TABLE (FIXED)'   as "TABLE (FIXED)",
       'SEQUENCE'        as "SEQUENCE",
       'REMOTE'          as "REMOTE"
    )
)
order by
   case when operation = 'TABLE ACCESS' then
        case when options = 'FULL'      then    '01'
             when options = 'CLUSTER'   then    '03'
        else                                    '02'
        end
        when operation = 'INDEX'        then    '10'
        when operation = 'BITMAP INDEX' then    '20'
        when operation like 'VIEW%'     then    '30'
   else operation end,
   options;

Find parent child operation relations

From v$sql_plan: 
select
   count(*) cnt,
   p .operation, p .options,
   c1.operation, c1.options,
   c2.operation, c2.options,
   c3.operation, c3.options
from
   v$sql_plan    p                                                                                                           left join
   v$sql_plan    c1 on p.sql_id = c1.sql_id and p.child_number = c1.child_number and p.id = c1.parent_id and c1.position = 1 left join
   v$sql_plan    c2 on p.sql_id = c2.sql_id and p.child_number = c2.child_number and p.id = c2.parent_id and c2.position = 2 left join
   v$sql_plan    c3 on p.sql_id = c3.sql_id and p.child_number = c3.child_number and p.id = c3.parent_id and c3.position = 3
where
   p.operation = 'PX BLOCK'
group by
   p .operation, p .options,
   c1.operation, c1.options,
   c2.operation, c2.options,
   c3.operation, c3.options
order by
   p .operation, p .options,
   c1.operation, c1.options,
   c2.operation, c2.options,
   c3.operation, c3.options
;
Same idea, but from dba_hist_sql_plan: 
select
   count(*) cnt,
   p .operation, p .options,
   c1.operation, c1.options,
   c2.operation, c2.options,
   c3.operation, c3.options
from
   dba_hist_sql_plan    p                                                                                                                                      left join
   dba_hist_sql_plan    c1 on p.dbid = c1.dbid and p.plan_hash_value = c1.plan_hash_value and p.sql_id = c1.sql_id and p.id = c1.parent_id and c1.position = 1 left join
   dba_hist_sql_plan    c2 on p.dbid = c2.dbid and p.plan_hash_value = c2.plan_hash_value and p.sql_id = c2.sql_id and p.id = c2.parent_id and c2.position = 2 left join
   dba_hist_sql_plan    c3 on p.dbid = c3.dbid and p.plan_hash_value = c3.plan_hash_value and p.sql_id = c3.sql_id and p.id = c3.parent_id and c3.position = 3
where
   p.operation = 'SELECT STATEMENT'  -- and p.options = 'BY INDEX ROWID BATCHED'
group by
   p .operation, p .options,
   c1.operation, c1.options,
   c2.operation, c2.options,
   c3.operation, c3.options
order by
   p .operation, p .options,
   c1.operation, c1.options,
   c2.operation, c2.options,
   c3.operation, c3.options
;

Find entire plans where a given operation is present

with s as (
   select distinct
      sql_id,
      child_number
   from
      v$sql_plan
   where
      operation = 'VIEW'
)
select
    lpad(' ', 2*p.depth) || p.operation || ' ' || p.options,
    p.object_name,
    p.object_alias,
    p.object_type,
    t.sql_text,
    count(*) over (partition by s.sql_id, s.child_number) cnt
from
    s                                                 join
    v$sql_plan p on s.sql_id       = p.sql_id and
                    s.child_number = p.child_number  join
    v$sqlarea  t on t.sql_id       = p.sql_id
order by
    cnt,
    s.sql_id,
    s.child_number,
    p.id;

See also

v$sql_plan_statistics provides actual (not estimated as in v$sql_plan) statistics for every plan operation of an executed SQL statement.
v$sql_plan_monitor reports those actual data even broken down to a single execution of an SQL statement.
v$sql_workarea shows some information about plan operators, for example how much memory the needed (work area).
This SQL statement creates a matrix of plan operations and columns in plan_table and can be used to which operators fill which column.
SQL statement execution plan
Row source statistics
An execution plan operator may send multiple rows at once to its parent. In order to force the plan operators to send one row at at a time to their parents, the init parameter _rowsets_enabled can be set to false.

Index