How to use ASSM and manage segment Storage Efficiently in Orace 10g .

[ashishprak]

Date base Architecture related and Performance Related.
How to use Autimatic Segment Storage Management and manage segment Storage Efficiently in Orace 10g .?

[ashishprak]

In oracle 10g , Segment Management has beem much improved using which you can easlisy reclaim wasted space and comact objects on runtime without affecting current users.

Need :
As we know most granular thing in which our data is stored is at Block level , lets consider a segment such as table where blocks are filled on insert operation
("-" means filled blocked and " " is empty block)
---------------------------------------------------------------------
Header Block|-|-|-|-|-|-|-|-|-|-|-|-|-| | Free blocks
---------------------------------------------------------------------

On delete operation of some rows it will look like
---------------------------------------------------------------------
Header Block|-|-|-|-|-| |-| | |-| | |-| | | Free blocks
----------------------------------------- High water mark------
Oracle maintains HWM (High water mark level  for maximum space occupied by a segment and dont release space for use by other objects.

Problems arised :
1) When FTS( full table scan is issued ) oracle scans upto HWM(High water mark level)taking more time including scanning thru empty blocks also.
2) When new rows are insterted it is inserted above HWM wasting the space already emptied by certain blocks.

Prior to Oracle 10g , we can reclaim space by dropping table, recreating table and loading data again or move table to different tablespace.
In Oracle 10g onwards , it provides ONLINE TABLE REORGANIZATION claiming waste space to improve performance .
The only eligibilty is that ASSM feature should be enable on your tablespace at segment level.
check using :
SELECT tablespace_name, extent_management,s egment_space_management
FROM dba_tablespaces;;

Oracle10g provides faclity to reclaim space from a segment by shrinking of the segment. Shrinking a segment will make unused space available to other segments in the tablespace and may improve the performance of queries and DML operations.

Now we need to identify which segments can be shrink to reclaim space , it can done in two ways :
a) using Enterprise manager console ->performance->segment advisor
b)follow script to identify given by (Burleson book
 http://www.dba-oracle.com/oracle10g_tuning/t_online_table_reorganization.htm )


drop type BlckFreeSpaceSet;
drop type BlckFreeSpace;
 

create type BlckFreeSpace as object
(
 seg_owner varchar2(30),
 seg_type varchar2(30),
 seg_name varchar2(100),
 fs1 number,
 fs2 number,
 fs3 number,
 fs4 number,
 fb  number
 );

create type BlckFreeSpaceSet as table of  BlckFreeSpace;

create or replace function BlckFreeSpaceFunc (seg_owner IN varchar2, seg_type in varchar2 default null) return BlckFreeSpaceSet

pipelined
is
   outRec BlckFreeSpace := BlckFreeSpace(null,null,null,null,null,null,null,null);
   fs1_b number;
   fs2_b number;
   fs3_b number;
   fs4_b number;
   fs1_bl number;
   fs2_bl number;
   fs3_bl number;
   fs4_bl number;
   fulb number;
   fulbl number;
   u_b number;
   u_bl number;

begin

  for rec in (select s.owner,s.segment_name,s.segment_type from dba_segments s where owner = seg_owner and segment_type = nvl(seg_type,segment_type) )
  loop
    dbms_space.space_usage(
      segment_owner      => rec.owner,
      segment_name       => rec.segment_name,
      segment_type       => rec.segment_type,
      fs1_bytes          => fs1_b,
      fs1_blocks         => fs1_bl,
      fs2_bytes          => fs2_b,
      fs2_blocks         => fs2_bl,
      fs3_bytes          => fs3_b,
      fs3_blocks         => fs3_bl,
      fs4_bytes          => fs4_b,
      fs4_blocks         => fs4_bl,
      full_bytes         => fulb,
      full_blocks        => fulbl,
      unformatted_blocks => u_bl,
      unformatted_bytes  =>u_b
   );

   outRec.seg_owner := rec.owner;
   outRec.seg_type := rec.segment_type;
   outRec.seg_name := rec.segment_name;
   outRec.fs1 := fs1_bl;
   outRec.fs2 := fs2_bl;
   outRec.fs3 := fs3_bl;
   outRec.fs4 := fs4_bl;
   outRec.fb  := fulbl;

    Pipe Row (outRec);

 end loop;
  return;
end;

The following script can be used to quickly generate a report showing which data segments are good candidates for segment shrinking, thus restoring the wasted space to the tablespace :

col seg_owner heading 'Segment|Owner' format a10
col seg_type heading 'Segment|Type'   format a10
col seg_name heading 'Segment|Name'   format a30
col fs1 heading '0-25%|Free Space'    format 9,999
col fs2 heading '25-50%|Free Space'   format 9,999
col fs3 heading '50-75%|Free Space'   format 9,999
col fs4 heading '75-100%|Free Space'  format 9,999
col fb  heading 'Full|Blocks'         format 9,999
accept user_name prompt 'Enter Segment Owner:'
break on seg_owner


select  * from  Table ( BlckFreeSpaceFunc ('&user_name', 'TABLE' ) )
order by fs4 desc ;


Based on the 75-100% freespace numbers in the above output, we can identify which are good candidates for shrinking.
Following up , we can use alter table enable row movement and alter table shrink space compact statements can be issued to shrink the segments.

ALTER TABLE <tablename identified>ENABLE ROW MOVEMENT;

ALTER TABLE <tablename identified> SHRINK SPACE COMPACT;

However, When segment shrink is performed on tables with indexes on them, the indexes are automatically maintained when rows are moved around for compaction. User defined triggers are not fired, however, because compaction is a purely physical operation and does not impact the application.

After this operation it looks like :

---------------------------------------------------------------------
Header Block|-|-|-|-|-|-|-| | | | | | | | | Free blocks
---------------------------High water mark----------------

Thanks & Regards,
Ashish