CODE
Oracle Code Library
JOBS
Find Or Post Oracle Jobs
FORUM
Oracle Discussion & Chat
PSOUG Home Code Snippets Oracle Reference Oracle Functions PSOUG Forum Oracle Blogs Bookmark and Share
 
 Search the Reference Library pages:  

Free
Oracle Magazine
Subscriptions
and Oracle White Papers

Oracle Explain Plan
Version 11.1
 
General
Data Dictionary Objects
plan_table$ v_$sql_plan_statistics
v_$sql_plan v_$sql_plan_statistics_all
 
Preparation
Create the plan table if it does not already exist -- 9i or earlier
@? dbms\admin\utlxplan.sql
-- 10g or above
@? dbms\admin\catplan.sql
Create test data Run the script servers.sql downloaded by clicking here.
@c: est\servers.sql
Gather statistics for the Cost Based Optimizer exec dbms_stats.gather_schema_stats(USER, cascade=>TRUE);
 
Syntax
Explain Plan Syntax EXPLAIN PLAN
[SET statement_id = <statement_identifier>]
[INTO <table_name>]
FOR
<SQL statement here>;

Paleolithic Explain Plan Demo

Now that you've seen it ... don't do it!
EXPLAIN PLAN
SET STATEMENT_ID = 'abc' FOR
SELECT srvr_id
FROM servers
INTERSECT
SELECT srvr_id
FROM serv_inst;

SELECT LPAD(' ',2*(level-1)) || operation || ' ' || options ||' ' || object_name || ' ' ||
DECODE(id,0,'Cost = ' || position) QUERY_OUTPUT
FROM plan_table
START WITH id = 0
AND statement_id = 'abc'
CONNECT BY PRIOR id = parent_id
AND statement_id = 'abc';

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 plan_view AS
SELECT * FROM table(dbms_xplan.display);

SELECT * FROM plan_view;
 
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  |     6  (84)|
|  1 |  INTERSECTION          |              |      |       |            |
|  2 |   SORT UNIQUE NOSORT   |              |  141 |  564  |     2  (50)|
|  3 |    INDEX FULL SCAN     | PK_SERVERS   |  141 |  564  |     1   (0)|
|  4 |   SORT UNIQUE          |              |  999 | 3996  |     4  (25)|
|  5 |    INDEX FAST FULL SCAN| IX_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 |    4 (25)  |
|  1 |  NESTED LOOPS           |              |   11 |    88 |    4 (25)  |
|  2 |   SORT UNIQUE           |              |  999 |  3996 |    3  (0)  |
|  3 |    INDEX FAST FULL SCAN | IX_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 |    4  (25)|
|  1 |  NESTED LOOPS             |              |   11 |   187 |    4  (25)|
|  2 |   VIEW                    | VW_NSO_1     |  999 | 12987 |    3   (0)|
|  3 |    HASH UNIQUE            |              |   11 |  7992 |           |
|  4 |     NESTED LOOPS          |              |  999 |  7992 |    3   (0)|
|  5 |      INDEX FAST FULL SCAN | IX_SERV_INST |  999 |  3996 |    3   (0)|
|* 6 |      INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |    0   (0)|
|* 7 |   INDEX UNIQUE SCAN       | PK_SERVERS   |    1 |     4 |    0   (0)|
----------------------------------------------------------------------------

6 -- access("I"."SRVR_ID"="S"."SRVR_ID")
7 -- access("SRVR_ID"="$nso_col_1")

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 |    4  (25)|
|  1 |  HASH UNIQUE            |              |   11 |    88 |    4  (25)|
|  2 |   NESTED LOOPS          |              |  999 |  7992 |    3   (0)|
|  3 |    INDEX FAST FULL SCAN | IX_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 # 4B

