Skip to content

Instantly share code, notes, and snippets.

@xtender

xtender/PLAN

Created June 7, 2015 19:11
Show Gist options

  • Select an option

    Learn more about clone URLs
  • Save xtender/5bc16e9fb4565af098a9 to your computer and use it in GitHub Desktop.

Select an option

Learn more about clone URLs
Save xtender/5bc16e9fb4565af098a9 to your computer and use it in GitHub Desktop.
Download ZIP
Solution by Randolf Geist (to add UNION ALL with fake FTS)
Raw
PLAN
PLAN_TABLE_OUTPUT
----------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID b5smw3hmc9bw7, child number 0
-------------------------------------
with v as( select /*+ materialize */ * from ( select /*+
index(li) */ rowid rid from z_line_items li where
li.item_id = 42 union all select rowid rid from
z_line_items li where 1 = 2 ) ) select * From ( select /*+
leading(v li o) use_nl(li o) parallel(v 8) */ li.*, o.* from v
, z_line_items li , z_orders o where li.rowid = v.rid
and o.id = li.order_id and o.order_type != 0 ) where rownum > 1
Plan hash value: 3124400537
----------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | TQ |IN-OUT| PQ Distrib |
----------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 110K(100)| | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | | | |
| 2 | PX COORDINATOR | | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 393K| 4613K| 889 (1)| 00:00:11 | Q1,01 | P->S | QC (RAND) |
| 4 | LOAD AS SELECT | | | | | | Q1,01 | PCWP | |
| 5 | VIEW | | 393K| 4613K| 889 (1)| 00:00:11 | Q1,01 | PCWP | |
| 6 | UNION-ALL | | | | | | Q1,01 | PCWP | |
| 7 | BUFFER SORT | | | | | | Q1,01 | PCWC | |
| 8 | PX RECEIVE | | | | | | Q1,01 | PCWP | |
| 9 | PX SEND ROUND-ROBIN | :TQ10000 | | | | | | S->P | RND-ROBIN |
|* 10 | INDEX RANGE SCAN | ITEM_ID__Z_LINE_ITEMS | 393K| 5382K| 889 (1)| 00:00:11 | | | |
|* 11 | FILTER | | | | | | Q1,01 | PCWC | |
| 12 | PX BLOCK ITERATOR | | 4000K| 45M| 423 (1)| 00:00:06 | Q1,01 | PCWC | |
|* 13 | TABLE ACCESS FULL | Z_LINE_ITEMS | 4000K| 45M| 423 (1)| 00:00:06 | Q1,01 | PCWP | |
| 14 | COUNT | | | | | | | | |
|* 15 | FILTER | | | | | | | | |
| 16 | PX COORDINATOR | | | | | | | | |
| 17 | PX SEND QC (RANDOM) | :TQ20000 | 357K| 11M| 109K (1)| 00:21:53 | Q2,00 | P->S | QC (RAND) |
| 18 | NESTED LOOPS | | 357K| 11M| 109K (1)| 00:21:53 | Q2,00 | PCWP | |
| 19 | NESTED LOOPS | | 393K| 11M| 109K (1)| 00:21:53 | Q2,00 | PCWP | |
| 20 | NESTED LOOPS | | 393K| 9996K| 54717 (1)| 00:10:57 | Q2,00 | PCWP | |
| 21 | VIEW | | 393K| 4613K| 25 (4)| 00:00:01 | Q2,00 | PCWP | |
| 22 | PX BLOCK ITERATOR | | 393K| 4613K| 25 (4)| 00:00:01 | Q2,00 | PCWC | |
|* 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6AEC_AB10D34 | 393K| 4613K| 25 (4)| 00:00:01 | Q2,00 | PCWP | |
| 24 | TABLE ACCESS BY USER ROWID| Z_LINE_ITEMS | 1 | 14 | 1 (0)| 00:00:01 | Q2,00 | PCWP | |
|* 25 | INDEX UNIQUE SCAN | ID__Z_ORDERS | 1 | | 0 (0)| | Q2,00 | PCWP | |
|* 26 | TABLE ACCESS BY INDEX ROWID | Z_ORDERS | 1 | 8 | 0 (0)| | Q2,00 | PCWP | |
----------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$7286615E
2 - SEL$1
5 - SET$1 / from$_subquery$_001@SEL$1
6 - SET$1
10 - SEL$2 / LI@SEL$2
11 - SEL$3
13 - SEL$3 / LI@SEL$3
21 - SEL$D67CB2D2 / V@SEL$5
23 - SEL$D67CB2D2 / T1@SEL$D67CB2D2
24 - SEL$7286615E / LI@SEL$5
25 - SEL$7286615E / O@SEL$5
26 - SEL$7286615E / O@SEL$5
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.4')
DB_VERSION('11.2.0.4')
ALL_ROWS
OUTLINE_LEAF(@"SEL$2")
OUTLINE_LEAF(@"SEL$3")
OUTLINE_LEAF(@"SET$1")
OUTLINE_LEAF(@"SEL$1")
OUTLINE_LEAF(@"SEL$D67CB2D2")
OUTLINE_LEAF(@"SEL$7286615E")
MERGE(@"SEL$5")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$4")
OUTLINE(@"SEL$5")
NO_ACCESS(@"SEL$7286615E" "V"@"SEL$5")
ROWID(@"SEL$7286615E" "LI"@"SEL$5")
INDEX(@"SEL$7286615E" "O"@"SEL$5" ("Z_ORDERS"."ID"))
LEADING(@"SEL$7286615E" "V"@"SEL$5" "LI"@"SEL$5" "O"@"SEL$5")
USE_NL(@"SEL$7286615E" "LI"@"SEL$5")
USE_NL(@"SEL$7286615E" "O"@"SEL$5")
NLJ_BATCHING(@"SEL$7286615E" "O"@"SEL$5")
PQ_DISTRIBUTE(@"SEL$7286615E" "LI"@"SEL$5" NONE BROADCAST)
PQ_DISTRIBUTE(@"SEL$7286615E" "O"@"SEL$5" NONE BROADCAST)
FULL(@"SEL$D67CB2D2" "T1"@"SEL$D67CB2D2")
NO_ACCESS(@"SEL$1" "from$_subquery$_001"@"SEL$1")
FULL(@"SEL$3" "LI"@"SEL$3")
INDEX(@"SEL$2" "LI"@"SEL$2" ("Z_LINE_ITEMS"."ITEM_ID"))
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
10 - access("LI"."ITEM_ID"=42)
11 - filter(NULL IS NOT NULL)
13 - access(:Z>=:Z AND :Z<=:Z)
15 - filter(ROWNUM>1)
23 - access(:Z>=:Z AND :Z<=:Z)
25 - access("O"."ID"="LI"."ORDER_ID")
26 - filter("O"."ORDER_TYPE"<>0)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
1 - "V"."RID"[ROWID,10], "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22], "LI"."PRICE"[NUMBER,22],
"O".ROWID[ROWID,10], "O"."ID"[NUMBER,22], "O"."ORDER_TYPE"[NUMBER,22], ROWNUM[4]
2 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[96], SYSDEF[0]
3 - (#keys=0) SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[96], SYSDEF[0]
4 - SYSDEF[4], SYSDEF[0], SYSDEF[1], SYSDEF[96], SYSDEF[0]
5 - "from$_subquery$_001"."RID"[ROWID,10]
6 - STRDEF[10]
7 - (#keys=0) ROWID[ROWID,10]
8 - ROWID[ROWID,10]
9 - (#keys=0) ROWID[ROWID,10]
10 - ROWID[ROWID,10], "LI"."ITEM_ID"[NUMBER,22]
11 - ROWID[ROWID,10]
12 - ROWID[ROWID,10]
13 - ROWID[ROWID,10]
14 - "V"."RID"[ROWID,10], "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22], "LI"."PRICE"[NUMBER,22],
"O".ROWID[ROWID,10], "O"."ID"[NUMBER,22], "O"."ORDER_TYPE"[NUMBER,22], ROWNUM[4]
15 - "V"."RID"[ROWID,10], "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22], "LI"."PRICE"[NUMBER,22],
"O".ROWID[ROWID,10], "O"."ID"[NUMBER,22], "O"."ORDER_TYPE"[NUMBER,22]
16 - "V"."RID"[ROWID,10], "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22], "LI"."PRICE"[NUMBER,22],
"O".ROWID[ROWID,10], "O"."ID"[NUMBER,22], "O"."ORDER_TYPE"[NUMBER,22]
17 - (#keys=0) "V"."RID"[ROWID,10], "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22],
"LI"."PRICE"[NUMBER,22], "O".ROWID[ROWID,10], "O"."ID"[NUMBER,22], "O"."ORDER_TYPE"[NUMBER,22]
18 - "V"."RID"[ROWID,10], "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22], "LI"."PRICE"[NUMBER,22],
"O".ROWID[ROWID,10], "O"."ID"[NUMBER,22], "O"."ORDER_TYPE"[NUMBER,22]
19 - "V"."RID"[ROWID,10], "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22], "LI"."PRICE"[NUMBER,22],
"O".ROWID[ROWID,10], "O"."ID"[NUMBER,22]
20 - "V"."RID"[ROWID,10], "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22], "LI"."PRICE"[NUMBER,22]
21 - "V"."RID"[ROWID,10]
22 - "C0"[ROWID,10]
23 - "C0"[ROWID,10]
24 - "LI".ROWID[ROWID,10], "LI"."ORDER_ID"[NUMBER,22], "LI"."ITEM_ID"[NUMBER,22], "LI"."PRICE"[NUMBER,22]
25 - "O".ROWID[ROWID,10], "O"."ID"[NUMBER,22]
26 - "O".ROWID[ROWID,10], "O"."ORDER_TYPE"[NUMBER,22]
Raw
QUERY
alter session force parallel query parallel 8;
with v as(
select /*+ materialize */ * from (
select /*+ index(li) */
rowid rid
from z_line_items li
where li.item_id = 42
union all
select rowid rid
from z_line_items li
where 1 = 2
)
)
select *
From (
select /*+ leading(v li o) use_nl(li o) parallel(v 8) */
li.*, o.*
from v
, z_line_items li
, z_orders o
where li.rowid = v.rid
and o.id = li.order_id
and o.order_type != 0
)
where rownum > 1
;
Raw
RTSM Report
SQL> @rtsm/sqlid b5smw3hmc9bw7
SQLMON
------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
SQL Monitoring Report
SQL Text
------------------------------
with v as( select /*+ materialize */ * from ( select /*+ index(li) */ rowid rid from z_line_items li where li.item_id = 42 union all select rowid rid from z_line_items li where 1 = 2 ) ) select * From ( select /*+ leading(v li o) use_nl(li o) parallel(v 8) */ li.*, o.* from v , z_line_items li , z_o
rders o where li.rowid = v.rid and o.id = li.order_id and o.order_type != 0 ) where rownum > 1
Global Information
------------------------------
Status : DONE (ALL ROWS)
Instance ID : 1
Session : XTENDER (199:1741)
SQL ID : b5smw3hmc9bw7
SQL Execution ID : 16777216
Execution Started : 06/07/2015 19:28:22
First Refresh Time : 06/07/2015 19:28:22
Last Refresh Time : 06/07/2015 19:28:29
Duration : 7s
Module/Action : SQL*Plus/-
Service : baikal
Program : sqlplus.exe
Fetch Calls : 1
Global Stats
========================================================================================================================
| Elapsed | Cpu | IO | Application | Concurrency | Other | Fetch | Buffer | Read | Read | Write | Write |
| Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Calls | Gets | Reqs | Bytes | Reqs | Bytes |
========================================================================================================================
| 33 | 3.37 | 29 | 0.01 | 0.00 | 1.13 | 1 | 807K | 11704 | 100MB | 100 | 9MB |
========================================================================================================================
Parallel Execution Details (DOP=8 , Servers Allocated=16)
=========================================================================================================================================================================================
| Name | Type | Group# | Server# | Elapsed | Cpu | IO | Application | Concurrency | Other | Buffer | Read | Read | Write | Write | Wait Events |
| | | | | Time(s) | Time(s) | Waits(s) | Waits(s) | Waits(s) | Waits(s) | Gets | Reqs | Bytes | Reqs | Bytes | (sample #) |
=========================================================================================================================================================================================
| PX Coordinator | QC | | | 1.12 | 0.37 | 0.56 | 0.01 | | 0.18 | 1359 | 1019 | 8MB | 32 | 1MB | |
| p000 | Set 1 | 1 | 1 | 0.10 | 0.03 | 0.03 | | | 0.04 | 138 | 1 | 8192 | 8 | 1MB | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p001 | Set 1 | 1 | 2 | 0.10 | 0.03 | 0.05 | | | 0.02 | 138 | 1 | 8192 | 8 | 1MB | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p002 | Set 1 | 1 | 3 | 0.09 | | 0.01 | | | 0.08 | 138 | 1 | 8192 | 8 | 1MB | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p003 | Set 1 | 1 | 4 | 0.10 | 0.05 | 0.01 | | | 0.05 | 138 | 1 | 8192 | 8 | 1MB | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file scattered read (2) |
| | | | | | | | | | | | | | | | db file sequential read (4) |
| p004 | Set 1 | 1 | 5 | 0.14 | 0.09 | 0.05 | | | | 138 | 1 | 8192 | 9 | 1MB | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p005 | Set 1 | 1 | 6 | 0.09 | | 0.04 | | | 0.05 | 138 | 1 | 8192 | 9 | 1MB | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file scattered read (2) |
| | | | | | | | | | | | | | | | db file sequential read (4) |
| p006 | Set 1 | 1 | 7 | 0.09 | | 0.03 | | | 0.06 | 138 | 1 | 8192 | 9 | 1MB | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p007 | Set 1 | 1 | 8 | 0.09 | | 0.01 | | | 0.09 | 138 | 1 | 8192 | 9 | 1MB | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p000 | Set 1 | 2 | 1 | 3.89 | 0.28 | 3.48 | | | 0.13 | 94276 | 1254 | 11MB | | . | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p001 | Set 1 | 2 | 2 | 3.85 | 0.33 | 3.45 | | 0.00 | 0.08 | 94530 | 1246 | 11MB | | . | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p002 | Set 1 | 2 | 3 | 3.95 | 0.41 | 3.53 | | 0.00 | 0.02 | 109K | 1436 | 12MB | | . | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p003 | Set 1 | 2 | 4 | 3.96 | 0.39 | 3.54 | | 0.00 | 0.02 | 107K | 1441 | 12MB | | . | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p004 | Set 1 | 2 | 5 | 3.99 | 0.48 | 3.50 | | 0.00 | | 95679 | 1236 | 11MB | | . | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p005 | Set 1 | 2 | 6 | 3.93 | 0.28 | 3.51 | | 0.00 | 0.13 | 99079 | 1338 | 11MB | | . | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file scattered read (2) |
| | | | | | | | | | | | | | | | db file sequential read (4) |
| p006 | Set 1 | 2 | 7 | 3.95 | 0.39 | 3.56 | | 0.00 | 0.01 | 104K | 1414 | 12MB | | . | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file sequential read (6) |
| p007 | Set 1 | 2 | 8 | 3.91 | 0.23 | 3.51 | | 0.00 | 0.16 | 100K | 1312 | 11MB | | . | db file parallel read (2) |
| | | | | | | | | | | | | | | | db file scattered read (2) |
| | | | | | | | | | | | | | | | db file sequential read (4) |
=========================================================================================================================================================================================
SQL Plan Monitoring Details (Plan Hash Value=3124400537)
======================================================================================================================================================================================================================
| Id | Operation | Name | Rows | Cost | Time | Start | Execs | Rows | Read | Read | Write | Write | Mem | Activity | Activity Detail |
| | | | (Estim) | | Active(s) | Active | | (Actual) | Reqs | Bytes | Reqs | Bytes | (Max) | (%) | (# samples) |
======================================================================================================================================================================================================================
| 0 | SELECT STATEMENT | | | | | | 1 | | | | | | | | |
| 1 | TEMP TABLE TRANSFORMATION | | | | | | 1 | | 1 | 8192 | 32 | 1MB | | | |
| 2 | PX COORDINATOR | | | | 1 | +3 | 9 | 8 | | | | | | | |
| 3 | PX SEND QC (RANDOM) | :TQ10001 | 394K | 889 | 1 | +3 | 8 | 8 | | | | | | | |
| 4 | LOAD AS SELECT | | | | 1 | +3 | 8 | 16 | | | 32 | 7MB | 4M | | |
| 5 | VIEW | | 394K | 889 | 1 | +3 | 8 | 396K | | | | | | | |
| 6 | UNION-ALL | | | | 1 | +3 | 8 | 396K | | | | | | | |
| 7 | BUFFER SORT | | | | 1 | +3 | 8 | 396K | | | | | 16M | | |
| 8 | PX RECEIVE | | | | 1 | +3 | 8 | 396K | | | | | | | |
| 9 | PX SEND ROUND-ROBIN | :TQ10000 | | | 2 | +2 | 1 | 396K | | | | | | | |
| 10 | INDEX RANGE SCAN | ITEM_ID__Z_LINE_ITEMS | 394K | 889 | 2 | +2 | 1 | 396K | 1014 | 8MB | | | | | |
| 11 | FILTER | | | | | | 8 | | | | | | | | |
| 12 | PX BLOCK ITERATOR | | 4M | 423 | | | | | | | | | | | |
| 13 | TABLE ACCESS FULL | Z_LINE_ITEMS | 4M | 423 | | | | | | | | | | | |
| 14 | COUNT | | | | | | 1 | | | | | | | | |
| 15 | FILTER | | | | 2 | +6 | 1 | 0 | | | | | | | |
| 16 | PX COORDINATOR | | | | 2 | +6 | 9 | 361K | | | | | | | |
| 17 | PX SEND QC (RANDOM) | :TQ20000 | 358K | 109K | 3 | +5 | 8 | 361K | | | | | | | |
| 18 | NESTED LOOPS | | 358K | 109K | 3 | +5 | 8 | 361K | | | | | | | |
| 19 | NESTED LOOPS | | 394K | 109K | 3 | +5 | 8 | 396K | | | | | | | |
| 20 | NESTED LOOPS | | 394K | 54717 | 3 | +5 | 8 | 396K | | | | | | | |
| 21 | VIEW | | 394K | 25 | 3 | +5 | 8 | 396K | | | | | | | |
| 22 | PX BLOCK ITERATOR | | 394K | 25 | 3 | +5 | 8 | 396K | | | | | | | |
| 23 | TABLE ACCESS FULL | SYS_TEMP_0FD9D6AEC_AB10D34 | 394K | 25 | 3 | +5 | 96 | 396K | 103 | 9MB | | | | 6.25 | db file scattered read (4) |
| 24 | TABLE ACCESS BY USER ROWID | Z_LINE_ITEMS | 1 | 1 | 4 | +4 | 427K | 396K | 10533 | 82MB | | | | 93.75 | db file parallel read (16) |
| | | | | | | | | | | | | | | | db file sequential read (44) |
| 25 | INDEX UNIQUE SCAN | ID__Z_ORDERS | 1 | | 3 | +5 | 397K | 396K | 25 | 200KB | | | | | |
| 26 | TABLE ACCESS BY INDEX ROWID | Z_ORDERS | 1 | | 3 | +5 | 397K | 361K | 16 | 128KB | | | | | |
======================================================================================================================================================================================================================
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment