Search

OakieTags

Who's online

There are currently 0 users and 26 guests online.

Recent comments

Oakies Blog Aggregator

Enkitec E4 2013

The 2nd annual Enkitec Extreme Exadata Expo (E4) is coming your way August 5-6, 2013 at the Four Seasons Hotel & Resort in Irving, TX. Last year's inaugural event received rave reviews from participants and this year's event should be even better!

After the conference, stick around and join me for a 3-day SQL/Exadata Performance Intensive course. I'll be covering how to approach optimizing SQL in both Exadata and non-Exadata environments. We'll start with some fundamentals that apply to how to approach tuning SQL in general and then look at how your focus needs to shift to take advantage of Exadata specific features.

Find and follow Enkitec in your favorite social media outlet to keep up with E4 news and lots more.

Hope to see you in August!

Japanese translation of some of my blog articles

Ryota Watabe has translated some of my articles into Japanese and intends to translate more in the future (thanks for that! :)

I have added a little “translations” section to the left sidebar of the blog (scroll down). Or just go to this link:

You might also want to follow Watabe-san on Twitter to get notified of any new Japanese translations that show up.


If you want something done, ask a busy person…

New post on the ODTUG Board page…

dbms_xplan bug

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

Fedora 18 : Upgrading from Fedora 17…

I’ve just got to the end of a real upgrade of a Fedora 17 server to Fedora 18. The basic process goes like this.

  • Download the Fedora 18 ISO.
  • Update your current Fedora 17 system by issuing the “yum update” command and restart once it is complete.
  • Install the “fedup” package. “yum –enablerepo=updates-testing install fedup”
  • Run the fedup command pointing it to the Fedora 18 ISO you downloaded. “fedup-cli –iso /home/user/fedora-18.iso –debuglog=fedupdebug.log”
  • Check for errors in the log and correct if found.
  • Reboot the machine and select the “System Upgrade” option from the Grub menu.
  • Wait!

The system came up OK after this, but there are some gotchas. The first thing I did on completion was to run a “yum update” and lots of things were broken. Why? Well, after a lot of messing around and manually updating individual packages I finally figured out:

  1. Google Chrome, Skype and VirtualBox were all holding on to F17 versions of packages so yum was not able to update them.
  2. I also had a bunch of packages from the RPM Fusion repository that seemed to confuse things. If you are using that repository I suggest you manually update it using these commands.

    wget http://download1.rpmfusion.org/free/fedora/rpmfusion-free-release-18.noarch.rpm
    rpm -Uvh rpmfusion-free-release-18.noarch.rpm

  3. The dropbox repository is failing, so add “enabled=0″ to the “/etc/yum.repos.d/dropbox.repo” file.

So IMHO, when you are going to do an upgrade, I would advise to remove any packages that didn’t come from the Fedora repository before starting, or be prepared to spend a few hours cleaning up afterwards. If I had stuck to just packages from the Fedora repository I think the upgrade would have been seamless. As it is, it was a bit of a pain, but now it’s all good.

I think I will have a little rest before trying another. :)

Cheers

Tim…

Update: I just did an upgrade to another machine. By taking my own advice and cleaning up first it was really easy. :)


Fedora 18 : Upgrading from Fedora 17… was first posted on January 17, 2013 at 6:27 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

Fedora 18 and Oracle 11gR2…

After several abortive attempts I finally got hold of Fedora 18 last night. Those mirrors are getting a real battering at the moment. :)

The first job was to do a basic installation.

I’d seen a few things written about the new installer, not all of which were positive. IMHO the installation was a really nice experience. It is very different to previous installers, which probably freaks some people out, but I think it works really well.

Once the installation is complete it’s pretty much business as usual. You’ve got newer versions of most of the packages, but to be honest I don’t really focus to much on that. I just keep doing “yum update” every few days and work with what I’ve got. :)

One thing that did take me by surprise is how much a hated GNOME Shell. I’ve written several times in that past that I thought it was OK. It was my default environment for quite a few months, but after spending an extended period on XFCE at home and Windows XP at work, I can safely say that I seriously don’t get GNOME Shell. Fortunately, Fedora 18 comes with a whole host of window managers including GNOME, KDE, XFCE, LXDE, Cinnamon and MATE, so I’m sure you’ll find something there that you can work with.

The next job was to see if Oracle would install on it.

