Re: logical storage philosophy?

"Daniel A. Morgan" <dmorgan_at_exesolutions.com> wrote in message news:3B00B9AD.B467F18E_at_exesolutions.com...
> Bruno Jargot wrote:
>
> > On Sun, 13 May 2001 23:29:44 -0700, Daniel A. Morgan wrote:
> >
> > >Sybrand Bakker wrote:
 

> > >> Use equally sized extents and pct_increase 0, and maxextents
 unlimited in
> > >> Oracle 7.3 and 8.0
> > >
> > >A bit of extension if I might.
> > >
> > >If you are building tablespaces in 7.3 or 8.0 make the pct_increase on
 the
> > >tablespaces 1%. But be absolutely sure that you specify pct_increase of
 0% in
> > >every single table and index create statement as Sybrand suggests. The
 first
> > >parameter will make sure that SMON coalesces freespace. The second will
 make
> > >sure that you don't fragment the tablespace. Under no conditions build
 a table
> > >or index without specifying a storage clause.
> >
> > If all extents are equally sized, there will not be fragmentation of
> > the tablespace and the coalescing of free space is totally useless.
>
> You are, of course, assuming that they will all be of the same size which
 is highly
> unlikely in the real world except in 8i with locally managed tablespaces.
>
> Otherwise I agree with you.
>
> Daniel A. Morgan
>

The 1% idea was a complete loser from the word go, and is an extremely bad idea, whatever your version, and however odd your extent sizes might end up. You are abbrogating control of coalescing to Oracle, which will invariably choose to do the necessary i/o at just the most inconventient times (ie, when people are using the database).

They didn't invent 'alter tablespace blah coalesce' for no reason... and I do believe we have cron jobs and 'at' commands to enable such a manual coalesce to be fired off at precisely the *most* convenient time.

As a recommendation, it ranks right up there with claims that "create database" creates rbs (or temp, data and index) tablespaces, and that the resulting database doesn't contain a system rollback segment! It's plain daft and deserves to be ignored utterly.

HJR Received on Tue May 15 2001 - 05:26:03 CDT