Re: logical storage philosophy?

Daniel A. Morgan wrote:
>
> Sybrand Bakker wrote:
>
> > "Fred A G" <nospam_at_allowed.localhost> wrote in message
> > news:BwuL6.4782$vR1.16338_at_nntpserver.swip.net...
> > > Bon jour, tout le monde!
> > >
> > > I am searching through Metalink and other sites for a source on storage
> > > parameters policies, but nothing much comes up. Aren't there any around?
> > > I am also interested to hear about the philosophies used by the database
> > > management people here, when it comes to Oracle logical storage
> > > parameters (like initial, next, max extent, pct increase, and so on).
> > >
> > > For instance, what behaviour do you expect for initial and additional
> > > extents. Aim for one big (enough) inital and only a few additional per
> > > year or at most per month? Is the max extent limit (e.g. 121) a
> > > safeguard and for normal operation it should be near unreachable, or is
> > > unlimited the way to go?
> > >
> > > Regards
> > > /Fad
> > >
> > >
> >
> > If you are running 8i: use Locally Managed Tablespaces (LMTs) which will
> > force you to use equally size extents and for the rest : don't bother
> > If you are not running 8i:
> > Pct_increase was one of the smartest ideas Oracle ever had, and is the main
> > source for tablespace fragmentation.
> > Use equally sized extents and pct_increase 0, and maxextents unlimited in
> > Oracle 7.3 and 8.0
> >
> > Hth,
> >
> > Sybrand Bakker, Oracle DBA
>
> 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.
>
> Daniel A. Morgan

Having pctfree 1 is an unnecessary overhead if every segment is the same size...I would go further and switch off the SMON check in this case using event 10269.

Cheers
Connor

-- 
===========================================
Connor McDonald
http://www.oracledba.co.uk (mirrored at
http://www.oradba.freeserve.co.uk)

"Some days you're the pigeon, some days you're the statue"