bugs

Jonathan Lewis's picture

Upgrades again

I’ve just spent a couple of days in Switzerland presenting seminar material to an IT company based in Delemont (about 50 minutes drive from Basle), and during the course I picked up a number of new stories about interesting things that have gone wrong at client sites. Here’s one that might be of particular interest to people thinking of upgrading from 10g to 11g – even if you don’t hit the bug described in the blog, the fact that the new feature has been implemented may leave you wondering where all your machine resources are going during the overnight run.

Jonathan Lewis's picture

Subquery Factoring

Here’s an interesting little conundrum about subquery factoring that hasn’t changed in the recent (11.2.0.3) patch for subquery factoring. It came to me from Jared Still (a fellow member of Oak Table Network) shortly after I’d made some comments about the patch. It’s an example based on the scott/tiger schema – which I’ve extracted from the script $ORACLE_HOME/rdbms/admin/utlsampl.sql (though the relevant scripts may be demobld.sql or scott.sql, depending on version).

As usual I’ve used an 8KB block size, LMT with uniform 1MB extents, and no ASSM to hold the data. I won’t reproduce the code to generate the schema, just the versions of the query with, then without, subquery factoring:

Jonathan Lewis's picture

Bug fixes

From MOS (Metalink) a search for “Patch Set – List of Bug Fixes by Problem” is a useful search, andother is “Availability and Known Issues”. Whenever you find some behaviour that looks like a bug, it’s worth checking the patch sets for the patches or release that are newer than the version that you’re running – you may find that your problem is a known bug with a patch that might be back-ported.

For ease of reference, here are some of the results I got (sorted in reverse order of version) from the searches; you will need a MOS account to follow the links:

Jonathan Lewis's picture

Index size bug

Here’s a follow-up to a post I did some time ago about estimating the size of an index before you create it. The note describes dbms_stats.create_index_cost() procedure, and how it depends on the results of a call to explain plan. A recent question on the OTN database forum highlighted a bug in explain plan, however, which I can demonstrate very easily. I’ll start with a small amount of data to demonstrate the basic content that is used to calculate the index cost.

Jonathan Lewis's picture

FBI Bug

Here’s a funny little optimizer bug – though one that seems to have been fixed by at least 10.2.0.3. It showed up earlier on today in a thread on the OTN database forum. We’ll start (in 9.2.0.8) with a little table and two indexes – one normal, the other descending.

Jonathan Lewis's picture

Correlation oddity

This one’s so odd I nearly posted it as a “Quiz Night” – but decided that it would be friendlier simply to demonstrate it. Here’s a simple script to create a couple of identical tables. It’s using my standard environment but, apart from fiddling with optimizer settings, I doubt if there’s any reason why you need to worry too much about getting the environment exactly right.

Niall Litchfield's picture

New Defaults, Old Side Effects

When 11.2 came out I posted about deferred segment creation at http://orawin.info/blog/2010/04/25/new-features-new-defaults-new-side-effects/ and a couple of odd side effects. Oracle published a Note  1050193.1 that makes the quite extraordinary claim that Sequences are not guaranteed to generate all consecutive values starting with the ‘START WITH’ value. It’s absolutely true that sequences don’t guarantee no gaps – but [...]

Jonathan Lewis's picture

System Stats

A quick collation – and warning – for 11.2

Bottom line – be careful about what you do with system stats on 11.2

Footnote: the MOS link is a search string  producing a list of references. I set it up like that because one of the articles referencing the bug is called “Things to consider before upgrade to 11.2.0.2″ and it’s worth reading.

Addendum: one of the people on the two-day course I’ve just run in Berlin sent me a link for a quick note on how to set your own values for the system stats if you hit this bug. It’s actually quite a reasonable thing to do whether or not you hit the bug given the way that gathering the stats can produce unsuitable figures anyway:  setting system stats. (I’ve also added their company blog to the links on the right, they have a number interesting items and post fairly regularly.)

Jonathan Lewis's picture

Trouble-shooting

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

Jonathan Lewis's picture

Virtual bug

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.

To prevent automated spam submissions leave this field empty.
Syndicate content