Oracle-L: RE: Coalescing tablespace

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Mon, 27 Oct 2003 06:49:29 -0800
Message-ID: <F001.005D4AF8.20031027064929@fatcity.com>

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: Ross Collado
INET: Ross.Collado_at_techpac.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).
--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Khedr, Waleed
INET: Waleed.Khedr_at_FMR.COM

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------

--

Please see the official ORACLE-L FAQ: http://www.orafaq.net
--

Author: Cary Millsap
INET: cary.millsap_at_hotsos.com

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------