The Few Extents Myth(?) Revisited

From: damorgan <damorgan_at_exesolutions.com>
Date: Fri, 07 Jun 2002 19:29:33 GMT
Message-ID: <3D0109C5.F779E8E3@exesolutions.com>

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:

Identify, but do NOT drop the table
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.
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 Received on Fri Jun 07 2002 - 14:29:33 CDT