As I told in my email, I have a 27G tables with around 600 extents.. So
too-many-extents problem is pretty much solved by autoallocate LMT.
Having too many extents makes uet$ and fet$ system tables (please note
that they're clustered) big and this may slow down=20
operations that allocates/deallocates extents.
Normally this should not be big trouble in LMTs if your're not
truncating volatile tables in middle of day millions of times.
To see the different please test the following code in both uniform
sized (8K) and autoallocate LMT and let us know the results:
CREATE TABLE proof
(segment_name char(2000),
segment_type char(2000),
segment_name2 char(2000),
segment_type2 char(2000)
)
TABLESPACE LMTTBS
drop table proof
INSERT INTO proof (segment_name, segment_type,segment_name2,
segment_type2)
SELECT segment_name, segment_type, segment_name, segment_type
FROM dba_segments;
commit;
INSERT INTO proof (segment_name, segment_type,segment_name2,
segment_type2)
SELECT segment_name, segment_type, segment_name, segment_type
FROM proof;
commit;
INSERT INTO proof (segment_name, segment_type,segment_name2,
segment_type2)
SELECT segment_name, segment_type, segment_name, segment_type
FROM proof;
commit;
INSERT INTO proof (segment_name, segment_type,segment_name2,
segment_type2)
SELECT segment_name, segment_type, segment_name, segment_type
FROM proof;
commit;
INSERT INTO proof (segment_name, segment_type,segment_name2,
segment_type2)
SELECT segment_name, segment_type, segment_name, segment_type
FROM proof;
commit;
select sysdate from dual
/
truncate table proof
/
select sysdate from dual
/
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: Sunday, April 24, 2005 3:45 PM
> To: oracle-l_at_freelists.org
> Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE
>=20
>=20
> Exactly why might a large number of extents be a bad thing? =20
> In other words, are you sure you are attaching the proper=20
> level of importance to the issue?
>=20
> To help figure out if this is true, can you describe exactly=20
> what operations might be affected by the number of extents,=20
> and how? Queries? Inserts/updates/deletes? Truncates? =20
> Drops? Monitoring queries?
>=20
> And, are you certain that autoLMT resolves the problem of=20
> "too many extents"? Isn't there an upper limit on extent=20
> size even with autoLMT? If so, then how is this different=20
> from intelligently sized uniform LMTs?
>=20
> My apologies for the Socratic questioning, but this thread=20
> contained too many assertions that need a little more examination...
>=20
> -Tim
>=20
>=20
> on 4/22/05 11:07 PM, Dogan, Ibrahim - Ibrahim at=20
> Ibrahim.Dogan_at_Lowes.com
> wrote:
>=20
> >=20
> > Even with LMTs, you still wory about number of extents whenever you=20
> > run any command that performs extent=20
> allocation/deallocation (create=20
> > table/rebuild index/truncate table etc..)
> >=20
> > My point was that I saw many people going back to DMT=20
> because of very=20
> > same issue you're experiencing with LMTs.. When LMT with uniform=20
> > extent size is used, you need to babysit the segments to make sure=20
> > they don't go beyong couple thousand extents.. But you=20
> don't have this=20
> > problem if you use LMT with AUTO extent allocation. My=20
> biggest table=20
> > is 27G in a AUTO LMT and it has around 600 extents..
> >=20
> > I generally prefer AUTO LMT and reorg the tables after bulk=20
> deletes...
> >=20
> >=20
> > Thanks,
> >=20
> > Ibrahim DOGAN
> > Sr. Sybase/Oracle DBA
> > www.lowes.com
> >=20
> >=20
> >> -----Original Message-----
> >> From: Wolfson Larry - lwolfs [mailto:lawrence.wolfson_at_acxiom.com]
> >> Sent: Saturday, April 23, 2005 12:55 AM
> >> To: Dogan, Ibrahim - Ibrahim; oracle-l_at_freelists.org
> >> Subject: RE: LOCALLY MANAGED EXTENT PERFORMANCE
> >>=20
> >> And I thought Oracle said you didn't have to worry about=20
> how many any=20
> >> more.
> >>=20
> >> I didn't exactly blame LMTs I just said there were a
> >> lot of extents.
> >> Not my app.
> >>=20
> >> Thanks for info
> >>=20
> >> -----Original Message-----
> >> From: Dogan, Ibrahim - Ibrahim [mailto:Ibrahim.Dogan_at_Lowes.com]
> >> Sent: Friday, April 22, 2005 11:46 PM
> >> To: Wolfson Larry - lwolfs; oracle-l_at_freelists.org
> >> Subject: RE: LOCALLY MANAGED EXTENT PERFORMANCE
> >>=20
> >> Do not use uniform sized LMTs for volatile tables since=20
> you may end=20
> >> up having a table/index with thousand of extents..
> >>=20
> >> More important, don't blame LMTs for it..
> >>=20
> >> If you use AUTO extent allocation instead of uniform size,=20
> you won't=20
> >> have problem...
> >>=20
> >> Thanks,
> >>=20
> >> Ibrahim DOGAN
> >> Sr. Sybase/Oracle DBA
> >> www.lowes.com
>=20
> --
> http://www.freelists.org/webpage/oracle-l
>=20
--
http://www.freelists.org/webpage/oracle-l
Received on Mon Apr 25 2005 - 16:26:56 CDT
