RE: LOCALLY MANAGED EXTENT PERFORMANCE

Ever heard "3 Party App" where you have no access to the code?

I was also temped by the theory that number of extents don't matter much
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...

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 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:
...
2.1.4     Monitor and Potentially Relocate Segments Having More Than
1024 Extents
Oracle supports an unlimited number of extents in a segment.  The
performance for DML operations is largely independent of the number of
extents in the segment. However, certain DDL operations such as dropping
and truncating of segments are sensitive to the number of extents.
Performance measures for these operations have shown that a few thousand
extents can be supported by Oracle without a significant impact on
performance. A reasonable maximum has been determined to be 4096.
..

So if number of extents is such a benign thing, then why would Oracle
warn DBAs about it and recommend monitoring tables/indexes with more
than 4096 extents ?

I was also a little Socratic about your next N extent "alert" ? We still
didn't hear from you about it.. I already started losing my confidence
about your "alert".. The questions are still open: does it handle
PCT_INCREASE? Does it compare only current biggest free extent or last N
free extents in the tablespace ? If it is using only biggest free extent
it is not doing the "job" you are claiming it is doing..
Unless you sacrifice a good deal of CPU power, it is not easy to monitor
free space for next N extents..


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


> -----Original Message-----
> From: oracle-l-bounce@xxxxxxxxxxxxx=20
> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
> Sent: Monday, April 25, 2005 5:10 PM
> To: oracle-l@xxxxxxxxxxxxx
> Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE
>=20
...
..=20
>     1. How often do I truncate?
>     2. Even if I truncate often, could I use the "REUSE=20
> STORAGE" clause
>        instead of the default "DROP STORAGE"?  After all, repeated
>        truncations indicate that the space is getting reused over and
>        over again.  Why deallocate storage if its going to be reused?
>     3. Why should truncate performance, even if done frequently, trump
>        other considerations?
>=20
> I imagine that you don't truncate your 27G table too often,=20
> so I'm curious as to why truncate performance is so=20
> important?  Follow that logic a little further, and you'll=20
> have to ask yourself exactly what autoallocate has improved=20
> by storing 27G in 600 extents?
>=20
> How about testing query performance, insert/update/delete=20
> performance, etc? I imagine that the performance of those=20
> operations are quite important against your 27G table with=20
> 600 extents.  Rest assured that the performance of SQL=20
> statements will not change with the number of extents, unless=20
> the extent size is set so small that multi-block reads for=20
> full table-scans are inhibited (i.e. 256K or less, usually),=20
> but why would anyone allocate such small extents for a 27G table?
>=20
> Just food for thought...
>=20
> -Tim
>=20
>=20
>=20
> on 4/25/05 2:22 PM, Dogan, Ibrahim - Ibrahim at=20
> Ibrahim.Dogan@xxxxxxxxx
> wrote:
>=20
> >=20
> > As I told in my email, I have a 27G tables with around 600=20
> extents..=20
> > So too-many-extents problem is pretty much solved by=20
> autoallocate LMT.
> >=20
> > Having too many extents makes uet$ and fet$ system tables=20
> (please note=20
> > that they're clustered) big and this may slow down operations that=20
> > allocates/deallocates extents.
> >=20
> > Normally this should not be big trouble in LMTs if your're not=20
> > truncating volatile tables in middle of day millions of times.
> >=20
> > Thanks,
> >=20
> > Ibrahim DOGAN
> > Sr. Sybase/Oracle DBA
> > www.lowes.com
> >=20
> >=20
> >> -----Original Message-----
> >> From: oracle-l-bounce@xxxxxxxxxxxxx=20
> >> [mailto:oracle-l-bounce@xxxxxxxxxxxxx] On Behalf Of Tim Gorman
> >> Sent: Sunday, April 24, 2005 3:45 PM
> >> To: oracle-l@xxxxxxxxxxxxx
> >> Subject: Re: LOCALLY MANAGED EXTENT PERFORMANCE
> >>=20
> >>=20
> >> Exactly why might a large number of extents be a bad=20
> thing? In other=20
> >> words, are you sure you are attaching the proper level of=20
> importance=20
> >> to the issue?
> >>=20
> >> To help figure out if this is true, can you describe exactly what=20
> >> operations might be affected by the number of extents, and how? =20
> >> Queries? Inserts/updates/deletes?  Truncates? Drops?  Monitoring=20
> >> queries?
> >>=20
> >> And, are you certain that autoLMT resolves the problem of=20
> "too many=20
> >> extents"?  Isn't there an upper limit on extent size even with=20
> >> autoLMT?  If so, then how is this different from=20
> intelligently sized=20
> >> uniform LMTs?
> >>=20
> >> My apologies for the Socratic questioning, but this thread=20
> contained=20
> >> too many assertions that need a little more examination...
> >>=20
> >> -Tim
>=20
> --
> http://www.freelists.org/webpage/oracle-l
>=20
--
http://www.freelists.org/webpage/oracle-l

Other related posts: