Re: LOCALLY MANAGED EXTENT PERFORMANCE

From: Tim Gorman <tim_at_evdbt.com>
Date: Mon, 25 Apr 2005 15:09:56 -0600
Message-ID: <BE92B8C4.265FF%tim@evdbt.com>

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:

How often do I truncate?
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?
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_at_Lowes.com 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_at_freelists.org
>> [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
>>
>>
>> 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

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

Received on Mon Apr 25 2005 - 18:31:21 CDT