Re: The Few Extents Myth(?) Revisited

damorgan wrote:
>
> Just found the following on metalink while looking for something else.
>
> Note:68836.1
> Subject: How To Efficiently Drop A Table With Many Extents
> Last Revision Date: 28-MAY-2002
>
> How to efficiently drop a table with many extents
>
> PURPOSE
> ~~~~~~~
>
> This note describes why a user process can consume large amounts of
> CPU
> after dropping a table consisting of many extents, and a potential
> workaround to stop the problem occurring. Essentially the CPU is
> being
> used to manipulate the extents i.e. moving used extents (uet$) to
> free
> extents (fet$). In certain circumstances it may be possible to
> regulate
> this CPU activity.
>
> SCOPE & APPLICATION
> ~~~~~~~~~~~~~~~~~~~
> This article is intended to assist DBAs who may need to drop a table
> consisting of many extents.
>
> RELATED DOCUMENTS
> ~~~~~~~~~~~~~~~~~
> Note:61997.1 SMON - Temporary Segment Cleanup and Free Space Coalescing
>
> Permanent object cleanup
> ~~~~~~~~~~~~~~~~~~~~~~~~
> If a permanent object (table) is made up of many extents, and the
> object is
> to be dropped, the user process dropping the object will consume
> large
> amounts of CPU - this is an inescapable fact. However, with some
> forethought
> it is possible to mitigate the effects of CPU usage (and hence the
> knock-on
> effect on other users of system resources) thus:
>
> 1. Identify, but do NOT drop the table
> 2. Truncate the table, specifying the REUSE STORAGE clause. This
> will be
> quick as extents are not deallocated; the highwater mark is
> simply
> adjusted to the segment header block.
> 3. Deallocate unused extents from the table, SPECIFYING THE KEEP
> CLAUSE.
> This is the crux - you can control how many extents are to be
> deallocated
> by specifying how much (in terms of Kb or Mb) of the table is NOT
>
> to be deallocated.
>
> Daniel Morgan

This is true, but only applies to dict managed tspaces which of course no-one is using anymore :-) Similarly, its only an issue for tables that you truncate or drop - and of course, we're using global temp tables for that :-)

hth
connor

-- 
==============================
Connor McDonald

http://www.oracledba.co.uk

"Some days you're the pigeon, some days you're the statue..."