Oracle Scratchpad

August 3, 2011

Trouble-shooting

Filed under: Bugs,Oracle,Troubleshooting — Jonathan Lewis @ 5:37 pm BST Aug 3,2011

How do you trouble-shoot a problem ? It’s not an easy question to answer when posed in this generic fashion; but perhaps it’s possible to help people trouble-shoot by doing some examples in front of them. (This is why I’ve got so many statspack/AWR examples – just reading a collection of different problems helps you to get into the right mental habit.)

So here’s a problem someone sent me yesterday. Since it only took a few seconds to read, and included a complete build for a test case, with results, and since it clearly displayed an Oracle bug, I took a look at it. (I’ve trimmed the test a little bit, there were a few more queries leading up to the error):


create table person (id number(2), name varchar2(10)) ;

insert into person values (1, 'Alpha') ;
insert into person values (2, 'Bravo') ;
insert into person values (3, 'Charlie') ;
insert into person values (4, 'Charles') ;
insert into person values (5, 'Delta') ;

create or replace view vtest as
select id, 'C' as letter from person where name like 'C%' ;

select p.id, p.name, v.id, v.letter
from person p
left join vtest v on v.id = p.id
order by p.id ;

The problem was that 10.2.0.4 and 11.2.0.2 gave different results – and the 11.2.0.2 result was clearly wrong. So the question was: “is there something broken with outer joins on views, or possibly ANSI outer joins?” (The ansswer to the last question is always “probably” as far as I’m concerned, but I wouldn’t turn that into a “yes” without checking first.) Here are the two results:

10.2.0.4:
========
        ID NAME               ID L
---------- ---------- ---------- -
         1 Alpha
         2 Bravo
         3 Charlie             3 C
         4 Charles             4 C
         5 Delta

11.2.0.2
========
        ID NAME               ID L
---------- ---------- ---------- -
         1 Alpha                 C
         2 Bravo                 C
         3 Charlie             3 C
         4 Charles             4 C
         5 Delta                 C

Clearly the extra ‘C’s in the letter column are wrong.

So what to do next ? Knowing that Oracle transforms ANSI SQL before evaluating an execution plan I decided to run the 10053 trace. Sometimes you get lucky and see the “unparsed SQL” in this trace file, a representation (though not necessarily 100% exact) image of the statement for which Oracle will generate a plan. I was lucky, this was the unparsed SQL (cosmetically enhanced):


SELECT
	P.ID ID,
	P.NAME NAME,
	PERSON.ID ID,
	CASE  WHEN PERSON.ROWID IS NOT NULL THEN 'C' ELSE NULL END  LETTER
FROM
	TEST_USER.PERSON P,
	TEST_USER.PERSON PERSON
WHERE
	PERSON.ID  (+) = P.ID
AND	PERSON.NAME(+) LIKE 'C%'
ORDER BY
	P.ID
;

So I ran this query, and found that the same error appeared – so it wasn’t about ANSI or views. So possibly it’s something about the CASE statement and/or the ROWID in the CASE statement, which I tested by adding three extra columns to the query:

        person.name,
        person.rowid,
        CASE  WHEN PERSON.name IS NOT NULL THEN 'C' ELSE NULL END  LETTER

With these extra columns I got the following results from the query:

        ID NAME               ID NAME       ROWID              L L
---------- ---------- ---------- ---------- ------------------ - -
         1 Alpha                                               C
         2 Bravo                                               C
         3 Charlie             3 Charlie    AAAT7gAAEAAAAIjAAC C C
         4 Charles             4 Charles    AAAT7gAAEAAAAIjAAD C C
         5 Delta                                               C

So the CASE did the right thing with the person.name column, but the wrong thing with the person.rowid column.
Time to get onto MOS (Metalink).

I searched the bug database with the key words: case rowid null
This gave me 2,887 hits, so I added the expression (with the double quotes in place) “outer join”
This gave me 110 hits, so from the “product category” I pick “Oracle Database Products”
This gave me 80 hits, and the first one on the list was:

