oracle fragment concept

Fragment  Concept

Classfication  

①Tablesapce Freespace Fragmentation(TFF)

  Cause: only dropping an object will create TFF and usually this can be controlled with solid application design and properly managed storage parameters.

Detection methods：get the info  from sys.fet$, the sys.uet$, and the sys.ts$ (tablespace details) tables.

Resolution:The only way to eliminate TFF is to physically rearrange Oracle segments.

②Segment Fragmentation(SF)

Cause:SF is the existence of multiple segment extents.  When an Oracle segment has more than on fragmented.  If there is two extents or two hundred extents, the segment is still fragmented.

Detetion methods:select owner,segment_name,segment_type,count(*) from dba_extents group by owner, segment_name ,segment_type;

Resolution:In general, SF is not a performance issue.  In fact, SF is anatural and desirable phenomenon in an Oracle database.

③Data Block Fragmentation（DBF）

Cause：DBF occurs whenever a row is deleted from a table and therefore removed from a data block.

Detetion methods: Keep in mind that it is not necessary to quantify block fragmentation for each table.  Only quantify DBF for tables you suspect may have DBF issues. The following script may  give you some suggest about  fragment  segments in your DB .

SELECT OWNER, SEGMENT_NAME TABLE_NAME, SEGMENT_TYPE, LAST_ANALYZED,
GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS,0)/GREATEST(NVL(HWM,1),1) ), 2), 0) WASTE_PER,
ROUND(BYTES/1024, 2) TABLE_KB, NUM_ROWS,
BLOCKS, EMPTY_BLOCKS, HWM HIGHWATER_MARK, AVG_USED_BLOCKS,
CHAIN_PER, O_TABLESPACE_NAME TABLESPACE_NAME
FROM
(SELECT A.OWNER OWNER, A.SEGMENT_NAME, A.SEGMENT_TYPE, B.LAST_ANALYZED, A.BYTES,
B.NUM_ROWS, A.BLOCKS BLOCKS, B.EMPTY_BLOCKS EMPTY_BLOCKS,
A.BLOCKS - B.EMPTY_BLOCKS - 1 HWM,
DECODE( ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0),
0, 1,
ROUND((B.AVG_ROW_LEN * NUM_ROWS * (1 + (PCT_FREE/100)))/C.BLOCKSIZE, 0)
) + 2 AVG_USED_BLOCKS,
ROUND(100 * (NVL(B.CHAIN_CNT, 0)/GREATEST(NVL(B.NUM_ROWS, 1), 1)), 2) CHAIN_PER,
B.TABLESPACE_NAME O_TABLESPACE_NAME
FROM SYS.DBA_SEGMENTS A,
SYS.DBA_TABLES B,
SYS.TS$ C
WHERE A.OWNER =B.OWNER and
SEGMENT_NAME = TABLE_NAME and
SEGMENT_TYPE = 'TABLE' AND
B.TABLESPACE_NAME = C.NAME)
WHERE GREATEST(ROUND(100 * (NVL(HWM - AVG_USED_BLOCKS, 0)/GREATEST(NVL(HWM, 1), 1) ), 2), 0) > 25
AND OWNER like 'NEVA2%' AND BLOCKS > 100
ORDER BY 10 DESC, 1 ASC, 2 ASC;

 

Resolutions:There are two basic resolution methods.  The first method is to alter the table's storage parameters allowing the blocks to naturally "fill up the holes" in each block.  The second method is to rebuild the object. Rebuilding the object is rarely the best solution, especially with large production objects (which are the objects we are typically looking at anyway).

④Index Leaf Block Fragmentation（ILBF）

Cause：Only row deletes causes index leaf block fragmentation. 

Detection methods: We can gather many the index statistics by simply performing an analyze index  validate structure.

Resolutions:ILBF can be naturally resolved by simply changing the storage parameters or the index can be physically rebuilt while the application is available.

⑤Row  Fragmentation(RF)

Cause:RF can only be caused by an  update statement that increases row length.

Deteciton methods:Simply analyze the table and query the chain_cnt column from the user_tables view. For a RF system wide perspective and impact analysis, as shown below, simply query the  v$sysstat table. The output below shows the number of fragmented rows the Oracle

instance has touched since startup.

SQL> select value
  2  from v$sysstat
  3  where name = 'table fetch continued row';

SQL> analyze table gl_balances compute statistics;
Table analyzed.
SQL> Select chain_cnt
  2  From user_tables
  3  Where table_name = 'GL_BALANCES';

 

Resolutions:The preventative method is to increase the pctfree storage parameter.