RE: LOCALLY MANAGED EXTENT PERFORMANCE

From: Dogan, Ibrahim - Ibrahim <Ibrahim.Dogan_at_Lowes.com>
Date: Mon, 25 Apr 2005 16:07:43 -0400
Message-ID: <61C900F558E4184DBD8E177CC9D51F67035D76A9@msexdb06.lowes.com>

Very simple, this gives a rough idea of what your next extents will be:

SELECT e.owner, e.segment_name, e.extent_id, bytes AS last_extent_size FROM dba_extents e,=20

        (SELECT owner, segment_name, MAX(extent_id) AS extent_id
        FROM  DBA_EXTENTS
        GROUP BY owner, segment_name) ee
WHERE  e.owner=3D 'xxx' AND
       e.owner =3D ee.owner AND
       e.segment_name =3D ee.segment_name AND
       e.extent_id =3D ee.extent_id;

Thanks,
=20
Ibrahim DOGAN
Sr. Sybase/Oracle DBA
www.lowes.com

> -----Original Message-----
> From: oracle-l-bounce_at_freelists.org=20
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
> Sent: Monday, April 25, 2005 3:22 PM
> To: oracle-l_at_freelists.org
> Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE

>=20
>=20

> From a production-support perspective...

>=20

> For years, we've been using a script that would alert if any=20
> segments in a tablespace are going to run out of space within=20
> "N" extents (i.e. 5, 10, whatever). How do you do this for=20
> autoallocate tablespaces?

>=20

> I know that some folks have reverse-engineered the sizing=20
> algorithm for autoallocate, but I don't think it is yet=20
> documented anywhere. Which means that Oracle can feel free=20
> to "tweak" it whenever they wish...

>=20

> ...which means lots of pages in the middle of the night...

>=20
>=20
>=20
>=20

> on 4/24/05 8:53 PM, Tanel P=3DF5der at tanel.poder.003_at_mail.ee wrote:

>=20


> > Hi,

> >=3D20
> > I haven't read the whole thread - but I'd just like to=20
> contribute the=20
> >fac=3D
> t,
> > that nowadays I save my time and create all tablespaces as=20
> >autoallocate - and haven't seen any performance nor other=20
> problems so=20
> >far. And I don't worry about the number or size of extents=20
> at all. =3D20
> > Tanel.
> >=3D20

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

Received on Mon Apr 25 2005 - 16:12:58 CDT