Every time I do this I think to myself it’s going to be the last time. It’s far more sensible to use a VM and install the database on a supported distro. Oracle Linux is free and a supported platform, so messing around with installs on a non-supported distro, like Fedora, really doesn’t make sense. Maybe this will actually be the last time. :)

So what next? I’ve got some physical servers running Fedora 17 as the host OS which will have to be upgraded. I think I’m going to play around with Cinnamon and MATE before I do that. If they don’t work for me it’s back to plain and simple XFCE for my window manager. :)

Cheers

Tim…


Fedora 18 and Oracle 11gR2… was first posted on January 17, 2013 at 1:58 pm.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.

What the heck is the INTERNAL_FUNCTION in execution plan predicate section?

Sometimes you see something like this in an execution plan:

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    22 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("B"=INTERNAL_FUNCTION("A"))

There’s quite a little information available about what the INTERNAL_FUNCTION really is and why does it show up, thus this blog entry.

There’s actually no function called INTERNAL_FUNCTION in Oracle and this is (partially) confirmed also by querying the V$SQLFN_METADATA view:

SQL> @sqlfn %internal%

no rows selected

The common understanding, coming from Oracle documentation is that the INTERNAL_FUNCTION is some sort of a special function doing (implicit) datatype conversion. This is only partially true and not the whole truth – but let’s examine the datatype conversion first and proceed to the explanation and other examples later on.

Implicit datatype conversion

I’m creating a table which stores a date in VARCHAR2 datatype in column A and a date in DATE datatype in column B:

SQL> CREATE TABLE t(a VARCHAR2(20), b DATE);

Table created.

SQL> @desc t
           Name                            Null?    Type
           ------------------------------- -------- ----------------------------
    1      A                                        VARCHAR2(20)
    2      B                                        DATE

SQL> INSERT INTO t VALUES( TO_CHAR(sysdate), sysdate) ;

1 row created.

Now let’s run a simple select query and see its execution plan:

SQL> SELECT * FROM t WHERE a = b;

A                    B
-------------------- -----------------
20130116 17:41:49    20130116 17:41:49

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

--------------------------------------------------------------------------
| Id  | Operation         | Name | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------
|   0 | SELECT STATEMENT  |      |       |       |     2 (100)|          |
|*  1 |  TABLE ACCESS FULL| T    |     1 |    21 |     2   (0)| 00:00:01 |
--------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------
   1 - filter("B"=INTERNAL_FUNCTION("A"))

What happens here is that Oracle is forced to (implicitly) add a datatype conversion function around column A, to be able to physically compare two different datatypes. Internally Oracle is not running a comparison "WHERE a = b" anymore, but rather something like "WHERE TO_DATE(a) = b". This is one of the reasons why the INTERNAL_FUNCTION shows up – the code generating the human-readable execution plan from the actual “binary” execution plan is not able to convert the internal opcode to a corresponding human-readable function name, thus shows a default “INTERNAL_FUNCTION” string there instead.

This is Oracle 10g+ behavior, in 9i and before, Oracle just printed nothing as that function name, example output from 9i is below:

1 - filter(("DUAL"."DUMMY")=:TEST_VAR)

Do you see that there are seemingly unnecessary brackets around “DUAL”.”DUMMY” above? Why not just "DUAL.DUMMY"=:TEST_VAR, is this ("DUAL"."DUMMY"):=TEST_VAR usage some typo? It’s actually not a typo, before Oracle 10g you had to be careful to spot any such “unneccesary” brackets as they really indicated there was some function called, something like F(“DUAL”.”DUMMY”), only that the “F” was never printed. In Oracle 10g onwards, a generic “INTERNAL_FUNCTION” is printed instead of nothing in such cases – at least we know there’s some function applied to the column/variable. You need to look into the code (and possibly session-level NLS_ settings) to figure out what function it could logically be (like a function casting TIMESTAMP into a DATE when comparing these datatypes).

Un-unparseable Complex Expressions

Ok, the datatype conversion reason is already covered in Oracle docs and blogosphere. But there’s more.

Check out this example with an OR clause:

SQL> CREATE TABLE t AS SELECT * FROM dba_objects;

Table created.

SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM';

  COUNT(*)
----------
     32272

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 36652 |   608K|   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter(("OWNER"='SYS' OR "OWNER"='SYSTEM'))

All is fine so far – the DBMS_XPLAN.DISPLAY_CURSOR function, which reads the plans directly from library cache is able to explain the predicate correctly.

