Home > Oracle, Performance > LMT – Tablespace Fragmentation – Do we really need to act?

LMT – Tablespace Fragmentation – Do we really need to act?


In olden days, the dictionary managed tablespaces (DMT) used failed allocating extents even if there are continues free space, but not coalesced.  So, there were manual intervention required to coalesce the free space to make it a bigger extent.  But, when Oracle introduced recyclebin, we can’t really coalesce the free space as the extents are not really dropped rather marked for deletion and space will be re-claimed when there is a space pressure. 

I was wondering what will happen if all my recyclebin extents are 64KB and a segment is looking for a 1024 KB free extent – when we use autoallocate.   In the case of  the dictionary  tablespace, this is going fail as any single free extent cannot allocate 1024 KB out of multiple fragments of 64KB free extents.  DB A need to run COALESCE command and make it bigger extent, so that the space request will go through.

So, what will happen in the case of a LOCALLY MANAGED TABLESPACE with AUTOALLOCATE clause – if we use UNIFORM SIZE this issue will never occur.   

I have a tablespace F5, 54MB size and all its free spaces is in recyclebin, fragmented badly.

SQL> select Tablespace_size_MB, free_space_KB, recyclebin_size_KB, Segment_size_KB
  2  from
  3  ( select nvl(sum(bytes)/1024,0) free_space_KB from dba_free_space
  4    where tablespace_name = ‘&&Tablespace_name’),
  5    ( select sum(bytes)/1024/1024 Tablespace_size_MB from dba_data_files
  6        where tablespace_name = ‘&&Tablespace_name’),
  7    ( select nvl(sum(space) * 8,0) recyclebin_size_KB from dba_recyclebin
  8      where ts_name = ‘&&Tablespace_name’),  (select sum(bytes)/1024 Segment_size_KB from dba_segments where tablespace_name = ‘&&Tablespace_name’)
  9   /
old   4:   where tablespace_name = ‘&&Tablespace_name’),
new   4:   where tablespace_name = ‘F5’),
old   6:       where tablespace_name = ‘&&Tablespace_name’),
new   6:       where tablespace_name = ‘F5’),
old   8:     where ts_name = ‘&&Tablespace_name’),  (select sum(bytes)/1024 Segment_size_KB from dba_segments where tablespace_name = ‘&&Tablespace_name’)
new   8:     where ts_name = ‘F5’),  (select sum(bytes)/1024 Segment_size_KB from dba_segments where tablespace_name = ‘F5′)
TABLESPACE_SIZE_MB FREE_SPACE_KB RECYCLEBIN_SIZE_KB SEGMENT_SIZE_KB
—————— ————- —————— —————
                54          5056               4992           55168

SQL>  select bytes/1024/1024 from dba_free_space where tablespace_name=’F5’;
BYTES/1024/1024
—————
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625
          .0625

79 rows selected.

There are 79 extents of 64KB.  Now what will happen if there is a request comes of 1024KB?  Will it defragment (COALESCE) the fragmented recyclebin space or fail as the tablespace is highly fragmented? 

I have a table in the same tablespace as below,

SQL> col segment_name for a30
select segment_name,bytes/1024,count(*),sum(bytes/1024)
from dba_extents where tablespace_name=’F5′
group by segment_name,bytes/1024;SQL>   2    3

SEGMENT_NAME                   BYTES/1024   COUNT(*) SUM(BYTES/1024)
—————————— ———- ———- —————
TAB1                                 1024         48           49152
TAB1                                   64         16            1024

There are 48 extents with 1024KB size and 16 extents with 64KB. So any new allocation should be 1024KB as per the autoallocate rule.  Let me add more records till it fails to allocate space.

SQL> insert into tab1 select * from tab1;
insert into tab1 select * from tab1
            *
ERROR at line 1:
ORA-01653: unable to extend table THOMAS.TAB1 by 128 in tablespace F5

And it was very interesting to see that, Oracle allocated another 4 extents of 1024KB size out of fragmented 64KB free space – from recyclebin, and the tablespace free space reduced to 960KB.

SQL> col segment_name for a30
select segment_name,bytes/1024,count(*),sum(bytes/1024)
from dba_extents where tablespace_name=’F5′
group by segment_name,bytes/1024;SQL>   2    3

SEGMENT_NAME                   BYTES/1024   COUNT(*) SUM(BYTES/1024)
—————————— ———- ———- —————
TAB1                                 1024         52           53248
TAB1                                   64         16            1024

 

SQL> select bytes/1024,count(*),sum(bytes/1024) from dba_free_space

where tablespace_name=’F5′ group by bytes/1024;

BYTES/1024   COUNT(*) SUM(BYTES/1024)
———- ———- —————
       960          1             960

So, de-fragmentation and coalescing tablespace is no more needed for a LMT and Oracle will do it for you as when required.  This will bring another risk – what if Oracle is not able to coalesce ( not being continue recylebin free extents) while there is enough free space.  Unfortunately we can’t pro-actively monitor this.  Or if there is a way, please feel free to comment.

Advertisements
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: