Re: Please comments This Tablespace Setup

From: Howard J. Rogers <howardjr_at_www.com>
Date: 2000/07/23
Message-ID: <397a2c5a_at_news.iprimus.com.au>#1/1


"Alex Hudghton" <alex_at_alenda.NOSPAM!.freeserve.co.uk> wrote in message news:39770921.23359429_at_news.freeserve.net...
> On Wed, 19 Jul 2000 18:47:27 +1000, "Howard J. Rogers"
> <howardjr_at_www.com> wrote:
>
> <<snip>>
> >
> >PCTINCREASE should never be anything other than zero (fragmentation).
> >MAXEXTENT of 505?? Unusual number...but regardless,
>
>
>
> >for optimal
> >performance, no segment should ever have more than half a dozen extents.
>
> I can't let that one slip by I'm afraid ! The number of segments makes
> no differance to performance apart from
>
> a) the time it takes to drop the object
>
> b) parallel operations on very busy tables
>
> unless you're in the thousands range
>
> Regards

Not true Alex. Well, let me qualify that. It is true that multiple extents will cause drops to take ages and so on, as you state. However, if you are managing your extents via the data dictionary, which is true of all Oracles before 8i, then all extent allocations will have to be recorded in the data dictionary.

Now, as you'll probably know, used extents are recorded in the UET$ table, which happens to be clustered in the C_FILE#_BLOCK# cluster -which is sized on the assumption that segments will have an average of just 4 or 5 extents each. Therefore, creating stacks of extents *will* cause cluster block chaining in this cluster -and that means performance does indeed degrade, because you are introducing chaining into the data dictionary.

You can of course get round this by specially customising the scripts that create the data dictionary, but I don't suppose many would have done this.

In 8i, of course, locally managed tablespaces don't require entries in the data dictionary, so the chaining issue is of no concern.

In short, there *are* performance worries with even mere dozens of extents, not to mention the thousands that you mentioned.

Hope that clarifies what I was trying to say!

Regards
HJR
>
> Alex
>
>
>
> <<snip>>
Received on Sun Jul 23 2000 - 00:00:00 CEST

Original text of this message