Oracle Scratchpad

January 17, 2013

dbms_xplan bug

Filed under: Bugs,dbms_xplan,Oracle — Jonathan Lewis @ 6:22 pm BST Jan 17,2013

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

1 Comment »

  1. […] was applied. Generally this isn’t too difficult, although subquery pushing (and a couple of presentation bugs relating to other scalar subqueries) can cause a little confusion; and the difference between join […]

    Pingback by Parallel Execution – 1 | Oracle Scratchpad — October 13, 2013 @ 7:43 pm BST Oct 13,2013 | Reply


RSS feed for comments on this post. TrackBack URI

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

The Rubric Theme Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 3,453 other followers