Bug 10269193: WRONG RESULTS WITH OUTER JOIN AND CASE EXPRESSION OPTIMIZATION CONTAINING ROWID

The text matched my problem, so job done – except it’s reported as not fixed until 12.1

This isn’t a nice bug, of course, because the particular problem can be generated automatically in the transformation of ANSI outer joins to Oracle outer joins, so you can’t just change the code.

In passing, it’s taken me 31 minutes to write this note – that’s 10 minutes longer than it took to pin down the bug, but I have to say I got lucky on two counts: first, that the “unparsed SQL” was available, second that my choice of key words for MOS got me to the bug so quickly (which is where I usually find I waste most time).

9 Comments »

  1. Jonathan, Really it’s interesting test case, but I am surprized it was working fine in 11.2.0.1

    SQL> create table person (id number(2), name varchar2(10)) ;

    Table created.

    SQL>
    SQL> insert into person values (1, ‘Alpha’) ;

    1 row created.

    SQL> insert into person values (2, ‘Bravo’) ;

    1 row created.

    SQL> insert into person values (3, ‘Charlie’) ;

    1 row created.

    SQL> insert into person values (4, ‘Charles’) ;

    1 row created.

    SQL> insert into person values (5, ‘Delta’) ;

    1 row created.

    SQL> commit;

    Commit complete.

    SQL> create or replace view vtest as
    2 select id, ‘C’ as letter from person where name like ‘C%’ ;

    View created.

    SQL>
    SQL> select p.id, p.name, v.id, v.letter
    2 from person p
    3 left join vtest v on v.id = p.id
    4 order by p.id ;

    ID NAME ID L
    ———- ———- ———- –
    1 Alpha
    2 Bravo
    3 Charlie 3 C
    4 Charles 4 C
    5 Delta

    SQL> select * from v$version;

    BANNER
    ———————————————————————-
    Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 – Production
    PL/SQL Release 11.2.0.1.0 – Production
    CORE 11.2.0.1.0 Production
    TNS for 32-bit Windows: Version 11.2.0.1.0 – Production
    NLSRTL Version 11.2.0.1.0 – Production

    Sir, correct me any thing missing from my end. I will try to test it on version which you mentioned across, let me cross check across.

    Comment by Pavan Kumar Kumar — August 3, 2011 @ 6:13 pm BST Aug 3,2011 | Reply

  2. Jonathan ,

    It was fixed in 11.2.0.2 ( patchset 8 for Windows ) .
    Bug no ( 10269193 ) listed in the 2nd line from the bottom of the “Bugs Fixed”

    SQL> Select * from v$version;
    
    BANNER
    --------------------------------------------------------------------------------
    Oracle Database 11g Enterprise Edition Release 11.2.0.2.0 - 64bit Production
    PL/SQL Release 11.2.0.2.0 - Production
    CORE    11.2.0.2.0      Production
    TNS for 64-bit Windows: Version 11.2.0.2.0 - Production
    NLSRTL Version 11.2.0.2.0 - Production
    
    SQL> create table person (id number(2), name varchar2(10)) ;
    
    Table created.
    
    SQL>
    SQL> insert into person values (1, 'Alpha') ;
    
    1 row created.
    
    SQL> insert into person values (2, 'Bravo') ;
    
    1 row created.
    
    SQL> insert into person values (3, 'Charlie') ;
    
    1 row created.
    
    SQL> insert into person values (4, 'Charles') ;
    
    1 row created.
    
    SQL> insert into person values (5, 'Delta') ;
    
    1 row created.
    
    SQL>
    SQL> create or replace view vtest as
      2  select id, 'C' as letter from person where name like 'C%' ;
    
    View created.
    
    SQL>
    SQL> select p.id, p.name, v.id, v.letter
      2  from person p
      3  left join vtest v on v.id = p.id
      4  order by p.id ;
    
            ID NAME               ID L
    ---------- ---------- ---------- -
             1 Alpha
             2 Bravo
             3 Charlie             3 C
             4 Charles             4 C
             5 Delta
    
    SQL>
    
    Installed Top-level Products (1):
    
    Oracle Database 11g                                                  11.2.0.2.0
    There are 1 products installed in this Oracle Home.
    
    
    Interim patches (1) :
    
    Patch  12714463     : applied on Wed Jul 20 11:13:02 EDT 2011
    Unique Patch ID:  13923186
       Created on 18 Jul 2011, 22:05:04 hrs PST8PDT
       Bugs fixed:
         12714463, 9572787, 12431716, 10278372, 1170769, 11695285, 9078442
         10157249, 9897335, 9936659, 12544032, 11740670, 11883804, 12332340
         10165223, 9589791, 12579349, 8532430, 10621549, 9073910, 10371288
         12345717, 11074393, 9916260, 11797217, 11683713, 9913542, 10365159
         10620808, 11801609, 9285259, 12591120, 11733205, 11727498, 10625485
         10239480, 11814891, 10284570, 11788856, 11927239, 10149919, 10420891
         9943960, 12340808, 11876260, 12400751, 11939823, 9859425, 10114391
         9823660, 10235640, 12398567, 11789566, 11804097, 12529945, 12429531
         10222321, 12421207, 12363485, 10399808, 11839701, 10362871, 11863940
         11892888, 10372924, 10032414, 9935787, 12362914, 10241251, 10422126
         11055942, 11906764, 10091171, 10279045, 11933135, 9764806, 10394335
         8223165, 9735282, 9748749, 10053725, 10061015, 10126094, 10233732
         10324526, 10636231, 11651810, 9939306, 10361177, 11069614, 9953542
         11800170, 10648873, 10419987, 11682409, 11812615, 11899801, 9869401
         11674485, 6892311, 10155605, 10310299, 10324294, 10356782, 10367188
         10621169, 11065646, 11707302, 11800854, 9885553, 12410846, 10013177
         10094732, 10142788, 10155684, 10193846, 10233062, 10261389, 10390344
         10622001, 11695416, 11730047, 11804055, 11831371, 11853331, 11938301
         12313857, 12341758, 6055658, 9500046, 9825461, 9926929, 11706582
         10211294, 10259201, 10043886, 11742935, 11781669, 11743861, 11790220
         9925648, 12344273, 10287894, 10051315, 12326246, 11778458, 11732203
         11855398, 10040921, 10094823, 9457109, 10194490, 10249791, 10236704
         9871302, 10237773, 10144882, 10154072, 11782883, 11930680, 11934878
         9978195, 9842573, 10055063, 11686456, 9956835, 10156303, 10159846
         9578533, 11742094, 11775474, 12318560, 11896292, 10019002, 10094416
         10132870, 10140809, 10190642, 10222184, 10246472, 10269503, 10295754
         10360823, 10368698, 10400142, 10422748, 10625566, 10627301, 11067567
         11660032, 11666959, 11676888, 11677026, 11714159, 11723722, 11772687
         11818335, 9215461, 9401552, 9620994, 9716877, 9742032, 9776940, 9824435
         9866728, 9903704, 9910484, 12320743, 10211260, 11731184, 10018789
         10040552, 10052141, 10052956, 10084145, 10102506, 10129643, 10161774
         10187168, 10205230, 10218814, 10219576, 10220194, 10222719, 10227288
         10228151, 10230571, 10245086, 10248178, 10264680, 10299224, 10317487
         10329146, 10332111, 10332589, 10356513, 10358019, 10360592, 10373381
         10378005, 10383833, 10388142, 10388660, 10401327, 10407744, 10411618
         10419629, 11069199, 11076894, 11664046, 11695060, 11699057, 11699884
         6523037, 7365514, 9309735, 9591812, 9724970, 9727147, 9860769, 9877980
         10637621, 11669995, 10030675, 9905049, 9868876, 9679507, 10329257
         10259620, 9919654, 10121968, 10143503, 10215431, 10242303, 10258337
         9443361, 10054513, 10420027, 10080167, 10220046, 9826065, 10412247
         10157313, 10175192, 10158493, 9906422, 10082277, 10015652, 10130633
         8331063, 10150497, 10147873, 10169701, 10211776, 10276515, 10366094
         10084931, 10160615, 9255996, 9930649, 10201938, 11660635, 10640392
         10432053, 10151017, 10074437, 9788588, 10232225, 9735237, 9744252
         10232083, 9956713, 9715581, 9770451, 10302581, 10170431, 10079168
         10166489, 10013431, 10039318, 9436324, 10238786, 10217802, 9707965
         10220058, 9930315, 10134677, 9668086, 10213073, 10080579, 10110863
         10058195, 10073683, 10077191, 10019218, 10229719, 9539440, 6904068
         10022980, 10209232, 10213940, 8507266, 9881076, 10269193, 10327241
         9438890, 10141971, 10157402, 10040531, 9651350, 10149223
    
    
    
    --------------------------------------------------------------------------------
    
    OPatch succeeded.
    

    Comment by Zahir Mohideen — August 3, 2011 @ 8:13 pm BST Aug 3,2011 | Reply

  3. This bug is fixed and is backported already to 11.2.0.2.
    Linux x86-64 fix is available here http://aru.us.oracle.com:8080/ARU/ViewPatchRequest/process_form?aru=13290641
    Other ports are also available.

    Comment by Greg Rahn — August 3, 2011 @ 8:13 pm BST Aug 3,2011 | Reply

  4. Greg, Zahir,

    Thanks for the update – once I’d found the note with it’s “fixed in 12.1” tag I didn’t carry on looking for patches. I should have realised that there might have been a backport or two.

    Comment by Jonathan Lewis — August 3, 2011 @ 8:45 pm BST Aug 3,2011 | Reply

    • Bugs can never be “fixed in” a shipped release or patch set — they are always fixed in the current “main” code branch, then backported if possible.

      Comment by Greg Rahn — August 4, 2011 @ 1:35 pm BST Aug 4,2011 | Reply

  5. This is a very interesting article.
    You make a good point about systematically analysing and trouble-shooting (Tanel will be pleased).
    I didn’t know that Oracle actually interprets ANSI SQL into Oracle SQL.
    I thought that both SQL standards were directly interpreted into the operations that Oracle performs.
    Your article has now put a slight kibosh on my use of ANSI SQL in Oracle for fear of suffering future problems. How would you know you’re seeing the right results in a complex statement? A trust issue has been created, in my eyes.

    Comment by Darryl Griffiths — August 4, 2011 @ 2:20 pm BST Aug 4,2011 | Reply

    • Darryl,

      I wouldn’t let the fact that ANSI (usually) has to be transformed to “Oracle” SQL before anything else happens – there’s so much transformation available for ordinary Oracle SQL that a little bit more probably won’t make much difference to correctness. And to respond to your worry about right results – I first asked that question way back in 7.2 days when it because possible to run 10 second queries that summed millions of rows: how do you know the answer’s right: take a look at the “Wrong Results” section of an patch release and look how many non-ANSI bugs there are that can get you the wrong answer.

      If you want an argument against ANSI in Oracle, the one that gets to me is the one about hinting: https://jonathanlewis.wordpress.com/2010/12/03/ansi-argh/

      Comment by Jonathan Lewis — August 6, 2011 @ 8:46 am BST Aug 6,2011 | Reply

  6. […] premier Oracle Expert Jonathan Lewis blogs about not-so-easy question; How do you trouble-shoot a […]

    Pingback by Log Buffer #232, A Carnival of the Vanities for DBAs | The Pythian Blog — August 5, 2011 @ 3:01 pm BST Aug 5,2011 | 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

Blog at WordPress.com.

Follow

Get every new post delivered to your Inbox.

Join 6,668 other followers