SSTS Blog

Some news and tidbits that we share

Oracle Fragmentation - it does exist. How to detect and deal with it

by SSTS
SSTS
Server Side Technology Solutions is a consulting firm that specializes in databa
User is currently offline
on Mar 21 in Blog 0 Comments

I've heard the argument that you don't need to worry about fragmentation with Oracle and Locally managed tablespaces. However, I have come across a real world situation where it needed to be dealt with. The situation involved areference table that was being re-populated in full each night and it had grown to several times the size than was required (288MB vs 44MB). Since the table was a lookup table it was being joined with much larger tables in queries and these were wreaking havoc on the database.

In essence, the table involved was synchronized with an external source via the following process:

  • Begin transaction
  • Delete all rows from the target table
  • Select all rows from the source and insert into the target table
  • Commiit transaction

 After dealing with the situation I decided to be proactive about detecting the situation moving forward. To do so, I wrote the following query:

select owner,table_name,blocks,num_rows,avg_row_len,round(((blocks*8/1024)),2)||'MB' "TOTAL_SIZE",
 round((num_rows*avg_row_len/1024/1024),2)||'Mb' "ACTUAL_SIZE",
round(((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)),2) ||'MB' "FRAGMENTED_SPACE"
 from all_tables
 where
  ((blocks*8/1024)-(num_rows*avg_row_len/1024/1024)) > (num_rows*avg_row_len/1024/1024)
  and  owner not in ('SYS', 'SYSTEM')
  and ((blocks*8/1024)) > 10
  

The above query can be used to detect tables that have more wasted space than actual used space and that are at least 10MB in size (you can adjust as needed).

After detecting tables as such you can use the ALTER TABLE... SHRINK space command on the table(s). You need to enable row moviement on the tables in order to run the SHRIN SPACE command.

i.e.

ALTER TABLE ENABLE ROW MOVEMENT

ALTER TABLE SHRINK SPACE

 

 

Tags: Untagged
Hits: 73884

About the author

SSTS

Server Side Technology Solutions is a consulting firm that specializes in database design, development and support.

Comments

Please login first in order for you to submit comments