Oracle Explain Plan

Oracle Explain Plan Version 11.2.0.3

General Information

In many of the Explain Plan reports included below non-critical columns such as "Time" have been removed to fit the website format.

Data Dictionary Objects

PLAN_TABLE$	V$SQL_PLAN_STATISTICS	V$SQL_PLAN_STATISTICS_ALL
V$SQL_PLAN

Preparation

Create the plan table if it does not already exist

SQL> conn / as sysdba

SQL> @?\rdbms\admin\catplan.sql

Create test data if not already done

Run the script servers.sql downloaded by clicking here.

@c:\test\servers.sql

Gather statistics for the CBO

conn uwclass/uwclass

exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);

Generation Syntax

Explain Plan Syntax

EXPLAIN PLAN
[SET statement_id = <statement_identifier>]
[INTO <table_name>]
FOR
<SQL statement here>;

Report Syntax

Explain Plan Analysis Using DBMS_XPLAN

dbms_xplan.display(
table_name IN VARCHAR2 DEFAULT 'PLAN_TABLE',
statement_id IN VARCHAR2 DEFAULT NULL,
format IN VARCHAR2 DEFAULT 'TYPICAL');

Format choices are:
BASIC ..... displays minimum information
TYPICAL ... displays most relevant information
SERIAL .... like TYPICAL but without parallel information
ALL ....... displays all information

Follow the link to dbms_stats.gather_system_statistics for information on CPU costing.

EXPLAIN PLAN
SET STATEMENT_ID = 'abc'
FOR
SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

set pagesize 25
set linesize 121

-- to display the last plan explained
SELECT * FROM TABLE(dbms_xplan.display);

-- to display a specific plan by name
SELECT * FROM TABLE(dbms_xplan.display('PLAN_TABLE','abc','ALL'));

Using A View To Display The DBMS_XPLAN Output

CREATE OR REPLACE VIEW xpan AS
SELECT * FROM table(dbms_xplan.display);

SELECT * FROM plan_view;

GRANT select ON xplan TO public;

Test Statements

Test Statement # 1

INTERSECT

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              | 141 | 4560 |      4 (75)|
| 1 | INTERSECTION          |              |      |       |            |
| 2 |   SORT UNIQUE NOSORT   |              | 141 |   564 |      1 (0)|
| 3 |    INDEX FULL SCAN     | PK_SERVERS   | 141 |   564 |      1 (0)|
| 4 |   SORT UNIQUE          |              | 999 | 3996 |      3 (0)|
| 5 |    INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |      3 (0)|
--------------------------------------------------------------------------

Test Statement # 2

Simple IN

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
SELECT srvr_id
FROM serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |   11 |    88 |      3 (0)|
| 1 | NESTED LOOPS          |              |   11 |    88 |      3 (0)|
| 2 |   SORT UNIQUE          |              | 999 | 3996 |      3 (0)|
| 3 |    INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |      3 (0)|
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |      0 (0)|
--------------------------------------------------------------------------
4 - access("SRVR_ID"="SRVR_ID")

Test Statement # 3

