RE: Locally Managed Tablespaces

From: David Kurtz <info_at_go-faster.co.uk>
Date: Fri, 21 Jan 2005 00:19:09 +0100
Message-ID: <CKEAJBMGFEOCDBFILPJDOEGNFHAA.info@go-faster.co.uk>

I think also it can depend on how much control you have over tablespace layout. If you have total discretion over which table or index goes into which tablespace, then there is no reason not to use uniform extents. However, if you are dealing with a packaged application there may be other considerations.

For example, PeopleSoft dynamically generates the DDL to build database objects in the Application Designer, and this includes the tablespace name. So it can be a lot of work to create a new tablespace model, and have it maintained from within the proprietory PeopleSfot tools, and then there is more work on maintaining it because when PeopleSoft delivers patches or upgrades, table and index definitions are delivered with the default PeopleSoft tablespace. So the question to ask is whether the advantages of uniform extent sizing, over autoallocation outweigh the administrative overhead of maintaining a different tablespace?

You may get some small free space fragments in an autoallocate LMT, but much less than a dictionary managed table. But that doesn't really matter. The advantange of LMTs is not the space saving, but the reduction in overhead of maintaining the central dictionary by using a bitmap in the tablespace, and reduction in ST enqueue contention. You still get those advantages with autoallocate.

On some systems I have partitioned some of the largest tables, putting each range partition into a new and separate tablespace. Because these are _new_ tablespaces that I have created, and I have taken the decision to control the objects that go into them, thses have a uniform extent sizing and the rest at autoallocate.

regards

David Kurtz
Go-Faster Consultancy Ltd.

tel: +44 (0)7771 760660
fax: +44 (0)7092 348865
web: www.go-faster.co.uk

mailto:david.kurtz_at_go-faster.co.uk
Book: PeopleSoft for the Oracle DBA: http://www.psftdba.com PeopleSoft DBA Forum: http://groups.yahoo.com/group/psftdba

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org
> [mailto:oracle-l-bounce_at_freelists.org]On Behalf Of Niall Litchfield
> Sent: 20 January 2005 21:30
> To: Nagarajan.Subbiah_at_aetn.com
> Cc: rlsmith_at_kmg.com; oracle-l_at_freelists.org
> Subject: Re: Locally Managed Tablespaces
>
>
> On Thu, 20 Jan 2005 15:12:25 -0500, Subbiah, Nagarajan
> <Nagarajan.Subbiah_at_aetn.com> wrote:
> > Is the Uniform allocation has any advantageous than SYSTEM
> allocation type?
> > Does the SYSTEM allocation type has any issues?
> >
> > Raja.
>
> With uniform allocation you cannot get free space fragmentation
> problems, with system managed you can (but are unlikely to).
>
> With uniform allocation if you guess^H^H^H^H calculate the segment
> size wrongly you can end up with very large or very small numbers of
> extents.
>
> I prefer and have long advocated uniform extent sizing, others have
> advocated a system policy.
>
>
> --
> Niall Litchfield
> Oracle DBA
> http://www.niall.litchfield.dial.pipex.com
> --
> http://www.freelists.org/webpage/oracle-l
>

--
http://www.freelists.org/webpage/oracle-l

Received on Thu Jan 20 2005 - 18:25:18 CST