Re: LOCALLY MANAGED EXTENT PERFORMANCE

From: Niall Litchfield <niall.litchfield_at_gmail.com>
Date: Tue, 26 Apr 2005 20:38:53 +0100
Message-ID: <7765c897050426123865d50a14@mail.gmail.com>

On 4/26/05, Paul Drake <bdbafh_at_gmail.com> wrote:
> On 4/26/05, Dogan, Ibrahim - Ibrahim <Ibrahim.Dogan_at_lowes.com> wrote:
> >=3D20
> > Ever heard "3 Party App" where you have no access to the code?
> >=3D20
> > I was also temped by the theory that number of extents don't matter muc=
h
> > but I changed my mind after testing LMT with uniform extents for a 3.
> > party app in which there are several tables that jump to 3-4G from 0
> > bytes during day..all users started screaming and we had to go to LMT
> > with auto allocate to calm down the users...
> >=3D20
> > You keep repeating yourself, telling well-known facts making it like I
> > oppose them. I'm not saying uniform LMT is a bad thing. All I'm saying
> > is it should be used if you know estimated size of tables. And even
> > though LMTs reduces the extent allocation cost dramatically, there are
> > some cases where thousands of extents may cause some headache..

Even with a third party app it *should* be possible to predict that 3-4gb of data was going to suddenly be stored in the db somewhere. I know that in practice this often surprises us, but when I get surprised like this its *always* because I didn't realize the implications of something (or its buried in footnote 10 to appendix 3 of the fine manual supplied by the supplier).

and a note on the next N alert (since I'm replying to two authors in one post). I don't believe that such an alert is possible with autoallocate LMTs. Its perfectly possible with uniform LMTs since next n =3D n* uniform size. PCTIncrease is just ignored in LMTs - set it to 42 that's my advice :) .

> >=3D20
> > Even popular Oracle paper, "How to Stop Defragmenting and Start Living:
> > The Definitive Word on Fragmentation", warns DBAs about excessive
> > extents. Below is a clip from the paper:
> > ...

>=20

> The paper can still be found here:
> http://metalink.oracle.com/metalink/plsql/showdoc?db=3D3DNOT&id=3D3D23689=
5.1

>=20

> I don't see the create_date of that paper listed, but I believe that
> 9i Release 1 had not yet been released yet. My very short point here
> is

>=20

> Rather than chasing the tail of dog---ma, one should not assume that
> seminal papers that are based upon earlier versions are necessarily
> current.

Indeed. The paper abstract states

"This paper is targeted at an audience of experienced database administrators. It is based on Oracle7 release 7.3 and also covers new features introduced in Oracle8 version 8.0. "

LMTs weren't available until 8i (IIRC).=20

--=20
Niall Litchfield
Oracle DBA
http://www.niall.litchfield.dial.pipex.com

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

Received on Tue Apr 26 2005 - 15:43:11 CDT