Re: LOCALLY MANAGED EXTENT PERFORMANCE
- From: Tim Gorman <tim@xxxxxxxxx>
- To: <oracle-l@xxxxxxxxxxxxx>
- Date: Mon, 25 Apr 2005 15:09:56 -0600
Ibrahim,
You are confusing dictionary-managed tablespaces (DMT) and locally-managed
tablespaces (LMT). Mention of the clustered UET$ and FET$ tables have no
place in a discussion of LMT, since they are only used by DMT.
========
No doubt it takes less time to truncate fewer extents, though I doubt the
difference is very significant. What you have to keep in mind is:
1. How often do I truncate?
2. Even if I truncate often, could I use the "REUSE 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?
I imagine that you don't truncate your 27G table too often, so I'm curious
as to why truncate performance is so important? Follow that logic a little
further, and you'll have to ask yourself exactly what autoallocate has
improved by storing 27G in 600 extents?
How about testing query performance, insert/update/delete performance, etc?
I imagine that the performance of those operations are quite important
against your 27G table with 600 extents. Rest assured that the performance
of SQL statements will not change with the number of extents, unless the
extent size is set so small that multi-block reads for full table-scans are
inhibited (i.e. 256K or less, usually), but why would anyone allocate such
small extents for a 27G table?
Just food for thought...
-Tim
on 4/25/05 2:22 PM, Dogan, Ibrahim - Ibrahim at Ibrahim.Dogan@xxxxxxxxx
wrote:
>
> 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
> 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.
>
> Thanks,
>
> Ibrahim DOGAN
> Sr. Sybase/Oracle DBA
> www.lowes.com
>
>
>> -----Original Message-----
>> From: oracle-l-bounce@xxxxxxxxxxxxx
>> [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
>>
>>
>> Exactly why might a large number of extents be a bad thing?
>> In other words, are you sure you are attaching the proper
>> level of importance to the issue?
>>
>> To help figure out if this is true, can you describe exactly
>> what operations might be affected by the number of extents,
>> and how? Queries? Inserts/updates/deletes? Truncates?
>> Drops? Monitoring queries?
>>
>> And, are you certain that autoLMT resolves the problem of
>> "too many extents"? Isn't there an upper limit on extent
>> size even with autoLMT? If so, then how is this different
>> from intelligently sized uniform LMTs?
>>
>> My apologies for the Socratic questioning, but this thread
>> contained too many assertions that need a little more examination...
>>
>> -Tim
--
//www.freelists.org/webpage/oracle-l
Other related posts:
