DESCRIPTION
Tablespaces in Oracle are the logical storage unit as it is not physically exists like datafiles. In other words, it is a logical link between tables and indexes to datafiles as each tablespace must have at least one datafile. There is one to many relationship between tablespaces and datafiles so one tablespace can have more than one datafile but one to one relationship between a datafile and a tablespace i.e. One datafile can only belong to one tablespace.
Tablespaces gets fragmented if they are created as Dictionary managed tablespace or ASSM without using AUTO and UNIFORM extent option. Other reason of fragmentation is that even though tablespace is created properly with auto extent on and using uniform size but tables and indexes are created with storage parameters. Using storage parameters with tables and indexes overrides the tablespace storage settings and allow different size and number of extents in database.
Due to regular DML operation tablespaces gets fragmented in two ways. One is honeycomb fragmentation and second is Bubble fragmentation.
Honeycomb fragmentation occurs when more than one extent next to each other becomes empty so it is easier to fix this problem by coalescing space together. You can do it manually or leave it to SMON process to do this job automatically.
Bubble fragmentation is difficult to fix. Best option is to used locally managed tablespace and don’t use storage parameters with tables and indexes so that oracle manages extent allocation and deallocation by itself.
There is a famous model that employs an arbitrary scoring system to establish if a tablespace needs a free space rebuild. The system used is that defined by Loney in Oracle DBA Handbook by Oracle Press, ISBN 0-07-881182-1. This Free Space Fragmentation Index (FSFI) calculates the size of the largest extent as a
percentage of the total free space and also considers the number of extents in a tablespace. This index is not a monitor of the amount of free space available. It indicates the structure of the free space.
Over time, free space within a tablespace can become fragmented. Use this resource model to determine if any tablespace has a high free space fragmentation index. A fragmented tablespace can lack the contiguous free space needed to allocate new extents. Identifying and coalescing tablespaces that have become fragmented can enhance database performance.
The best-case response from this resource model is 100%. As the total number of extents increase for a tablespace, the FSFI rating drops accordingly. In general, a tablespace with sufficient free space and an FSFI rating of above 30 should not experience free space availability problems.
select TABLESPACE_NAME,
SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS)))) Fsfi
from dba_free_space group
by tablespace_name order by 1;
In mathematical terms, this is:
100(m/s)1/2 ( m )
FSFI = ---------------- = 100 √(--------)
c1/4 ( s x √c )
where m = max(blocks)
s = sum(blocks)
c = count(blocks)
On the other hand if tablespace is locally managed tablespace with Segment management auto and uniform extents then there is no need to defragement a tablespace as all extents will be of same size and oracle use bitmap to manage each extent so it does not really matter much if the extent is at the beginning, end or in the middle of a tablespace from performance point of view.
DEFRAGMENTING UNDO TABLESPACE
It is difficult to defragment UNDO tablespace as it does not have any tables and indexes that we can be re build or shrink. Oracle SMON process normally defrags UNDO tablespace but in busy system it is become literally impossible. So best way to defrag UNDO tablespace is to create a secondary UNDO tablespace e.g UNDO2 and switch it as default UNDO tablespace and allow oracle to clear down extents in original UNDO tablespace. Once original UNDO is free then you can switch back default undo to UNDO tablespace and drop the new UNDO2 tablespace.
This method does not require any outage and completely safe from DBA point of view. I have just implemented it in UAT database where we have allocated around 57Gb of Undo tablespace
which gets fragmented overtime. FSFI was 14% which is now 40% percent so we have defrag 26% of space so far and notice that we are using 20Gb of space at the moment compare to 57Gb.
SCRIPTS
This script will create the mapping for a tablespace and provides information about
1) Owner of an objects
2) Name of Object
3) Starting block id
4) Number of blocks used
5) Amount of space in MB
-------------------------------------------------------------------------------------------------------------
UNDEF ENTER_TABLESPACE_NAME
set heading off
select 'Tablespace Fragmentation Report ' from dual;
set heading on
COLUMN value NEW_VALUE dbblksiz NOPRINT
COLUMN owner format a20
COLUMN segment_name format a20
SELECT value FROM v$parameter WHERE name='db_block_size';
select ' *** FREE SPACE ***' owner , ' ' segment_name, file_id ,
block_id , blocks blocks_cnt,(blocks*&dbblksiz)/(1024*1024) meg
from sys.dba_free_space
where tablespace_name = '&&ENTER_TABLESPACE_NAME'
union
select substr(owner,1,12), substr(segment_name,1,32), file_id, block_id,
blocks block_cnt,(blocks*&dbblksiz)/(1024*1024) meg
from sys.dba_extents
where tablespace_name = '&&ENTER_TABLESPACE_NAME'
order by 3,4
/
-------------------------------------------------------------------------------------------------------------
Script below will use FSFI as criteria to calculate the level of fragmentation. If FSFI is lower than 30% then it will use alert_level 2 (Critical) and if it is between 31-40 then alert_level is 1 and greater than 40% is OK.
-------------------------------------------------------------------------------------------------------------
rem
rem Name: free_space.sql
rem
rem FUNCTION: Provide data on tablespace extent status
rem FUNCTION: Use 0 = OK , 1= Warning , 2= Critical
rem FUNCTION: includes fsfi from DBA Handbook
rem
SET FEED OFF
SET FLUSH OFF
SET VERIFY OFF
set pages 58 LINES 132
COLUMN tablespace HEADING Name FORMAT a25
COLUMN files HEADING '#Files' FORMAT 9,999
COLUMN pieces HEADING 'Frag' FORMAT 9,999
COLUMN free_bytes HEADING 'Free
Byte' FORMAT 9,999,999,999,999
COLUMN free_blocks HEADING 'Free
Blk' FORMAT 999,999,999
COLUMN largest_bytes HEADING 'Biggest
Bytes' FORMAT 9,999,999,999,999
COLUMN largest_blks HEADING 'Biggest
Blks' FORMAT 999,999,999
COLUMN ratio HEADING ' Percent' FORMAT 999.999
COLUMN average_fsfi HEADING 'Average
FSFI' FORMAT 999.999
COLUMN alert_level HEADING 'Alert
level' FORMAT 99
SELECT
tablespace,
files,
pieces,
free_bytes,
free_blocks,
largest_bytes,
largest_blks,
ratio,
average_fsfi,
case when average_fsfi < 30 then 2
when average_fsfi between 30 and 40 then 1
else 0
end alert_level
FROM
(SELECT
tablespace,
COUNT(*) files,
SUM(pieces) pieces,
SUM(free_bytes) free_bytes,
SUM(free_blocks) free_blocks,
SUM(largest_bytes) largest_bytes,
SUM(largest_blks) largest_blks,
SUM(largest_bytes)/sum(free_bytes)*100 ratio,
SUM(fsfi)/COUNT(*) average_fsfi
FROM
(SELECT tablespace_name tablespace, file_id, COUNT(*) pieces,
SUM(bytes) free_bytes, SUM(blocks) free_blocks,
MAX(bytes) largest_bytes, MAX(blocks) largest_blks,
SQRT(MAX(blocks)/SUM(blocks))*(100/SQRT(SQRT(COUNT(blocks)))) fsfi
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id, relative_fno)
GROUP BY
tablespace)
/
------------------------------------------------------------------------------------------------------------
HOW TO FIND TABLES FOR DEFRAGMENTATION
The logical next question is that how to find out tables those are candidate defragmentation? If you have hundreds of table on a tablespace then you are not going to rebuild all of them to get space. First we need to calculate who the best candidates for defragmentation are.
Script below provides a list of tables that has more than 10 blocks allocated and using less than 30% of allocated space i.e. below HWM. There are two calculation of space usage has been done in this script. One is logical space calculation used by dbms_stats and other is physical space usage by using dba_segments.
This script also generates script to shrink the table by using table shrinking method which we will discuss later.
-------------------------------------------------------------------------------------------------------------
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME
SET pages 56 lines 132 newpage 0 verify off echo off feedback off
SET serveroutput on
-- when the table is > 10 blocks and
-- total space usage up to the HWM is < 30% of the size of the HWM, we will consider the table
-- a candidate for shrinking.
-- In dba_tables, the "blocks" are calculated when you run dbms_stats, while in dba_segments, "blocks" is the actual number of blocks used by the object on disk.
BEGIN
DBMS_OUTPUT.put_line ('This report produces a list of tables that may benefit from compacting.');
DBMS_OUTPUT.put_line (CHR (13));
FOR space_usage IN
(SELECT /*+ FIRST_ROWS */
dba_tables.owner, dba_tables.table_name,
dba_tables.blocks blocks_below,
dba_segments.blocks total_blocks,
dba_tables.num_rows * dba_tables.avg_row_len total_data_size,
ROUND (( 100 * (dba_tables.num_rows * dba_tables.avg_row_len) / (GREATEST (dba_tables.blocks,1)* v$parameter.VALUE) ), 3 ) hwm_full,
ROUND (( 100 * (dba_tables.num_rows * dba_tables.avg_row_len) / (GREATEST(dba_segments.blocks,1) * v$parameter.VALUE ) ), 3 ) space_full
FROM dba_tables, v$parameter, dba_segments
WHERE dba_tables.owner NOT IN ('SYS', 'SYSTEM')
AND dba_tables.owner LIKE UPPER ('&&ENTER_OWNER_NAME')
AND dba_tables.table_name LIKE UPPER ('&&ENTER_TABLE_NAME')
AND dba_tables.owner = dba_segments.owner
AND dba_tables.table_name = dba_segments.segment_name
AND v$parameter.NAME = LOWER ('db_block_size')
AND ( 100 * (dba_tables.num_rows * dba_tables.avg_row_len)
/ (GREATEST (dba_segments.blocks, 1) * v$parameter.VALUE) ) < 30 AND dba_segments.blocks > 10)
LOOP
DBMS_OUTPUT.put_line ( 'Candidate table is ' space_usage.owner '.' space_usage.table_name );
DBMS_OUTPUT.put_line ( 'Which is using ' space_usage.space_full '% of allocated space. ' );
DBMS_OUTPUT.put_line ( 'Which is using ' space_usage.hwm_full '% of allocated space to the HWM. ' );
DBMS_OUTPUT.put_line ('You can use this script to compact the table:');
DBMS_OUTPUT.put_line ( 'alter table ' space_usage.owner '.' space_usage.table_name ' enable row movement; ');
DBMS_OUTPUT.put_line ( 'alter table ' space_usage.owner '.' space_usage.table_name ' shrink space cascade; ');
DBMS_OUTPUT.put_line (CHR (13));
END LOOP;
END;
/
-------------------------------------------------------------------------------------------------------------
ALTER TABLE MOVE OR ALTER TABLE SHRINK
In 9i, oracle introduced this option of alter table move command. Before that options available were export /import and truncate and re-insert data but both of them make table unavailable and are candidates of to human error.
ALTER TABLE will cause that
* the rows will have new physical addresses
* High water mark will be adjusted.
But the drawback is that you need a full (exclusive) table lock and indexes will become unusable (due to old rowids) so need to be rebuilt.
Segment Shrinking was introduced from oracle 10g and is only available for tablespaces that are using ASSM (Automatic Segment Space Management).
ASSM has two types of HWM. One is HHWM (high HWM) and LHWM( low HWM). HHWM referred as a point above which all blocks are unformatted where LHWM represent a point below which all blocks are formatted.
Shrink command runs in two steps:
First step makes the segment compact by moving rows further down to free blocks at the beginning of the segment. This is basically DML operation of deleting from one blocks and reinserting into another. That is why you have to make sure that you have enough Temporary and UNDO space available for Shrink command.
Second step adjust the HWM. For this oracle needs an exclusive table lock for a short amount of time.
Benefits of Segment Shrinking
* will adjust HWM
* can be done online
* Will cause on row level locking and table level is at the end for a very small amount fo time.
* Indexes will be maintained and remain usable.
* can be made in one go
* Can be made into two steps.
Restriction on Segment Shrinking
* Only possible in tablespaces with ASSM
* Not allowed on UNDO segments, Temporary segments, clustered tables, tables with a column of datatype LONG
* LOB indexes
* IOT mapping tables and IOT overflow segments
* Tables with MVIEWS with ON COMMIT
* Tables with MVIEWS which are based on ROWIDs
INDEX MANAGEMENT DURING SHRINK COMMAND
In the first phase Oracle scans the segment from the back to find the position of the last row. Afterwards it scans the segment from the beginning to find the position of the first free slot in a block in this segment. In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes the row from the back and inserts it into the free position at front of the segment. Now Oracle scans the segment from the back and front again and again until it finds that the two positions are the same. Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first part of SHRINK TABLE statement.
ACTUAL COMMAND
There are few ways to Shrink a table
a) Shrink and move HWM in one step
ALTER TABLE SHRINK SPACE;
b) Shrink and move HWM in one step for a table and all dependent objects
ALTER TABLE SHRINK SPACE CASCASDE;
c) Shrink and move HWM in two steps
ALTER TABLE SHRINK SPACE COMPACT; ---- Only moves the rows
ALTER TABLE SHRINK SPACE; --- now this will move HWM down
CONCLUSION
This document should provide necessary information about how to find fragmentation and further discusses a method to fix it in 10g database onwards. This does not conclude that SHRINK command is the best way to defragment a table as it depend case by case basis. In some case you may want to consider combination of both alter table move and alter table shrink command.
Tablespaces in Oracle are the logical storage unit as it is not physically exists like datafiles. In other words, it is a logical link between tables and indexes to datafiles as each tablespace must have at least one datafile. There is one to many relationship between tablespaces and datafiles so one tablespace can have more than one datafile but one to one relationship between a datafile and a tablespace i.e. One datafile can only belong to one tablespace.
Tablespaces gets fragmented if they are created as Dictionary managed tablespace or ASSM without using AUTO and UNIFORM extent option. Other reason of fragmentation is that even though tablespace is created properly with auto extent on and using uniform size but tables and indexes are created with storage parameters. Using storage parameters with tables and indexes overrides the tablespace storage settings and allow different size and number of extents in database.
Due to regular DML operation tablespaces gets fragmented in two ways. One is honeycomb fragmentation and second is Bubble fragmentation.
Honeycomb fragmentation occurs when more than one extent next to each other becomes empty so it is easier to fix this problem by coalescing space together. You can do it manually or leave it to SMON process to do this job automatically.
Bubble fragmentation is difficult to fix. Best option is to used locally managed tablespace and don’t use storage parameters with tables and indexes so that oracle manages extent allocation and deallocation by itself.
There is a famous model that employs an arbitrary scoring system to establish if a tablespace needs a free space rebuild. The system used is that defined by Loney in Oracle DBA Handbook by Oracle Press, ISBN 0-07-881182-1. This Free Space Fragmentation Index (FSFI) calculates the size of the largest extent as a
percentage of the total free space and also considers the number of extents in a tablespace. This index is not a monitor of the amount of free space available. It indicates the structure of the free space.
Over time, free space within a tablespace can become fragmented. Use this resource model to determine if any tablespace has a high free space fragmentation index. A fragmented tablespace can lack the contiguous free space needed to allocate new extents. Identifying and coalescing tablespaces that have become fragmented can enhance database performance.
The best-case response from this resource model is 100%. As the total number of extents increase for a tablespace, the FSFI rating drops accordingly. In general, a tablespace with sufficient free space and an FSFI rating of above 30 should not experience free space availability problems.
select TABLESPACE_NAME,
SQRT(MAX(BLOCKS)/SUM(BLOCKS))*(100/SQRT(SQRT(COUNT(BLOCKS)))) Fsfi
from dba_free_space group
by tablespace_name order by 1;
In mathematical terms, this is:
100(m/s)1/2 ( m )
FSFI = ---------------- = 100 √(--------)
c1/4 ( s x √c )
where m = max(blocks)
s = sum(blocks)
c = count(blocks)
On the other hand if tablespace is locally managed tablespace with Segment management auto and uniform extents then there is no need to defragement a tablespace as all extents will be of same size and oracle use bitmap to manage each extent so it does not really matter much if the extent is at the beginning, end or in the middle of a tablespace from performance point of view.
DEFRAGMENTING UNDO TABLESPACE
It is difficult to defragment UNDO tablespace as it does not have any tables and indexes that we can be re build or shrink. Oracle SMON process normally defrags UNDO tablespace but in busy system it is become literally impossible. So best way to defrag UNDO tablespace is to create a secondary UNDO tablespace e.g UNDO2 and switch it as default UNDO tablespace and allow oracle to clear down extents in original UNDO tablespace. Once original UNDO is free then you can switch back default undo to UNDO tablespace and drop the new UNDO2 tablespace.
This method does not require any outage and completely safe from DBA point of view. I have just implemented it in UAT database where we have allocated around 57Gb of Undo tablespace
which gets fragmented overtime. FSFI was 14% which is now 40% percent so we have defrag 26% of space so far and notice that we are using 20Gb of space at the moment compare to 57Gb.
SCRIPTS
This script will create the mapping for a tablespace and provides information about
1) Owner of an objects
2) Name of Object
3) Starting block id
4) Number of blocks used
5) Amount of space in MB
-------------------------------------------------------------------------------------------------------------
UNDEF ENTER_TABLESPACE_NAME
set heading off
select 'Tablespace Fragmentation Report ' from dual;
set heading on
COLUMN value NEW_VALUE dbblksiz NOPRINT
COLUMN owner format a20
COLUMN segment_name format a20
SELECT value FROM v$parameter WHERE name='db_block_size';
select ' *** FREE SPACE ***' owner , ' ' segment_name, file_id ,
block_id , blocks blocks_cnt,(blocks*&dbblksiz)/(1024*1024) meg
from sys.dba_free_space
where tablespace_name = '&&ENTER_TABLESPACE_NAME'
union
select substr(owner,1,12), substr(segment_name,1,32), file_id, block_id,
blocks block_cnt,(blocks*&dbblksiz)/(1024*1024) meg
from sys.dba_extents
where tablespace_name = '&&ENTER_TABLESPACE_NAME'
order by 3,4
/
-------------------------------------------------------------------------------------------------------------
Script below will use FSFI as criteria to calculate the level of fragmentation. If FSFI is lower than 30% then it will use alert_level 2 (Critical) and if it is between 31-40 then alert_level is 1 and greater than 40% is OK.
-------------------------------------------------------------------------------------------------------------
rem
rem Name: free_space.sql
rem
rem FUNCTION: Provide data on tablespace extent status
rem FUNCTION: Use 0 = OK , 1= Warning , 2= Critical
rem FUNCTION: includes fsfi from DBA Handbook
rem
SET FEED OFF
SET FLUSH OFF
SET VERIFY OFF
set pages 58 LINES 132
COLUMN tablespace HEADING Name FORMAT a25
COLUMN files HEADING '#Files' FORMAT 9,999
COLUMN pieces HEADING 'Frag' FORMAT 9,999
COLUMN free_bytes HEADING 'Free
Byte' FORMAT 9,999,999,999,999
COLUMN free_blocks HEADING 'Free
Blk' FORMAT 999,999,999
COLUMN largest_bytes HEADING 'Biggest
Bytes' FORMAT 9,999,999,999,999
COLUMN largest_blks HEADING 'Biggest
Blks' FORMAT 999,999,999
COLUMN ratio HEADING ' Percent' FORMAT 999.999
COLUMN average_fsfi HEADING 'Average
FSFI' FORMAT 999.999
COLUMN alert_level HEADING 'Alert
level' FORMAT 99
SELECT
tablespace,
files,
pieces,
free_bytes,
free_blocks,
largest_bytes,
largest_blks,
ratio,
average_fsfi,
case when average_fsfi < 30 then 2
when average_fsfi between 30 and 40 then 1
else 0
end alert_level
FROM
(SELECT
tablespace,
COUNT(*) files,
SUM(pieces) pieces,
SUM(free_bytes) free_bytes,
SUM(free_blocks) free_blocks,
SUM(largest_bytes) largest_bytes,
SUM(largest_blks) largest_blks,
SUM(largest_bytes)/sum(free_bytes)*100 ratio,
SUM(fsfi)/COUNT(*) average_fsfi
FROM
(SELECT tablespace_name tablespace, file_id, COUNT(*) pieces,
SUM(bytes) free_bytes, SUM(blocks) free_blocks,
MAX(bytes) largest_bytes, MAX(blocks) largest_blks,
SQRT(MAX(blocks)/SUM(blocks))*(100/SQRT(SQRT(COUNT(blocks)))) fsfi
FROM sys.dba_free_space
GROUP BY tablespace_name, file_id, relative_fno)
GROUP BY
tablespace)
/
------------------------------------------------------------------------------------------------------------
HOW TO FIND TABLES FOR DEFRAGMENTATION
The logical next question is that how to find out tables those are candidate defragmentation? If you have hundreds of table on a tablespace then you are not going to rebuild all of them to get space. First we need to calculate who the best candidates for defragmentation are.
Script below provides a list of tables that has more than 10 blocks allocated and using less than 30% of allocated space i.e. below HWM. There are two calculation of space usage has been done in this script. One is logical space calculation used by dbms_stats and other is physical space usage by using dba_segments.
This script also generates script to shrink the table by using table shrinking method which we will discuss later.
-------------------------------------------------------------------------------------------------------------
UNDEF ENTER_OWNER_NAME
UNDEF ENTER_TABLE_NAME
SET pages 56 lines 132 newpage 0 verify off echo off feedback off
SET serveroutput on
-- when the table is > 10 blocks and
-- total space usage up to the HWM is < 30% of the size of the HWM, we will consider the table
-- a candidate for shrinking.
-- In dba_tables, the "blocks" are calculated when you run dbms_stats, while in dba_segments, "blocks" is the actual number of blocks used by the object on disk.
BEGIN
DBMS_OUTPUT.put_line ('This report produces a list of tables that may benefit from compacting.');
DBMS_OUTPUT.put_line (CHR (13));
FOR space_usage IN
(SELECT /*+ FIRST_ROWS */
dba_tables.owner, dba_tables.table_name,
dba_tables.blocks blocks_below,
dba_segments.blocks total_blocks,
dba_tables.num_rows * dba_tables.avg_row_len total_data_size,
ROUND (( 100 * (dba_tables.num_rows * dba_tables.avg_row_len) / (GREATEST (dba_tables.blocks,1)* v$parameter.VALUE) ), 3 ) hwm_full,
ROUND (( 100 * (dba_tables.num_rows * dba_tables.avg_row_len) / (GREATEST(dba_segments.blocks,1) * v$parameter.VALUE ) ), 3 ) space_full
FROM dba_tables, v$parameter, dba_segments
WHERE dba_tables.owner NOT IN ('SYS', 'SYSTEM')
AND dba_tables.owner LIKE UPPER ('&&ENTER_OWNER_NAME')
AND dba_tables.table_name LIKE UPPER ('&&ENTER_TABLE_NAME')
AND dba_tables.owner = dba_segments.owner
AND dba_tables.table_name = dba_segments.segment_name
AND v$parameter.NAME = LOWER ('db_block_size')
AND ( 100 * (dba_tables.num_rows * dba_tables.avg_row_len)
/ (GREATEST (dba_segments.blocks, 1) * v$parameter.VALUE) ) < 30 AND dba_segments.blocks > 10)
LOOP
DBMS_OUTPUT.put_line ( 'Candidate table is ' space_usage.owner '.' space_usage.table_name );
DBMS_OUTPUT.put_line ( 'Which is using ' space_usage.space_full '% of allocated space. ' );
DBMS_OUTPUT.put_line ( 'Which is using ' space_usage.hwm_full '% of allocated space to the HWM. ' );
DBMS_OUTPUT.put_line ('You can use this script to compact the table:');
DBMS_OUTPUT.put_line ( 'alter table ' space_usage.owner '.' space_usage.table_name ' enable row movement; ');
DBMS_OUTPUT.put_line ( 'alter table ' space_usage.owner '.' space_usage.table_name ' shrink space cascade; ');
DBMS_OUTPUT.put_line (CHR (13));
END LOOP;
END;
/
-------------------------------------------------------------------------------------------------------------
ALTER TABLE MOVE OR ALTER TABLE SHRINK
In 9i, oracle introduced this option of alter table move command. Before that options available were export /import and truncate and re-insert data but both of them make table unavailable and are candidates of to human error.
ALTER TABLE will cause that
* the rows will have new physical addresses
* High water mark will be adjusted.
But the drawback is that you need a full (exclusive) table lock and indexes will become unusable (due to old rowids) so need to be rebuilt.
Segment Shrinking was introduced from oracle 10g and is only available for tablespaces that are using ASSM (Automatic Segment Space Management).
ASSM has two types of HWM. One is HHWM (high HWM) and LHWM( low HWM). HHWM referred as a point above which all blocks are unformatted where LHWM represent a point below which all blocks are formatted.
Shrink command runs in two steps:
First step makes the segment compact by moving rows further down to free blocks at the beginning of the segment. This is basically DML operation of deleting from one blocks and reinserting into another. That is why you have to make sure that you have enough Temporary and UNDO space available for Shrink command.
Second step adjust the HWM. For this oracle needs an exclusive table lock for a short amount of time.
Benefits of Segment Shrinking
* will adjust HWM
* can be done online
* Will cause on row level locking and table level is at the end for a very small amount fo time.
* Indexes will be maintained and remain usable.
* can be made in one go
* Can be made into two steps.
Restriction on Segment Shrinking
* Only possible in tablespaces with ASSM
* Not allowed on UNDO segments, Temporary segments, clustered tables, tables with a column of datatype LONG
* LOB indexes
* IOT mapping tables and IOT overflow segments
* Tables with MVIEWS with ON COMMIT
* Tables with MVIEWS which are based on ROWIDs
INDEX MANAGEMENT DURING SHRINK COMMAND
In the first phase Oracle scans the segment from the back to find the position of the last row. Afterwards it scans the segment from the beginning to find the position of the first free slot in a block in this segment. In case the two positions are the same, there is nothing to shrink. In case the two positions are different Oracle deletes the row from the back and inserts it into the free position at front of the segment. Now Oracle scans the segment from the back and front again and again until it finds that the two positions are the same. Since it is DML statements performed to move the rows, the indexes are maintained at the same time. Only row level locks are used for these operations in the first part of SHRINK TABLE statement.
ACTUAL COMMAND
There are few ways to Shrink a table
a) Shrink and move HWM in one step
ALTER TABLE SHRINK SPACE;
b) Shrink and move HWM in one step for a table and all dependent objects
ALTER TABLE SHRINK SPACE CASCASDE;
c) Shrink and move HWM in two steps
ALTER TABLE SHRINK SPACE COMPACT; ---- Only moves the rows
ALTER TABLE SHRINK SPACE; --- now this will move HWM down
CONCLUSION
This document should provide necessary information about how to find fragmentation and further discusses a method to fix it in 10g database onwards. This does not conclude that SHRINK command is the best way to defragment a table as it depend case by case basis. In some case you may want to consider combination of both alter table move and alter table shrink command.
No comments:
Post a Comment