Re: number of extents question

"Howard J. Rogers" <howardjr2000_at_yahoo.com.au> wrote in message news:<3d80ec5c_at_dnews.tpgi.com.au>...
>
> That is an excellent question -and in this particular case, it can (I think)
> be answered. Oracle itself put out a piece of information with version 5
> (read that and weep: version FIVE) stating that modest performance benefits
> would arise if everything was in one extent. It wasn't particularly true
> then, but given that the number of extents a segment could have was finite,
> and strictly enforced, I suppose they were really just trying to say 'don't
> run out of extents, because then you won't be able to extend further'.
>
> But this got picked up by everyone as saying 'one extent in and of itself
> brings performance benefits'. And the myth has persisted ever since.
>

Actually I have a slightly different version. Oracle indeed put out that little bit of info. In a technical note ages old. V5 I think, but I'm not sure. Then in V6 they proceeded to make two parameters available in init.ora to get rid of the problem. dc_used_extents and dc_free_extents. In typical Oracle fashion, they set the default for the parameters ridiculously low and proceeded to put the fear of God on any1 that even dared think about them.

Then in a few User Group meetings, a paper did the rounds. From a "highly credible" site. They claimed increased performance when tables were "shrunk" to a single extent. I questioned the author of the paper at the time on if the two parameters had been adjusted and their effect taken into account. Only to be met by a surprised blank stare. You know: lights are on, nobody home. That said all I needed to hear.

From there to the myth we know and hate, there was a very small step. In the meantime I and many others did the usual: we ignored all these "pseudo-expert" bull and just measured performance. Found no difference whatsoever provided the parameters were changed from default. And we stopped defragmenting and started living since around 1992...

Interestingly, these parameters are still in v$rowcache even in 8.1.7. They became "self-tuned" since V7. Which basically meant Oracle would allocate as much to them as ever needed. Like it does to everything else in v$rowcache. And people act surprised when I claim "self-tuning" a-la Oracle will never work efficiently...

:D

Cheers
Nuno Souto
nsouto_at_optushome.com.au Received on Thu Sep 12 2002 - 22:45:17 CDT