Now let’s make the predicate a little more complex, I will add another OR to this predicate, but it’s against a different column:

SQL> SELECT COUNT(*) FROM t WHERE owner = 'SYS' OR owner = 'SYSTEM' OR #ff0000;">object_id = 123;
...
SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    30 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 36652 |  1073K|   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter((INTERNAL_FUNCTION("OWNER") OR "OBJECT_ID"=123))

Now, suddenly the two conditions on the OWNER table are gone and replaced by an INTERNAL_FUNCTION?

Let’s try an IN operator instead of the OR, but wait, we are checking for values in two different columns (so we can’t put them all into the same IN clause):

SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';

  COUNT(*)
----------
      1178

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   374 | 10472 |   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter((INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE'))

Still no luck.
Let’s try a logically simpler operation, with just searching for 3 values in the same column:

SQL> SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT');

  COUNT(*)
----------
     32278

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |       |       |   293 (100)|          |
|   1 |  SORT AGGREGATE    |      |     1 |    17 |            |          |
|*  2 |   TABLE ACCESS FULL| T    | 31960 |   530K|   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM'))

Works! Oracle has transformed (or at least shows it in the explanation) this IN predicate to a bunch of OR-ed conditions (against the same column).

You might already see what’s going on with the earlier examples – DBMS_XPLAN.DISPLAY_CURSOR is not able to explain “complex” composite predicates applied in a single execution plan step, which include multiple different columns AND at least one of the columns has multiple values to check for (like an in-list or OR-ed predicates).

Where does DISPLAY_CURSOR get its data from and an explanation

DBMS_XPLAN.DISPLAY_CURSOR gets its plan data from V$SQL_PLAN, the predicate section comes from ACCESS_PREDICATES and FILTER_PREDICATES columns. But when I query the V$SQL_PLAN directly, I still see the same problem:

SQL> SELECT id, filter_predicates FROM v$sql_plan WHERE sql_id = 'gcqgrmtna9g1u';

        ID FILTER_PREDICATES
---------- ------------------------------------------------------------
         0
         1
         2 (INTERNAL_FUNCTION("OWNER") AND "OBJECT_TYPE"='TABLE')

And you may have noticed that there are brackets() around the raw ORed conditions above too, which in 9i meant that there was an “unexplained” internal function in the “binary” execution plan around the reported predicates, but in this case (as 10g+ supports the internal_function naming), blank function names shouldn’t occur… Not really sure why there about this, but that’s too low level little detail for this post.

The V$SQL_PLAN view itself accesses the actual “binary” child cursor in library cache (after taking appropriate latches/pins/mutexes) and UNPARSES it. Why such term – well isn’t parsing something that takes a human readable input and translates it into computer-understandable “binary” format. Thus unparsing is the opposite – V$SQL_PLAN accesses the cursor’s “binary” execution plan memory structure and translates it to human-readable execution plan output. There’s even a parameter controlling this V$SQL_PLAN behavior, if it’s set to false, the ACCESS_PREDICATES and FILTER_PREDICATES columns will be empty there:

SQL> @pd unparse
Show all parameters and session values from x$ksppi/x$ksppcv...

NAME                             VALUE                                      DESCRIPTION
----------------------------- --------- -----------------------------------------------
_cursor_plan_unparse_enabled      TRUE          enables/disables using unparse to build
                                                                  projection/predicates

By the way, why do I keep saying “binary” execution plan and in double quotes? It’s because I want to emphasize that the real execution plan that Oracle executes is not in the text form like we see on the screen, the text is just generated for humans, for troubleshooting reasons. The execution plan is not a real executable binary (as in oracle.exe) either, it’s not directly fed to the CPUs for execution. The physical execution plan in the library cache child cursor is a bunch of opcodes, object_ids and pointers for defining the hierarchy and order of rowsource execution. It’s the SQL execution engine, which then loops through these opcodes, decodes them and knows what to do (which rowsource function to call) next.

So, as we’ve seen above, some predicates with complex AND/OR conditions chained together are displayed as INTERNAL_FUNCTION() by DBMS_XPLAN.DISPLAY_CURSOR and V$SQL_PLAN as they are unable to decode (unparse) the execution plan info fully.

Using the good old EXPLAIN PLAN

There’s some good news though! The good old EXPLAIN PLAN command can unparse (some of) these complex predicates properly! As the EXPLAIN PLAN operation parses the given SQL again, in a special, more instrumented way, it has more information at hand apparently (and it uses more memory too). Or it could just be that whoever wrote V$SQL_PLAN, didn’t write the piece of code for unparsing more complex predicates :)

Check this output, where I’m using the explain plan command instead of just running the SQL:

SQL> EXPLAIN PLAN FOR 
     SELECT COUNT(*) FROM t WHERE owner IN ('SYS','SYSTEM','SCOTT') AND object_type = 'TABLE';

Explained.

SQL> SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

---------------------------------------------------------------------------
| Id  | Operation          | Name | Rows  | Bytes | Cost (%CPU)| Time     |
---------------------------------------------------------------------------
|   0 | SELECT STATEMENT   |      |     1 |    28 |   293   (1)| 00:00:04 |
|   1 |  SORT AGGREGATE    |      |     1 |    28 |            |          |
|*  2 |   TABLE ACCESS FULL| T    |   374 | 10472 |   293   (1)| 00:00:04 |
---------------------------------------------------------------------------

   2 - filter(("OWNER"='SCOTT' OR "OWNER"='SYS' OR "OWNER"='SYSTEM')
              AND "OBJECT_TYPE"='TABLE')

It’s a miracle! The INTERNAL_FUNCTION is gone and all the predicate values are shown correctly. EXPLAIN PLAN command was very useful here.

So, while I usually don’t use the EXPLAIN PLAN command as explain plan can lie to you, then whenever I see an INTERNAL_FUNCTION in the DISPLAY_CURSOR/V$SQL_PLAN/SQL Monitor output, I run an explain plan command for the same query in hope of quickly finding out what the predicates in there really are.

Ok, it’s 2:30am here yet again – off to sleep! :)


