Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: segment fragmentation

RE: segment fragmentation

From: Khedr, Waleed <Waleed.Khedr_at_FMR.COM>
Date: Wed, 8 Sep 2004 18:51:26 -0400
Message-ID: <42BBD772AC30EA428B057864E203C9998AC414@MSGBOSCLF2WIN.DMN1.FMR.COM>


How does LMT prevent segment fragmentation? If I did not forget everything during vacation, LMT has nothing to do with object/segment fragmentation. It might help later in re-allocating freed extents by the same segment or other segments since it enforces rules that govern the specs of allocated extents.

A big delete will affect the efficiency of indexes (depending on how fast the deleted data will be replaced and the domain of the indexed keys), HWM (if empty blocks were not re-used), efficiency of FTS, etc.

Regards,
Waleed

-----Original Message-----
From: Bobak, Mark [mailto:Mark.Bobak_at_il.proquest.com]=20 Sent: Wednesday, September 08, 2004 5:07 PM To: thump_at_cosmiccooler.org; oracle-l_at_freelists.org Subject: RE: segment fragmentation

David,

As Ryan mentioned, LMTs prevent fragmentation, if you have uniform =3D extents, or at least mitigate it, if you have autoallocate extents. =3D However, if you delete a significant amount of data from a table, then,
=3D

there could be some impact due to high-water mark. Consider, however, =
=3D

when the high-water mark is referenced. For a table, only on full table
=3D

scan. For indexes on the table, only on an index fast full scan. So, =
=3D

if your queries don't utilize those access paths, then you really don't
=3D

need to concern yourself w/ HWM.

You don't mention what version you're on. If HWM is an issue, you don't
=3D

need to resort to export/import, if you're at least at 9i. You can =3D ALTER TABLE table_name move; to reorganize the table data. Note that =
=3D

when that's done, all indexes will be invalidated and will need to be =
=3D

rebuilt. Consider the possibility of NOLOGGING, but make sure you fully
=3D

understand the recovery implications before proceeding there. Also, I'm
=3D

not really a 10g guy, yet, but in 10g, there's a 'shrink' clause to =3D ALTER TABLE that (from what I've read) does a nice job, maintains the =
=3D

object on-line availability and maintains indexes at the same time. =3D But, I haven't used that....

Hope that helps,

-Mark

-----Original Message-----
From: oracle-l-bounce_at_freelists.org
[mailto:oracle-l-bounce_at_freelists.org]On Behalf Of David Sent: Wednesday, September 08, 2004 3:58 PM To: oracle-l_at_freelists.org
Subject: segment fragmentation

I have been asked to determine segment/object fragmentation levels after
=3D

a
mass delete has been performed.

Do LMT only negate fragmentation occuring at a tablespace level as an issue and segment fragmentation and high water mark issues are still relevant after a mass purge?

How does one check for this type of fragmentation?

It has been requested I perform an export/import of said schema after verifying segment fragmentation exists.

By the way, I have finally purchased Optimizing Oracle Performance and have begun the reading.

Regards,
--=3D20
..
David

--
To unsubscribe - =3D
mailto:oracle-l-request_at_freelists.org&subject=3D3Dunsubscribe=3D20
To search the archives - http://www.freelists.org/archives/oracle-l/
--
To unsubscribe -
mailto:oracle-l-request_at_freelists.org&subject=3Dunsubscribe=20
To search the archives - http://www.freelists.org/archives/oracle-l/
--
To unsubscribe - mailto:oracle-l-request_at_freelists.org&subject=unsubscribe 
To search the archives - http://www.freelists.org/archives/oracle-l/
Received on Wed Sep 08 2004 - 17:47:02 CDT

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US