Re: Locally Managed Tablespaces ... again!!!

From: Niall Litchfield <n-litchfield_at_audit-commission.gov.uk>
Date: Fri, 10 Jan 2003 16:33:21 -0000
Message-ID: <3e1ef5d2$0$235$ed9e5944@reading.news.pipex.net>

"Charlie Edwards" <charlie3101_at_hotmail.com> wrote in message news:217ac5a8.0301100657.43251985_at_posting.google.com...
> Oracle 8.1.7.2
>
> Well, on my last project I finally convinced my DBA to try LMTs. So
> he knows about them.
>
> I get transferred to another project and, lo-and-behold, we're back to
> Dictionary Managed Tablespaces (sigh), with tables and indexes in
> separate tablespaces (double sigh).

I wouldn't necessarily fight the Tables/index battle provided IO is reasonably distributed.

> He says theres no advantage to LMTs as long as you ensure that your
> PCTINCREASE is 0 and your extents are always <4 blocks * (a power of
> 2)> - i.e. 4, blocks, 8 blocks, 16 blocks, 32 blocks etc. His logic
> is that in this case any space freed up will always be available for
> use and so you won't have any fragmentation.

My comments would be

As others have said, he is missing the point that there is a performance impact of extent allocation, since extent information is no longer stored in the dictionary (UET$ and FET$).

2.How is he going to *ensure* that all objects meet his storage parameters. This is using ensure in the sense of *absolutely guarantee* not in the sense of *well if anyone fails to meet these guidelines I'll shout at them a lot.

3. In any case I don't quite see how filling a tablespace with differently sized extents 4,8,16 etc does anything *other* than cause fragmentation.
>
> I'd be grateful for any comments as I'm starting to get more than a
> bit frustrated.
>
> TIA,
>
>
> CE
Received on Fri Jan 10 2003 - 10:33:21 CST