| Author's name: Norman Dunbar;
Author's Email: Oracle@bountifulsolutions.co.uk |
Date written: 02/07/2001 Oracle version(s): 7.3.4 onwards |
| How do I go about reclaiming fragments of free space in my tablespaces and how can I avoid creating them in the first place. | |
Free space fragmentation is a situation that developes in a tablespace, where 'holes' appear in the tablespace. These holes are made up of free blocks, but no objects attempt to use them when they need to extend. They are created by the process of adding and deleting extents from the objects in the tablespace, where these objects have diferent initial and/or next extent sizes.
Having the object or the tablespace use a non-zero pctincrease value is a great way to cause this problem as well. There is no known need to have the pctincrease setting anything other than zero.
An example of how to create FSF. Imagine a tablespace which is 100Mb in size, and has nothing in it. A table is then created with an initial size of 20Mb and a next size of 12 Mb, min extents is set to 6. This uses up 80Mb of the tablespace in one swoop. Next an table with a different extent size is created, say initial = 22M, next = 7M minextents = 1 and over a period of time, this table grows in size. Each time it grows it needs an extra 7M of space in the tablespace.
If the first object has some data deleted from it, and drops one of it's extents as a result, then a block of 20M is added to the list of free space for this tablespace and it can be used by any other object. If the second table needs an extra extent, if might use 7M of the 20M freed by the first table - leaving a free block of 13M. This is now too small for the first table to reuse as it needs 20M, but will allow the second table to use another 7M of it, leaving 6M free. Unfortunately, this 6M is too small to be used by either table and so just sits there unused forever.
The following shows the state of the 20M extent that was dropped by the first table and re-used by the second, there are two chunks of 7M and one of 6M. The 6M is never going to be used by these two tables and is effectively wasted space.
20 +----------+ 19 | | 18 | | 17 | | <--- This block of 6M is 'wasted' space as neither table has a 6M 'next' size. 16 | | 15 | | 14 +----------+ 13 | | 12 | | 11 | | <--- This 7M block has been reused by table 2 as a 'next' extent. 10 | | 9 | | 8 | | 7 +----------+ 6 | | 5 | | 4 | | <--- This 7M block has been reused by table 2 as a 'next' extent. 3 | | 2 | | 1 +----------+
The easiest way to find out if your tablespaces are fragmenting, and what size each chunk of free space is, is to run the following script :
column tablespace_name format a15 column file_name format a30 SELECT fs.tablespace_name, df.file_name, COUNT(*) AS fragments, ROUND(SUM(fs.bytes)/1024,2) AS total_kb, ROUND(MAX(fs.bytes)/1024,2) AS biggest_kb FROM DBA_FREE_SPACE fs, DBA_DATA_FILES df WHERE fs.file_id(+) = df.file_id GROUP BY fs.tablespace_name,df.file_name;
The output from the above script will look something similar to the following :
TABLESPACE_NAME FILE_NAME FRAGMENTS TOTAL_KB BIGGEST_KB --------------- ------------------------------ ---------- ---------- ---------- CC /data2/DBADMIN/cc01.dbf 1 102352 102352 CCINDX /data2/DBADMIN/cc01indx.dbf 1 102392 102392 RBS /data2/DBADMIN/rbs01.dbf 1 18072 18072 SYSTEM /data2/DBADMIN/system01.dbf 1 90992 90992 TEMP /data2/DBADMIN/temp01.dbf 25 81912 30952 TESTS /data2/DBADMIN/users01.dbf 1576 101112 952
The above script will give you a quick look at how many fragments there are in your tablespaces free space. But it will not identify which chunk is what size. To do this, you need the following script which shows all the free blocks in a specific tablespace. Tablespace TESTS appears to be in a bad way! The following script will identify the free blocks in a specific tablespace :
SET pages 40
SET lines 132
COLUMN tablespace_name format a15
COLUMN file_name format a30
SELECT fs.tablespace_name, df.file_name, fs.block_id start_block,
fs.blocks num_blocks, fs.block_id + fs.blocks -1 end_block, fs.bytes/1024 kb
FROM DBA_FREE_SPACE fs, DBA_DATA_FILES df
WHERE fs.tablespace_name = UPPER('&tablespace_name')
AND fs.file_id = df.file_id
ORDER BY tablespace_name, file_name, start_block DESC;
Enter value for tablespace_name: tests TABLESPACE_NAME FILE_NAME START_BLOCK NUM_BLOCKS END_BLOCK KB --------------- ------------------------------ ----------- ---------- ---------- ---------- TESTS /data2/DBADMIN/users01.dbf 12182 119 12300 952 ....... TESTS /data2/DBADMIN/users01.dbf 84 20 103 160
Reclaiming the free space can be relatively simple, but can involve a lot of work during 'unsociable hours' if various objects need to be exported and imported again :
Further reading:
Practical Oracle 8i Building Efficient Databases by Jonathan Lewis, Chapter 8 Leveraging Tablespaces.
Oracle 8 Administrator's Guide.
All About Oracle Database Fragmentation - Craig Shallahamer on http://www.orapub.com (you need to sign up for membership - which is free)
How To Stop Defragmenting And Start Living: The Definitive Word On Fragmentation - Himatsingka & Loaiza, available on MetaLink (which needs a logon, but is free to sign up to).