Re: A couple of tablespace questions for the experts

"Niall Litchfield" <n-litchfield_at_audit-commission.gov.uk> wrote in message news:3f6affc4$0$15780$ed9e5944_at_reading.news.pipex.net...
> "Paul Dixon" <root_at_127.0.0.1> wrote in message
> news:bketj0$9p3$1_at_visp.bt.co.uk...
> > I am planning the design of a new database to host an existing decision
> > support sytem, and am trying to decide :-
> >
> > 1] Whether to use Automatic Segment Space Management for tablespaces or
to
> > use Uniform Extent sizes (as in Howard Rogers tablespace fragmentation
> > document) and manually manage the Freelists / Freelist Groups.
>
>
> ASSM is an alternative to Freelists, Uniform Extent allocation is an
> alternative to Automatic extent sizing. So you can use ASSM with either
> uniform or automatically sized segments in a tablespace.
>
>
> > 2] Whether there are any siginficant implications from using a small
> number
> > of large datafiles for a tablespace instead of a larger number of
smaller
> > (i.e. 2 GByte) datafiles.
>
> A datafile is the smallest unit of backup so very large datafiles imply
> larger parts of the database being backed up at once.
>
> A datafile is a file so the db files parameter needs to be sized
> appropriately.

Niall,

thanks very much for the response.

Yes I misread the syntax diagram for CREATE TABLESPACE in the SQL reference and incorrectly assumed from the supplied examples that ASSM also meant AUTO EXTENT MANAGEMENT was mandatory. So I can continue to use my small medium and large uniform extents regardless of ASSM or manual freelists. Just need to decide whether the benefits of ASSM in freelist management are outweighed by the costs ?

As for datafiles, I don't think that a DB_FILES value of 150 to 200 (if I used 2GByte max datafile size) is going to be an issue. The UNIX administrator is also happy to back up files larger than 2GBytes. If there aren't any other factors to consider I would probably choose for convenience to standardise on just 4 sizes for all the datafiles i.e. 100MB, 500MB, 1GB and 5GB.

Paul Dixon Received on Fri Sep 19 2003 - 09:53:31 CDT