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 the original description of the problem as it presented at the client’s site, Steve Karam published the details about 15 months ago. (There’s an alternative version of the story available on Google Books – page 537 of the 2nd edition of the “Definitive Reference” on tuning Oracle)
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.
If you want to see the test case Greg Rahn notes relating to the original posting I made to the OTN thread where Steve mentioned his blog example. He also had a copy of the test case, but this has gone missing after a site move and is now available here.]
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.