To check Fragmention Tables on Oracle Databases.

[Former Member]

Hi,

To find fragmented objects in the oracle Database normally considering size of segment(table) against (size of the segment - avg_row_len ) if the difference between two is more than GB, then could re-organize the table objects and rebuild the index to claim the space.

But SAP provided a script Space_HighWaterMarkFragmentation.sql which is considering various other factors like initial extents, blocks etc.,

Alias BI stands for basis_Info, Alias T stands for Table & Alias S stands for segment.

   ( BI.INITIAL_GREATER_THAN_HWM = ' ' OR

      T.INITIAL_EXTENT > T.BLOCKS * 8192 AND S.BYTES < T.INITIAL_EXTENT + 64 * 1024 * 1024 ) AND

    'TABLE' LIKE BI.SEGMENT_TYPE AND

    ( T.USER_STATS = 'NO' OR BI.EXCLUDE_USER_STATS_SEGMENTS = ' ' )

so, could you pls. explain any one, in what logic & why SAP uses these type of condition to fetch fragmented objects ??..

And also for to find out/maintains statistics of the Tables, does SAP recommends any specific scripts like above,

Pls. Guide me for the Above.

Best Regards,

Siva..

[fidel_vales · Accepted solution]

Hi,

I'm sorry but I do not understand the question. Therefore it is very difficult to answer properly to it.

First of all you are "confusing" two things (or that is my impression). Fragmentation caused by "empty" space in the middle of an object caused (usually) by deletions (lets called the "classic" one) and empty space at the end of the object because it has not growth enough. Unfortunately, the name of the script has the word fragmentation and this can cause such confusion.

So, HW "fragmentation" is caused by a INITIAL EXTENT too big. Usually happens in systems that had DICTIONARY MANAGED tablespaces, where there was a play with the extent size trying to avoid a big number of extents.

Since Oracle came with LOCALLY MANAGED tablespaces, the number of extents should not be an worry anymore.

Unfortunately, there are remanents from those times and you can find tables with a few number of rows (perhaps were archived) but with a very high initial extent, therefore they have a lot of wasted space. If you do not expect the tables to grow to fill that space you can reorganize them to liberate space to the tablespace.

[Former Member · Accepted solution]

Hi Fidel,

Many Thanks for your Support.

Siva..