Here’s a very long post (which is mainly an example) demonstrating a little bug in the “explain plan” functionality. It’s a variation of a bug which I thought had been fixed in 11g, but it still appears in some cases. Take a look at this execution plan, which comes from explaining “select * from dba_tab_cols” – the bit I want to emphasise is in lines 1 to 10:
------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 8 | 21704 | | 2387 (1)| 00:00:29 |
| 1 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 480 | 14400 | | 3 (0)| 00:00:01 |
|* 2 | INDEX RANGE SCAN | I_OBJ1 | 192 | | | 2 (0)| 00:00:01 |
| 3 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 480 | 14400 | | 3 (0)| 00:00:01 |
|* 4 | INDEX RANGE SCAN | I_OBJ1 | 192 | | | 2 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 480 | 14400 | | 3 (0)| 00:00:01 |
|* 6 | INDEX RANGE SCAN | I_OBJ1 | 192 | | | 2 (0)| 00:00:01 |
| 7 | TABLE ACCESS BY INDEX ROWID | OBJ$ | 480 | 14400 | | 3 (0)| 00:00:01 |
|* 8 | INDEX RANGE SCAN | I_OBJ1 | 192 | | | 2 (0)| 00:00:01 |
| 9 | TABLE ACCESS BY INDEX ROWID| OBJ$ | 480 | 14400 | | 3 (0)| 00:00:01 |
|* 10 | INDEX RANGE SCAN | I_OBJ1 | 192 | | | 2 (0)| 00:00:01 |
|* 11 | HASH JOIN | | 480 | 1929K| | 5 (20)| 00:00:01 |
|* 12 | INDEX RANGE SCAN | I_OBJ1 | 480 | 12480 | | 2 (0)| 00:00:01 |
| 13 | TABLE ACCESS FULL | USER$ | 59 | 117K| | 2 (0)| 00:00:01 |
| 14 | NESTED LOOPS OUTER | | 1 | 2084 | | 3 (0)| 00:00:01 |
| 15 | TABLE ACCESS BY INDEX ROWID | COL$ | 1 | 56 | | 2 (0)| 00:00:01 |
|* 16 | INDEX UNIQUE SCAN | I_COL3 | 1 | | | 1 (0)| 00:00:01 |
| 17 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 2028 | | 1 (0)| 00:00:01 |
|* 18 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 0 (0)| 00:00:01 |
| 19 | TABLE ACCESS BY INDEX ROWID | ATTRCOL$ | 1 | 2028 | | 2 (0)| 00:00:01 |
|* 20 | INDEX UNIQUE SCAN | I_ATTRCOL1 | 1 | | | 1 (0)| 00:00:01 |
|* 21 | FILTER | | | | | | |
|* 22 | HASH JOIN RIGHT OUTER | | 72962 | 188M| | 2387 (1)| 00:00:29 |
| 23 | TABLE ACCESS FULL | USER$ | 59 | 118K| | 3 (0)| 00:00:01 |
|* 24 | HASH JOIN RIGHT OUTER | | 72962 | 45M| | 2384 (1)| 00:00:29 |
|* 25 | TABLE ACCESS FULL | OBJ$ | 583 | 30899 | | 205 (0)| 00:00:03 |
|* 26 | HASH JOIN RIGHT OUTER | | 72962 | 41M| | 2178 (1)| 00:00:27 |
| 27 | TABLE ACCESS FULL | COLTYPE$ | 2886 | 174K| | 275 (0)| 00:00:04 |
|* 28 | HASH JOIN | | 72962 | 37M| | 1902 (1)| 00:00:23 |
| 29 | TABLE ACCESS FULL | USER$ | 59 | 1770 | | 3 (0)| 00:00:01 |
|* 30 | HASH JOIN | | 72962 | 35M| | 1899 (1)| 00:00:23 |
| 31 | INDEX FAST FULL SCAN | I_USER2 | 59 | 3068 | | 2 (0)| 00:00:01 |
|* 32 | HASH JOIN RIGHT OUTER | | 72962 | 31M| 2976K| 1896 (1)| 00:00:23 |
| 33 | TABLE ACCESS FULL | HIST_HEAD$ | 16280 | 2782K| | 70 (0)| 00:00:01 |
| 34 | NESTED LOOPS | | 72962 | 19M| | 660 (1)| 00:00:08 |
| 35 | TABLE ACCESS FULL | OBJ$ | 47960 | 3840K| | 205 (0)| 00:00:03 |
| 36 | TABLE ACCESS CLUSTER | COL$ | 2 | 404 | | 1 (0)| 00:00:01 |
|* 37 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 0 (0)| 00:00:01 |
|* 38 | TABLE ACCESS CLUSTER | TAB$ | 1 | 26 | | 2 (0)| 00:00:01 |
|* 39 | INDEX UNIQUE SCAN | I_OBJ# | 1 | | | 1 (0)| 00:00:01 |
| 40 | NESTED LOOPS | | 1 | 78 | | 3 (0)| 00:00:01 |
|* 41 | INDEX RANGE SCAN | I_OBJ4 | 1 | 39 | | 2 (0)| 00:00:01 |
|* 42 | INDEX RANGE SCAN | I_USER2 | 1 | 39 | | 1 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("O"."OBJ#"=:B1)
4 - access("O"."OBJ#"=:B1)
6 - access("O"."OBJ#"=:B1)
8 - access("O"."OBJ#"=:B1)
10 - access("O"."OBJ#"=:B1)
11 - access("O"."OWNER#"="USER#")
12 - access("O"."OBJ#"=:B1)
16 - access("CL"."OBJ#"=:B1 AND "CL"."INTCOL#"=:B2-1)
18 - access("RC"."OBJ#"(+)=:B1 AND "RC"."INTCOL#"(+)=:B2-1)
filter("CL"."INTCOL#"="RC"."INTCOL#"(+))
20 - access("TC"."OBJ#"=:B1 AND "TC"."INTCOL#"=:B2)
21 - filter((("O"."TYPE#"=3 OR "O"."TYPE#"=4) OR "O"."TYPE#"=2 AND NOT EXISTS (SELECT 0
FROM "SYS"."TAB$" "T" WHERE "T"."OBJ#"=:B1 AND (BITAND("T"."PROPERTY",512)=512 OR
BITAND("T"."PROPERTY",8192)=8192))) AND ("O"."TYPE#"<>4 AND "O"."TYPE#"<>5 AND "O"."TYPE#"<>7
AND "O"."TYPE#"<>8 AND "O"."TYPE#"<>9 AND "O"."TYPE#"<>10 AND "O"."TYPE#"<>11 AND
"O"."TYPE#"<>12 AND "O"."TYPE#"<>13 AND "O"."TYPE#"<>14 AND "O"."TYPE#"<>22 AND
"O"."TYPE#"<>87 AND "O"."TYPE#"<>88 OR BITAND("U"."SPARE1",16)=0 OR ("O"."TYPE#"=4 OR
"O"."TYPE#"=5 OR "O"."TYPE#"=7 OR "O"."TYPE#"=8 OR "O"."TYPE#"=9 OR "O"."TYPE#"=10 OR
"O"."TYPE#"=11 OR "O"."TYPE#"=12 OR "O"."TYPE#"=13 OR "O"."TYPE#"=14 OR "O"."TYPE#"=22 OR
"O"."TYPE#"=87) AND (SYS_CONTEXT('userenv','current_edition_name')='ORA$BASE' AND
"U"."TYPE#"<>2 OR "U"."TYPE#"=2 AND "U"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edit
ion_id')) OR EXISTS (SELECT 0 FROM SYS."USER$" "U2",SYS."OBJ$" "O2" WHERE "O2"."TYPE#"=88
AND "O2"."DATAOBJ#"=:B2 AND "U2"."TYPE#"=2 AND "O2"."OWNER#"="U2"."USER#" AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id'))))))
22 - access("OT"."OWNER#"="USER#"(+))
24 - access("AC"."TOID"="OT"."OID$"(+))
25 - filter("OT"."TYPE#"(+)=13)
26 - access("C"."OBJ#"="AC"."OBJ#"(+) AND "C"."INTCOL#"="AC"."INTCOL#"(+))
28 - access("O"."SPARE3"="U"."USER#")
30 - access("O"."OWNER#"="U"."USER#")
32 - access("C"."OBJ#"="H"."OBJ#"(+) AND "C"."INTCOL#"="H"."INTCOL#"(+))
37 - access("O"."OBJ#"="C"."OBJ#")
38 - filter(BITAND("T"."PROPERTY",512)=512 OR BITAND("T"."PROPERTY",8192)=8192)
39 - access("T"."OBJ#"=:B1)
41 - access("O2"."DATAOBJ#"=:B1 AND "O2"."TYPE#"=88)
42 - access("O2"."OWNER#"="U2"."USER#" AND "U2"."TYPE#"=2 AND
"U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
filter("U2"."SPARE2"=TO_NUMBER(SYS_CONTEXT('userenv','current_edition_id')))
If you try reading this execution plan using the rule of thumb (ROT) “the first thing that happens is near the top and over to the right” you’ll run into problems because (a) that’s not a good guideline and (b) there are a number of scalar subqueries in the select list and some filter subqueries in the where clause confusing the issue. Here’s the definition of the view dba_tab_cols:
select u.name, o.name,
c.name,
decode(c.type#, 1, decode(c.charsetform, 2, 'NVARCHAR2', 'VARCHAR2'),
2, decode(c.scale, null,
decode(c.precision#, null, 'NUMBER', 'FLOAT'),
'NUMBER'),
8, 'LONG',
9, decode(c.charsetform, 2, 'NCHAR VARYING', 'VARCHAR'),
12, 'DATE',
23, 'RAW', 24, 'LONG RAW',
58, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
69, 'ROWID',
96, decode(c.charsetform, 2, 'NCHAR', 'CHAR'),
100, 'BINARY_FLOAT',
101, 'BINARY_DOUBLE',
105, 'MLSLABEL',
106, 'MLSLABEL',
111, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
112, decode(c.charsetform, 2, 'NCLOB', 'CLOB'),
113, 'BLOB', 114, 'BFILE', 115, 'CFILE',
121, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
122, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
123, nvl2(ac.synobj#, (select o.name from obj$ o
where o.obj#=ac.synobj#), ot.name),
178, 'TIME(' ||c.scale|| ')',
179, 'TIME(' ||c.scale|| ')' || ' WITH TIME ZONE',
180, 'TIMESTAMP(' ||c.scale|| ')',
181, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH TIME ZONE',
231, 'TIMESTAMP(' ||c.scale|| ')' || ' WITH LOCAL TIME ZONE',
182, 'INTERVAL YEAR(' ||c.precision#||') TO MONTH',
183, 'INTERVAL DAY(' ||c.precision#||') TO SECOND(' ||
c.scale || ')',
208, 'UROWID',
'UNDEFINED'),
decode(c.type#, 111, 'REF'),
nvl2(ac.synobj#, (select u.name from "_BASE_USER" u, obj$ o
where o.owner#=u.user# and o.obj#=ac.synobj#),
ut.name),
c.length, c.precision#, c.scale,
decode(sign(c.null$),-1,'D', 0, 'Y', 'N'),
decode(c.col#, 0, to_number(null), c.col#), c.deflength,
c.default$, h.distcnt,
case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
then h.lowval
else null
end,
case when SYS_OP_DV_CHECK(o.name, o.owner#) = 1
then h.hival
else null
end,
h.density, h.null_cnt,
case when nvl(h.distcnt,0) = 0 then h.distcnt
when h.row_cnt = 0 then 1
when (h.bucket_cnt > 255
or
(h.bucket_cnt > h.distcnt
and h.row_cnt = h.distcnt
and h.density*h.bucket_cnt < 1))
then h.row_cnt
else h.bucket_cnt
end,
h.timestamp#, h.sample_size,
decode(c.charsetform, 1, 'CHAR_CS',
2, 'NCHAR_CS',
3, NLS_CHARSET_NAME(c.charsetid),
4, 'ARG:'||c.charsetid),
decode(c.charsetid, 0, to_number(NULL),
nls_charset_decl_len(c.length, c.charsetid)),
decode(bitand(h.spare2, 2), 2, 'YES', 'NO'),
decode(bitand(h.spare2, 1), 1, 'YES', 'NO'),
h.avgcln,
c.spare3,
decode(c.type#, 1, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
96, decode(bitand(c.property, 8388608), 0, 'B', 'C'),
null),
decode(bitand(ac.flags, 128), 128, 'YES', 'NO'),
decode(o.status, 1, decode(bitand(ac.flags, 256), 256, 'NO', 'YES'),
decode(bitand(ac.flags, 2), 2, 'NO',
decode(bitand(ac.flags, 4), 4, 'NO',
decode(bitand(ac.flags, 8), 8, 'NO',
'N/A')))),
decode(c.property, 0, 'NO', decode(bitand(c.property, 32), 32, 'YES',
'NO')),
decode(c.property, 0, 'NO', decode(bitand(c.property, 8), 8, 'YES',
'NO')),
decode(c.segcol#, 0, to_number(null), c.segcol#), c.intcol#,
case when nvl(h.row_cnt,0) = 0 then 'NONE'
when (h.bucket_cnt > 255
or
(h.bucket_cnt > h.distcnt and h.row_cnt = h.distcnt
and h.density*h.bucket_cnt < 1))
then 'FREQUENCY'
else 'HEIGHT BALANCED'
end,
decode(bitand(c.property, 1024), 1024,
(select decode(bitand(cl.property, 1), 1, rc.name, cl.name)
from sys.col$ cl, attrcol$ rc where cl.intcol# = c.intcol#-1
and cl.obj# = c.obj# and c.obj# = rc.obj#(+) and
cl.intcol# = rc.intcol#(+)),
decode(bitand(c.property, 1), 0, c.name,
(select tc.name from sys.attrcol$ tc
where c.obj# = tc.obj# and c.intcol# = tc.intcol#)))
from sys.col$ c, sys."_CURRENT_EDITION_OBJ" o, sys.hist_head$ h, sys.user$ u,
sys.coltype$ ac, sys.obj$ ot, sys."_BASE_USER" ut
where o.obj# = c.obj#
and o.owner# = u.user#
and c.obj# = h.obj#(+) and c.intcol# = h.intcol#(+)
and c.obj# = ac.obj#(+) and c.intcol# = ac.intcol#(+)
and ac.toid = ot.oid$(+)
and ot.type#(+) = 13
and ot.owner# = ut.user#(+)
and (o.type# in (3, 4) /* cluster, view */
or
(o.type# = 2 /* tables, excluding iot - overflow and nested tables */
and
not exists (select null
from sys.tab$ t
where t.obj# = o.obj#
and (bitand(t.property, 512) = 512 or
bitand(t.property, 8192) = 8192))))
I’ve left the line numbers in for this one to make it easier to refer to bits of the code. Notice that there are several nvl2() calls in the select list that include the option to query table obj$ – these are at lines 11, 19, 23, 25 and 27; these are the five scalar subqueries represented by lines 1 to 10 of the execution plan, and given that they are all within the same decode() statement you can appreciate that the corresponding pairs of lines in the execution plan should all be at the same indentation rather than forming the steadily increasing cascade that they do. This is an error in the calculated depth column of the plan table – an error which I first mentioned a few years ago with an example from 10g adding a throwaway comment that I had found a similar bug in 11g – but I’d not got around to showing an example until today and this example from 11.2.0.3
Footnote:
As a little instruction in reading execution plans – the main section of the query starts at line 21, with lines 22 – 37 representing the list of tables in the “from” list – including two tables representing the view _CURRENT_EDITION_OBJ.
Lines 38 and 39 represent the “not exists” subquery against table tab$.
Lines 40 – 42 represent an existence subquery embedded in the definition of view _CURRENT_EDITION_OBJ.
Lines 11 – 13 represent the inline scalar subquery in the nvl2() call at line 40 of the view definition.
Lines 14 – 20 represent the scalar subqueries in the decode() call at line 99 of the view definition, and again we have a depth problem, because lines 19 and 20 represent the second scalar subquery in that decode and line 19 ought to be indented to the same level as line 14.
If you write some old-style code to query the plan table, using a connect by between the id and parent_id columns, you’ll find that the indentation is correct – here’s the output from the plan table when reported by the old utlxpls.sql script from Oracle 8i:
Plan Table
--------------------------------------------------------------------------------
| Operation | Name | Rows | Bytes| Cost | Pstart| Pstop |
--------------------------------------------------------------------------------
| SELECT STATEMENT | | 8 | 21K| 2387 | | |
| TABLE ACCESS BY INDEX ROW|OBJ$ | 480 | 14K| 3 | | |
| INDEX RANGE SCAN |I_OBJ1 | 192 | | 2 | | |
| TABLE ACCESS BY INDEX ROW|OBJ$ | 480 | 14K| 3 | | |
| INDEX RANGE SCAN |I_OBJ1 | 192 | | 2 | | |
| TABLE ACCESS BY INDEX ROW|OBJ$ | 480 | 14K| 3 | | |
| INDEX RANGE SCAN |I_OBJ1 | 192 | | 2 | | |
| TABLE ACCESS BY INDEX ROW|OBJ$ | 480 | 14K| 3 | | |
| INDEX RANGE SCAN |I_OBJ1 | 192 | | 2 | | |
| TABLE ACCESS BY INDEX ROW|OBJ$ | 480 | 14K| 3 | | |
| INDEX RANGE SCAN |I_OBJ1 | 192 | | 2 | | |
| HASH JOIN | | 480 | 1M| 5 | | |
| INDEX RANGE SCAN |I_OBJ1 | 480 | 12K| 2 | | |
| TABLE ACCESS FULL |USER$ | 59 | 117K| 2 | | |
| NESTED LOOPS OUTER | | 1 | 2K| 3 | | |
| TABLE ACCESS BY INDEX RO|COL$ | 1 | 56 | 2 | | |
| INDEX UNIQUE SCAN |I_COL3 | 1 | | 1 | | |
| TABLE ACCESS BY INDEX RO|ATTRCOL$ | 1 | 1K| 1 | | |
| INDEX UNIQUE SCAN |I_ATTRCOL | 1 | | 0 | | |
| TABLE ACCESS BY INDEX ROW|ATTRCOL$ | 1 | 1K| 2 | | |
| INDEX UNIQUE SCAN |I_ATTRCOL | 1 | | 1 | | |
| FILTER | | | | | | |
| HASH JOIN RIGHT OUTER | | 72K| 188M| 2387 | | |
| TABLE ACCESS FULL |USER$ | 59 | 118K| 3 | | |
| HASH JOIN RIGHT OUTER | | 72K| 45M| 2384 | | |
| TABLE ACCESS FULL |OBJ$ | 583 | 30K| 205 | | |
| HASH JOIN RIGHT OUTER | | 72K| 41M| 2178 | | |
| TABLE ACCESS FULL |COLTYPE$ | 2K| 174K| 275 | | |
| HASH JOIN | | 72K| 37M| 1902 | | |
| TABLE ACCESS FULL |USER$ | 59 | 1K| 3 | | |
| HASH JOIN | | 72K| 35M| 1899 | | |
| INDEX FAST FULL SCA|I_USER2 | 59 | 2K| 2 | | |
| HASH JOIN RIGHT OUT| | 72K| 31M| 1896 | | |
| TABLE ACCESS FULL |HIST_HEAD | 16K| 2M| 70 | | |
| NESTED LOOPS | | 72K| 19M| 660 | | |
| TABLE ACCESS FULL|OBJ$ | 47K| 3M| 205 | | |
| TABLE ACCESS CLUS|COL$ | 2 | 404 | 1 | | |
| INDEX UNIQUE SCA|I_OBJ# | 1 | | 0 | | |
| TABLE ACCESS CLUSTER |TAB$ | 1 | 26 | 2 | | |
| INDEX UNIQUE SCAN |I_OBJ# | 1 | | 1 | | |
| NESTED LOOPS | | 1 | 78 | 3 | | |
| INDEX RANGE SCAN |I_OBJ4 | 1 | 39 | 2 | | |
| INDEX RANGE SCAN |I_USER2 | 1 | 39 | 1 | | |
--------------------------------------------------------------------------------