Simple INNER JOIN with HINT
EXPLAIN PLAN FOR
SELECT /*+ NO_USE_NL(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 |    6  (34)|
|  1 |  HASH UNIQUE            |              |   11 |    88 |    6  (34)|
|* 2 |   HASH JOIN             |              |  999 |  7992 |    5  (20)|
|  3 |    INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |    1   (0)|
|  4 |    INDEX FAST FULL SCAN | IX_SERV_INST |  999 |  3996 |    3   (0)|
--------------------------------------------------------------------------
2 - access("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           |              |    1 |    17 |    9 (45)|
|  1 |  HASH UNIQUE               |              |    1 |    17 |    9 (45)|
|* 2 |   HASH JOIN ANTI           |              |  140 |  2380 |    8 (38)|
|  3 |    INDEX FULL SCAN         | PK_SERVERS   |  141 |   564 |    1  (0)|
|  4 |    VIEW                    | VW_NSO_1     |  141 |  1833 |    6 (34)|
|  5 |     MINUS                  |              |      |       |          |
|  6 |      SORT UNIQUE           |              |  141 |   564 |          |
|  7 |       INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |    1  (0)|
|  8 |      SORT UNIQUE           |              |  999 |  3996 |          |
|  9 |       INDEX FAST FULL SCAN | IX_SERV_INST |  999 |  3996 |    3  (0)|
----------------------------------------------------------------------------
2 - access("SRVR_ID"="$nso_col_1")

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 |     4  (25)|
|  1 |  NESTED LOOPS           |              |   11 |    88 |     4  (25)|
|  2 |   SORT UNIQUE           |              |  999 |  3996 |     3   (0)|
|  3 |    INDEX FAST FULL SCAN | IX_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

WITH
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 |    4  (25)|
|  1 |  VIEW                    |              |   11 |   143 |    4  (25)|
|  2 |   HASH UNIQUE            |              |   11 |    88 |    4  (25)|
|  3 |    NESTED LOOPS          |              |  999 |  7992 |    3   (0)|
|  4 |     INDEX FAST FULL SCAN | IX_SERV_INST |  999 |  3996 |    3   (0)|
|* 5 |     INDEX UNIQUE SCAN    | PK_SERVERS   |    1 |     4 |    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 |    4  (25)|
|  1 |  HASH UNIQUE            |              |   11 |    88 |    4  (25)|
|  2 |   NESTED LOOPS OUTER    |              |  999 |  7992 |    3   (0)|
|  3 |    INDEX FAST FULL SCAN | IX_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           |              |  152 |  2432 |    7 (43)|
|* 1 |  FILTER                    |              |      |       |          |
|  2 |   HASH GROUP BY            |              |  152 |  2432 |    7 (43)|
|  3 |    VIEW                    |              |  152 |  2432 |    6 (34)|
|  4 |     UNION-ALL              |              |      |       |          |
|  5 |      SORT UNIQUE NOSORT    |              |  141 |   564 |    2 (50)|
|  6 |       INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |    1  (0)|
|  7 |      HASH UNIQUE           |              |   11 |    44 |    4 (25)|
|  8 |       INDEX FAST FULL SCAN | IX_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 |     6 (34)|
|  1 |  HASH UNIQUE            |              |   11 |    88 |     6 (34)|
|* 2 |   HASH JOIN             |              |  999 |  7992 |     5 (20)|
|  3 |    INDEX FULL SCAN      | PK_SERVERS   |  141 |   564 |     1  (0)|
|  4 |    INDEX FAST FULL SCAN | IX_SERV_INST |  999 |  3996 |     3  (0)|
--------------------------------------------------------------------------
2 - access("S"."SRVR_ID"+0="I"."SRVR_ID"+0)
 
Demos

Demo with NULLABLE column
CREATE TABLE nullable AS
SELECT * FROM serv_inst;

desc serv_inst
desc nullable

set pagesize 25
set linesize 141

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);

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);

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);

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);

Demo with Parallel Query
-- conn hr/hr

CREATE TABLE emp2 AS 
SELECT * FROM employees;

ALTER TABLE emp2 PARALLEL 2;

EXPLAIN PLAN FOR
SELECT SUM(salary) 
FROM emp2
GROUP BY department_id;

set pagesize 25
set linesize 141

SELECT plan_table_output FROM table(dbms_xplan.display);

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

set pagesize 25
set linesize 141

SELECT * FROM table(dbms_xplan.display);

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 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 Space Required

When a SQL statement requires TEMP space that too is shown in the XPLAN output.
-- With thanks to Jonathan Lewis.

explain plan for select source from sys.source$ order by source;

set linesize 121

select * from table(dbms_xplan.display);

PLAN_TABLE_OUTPUT
-----------------------------------------------------------------
Plan hash value: 995087943

-----------------------------------------------------------------
|Id | Operation         | Name  | Rows|Bytes|TempSpc|Cost (%CPU)|
-----------------------------------------------------------------
| 0 | SELECT STATEMENT  |       | 342K|  42M|       |  12285 (1)|
| 1 | SORT ORDER BY     |       | 342K|  42M|    92M|  12285 (1)|
| 2 | TABLE ACCESS FULL |SOURCE$| 342K|  42M|       |   2197 (1)| -----------------------------------------------------------------
9 rows selected.
 
Related Topics
DBMS_SPM
DBMS_STATS
DBMS_XPLAN
TKPROF
Trace
 
Home      :      Code Library      :      Sponsors      :      Privacy      :      Terms of Use      :      Contact Us [201 users online]    © 2010 psoug.org