Oracle Scratchpad

July 22, 2007


Filed under: Block Size,Infrastructure,Troubleshooting — Jonathan Lewis @ 7:48 pm BST Jul 22,2007

A comment I always make in my seminar about Oracle technology is that the best way to find bugs is to combine two different bits of the technology.  It’s easy to say, of course, but how can you possibly guess which combinations are going to produce bugs.

Here’s a really surprising one:  Bug no: 3469992 – fixed in 10.2 (Mar 2006) so probably no longer relevant to most people.

Rediscovery Information: 
        If flashback query fails with ORA-600[15201] and db_block_size is  
        bigger than 8K, it is due to this problem.    

        using smaller block_size 

How on earth are you supposed to guess that flashback query and blocksizes would have anything to do with each other ? After all, flashback query is just read-consistency from a different perspective – it couldn’t possibly go wrong !


  1. I guess in a perfect world, you shouldn’t need to guess because this would have been rooted out by QA a long time ago.
    You would hope that test scripts used by QA are being written with full input from development on the dependencies and boundary conditions that apply. Theoretically, static analysis of flashback query code could have uncovered some block-size dependency…
    …and then there’s what happens in the real world;)

    Comment by Paul — July 23, 2007 @ 1:15 am BST Jul 23,2007 | Reply

  2. I’m guessing the opposite is true too — the best way to avoid bugs is to not combine different bits of technology? I’m not trying to be snarky here, but generally the more options required for something, the more places for things to go wrong…

    Comment by Dominic Delmolino — July 23, 2007 @ 4:33 pm BST Jul 23,2007 | Reply

  3. >> “How on earth are you supposed to guess that flashback query and blocksizes would have anything to do with each other?”

    That’s probably why it didn’t get tested by Oracle :)

    Comment by David Aldridge — July 23, 2007 @ 7:27 pm BST Jul 23,2007 | Reply

  4. Jonathan,

    I don’t know if you saw a similar point in my recent blog posting (“Look before you Leap”), and it was a bit of advice from you, at the 3-day Optimising Oracle seminar I attended a couple of years ago, that alerted me to this potential “gotcha”. As you say, whilst a new feature in itself may well have been tested exhaustively prior to release, it’s unlikely that it’s been tested in combination with all the other new features coming out at the same time, hence the potential for issues. It was good advice and something I’ve borne in mind ever since, especially when I’m basing a project on a completely new area of functionality within a major release.

    — regards, Mark

    Comment by Mark Rittman — July 23, 2007 @ 8:52 pm BST Jul 23,2007 | Reply

  5. Interesting that it doesn’t say that flashback query never works with a db_block_size bigger than 8K. It suggests there’s another condition for the failure (maybe it tries to read the block into a chunk of memory that isn’t necessarily big enough, maybe it only reads the first 8k of the block and it works if the record is in that first 8k).

    Comment by Gary — July 23, 2007 @ 10:39 pm BST Jul 23,2007 | Reply

  6. Reminds me of an issue when we discovered (on 9.2 I think) )that a fast refresh on a single table aggregate materialized view would not work if you had a local copy of DUAL in your schema, and that DUAL was an IOT not a heap table….

    I remember the project manager ranting and raving…”Why don’t Oracle test this stuff!?!?!?”

    bit harsh I thought :-)

    Comment by Connor — July 31, 2007 @ 2:18 pm BST Jul 31,2007 | Reply

  7. Dominic,
    My take on that one is that as soon as you start to combine features – especially new or enhanced features – you need to start taking wild leaps of imagination into “if A is going to break B, where is it going to break”, and then try to devise appropriate test cases.

    It’s one of the common problems with seeing a problem – how far do you go, and how much more time do you spend, in trying to put exact boundaries on it. In our case (end-user land) we usually stop early; in Oracle’s case we would like to think they keep on to the complete solution.

    For example – this report showed a problem with a 16KB block size: but was that a coincidence: is there a problem only when the data block size is larger than the undo block size, or is it the absolute size of the data block; or is it specifically a 16KB data block with an 8KB undo block … and so on.

    Comment by Jonathan Lewis — August 2, 2007 @ 3:38 pm BST Aug 2,2007 | Reply

RSS feed for comments on this post. TrackBack URI

Comments and related questions are welcome.

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

You are commenting using your 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 )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Website Powered by

%d bloggers like this: