Re: Is there a performance limit of extents?

On Tue, 18 Apr 2000 23:17:41 +0200, "Sybrand Bakker" <postbus_at_sybrandb.demon.nl> wrote:

>Don't agree.
>Quite often you don't have any chaining!

Hmmm, that's not my experience, Sy.
In almost all cases I've seen of "better performance after reorg" since V7, it's been because chaining was gone, indexes had been defragged or free space in table compacted. Never seen concrete proof that extents were the prob since V6. And even then, setting dc_extents way up from default fixed it.

>Also, Oracle typically tries to process an extent as one unit, cache it
>completely in memory etc.

Duh? I thought cache was managed by blocks, not extents. Something new in 8i?

>When there are many extents, it needs to return to
>the table header frequently to retrieve the next extent info. This usually
>results in all kinds of recursive calls.

So what? You get the table header cached anyway (the old dc_extents in V6, now automated since V7). Even if it needs recursive calls, it will be only memory.

There might be a veeery slight performance advantage in less extents, but IMHO not worth the trouble of the frequent, religious reorgs.

I have run up to 90 extents in NT boxes (not noted for their performance feats...) without the sightest problem. Reorganizing didn't achieve any changes in performance whatsoever. Data wasn't chained or free-holed.

Having said that, I don't recommend THOUSANDS of extents!! But since V8, I don't bother until things go well over 100. With V7, I started to bother at 75. With V6, at about 20 using dc_extents jacked up.

Indexes however I do bother...

FWIW, YMMV, no animals were harmed testing this, etc,etc.

Cheers
Nuno Souto
nsouto_at_nsw.bigpond.net.au.nospam(!)
http://www.users.bigpond.net.au/the_Den/ Received on Wed Apr 19 2000 - 00:00:00 CDT