RE: LOCALLY MANAGED EXTENT PERFORMANCE

From: Dogan, Ibrahim - Ibrahim <Ibrahim.Dogan_at_Lowes.com>
Date: Tue, 26 Apr 2005 14:24:46 -0400
Message-ID: <61C900F558E4184DBD8E177CC9D51F67035D7859@msexdb06.lowes.com>

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_at_freelists.org=20
> [mailto:oracle-l-bounce_at_freelists.org] On Behalf Of Tim Gorman
> Sent: Monday, April 25, 2005 5:10 PM
> To: oracle-l_at_freelists.org
> 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_at_Lowes.com
> 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_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=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

Received on Tue Apr 26 2005 - 14:30:03 CDT