IN with INNER JOIN

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
SELECT i.srvr_id
FROM serv_inst i, servers s
WHERE i.srvr_id = s.srvr_id);

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------
| Id | Operation                | Name         | Rows | Bytes | Cost (%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT         |              |   11 |   187 |      3 (0)|
| 1 | NESTED LOOPS            |              |   11 |   187 |      3 (0)|
| 2 |   VIEW | VW_NSO_1        |              | 999 | 12987 |      3 (0)|
| 3 |    HASH UNIQUE           |              |   11 | 7992 |            |
| 4 |     NESTED LOOPS SEMI    |              | 999 | 7992 |      3 (0)|
| 5 |      INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |      3 (0)|
|* 6 |      INDEX UNIQUE SCAN   | PK_SERVERS   | 141 |   564 |      0 (0)|
|* 7 |   INDEX UNIQUE SCAN      | PK_SERVERS   |    1 |     4 |      0 (0)|
----------------------------------------------------------------------------
6 - access("I"."SRVR_ID"="S"."SRVR_ID")
7 - access("SRVR_ID"="SRVR_ID")

Test Statement # 4A

Simple INNER JOIN

EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost(%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |   11 |    88 |     3 (0)|
| 1 | HASH UNIQUE           |              |   11 |    88 |     3 (0)|
| 2 |   NESTED LOOPS SEMI    |              | 999 | 7992 |     3 (0)|
| 3 |    INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |     3 (0)|
|* 4 |    INDEX UNIQUE SCAN   | PK_SERVERS   | 141 |   564 |     0 (0)|
-------------------------------------------------------------------------
4 - access("S"."SRVR_ID"="I"."SRVR_ID")

Test Statement # 4B

Simple INNER JOIN with HINT

EXPLAIN PLAN FOR
SELECT /*+ USE_HASH(s,i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |    11 |    88 |      4 (0)|
| 1 | HASH UNIQUE           |              |    11 |    88 |      4 (0)|
|* 2 |   HASH JOIN SEMI       |              |    11 |    88 |      4 (0)|
| 3 |    INDEX FULL SCAN     | PK_SERVERS   |   141 |   564 |      1 (0)|
| 4 |    INDEX FAST FULL SCAN| PK_SERV_INST |   999 | 3996 |      3 (0)|
--------------------------------------------------------------------------
2 - access("S"."SRVR_ID"="I"."SRVR_ID")

Test Statement # C

Simple INNER JOIN with HINT

EXPLAIN PLAN FOR
SELECT /*+ USE_MERGE(s,i) */ DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT        |              |   11 |    88 |      4 (0)|
| 1 | SORT UNIQUE NOSORT     |              |   11 |    88 |      4 (0)|
| 2 |   MERGE JOIN SEMI       |              |   11 |    88 |      4 (0)|
| 3 |    INDEX FULL SCAN      | PK_SERVERS   | 141 |   564 |      1 (0)|
|* 4 |    SORT UNIQUE          |              | 999 | 3996 |      3 (0)|
| 5 |     INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |      3 (0)|
--------------------------------------------------------------------------
4 - access("S"."SRVR_ID"="I"."SRVR_ID")
    filter("S"."SRVR_ID"="I"."SRVR_ID")

Test Statement # 5

NOT IN with MINUS

EXPLAIN PLAN FOR
SELECT DISTINCT srvr_id
FROM servers
WHERE srvr_id NOT IN (
SELECT srvr_id
FROM servers
MINUS
SELECT srvr_id
FROM serv_inst);

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation                  | Name       | Rows | Bytes |Cost(%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT         |              | 140 | 2380 |    5 (0)|
|* 1 | HASH JOIN ANTI          |              | 140 | 2380 |    5 (0)|
| 2 |   INDEX FULL SCAN        | PK_SERVERS   | 141 |   564 |    1 (0)|
| 3 |    VIEW                  | VW_NSO_1     | 141 | 1833 |    4 (0)|
| 4 |    MINUS                 |              |      |       |          |
| 5 |     SORT UNIQUE          |              | 141 |   564 |          |
| 6 |      INDEX FULL SCAN     | PK_SERVERS   | 141 |   564 |    1 (0)|
| 7 |     SORT UNIQUE          |              | 999 | 3996 |          |
| 8 |      INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |    3 (0)|
--------------------------------------------------------------------------
1 - access("SRVR_ID"="SRVR_ID")

Test Statement # 6

EXISTS

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers s
WHERE EXISTS (
SELECT srvr_id
FROM serv_inst i
WHERE s.srvr_id = i.srvr_id);

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |   11 |    88 |      3 (0)|
| 1 | NESTED LOOPS          |              |   11 |    88 |      3 (0)|
| 2 |   SORT UNIQUE          |              | 999 | 3996 |      3 (0)|
| 3 |    INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |      3 (0)|
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |      0 (0)|
--------------------------------------------------------------------------
4 - access("S"."SRVR_ID"="I"."SRVR_ID")

Test Statement # 7

Common Table Expression / WITH Clause

EXPLAIN PLAN FOR
WITH q AS (
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id = i.srvr_id)
SELECT * FROM q;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost(%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT        |              |   11 |   143 |     3 (0)|
| 1 | VIEW                   |              |   11 |   143 |     3 (0)|
| 2 |   HASH UNIQUE           |              |   11 |    88 |     3 (0)|
| 3 |    NESTED LOOPS SEMI    |              | 999 | 7992 |     3 (0)|
| 4 |     INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |     3 (0)|
|* 5 |     INDEX UNIQUE SCAN   | PK_SERVERS   | 141 |   564 |     0 (0)|
--------------------------------------------------------------------------
5 - access("S"."SRVR_ID"="I"."SRVR_ID")

Test Statement # 8

OUTER JOIN

EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id(+) = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost(%CPU)|
-------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |   11 |    88 |     3 (0)|
| 1 | HASH UNIQUE           |              |   11 |    88 |     3 (0)|
| 2 |   NESTED LOOPS OUTER   |              | 999 | 7992 |     3 (0)|
| 3 |    INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |     3 (0)|
|* 4 |    INDEX UNIQUE SCAN   | PK_SERVERS   |    1 |     4 |     0 (0)|
-------------------------------------------------------------------------
4 - access("S"."SRVR_ID"(+)="I"."SRVR_ID")

Test Statement # 9

UNION ALL

EXPLAIN PLAN FOR
SELECT srvr_id
FROM (
SELECT srvr_id, SUM(cnt) SUMCNT
FROM (
    SELECT DISTINCT srvr_id, 1 AS CNT
    FROM servers
    UNION ALL
    SELECT DISTINCT srvr_id, 1
    FROM serv_inst)
GROUP BY srvr_id)
WHERE sumcnt = 2;

SELECT * FROM TABLE(dbms_xplan.display);

----------------------------------------------------------------------------
| Id | Operation                 | Name         | Rows | Bytes | Cost(%CPU)|
----------------------------------------------------------------------------
| 0 | SELECT STATEMENT          |              |    2 |    14 |     4 (0)|
|* 1 | FILTER                   |              |      |       |           |
| 2 |   HASH GROUP BY           |              |    2 |    14 |     4 (0)|
| 3 |    VIEW                   |              | 152 | 1064 |     4 (0)|
| 4 |     UNION-ALL             |              |      |       |           |
| 5 |      INDEX FULL SCAN      | PK_SERVERS   | 141 |   564 |     1 (0)|
| 6 |      HASH UNIQUE          |              |   11 |    44 |     3 (0)|
| 7 |       INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |     3 (0)|
----------------------------------------------------------------------------
1 - filter(SUM("CNT")=2)

Test Statement # 10

Alter the WHERE clause

EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i
WHERE s.srvr_id+0 = i.srvr_id+0;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |   11 |    88 |      4 (0)|
| 1 | HASH UNIQUE           |              |   11 |    88 |      4 (0)|
|* 2 |   HASH JOIN SEMI       |              |   11 |    88 |      4 (0)|
| 3 |    INDEX FULL SCAN     | PK_SERVERS   | 141 |   564 |      1 (0)|
| 4 |    INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |      3 (0)|
--------------------------------------------------------------------------
2 -access("S"."SRVR_ID"+0="I"."SRVR_ID"+0)

Test Statement # 11

Join also a small unnecessary table

EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i, dual d
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

-----------------------------------------------------------------------
| Id | Operation           | Name         | Rows | Bytes | Cost (%CPU)|
-----------------------------------------------------------------------
| 0 | SELECT STATEMENT    |              |   11 |    88 |      5 (0)|
| 1 | HASH UNIQUE         |              |   11 |    88 |      5 (0)|
| 2 | NESTED LOOPS SEMI   |              | 999 | 7992 |      5 (0)|
| 3 | NESTED LOOPS        |              | 999 | 3996 |      5 (0)|
| 4 | FAST DUAL           |              |    1 |       |      2 (0)|
| 5 | INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |      3 (0)|
|* 6 | INDEX UNIQUE SCAN   | PK_SERVERS   | 141 |   564 |      0 (0)|
-----------------------------------------------------------------------
6 - access("S"."SRVR_ID"="I"."SRVR_ID")

Test Statement # 12

Inline Views

EXPLAIN PLAN FOR
SELECT s.srvr_id
FROM
(SELECT DISTINCT srvr_id FROM servers) s,
(SELECT DISTINCT srvr_id FROM serv_inst) i
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------------------
| Id | Operation               | Name         | Rows | Bytes | Cost (%CPU)|
---------------------------------------------------------------------------
| 0 | SELECT STATEMENT        |              |   11 |   187 |      3 (0)|
| 1 | NESTED LOOPS           |              |   11 |   187 |      3 (0)|
| 2 |   VIEW                  |              |   11 |   143 |      3 (0)|
| 3 |    HASH UNIQUE          |              |   11 |    44 |      3 (0)|
| 4 |     INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |      3 (0)|
|* 5 |   INDEX UNIQUE SCAN     | PK_SERVERS   |    1 |     4 |      0 (0)|
---------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
5 - access("SRVR_ID"="I"."SRVR_ID")

Test Statement # 13

Joining a "small" superfluous view

EXPLAIN PLAN FOR
SELECT DISTINCT s.srvr_id
FROM servers s, serv_inst i, user_tables d
WHERE s.srvr_id = i.srvr_id;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------------------------------------
| Id | Operation                                     | Name       | Rows | Bytes|Cost (%CPU)|
---------------------------------------------------------------------------------------------
|   0|SELECT STATEMENT                               |            |   11 | 1320 | 2310   (8)|
|   1| HASH UNIQUE                                   |            |   11 | 1320 | 2310   (8)|
| * 2| HASH JOIN                                    |            | 5768K| 660M| 2151   (1)|
|   3|   INDEX FAST FULL SCAN                        |PK_SERV_INST| 999 | 3996 |    3   (0)|
|   4|   MERGE JOIN CARTESIAN                        |            | 814K|   90M| 2133   (1)|
| * 5|    HASH JOIN OUTER                            |            | 5774 | 631K| 563   (1)|
| * 6|     HASH JOIN RIGHT OUTER                     |            | 5774 | 603K| 492   (1)|
|   7|      INDEX FULL SCAN                          |I_USER2     | 128 | 512 |    1   (0)|
| * 8|      HASH JOIN OUTER                          |            | 5774 | 580K| 491   (1)|
| * 9|       HASH JOIN                               |            | 5774 | 535K| 419   (1)|
| 10|        TABLE ACCESS FULL                      |TS$         |    5 |   15 |    3   (0)|
|* 11|        HASH JOIN RIGHT OUTER                  |            | 5774 | 518K| 416   (1)|
| 12|         TABLE ACCESS FULL                     |SEG$        | 3899 |42889 |   33   (0)|
| 13|         NESTED LOOPS                          |            | 2408 | 190K| 383   (1)|
| 14|          MERGE JOIN CARTESIAN                 |            | 2848 | 141K| 306   (1)|
|* 15|           HASH JOIN                           |            |    1 |   37 |    1 (100)|
|* 16|            FIXED TABLE FULL                   |X$KSPPI     |    1 |   31 |    0   (0)|
|* 17|            FIXED TABLE FULL                   |X$KSPPCV    | 3190 |19140 |    0   (0)|
| 18|           BUFFER SORT                         |            | 2848 |39872 | 305   (0)|
|* 19|            TABLE ACCESS BY INDEX ROWID BATCHED|OBJ$        | 2848 |39872 | 305   (0)|
|* 20|             INDEX SKIP SCAN                   |I_OBJ1      | 2848 |      | 258   (0)|
|* 21|          TABLE ACCESS CLUSTER                 |TAB$        |    1 |   30 |    1   (0)|
|* 22|           INDEX UNIQUE SCAN                   |I_OBJ#      |    1 |      |    0   (0)|
| 23|       INDEX FAST FULL SCAN                    |I_OBJ1      |91136 | 712K|   71   (0)|
| 24|     INDEX FAST FULL SCAN                      |I_OBJ1      |91136 | 445K|   71   (0)|
| 25|    BUFFER SORT                                |            | 141 | 564 | 2062   (1)|
| 26|     INDEX FAST FULL SCAN                      |PK_SERVERS | 141 | 564 |    0   (0)|
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("S"."SRVR_ID"="I"."SRVR_ID")
5 - access("T"."BOBJ#"="CO"."OBJ#"(+))
6 - access("CX"."OWNER#"="CU"."USER#"(+))
8 - access("T"."DATAOBJ#"="CX"."OBJ#"(+))
9 - access("T"."TS#"="TS"."TS#")
11 - access("T"."FILE#"="S"."FILE#"(+) AND "T"."BLOCK#"="S"."BLOCK#"(+) AND "T"."TS#"="S"."TS#"(+))
15 - access("INDX"="INDX")
16 - filter("KSPPINM"='_dml_monitoring_enabled' AND ("CON_ID"=0 OR "CON_ID"=3))
17 - filter("CON_ID"=0 OR "CON_ID"=3)
19 - filter(BITAND("O"."FLAGS",128)=0)
20 - access("O"."OWNER#"=USERENV('SCHEMAID'))
filter("O"."OWNER#"=USERENV('SCHEMAID'))
21 - filter(BITAND("T"."PROPERTY",1)=0)
22 - access("O"."OBJ#"="T"."OBJ#")

Demos

Index Join

EXPLAIN PLAN FOR
SELECT COUNT(*)
FROM svc_merch.suborders so
WHERE so.suborder_status_id NOT IN (7, 14)
AND create_date > SYSDATE-90;

SELECT * FROM TABLE(dbms_xplan.display);

---------------------------------------------------------------------------------
| Id| Operation              | Name                    | Rows| Bytes| Cost(%CPU)|
---------------------------------------------------------------------------------
|  0| SELECT STATEMENT       |                         |   1 |    7 | 251K (1)|
| 1| SORT AGGREGATE        |                         |   1 |    7 |          |
|* 2|   VIEW                 | index$_join$_001        |1566K|   10M| 251K (1)|
|* 3|   HASH JOIN            |                         |     |      |           |
|* 4|    INDEX RANGE SCAN    | SUBORDER_CREATE_DATE_IDX|1566K|   10M| 53330 (1)|
|* 5|    INDEX FAST FULL SCAN| IDX_DATE_STATUS_DC      |1566K|   10M| 190K (1)|
---------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
   2 - filter("CREATE_DATE">SYSDATE@!-90)
   3 - access(ROWID=ROWID)
   4 - access("CREATE_DATE">SYSDATE@!-90)
   5 - filter("SO"."SUBORDER_STATUS_ID"<>14 AND "SO"."SUBORDER_STATUS_ID"<>7)

Transitive Closure

-- compare this in 11.2.0.3

EXPLAIN PLAN FOR
SELECT * FROM servers WHERE srvr_id < 0 AND srvr_id > 10;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------------
| Id | Operation                        | Name    | Rows | Bytes | Cost (%CPU)|
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                 |         | 8168 | 16336 |      21 (5)|
| 1 | COLLECTION ITERATOR PICKLER FETCH| DISPLAY |      |       |            |
-------------------------------------------------------------------------------

-- with this in 12.1.0.1

EXPLAIN PLAN FOR
SELECT * FROM servers WHERE srvr_id < 0 AND srvr_id > 10;

SELECT * FROM TABLE(dbms_xplan.display);

--------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                     |            |    1 |    37 |      0 (0)|
|* 1 | FILTER                              |            |      |       |            |
| 2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SERVERS    |    1 |    37 |      2 (0)|
|* 3 |    INDEX RANGE SCAN                  | PK_SERVERS |    7 |       |      1 (0)|
-------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
3 - access("SRVR_ID">10 AND "SRVR_ID"<0)

-- in both 11.2.0.3 and 12.1.0.1 the autotraceis the same as shown below

set autotrace traceonly explain
SELECT * FROM servers WHERE srvr_id < 0 AND srvr_id > 10;

--------------------------------------------------------------------------------------
| Id | Operation                            | Name       | Rows | Bytes | Cost (%CPU)|
--------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT                     |            |    1 |    37 |      0 (0)|
|* 1 | FILTER                              |            |      |       |            |
| 2 |   TABLE ACCESS BY INDEX ROWID BATCHED| SERVERS    |    1 |    37 |      2 (0)|
|* 3 |    INDEX RANGE SCAN                  | PK_SERVERS |    7 |       |      1 (0)|
--------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
1 - filter(NULL IS NOT NULL)
3 - access("SRVR_ID">10 AND "SRVR_ID"<0)

set autotrace off

Demo with NULLABLE column

CREATE TABLE nullable AS
SELECT * FROM serv_inst;

INSERT INTO nullable
SELECT * FROM serv_inst;

exec dbms_stats.gather_table_stats(USER, 'NULLABLE');

col table_name format a12
col column_name format a14
col low_value format a20
col high_value format a20

SELECT table_name, column_name, nullable, num_distinct, low_value, high_value, density, num_nulls
FROM dba_tab_cols
WHERE table_name in ('SERV_INST','NULLABLE')
ORDER BY 2,1;

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
SELECT srvr_id
FROM serv_inst);

SELECT plan_table_output FROM table(dbms_xplan.display);

-------------------------------------------------------------------------------------
| Id | Operation              | Name         | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT       |              |   11 |    88 |     3   (0)| 00:00:01 |
| 1 | NESTED LOOPS          |              |   11 |    88 |     3   (0)| 00:00:01 |
| 2 |   SORT UNIQUE          |              | 999 | 3996 |     3   (0)| 00:00:01 |
| 3 |    INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |     3   (0)| 00:00:01 |
|* 4 |   INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |     0   (0)| 00:00:01 |
-------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id IN (
SELECT srvr_id
FROM nullable);

SELECT plan_table_output FROM table(dbms_xplan.display);

--------------------------------------------------------------------------------
| Id | Operation           | Name       | Rows | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------
| 0 | SELECT STATEMENT    |            |   11 |    88 |     9   (0)| 00:00:01 |
| 1 | NESTED LOOPS       |            |   11 |    88 |     9   (0)| 00:00:01 |
| 2 |   SORT UNIQUE       |            | 999 | 3996 |     9   (0)| 00:00:01 |
| 3 |    TABLE ACCESS FULL| NULLABLE   | 999 | 3996 |     9   (0)| 00:00:01 |
|* 4 |   INDEX UNIQUE SCAN | PK_SERVERS |    1 |     4 |     0   (0)| 00:00:01 |
--------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id NOT IN (
SELECT srvr_id
FROM serv_inst);

SELECT plan_table_output FROM table(dbms_xplan.display);

------------------------------------------------------------------------------------
| Id | Operation             | Name         | Rows | Bytes | Cost (%CPU)| Time     |
------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT      |              | 130 | 1040 |     4   (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI       |              | 130 | 1040 |     4   (0)| 00:00:01 |
| 2 |   INDEX FULL SCAN     | PK_SERVERS   | 141 |   564 |     1   (0)| 00:00:01 |
| 3 |   INDEX FAST FULL SCAN| PK_SERV_INST | 999 | 3996 |     3   (0)| 00:00:01 |
------------------------------------------------------------------------------------

EXPLAIN PLAN FOR
SELECT srvr_id
FROM servers
WHERE srvr_id NOT IN (
SELECT srvr_id
FROM nullable);

SELECT plan_table_output FROM table(dbms_xplan.display);

-------------------------------------------------------------------------------
| Id | Operation          | Name       | Rows | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------
| 0 | SELECT STATEMENT   |            | 130 | 1040 |    10   (0)| 00:00:01 |
|* 1 | HASH JOIN ANTI NA |            | 130 | 1040 |    10   (0)| 00:00:01 |
| 2 |   INDEX FULL SCAN | PK_SERVERS | 141 |   564 |     1   (0)| 00:00:01 |
| 3 |   TABLE ACCESS FULL| NULLABLE   | 999 | 3996 |     9   (0)| 00:00:01 |
-------------------------------------------------------------------------------

Demo with Parallel Query

CREATE TABLE airparallel AS
SELECT * FROM airplanes;

ALTER TABLE airparallel PARALLEL 2;

EXPLAIN PLAN FOR
SELECT program_id, SUM(line_number)
FROM airparallel
GROUP BY program_id;

SELECT plan_table_output FROM table(dbms_xplan.display);

---------------------------------------------------------------------------------------------
| Id | Operation               | Name       | Rows |Bytes|Cost(%CPU)|TQ   |IN-OUT|PQ Distrib|
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT         |            |    5 | 45 |   186 (3)|     |      |          |
| 1 | PX COORDINATOR          |            |      |     |          |     |      |          |
| 2 |   PX SEND QC (RANDOM)    | :TQ10001   |    5 | 45 |   186 (3)|Q1,01| P->S | QC(RAND) |
| 3 |    HASH GROUP BY         |            |    5 | 45 |   186 (3)|Q1,01| PCWP |          |
| 4 |     PX RECEIVE           |            |    5 | 45 |   186 (3)|Q1,01| PCWP |          |
| 5 |      PX SEND HASH        | :TQ10000   |    5 | 45 |   186 (3)|Q1,00| P->P |     HASH |
| 6 |       HASH GROUP BY      |            |    5 | 45 |   186 (3)|Q1,00| PCWP |          |
| 7 |        PX BLOCK ITERATOR |            | 250K|2197K|   183 (1)|Q1,00| PCWC |          |
| 8 |         TABLE ACCESS FULL| AIRPARALLEL| 250K|2197K|   183 (1)|Q1,00| PCWP |          |
---------------------------------------------------------------------------------------------

Demo with Bitmap Index

EXPLAIN PLAN FOR
SELECT *
FROM serv_inst
WHERE location_code = 30386
OR ws_id BETWEEN 326 AND 333;

SELECT * FROM table(dbms_xplan.display);

---------------------------------------------------------------------------------------------
| Id| Operation                           | Name                      |Rows|Bytes|Cost(%CPU)|
---------------------------------------------------------------------------------------------
| 0| SELECT STATEMENT                    |                           | 30| 1260|   7   (0)|
| 1| TABLE ACCESS BY INDEX ROWID BATCHED|SERV_INST                  | 30| 1260|   7   (0)|
| 2|   BITMAP CONVERSION TO ROWIDS       |                           |    |     |          |
| 3|    BITMAP OR                        |                           |    |     |          |
| 4|     BITMAP MERGE                    |                           |    |     |          |
|* 5|      BITMAP INDEX RANGE SCAN        |BIX_SERV_INST_WS_ID        |    |     |          |
|* 6|     BITMAP INDEX SINGLE VALUE       |BIX_SERV_INST_LOCATION_CODE|    |     |          |
----------------------------------------------------------------------------------------------

Demo with IOT

CREATE TABLE reg_tab (
state   VARCHAR2(2),
city    VARCHAR2(30),
zipcode VARCHAR2(5));

ALTER TABLE reg_tab
ADD CONSTRAINT pk_reg_tab
PRIMARY KEY (zipcode)
USING INDEX;

INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98101');
INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98102');
INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98103');
INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98104');
INSERT INTO reg_tab VALUES ('WA', 'Seattle', '98105');

CREATE TABLE iot_tab (
state   VARCHAR2(2),
city    VARCHAR2(30),
zipcode VARCHAR2(5),
CONSTRAINT pk_iot_tab
PRIMARY KEY (zipcode))
ORGANIZATION INDEX;

INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98101');
INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98102');
INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98103');
INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98104');
INSERT INTO iot_tab VALUES ('WA', 'Seattle', '98105');
COMMIT;

EXPLAIN PLAN FOR
SELECT * FROM reg_tab WHERE zipcode = '98004';

SELECT * FROM table(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT * FROM iot_tab WHERE zipcode = '98004';

SELECT * FROM table(dbms_xplan.display);

Demo with Partitions and Local Indexes

-- tablespace build on the Partitions page

CREATE TABLE part_zip (
state VARCHAR2(2),
city VARCHAR2(30),
zipcode VARCHAR2(5))
PARTITION BY HASH (state)
PARTITIONS 3
STORE IN (part1, part2, part3);

INSERT INTO part_zip VALUES ('WA', 'Seattle', '98101');
INSERT INTO part_zip VALUES ('WA', 'Seattle', '98102');
INSERT INTO part_zip VALUES ('WA', 'Seattle', '98103');
INSERT INTO part_zip VALUES ('WA', 'Seattle', '98104');
INSERT INTO part_zip VALUES ('WA', 'Seattle', '98105');
INSERT INTO part_zip VALUES ('CA', 'San Francisco', '94105');
INSERT INTO part_zip VALUES ('CA', 'San Francisco', '94107');
INSERT INTO part_zip VALUES ('CA', 'San Francisco', '94111');
INSERT INTO part_zip VALUES ('HI', 'Honolulu', '96813');
INSERT INTO part_zip VALUES ('HI', 'Honolulu', '96817');
INSERT INTO part_zip VALUES ('HI', 'Honolulu', '96822');
COMMIT;

EXPLAIN PLAN FOR
SELECT *
FROM part_zip
WHERE state = 'HI';

SELECT * FROM table(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT *
FROM part_zip
WHERE state IN ('HI', 'WA');

SELECT * FROM TABLE(dbms_xplan.display);

EXPLAIN PLAN FOR
SELECT *
FROM part_zip
WHERE zipcode LIKE '%5%';

SELECT * FROM table(dbms_xplan.display);

TEMP Tablespace Usage Required

-- with thanks to Jonathan Lewis

EXPLAIN PLAN FOR
SELECT source
FROM sys.source$
ORDER BY source;

SELECT * FROM TABLE(dbms_xplan.display);

-------------------------------------------------------------------------------------
| Id | Operation        | Name    | Rows | Bytes | TempSpc | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT |         | 19060 |   763K|         |   250   (1)| 00:00:01 |
| 1 | SORT ORDER BY    |         | 19060 |   763K|    912K |   250   (1)| 00:00:01 |
| 2 | TABLE ACCESS FULL| SOURCE$ | 19060 |   763K|         |    46   (0)| 00:00:01 |
-------------------------------------------------------------------------------------