There are times as I browse through Metalink when I see descriptions of bugs that make me wonder how on earth anyone managed to find them. There are bugs which are so bizarre in their combination of prerequisites that you might think they’d never,ever, show up. Here’s one that got a mention on OTN some time back.
Problem: an update on a simple, unindexed, table takes 90 minutes if the table is in a tablespace using a 16KB block size; but closer to 90 seconds if the table is in a tablespace using a 4KB block size. The effect is totally reproducible.
If you want to read a description of the original problem as it presented at the client’s site, Steve Karam published the details about 15 months ago.
It’s not easy to figure out what’s broken here – especially when the requirement is so simple and you “know” it can’t possibly go wrong – but based on Steve’s description I built a reproducible test case of the scenario he had described and, after a flash of insight, discovered one of those idiotically impossible bugs.
- you’re using ASSM (automatic segment space management)
- you run a single transaction that causes lots of rows to increase in size
- the rows start, and end up, fairly short so you can gets lots in a block
- your PCTFREE setting is not appropriate, resulting in lots of row migration
- your block size is greater than 8KB
then performance is going to be dire.
The bug has been around since ASSM first appeared and it was years before anyone happened to run into it – that’s just the way it is with the best bugs.