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