Re: LMT with autoallocate

I'd have to vote with Sybrand for autoallocate in LMT. I've always preferred to set up my dictionary managed tablespaces with first and next the same and PCTINCREASE of zero. This defaults segments created in the tablespace to a uniform size. But I've always taught my students to remember to override the default storage clause when creating tables, indexes, etc. I still recommend that first and next be the same and PCTINCREASE equal zero. This causes all extents in the segment to be uniform, but not all extents in the tablespace. I teach this way because I am of the school of thought that prefers to isolate applications within a schema.

The other school of thought is to have a number of tablespaces with different default storage clauses supporting different extent sizes, and to separate database objects by appropriate extent sizes. If this is your philosophy then you would use uniform extents with LMTs. Under some conditions this *might* have better performance characteristics, but no one has provided me with a satisfactory explanation of when this would be the case. And unless the improvement were substantial, I'd still prefer to isolate my applications from each other by tablespace.

Best!

Randall

Sybrand Bakker <postbus_at_sybrandb.demon.nl> wrote in message news:tgleu5qbbpup46_at_beta-news.demon.nl...
> Comments embedded
>
> Hth,
>
> Sybrand Bakker, Oracle DBA
>
> "Vikas Agnihotri" <onlyforposting_at_yahoo.com> wrote in message
> news:77e87b58.0105220927.432b49d8_at_posting.google.com...
> > Since HJR doesnt have first-hand experience with autoallocate LMTs, I
> > wanted to check if these beasts are used in real-world production
> > databases at all.
>
>
> Yes they are
>
> >
> > Could someone who has used LMT (locally managed tablespaces) with
> > either autoallocate or uniform extents comment on their alleged
> > performance benefits, administrative issues, etc?
>
> Obviously, an enormous reduction of selects on fet$ and uet$ in system
> (where dictionary managed tablespaces have their repository)
>
> In 8.1.6 however on Solaris there is a problem with exporting uniform
 LMTs:
> this will result in EXP-0068. The data is exported though. Autoallocate
> doesn't have this problem.
>
>
> >
> > Would you recommend LMT over dictionary managed?
>
> Yes
>
>
> If so, would you
> > recommend uniform or autoallocate?
> >
>
> autoallocate. There is a very good discussion on this in Jonathan Lewis
 book
> Practical Oracle 8i
> Having extents bigger than can be read with 1 I/O request is according to
> him more or less useless.
> Oracle decides on whether it will use 64k or 1 M extents depending on O/S,
> so you just shouldn't bother as long as you use maxextents unlimited.
>
>
>
> > Thanks...
>
>
Received on Wed May 23 2001 - 16:35:55 CDT