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 188.8.131.52 gave different results – and the 184.108.40.206 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 220.127.116.11 ======== 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).
I’ve said in the past that one of the best new features, in my view, in 11g was the appearance of proper virtual columns; and I’ve also been very keen on the new “approximate NDV” that makes it viable to collect stats with the “auto_sample_size”.
Who’d have guessed that if you put them both together, then ran a parallel stats collection it would break
The bug number Karen quotes (10013177.8) doesn’t (appear to) mention extended stats – but since virtual columns, function-based indexes, and extended stats share a number of implementation details I’d guess that they might be affected as well.
I got a mail from long time Oracle guy Joel Garry regarding the twitter widget in the right hand column.
On orawin.info, you have a twitter feed, where each entry has a date/time link on it. But those links look like http://twitter.com/nlitchfield/statuses/8.0773403426E+16 which look to me like something is translating a big number to scientific notation…?
Well Joel was right. Something was translating a large number to scientific notation. That something turns out to be php itself. Twitter status updates are (apparently) a simple ever increasing integer. There are now a lot of twitter status updates. As this page shows recent versions of php will automatically display that in scientific notation unless explicitly told otherwise. Now I personally think that this is an odd thing for php to choose to do, but us database folks surely recognize the folly of the plugin programmers relying on default formats. Anyway my version of the plugin is now fixed – based on this forum post - and thanks to Joel for the heads up. Anyone reading this who programs in php against databases that might return large numbers might wish to reveiw their web pages for appropriate results.
Just a quick note for anyone who has missed the Oracle Security alerts email. If you downloaded either Enterprise Manager 11g or Oracle Database 18.104.22.168 before November 17th last year then you downloaded a version of OUI that sent unencrypted passwords for your oracle.com SSO account to Oracle over the intertubes. Not Good. Oracle have [...]