Delphix Live webcast: Database Virtualization

Live webcast: Realize Massive ROI with Database Virtualization
Date: Wednesday Jan 30, 2013 @12pm ET/9am PT
Click Here to Register

If you could create as many copies as you wanted of production databases for development, reporting and QA, how many would you create? 10,20, a 100? It is possible in the space of a single copy of the production database using thin provision cloning. Thin provision cloning give enormous disk savings by sharing the majority of source database data blocks. Thin provision cloning is one technology of database virtualization. Database virtualization goes beyond thin provision cloning technology to provide agile corporate data management.

Virtual databases can be created, refreshed, rolled back, rolled forward and deleted in seconds. Virtual databases can be provisioned from any second within the source databases retention window which is typically several weeks. Every developer can have their own full copy of production databases, production databases can have 50 days of backup live online in the space of one backup. Backups can be brought online in seconds, data reviewed or extracted and the copy removed in seconds. QA teams can go from one test environment to instead having multiple full copies of production database allowing running QA tests in parallel.

Delphix eliminates widespread IT inefficiencies caused by dragging behind enormous amounts of infrastructure, process and bureaucracy  required to provide database copies.  Delphix eliminates the drag and provides power through agile data management software and database virtualization. Join this session to learn how organizations like Deutsche Bank, Proctor and Gamble, Facebook, EA, Stubhub and many more are realizing significant returns with Delphix, including:

 

  • Greater agility: 500% greater application project output
  • Lower risk: 50% higher error detection in development
  • Reduced costs: 90% reduction in storage cost for copies and backups 

Join us on Jan 30 @12PM ET to learn more. Click Here to Register

 

Oracle 11gR2 RAC Installation on Oracle Linux 5

As promised in a recent post, I’ve updated the Oracle 11gR2 RAC on Oracle Linux 5 article. It now uses VirtualBox 4.2.6, rather than 3.2.8 as it was before, and Oracle Linux 5.8.

I’ve purposely left it as an 11.2.0.1 installation as you can get this from OTN without needing access to My Oracle Support (MOS). The process works just as well for 11.2.0.3 and I would recommend you use that if you do have access to MOS. Remember, if you are doing the RAC installation on Oracle Linux 6 you are going to need 11.2.0.3, so OL5 might be the right option if you are playing around with this at home with no access to MOS.

Cheers

Tim…


Oracle 11gR2 RAC Installation on Oracle Linux 5 was first posted on January 16, 2013 at 12:41 am.
©2012 "The ORACLE-BASE Blog". Use of this feed is for personal non-commercial use only. If you are not reading this article in your feed reader, then the site is guilty of copyright infringement.