Re: Coalescing tablespace

Cary,

Under 7.1, 7.2, and 7.3, there were some very real situations where even the most careful space management strategies came to naught. Specifically, the habit of parallel "direct-path" operations on tables and indexes to "trim" the last extent to avoid leaving "wasted" space above the high-water mark in the temporary segment before it was merged into the final segment. Event 10901 could be set to disable this "extent trimming" and allow the space to be "wasted" in order to maintain desired extent sizing. The reason for this "trim" operation was not to avoid wasting space per se, but rather to resolve the dilemma of merging multiple segments into one, each with it's own "high-water mark" to maintain.

The reason this became a problem was due to an apparent failure to coalesce on subsequent CREATE; it was not unusual for a series of DROPs followed by CREATEs in 7.1 and 7.2 to result in an out-of-space message, even when DBA_SEGMENTS clearly showed no segments in the tablespace in question. A coalesce operation using the fully-qualified "coalesce" event level that I replied in an earlier email would fix the problem directly.

The 10901 event became available in 7.3.3 (due to problems documented in 7.1 and 7.2) and became largely useless in v8.x. In v9.x, it is apparently a no-op.

I agree that "coalesce" operations as a "proactive" database maintenance measure are generally a waste of time, and are generally the product of the fragmentation nonsense that has been flying around for over a decade. It didn't help that some large companies had "de-frag" products and thus had financial incentive to propagate the myths of problems, including performance problems (which is utter bunk). Some years ago I put together a paper on the myths surrounding extent management (http://www.EvDBT.com/papers.htm)...

>From 8i onwards, of course, the use of LMTs completely negates the
discussion altogether.

Thanks!

-Tim

on 10/27/03 7:49 AM, Cary Millsap at cary.millsap_at_hotsos.com wrote:

> It's a note created in response to hundreds of customers shouting about
> how "VMS and DOS need defragmenting; therefore, Oracle must need
> defragmenting, too."
>
> There *was* a cluster bug way back in release 6 that caused CREATE
> statements to take a really long time if you ever let a tablespace get
> more than a certain number of freed extents in it. Oracle (Jonathan
> Klein) fixed it by 6.0.36. I described that problem to the list already,
> some months ago.
>
> This bug drove the perception that Oracle tablespaces needed periodic
> defragmentation. But not exactly. Unless you defragmented *before* so
> many extents were created, even defragmentation didn't do any good. If I
> recall correctly, the magic number was about 61 extents or something
> like that, with a 2KB Oracle database block size. So, if you got to 50
> free extents and coalesced them to 1, got to 50 again and coalesced
> again, and so on, then you'd be okay. But if you ever dropped a whole
> schema and produced 2,000 free extents (or even just 70), then no amount
> of defragmenting would help you, automatic or not. Let me repeat: THIS
> PROBLEM WAS FIXED FIFTEEN YEARS AGO.
>
> The bottom line is that manual coalescing just wastes time and system
> resources. SMON's automatic coalescing wastes even more. Always did;
> still does.
>
>
> Cary Millsap
> Hotsos Enterprises, Ltd.
> http://www.hotsos.com
>
> Upcoming events:
> - Performance Diagnosis 101: 10/28 Phoenix, 11/19 Sydney
> - SQL Optimization 101: 12/8-12 Dallas
> - Hotsos Symposium 2004: March 7-10 Dallas
> - Visit www.hotsos.com for schedule details...
>
>
> -----Original Message-----
> Khedr, Waleed
> Sent: Monday, October 27, 2003 8:04 AM
> To: Multiple recipients of list ORACLE-L
>
> I always saw this note (and hated it):
>
> http://metalink.oracle.com/metalink/plsql/ml2_documents.showDocument?p_d
> atab
> ase_id=NOT&p_id=31116.1
>
> Hope it helps,
>
> Waleed
>
> -----Original Message-----
> Sent: Sunday, October 26, 2003 8:04 PM
> To: Multiple recipients of list ORACLE-L
>
>
> Hi,
> For those like me still working on an Oracle 716 (hold the laughs), how
> do
> we coalesce a tablespace?
> Rgds,
> Ross

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Tim Gorman
  INET: tim_at_sagelogix.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).