The process of Elimination of Tablespace Fragmentation
Honeycomb tablespaces are very easy to be fixed using the following command.
Alter tablespace APPS_TS_TX_DATA coalesce;
For solving bubble type fragmentation, one can use locally managed tablespaces. Look for the procedure to convert dictionery managed tablespace to locally managed tablespace.
Object Fragmentation
Object fragmentation may occur due to following two reasons.
1. For dictionery managed objects if the extent management is not set to unlimited, the object's maxextents limit can be reached and object will be unable to expand.
2. Object fragmentation may occur due to tables “high water marks”. high water marks equals the last block in tables to hold the data. Tables that are subject to much insert-delete activity can develop performance problem due to wasted space being present.
The following script can be used to find the objects those are within five extents of their limit.
===========================
select
owner,
decode(partition_name,NULL,segment_name,segment_name ||
'.' || partition_name) segment_name,
segment_type,
extents,
max_extents,
initial_extent,
next_extent,
tablespace_name
from
sys.dba_segments
where
max_extents - extents <= 5 and
segment_type <> 'CACHE'
order by
1,2,3;
===========================
Another extent problem arises when an object in dictionery managed tablespace cannot extent bcz of lack of contiguous free space. To detect this kind of problem use the following scripts.
======
select
a.owner,
a.segment_name,
a.segment_type,
a.tablespace_name,
a.next_extent,
max(c.bytes) max_contig_space
from
sys.dba_segments a,
sys.dba_free_space c
where
a.tablespace_name = c.tablespace_name and
a.next_extent >
(select
max(bytes)
from
sys.dba_free_space b
where
a.tablespace_name = b.tablespace_name and
b.tablespace_name = c.tablespace_name)
group by
a.owner,
a.segment_name,
a.tablespace_name,
a.segment_type,
a.next_extent;
======
Correcting the object fragmentation
For correcting this total object reorganization is required. See the next section for details.
Table Diagonistic
For detecting high water mark and chained/migrated rows problem table diagonistic is required. Chaining occurs when a row is too large too fit inside one block. Then oracle keeps a pointer in old block and write the data in new block. It can cause several I/P performance issue.
The following script gives the info of wasted space inside one table. This script contain tables that contains 25% or more wasted space.
===============
SELECT
owner,
segment_name table_name,
segment_type,
round(bytes/1024,2) table_kb,
num_rows,
blocks,
empty_blocks,
hwm highwater_mark,
avg_used_blocks,
greatest(round(100 * (nvl(hwm - avg_used_blocks,0) /
greatest(nvl(hwm,1),1) ),2),0) block_inefficiency,
chain_pct,
max_extent_pct,
extents,
max_extents,
decode(greatest(max_free_space -
next_extent,0),0,'n','y') can_extend_space,
next_extent,
max_free_space,
o_tablespace_name tablespace_name
from
(select
a.owner owner,
segment_name,
segment_type,
bytes,
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_pct,
a.extents extents,
round(100 * (a.extents / a.max_extents),2) max_extent_pct,
a.max_extents max_extents,
b.next_extent next_extent,
b.tablespace_name o_tablespace_name
from
sys.dba_segments a,
sys.dba_all_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
union all
select
a.owner owner,
segment_name || '.' || b.partition_name,
segment_type,
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 * b.num_rows * (1 +
(b.pct_free/100))) /
c.blocksize,0),0,1,round((b.avg_row_len * b.num_rows *
(1 + (b.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_pct,
a.extents extents,
round(100 * (a.extents / a.max_extents),2) max_extent_pct,
a.max_extents max_extents,
b.next_extent,
b.tablespace_name o_tablespace_name
from
sys.dba_segments a,
sys.dba_tab_partitions b,
sys.ts$ c,
sys.dba_tables d
where
( a.owner = b.table_owner )
and
( segment_name = b.table_name )
and
( ( segment_type = 'TABLE PARTITION' ) )
and
b.tablespace_name = c.name
and
d.owner = b.table_owner
and
d.table_name = b.table_name
and
a.partition_name = b.partition_name),
(select
tablespace_name f_tablespace_name,
max(bytes) max_free_space
from
sys.dba_free_space
group by
tablespace_name)
where
f_tablespace_name = o_tablespace_name
and
greatest(round(100 * (nvl(hwm - avg_used_blocks,0) /
greatest(nvl(hwm,1),1) ),2),0) > 25
order by
10 desc, 1 asc,2 asc;
===============
Generally if you find a table to have a chain percent 25% or more we need to look the option to reorganize it.
Index Diagonistic
Like table, index diagonistic is needed for proper database performance. The following script will give all the information needed for index diagonistic.
====================
select
/*+ RULE */
owner,
segment_name index_name,
segment_type,
round(bytes/1024,2) index_kb,
num_rows,
clustering_factor,
blevel,
blocks,
max_extent_pct,
extents,
max_extents,
decode(greatest(max_free_space -
next_extent,0),0,'n','y') can_extend_space,
next_extent,
max_free_space,
o_tablespace_name
from
(select
a.owner owner,
segment_name,
segment_type,
bytes,
num_rows,
b.clustering_factor,
b.blevel,
a.blocks blocks,
a.extents extents,
round(100 * (a.extents / a.max_extents),2)
max_extent_pct,
a.max_extents max_extents,
b.next_extent next_extent,
b.tablespace_name o_tablespace_name
from
sys.dba_segments a,
sys.dba_indexes b,
sys.ts$ c
where
( a.owner = b.owner ) and
( segment_name = index_name ) and
( ( segment_type = 'INDEX' ) ) and
b.tablespace_name = c.name
union all
select
a.owner owner,
segment_name || '.' || b.partition_name,
segment_type,
bytes,
b.num_rows,
b.clustering_factor,
b.blevel,
a.blocks blocks,
a.extents extents,
round(100 * (a.extents / a.max_extents),2)
max_extent_pct,
a.max_extents max_extents,
b.next_extent,
b.tablespace_name o_tablespace_name
from
sys.dba_segments a,
sys.dba_ind_partitions b,
sys.ts$ c,
sys.dba_indexes d
where
( a.owner = b.index_owner ) and
( segment_name = b.index_name ) and
( ( segment_type = 'INDEX PARTITION' ) ) and
b.tablespace_name = c.name and
d.owner = b.index_owner and
d.index_name = b.index_name and
a.partition_name = b.partition_name),
(select
tablespace_name f_tablespace_name,
max(bytes) max_free_space
from
sys.dba_free_space
group by
tablespace_name)
where
f_tablespace_name = o_tablespace_name
order
by 1,2;
====================
If the index level exceeds level 4 or clustering factor is bad, index object reorganization is required.
Object Reorganization commands
While LMT has reduced the task of tablespace reorganization, object reorganization is still required. Oracle provides some handy commands for object reorganization. The following are few examples of those.
Heap table = Drop/Recreate table, alter table move, online table redefinition
Table partition = alter table move partition
Index-organized table = drop/recreate index, alter index rebuild
Index partition = alter index rebuild partition
Periodic reorganization of object is required for keeping the database performance well.
No comments :
Post a Comment