Segment Fragmentation

1] Table Fragmentation

Look for candidate ( Gain > 500 MB )

-- table candidate to shrink

select table_name, num_rows, ceil(tt) "Used_Size_MB", ceil(tr) "Total_Size_MB", ceil(lg) "Free_Size_MB" ,

case WHEN ceil(lg) > 500 then 'alter table '||OWNER||'.'||TABLE_NAME||' shrink space cascade ;' end SHRINKAGE

from (

SELECT   t.owner OWNER , t.table_name TABLE_NAME, t.num_rows, t.num_rows*t.avg_row_len/1024/1024 as tt,

         t.blocks*8192/1024/1024 as tr, TRUNC ( (t.blocks*8192 - t.num_rows*t.avg_row_len)/1024/1024,2 ) as lg

FROM dba_tables t WHERE t.num_rows > 0 AND t.owner = 'SCH1' AND t.table_name = 'TABLE1'

ORDER BY t.blocks*8192 - t.num_rows*t.avg_row_len DESC

)

TABLE_NAME	NUM_ROWS	Used_Size_MB	Total_Size_MB	Free_Size_MB	SHRINKAGE
TABLE1	37805913	1515	2429	915	alter table SCH1.TABLE1 shrink space cascade ;

SHRINK

SQLPLUS

alter table SCH1.TABLE1 enable row movement ;

alter table SCH1.TABLE1 shrink space cascade ;

alter table SCH1.TABLE1 disable row movement ;

Analyze table and get new Fragmentation values :

exec DBMS_STATS.GATHER_TABLE_STATS ( 'SCH1','TABLE1',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

TABLE_NAME	NUM_ROWS	Used_Size_MB	Total_Size_MB	Free_Size_MB
TABLE1	37949085	1231	1585	354

!! The SHRINK Commands are very Log Consuming !!

Another one : TRUNCATE when possible !

-- HFD_DAT 22 GB

-- table candidate to shrink

select table_name, num_rows, ceil(tt) "Used_Size_MB", ceil(tr) "Total_Size_MB", ceil(lg) "Free_Size_MB" ,

case WHEN ceil(lg) > 500 then 'alter table '||OWNER||'.'||TABLE_NAME||' shrink space cascade ;' end SHRINKAGE

from (

SELECT   t.owner OWNER , t.table_name TABLE_NAME, t.num_rows, t.num_rows*t.avg_row_len/1024/1024 as tt,

         t.blocks*8192/1024/1024 as tr, TRUNC ( (t.blocks*8192 - t.num_rows*t.avg_row_len)/1024/1024,2 ) as lg

FROM dba_tables t WHERE t.owner = 'HFD' AND t.table_name = 'DEBUG_RAPPEL'

ORDER BY t.blocks*8192 - t.num_rows*t.avg_row_len DESC

)

-- used 13000 ; total 15877

truncate table HFD.DEBUG_RAPPEL

exec DBMS_STATS.GATHER_TABLE_STATS ( 'HFD','DEBUG_RAPPEL',null,DBMS_STATS.AUTO_SAMPLE_SIZE,null,'FOR ALL INDEXED COLUMNS SIZE AUTO',2,'ALL',TRUE) ;

-- segment without any rows

-- used 0 ; total 0

Then reclaim Tablespace free space.

2] Analyze Index Fragmentation

Looks for in the following order :

BTREE_SPACE-USED_SPACE > 50 AND gain > 250 MB => REBUILD ( ASSM  =AUTO ) OR SHRINK

DEL_LF_ROWS*100/LF_ROWS Deleted >= 20% => REBUILD ( ASSM  =AUTO ) OR SHRINK

HEIGHT number of index levels is > 3 => REBUILD ( ASSM  =AUTO ) OR SHRINK

OPT_CMPR_COUNT compressed gain >= 30 %

index candidate to bitmap ?      

set serveroutput on

-- 1/ variables

Declare

  SCH           VARCHAR2 (20)   := '&1' ;

  tab_tag       varchar2(1000);

  action_name   VARCHAR2 (48)   := 'Analyze index fragmentation 1.0' ;

-- criterias

  pct_used_threshold    number  :=50 ;  -- a/ % used space =< 50 %              PCT_USED

  MB_gain_threshold     number  :=64 ; -- a'/ MB gain > 250 MB                                         BTREE_SPACE-USED_SPACE

  del_rate_threshold    number  :=20 ;  -- b/ Deleted >= 20%                    DEL_LF_ROWS*100/LF_ROWS

  height_threshold      number  :=4 ;   -- c/ number of index levels is > 3     HEIGHT

  opt_cmpr_threshold    number  :=1 ;   -- d/ index key compression             OPT_CMPR_COUNT

  opt_cmpr_gain_threshold       number  :=30 ; -- d'/ compressed gain >= 30 %   OPT_CMPR_PCTSAVE

  dist_keys_threshold   number  :=95 ;  -- e/ index candidate to bitmap ?       LF_ROWS-DISTINCT_KEYS*100/LF_ROWS

-- 2/ cursor

Cursor operation_cur is

select sum(num_rows) over ( partition by table_name),table_name ,index_name ,

       'analyze index '||a.owner||'.'||index_name||' validate structure' order1,

'select '

||'case when PCT_USED <= '||pct_used_threshold||' AND '  -- used space =< 50 %

||'trunc((BTREE_SPACE-USED_SPACE)/1024/1024,2) > '||MB_gain_threshold||' ' -- gain > 250 MB

||' then '

||'decode(SEGMENT_SPACE_MANAGEMENT,''AUTO'','

||'''Alter index '||owner||'.'||index_name||' shrink space ;-- Gain: ''||ceil((BTREE_SPACE-USED_SPACE)/1024/1024)||''MB original size: ''||ceil((BTREE_SPACE)/1024/1024)||''MB'','

||'''Alter index '||owner||'.'||index_name||' rebuild ; -- Gain: ''||trunc((BTREE_SPACE-USED_SPACE)/1024/1024,2)||'' MB'')'

||' else '

||'decode(SIGN(DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS)-'||del_rate_threshold||'),1,' -- Deleted >= 20%

||'decode(SEGMENT_SPACE_MANAGEMENT,''AUTO'','

||'''Alter index '||owner||'.'||index_name||' shrink space ; -- Del.rate too high: ''||trunc(DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS),2)||''%'','

||'''Alter index '||owner||'.'||index_name||' rebuild ; -- Del.rate too high: ''||trunc(DEL_LF_ROWS*100/decode(LF_ROWS, 0, 1, LF_ROWS),2)||''%''),'

||'decode(SIGN(HEIGHT-'||height_threshold||'),1,' -- number of index levels is > 3

||'decode(SEGMENT_SPACE_MANAGEMENT,''AUTO'','

||'''Alter index '||owner||'.'||index_name||' shrink space ;'','

||'''Alter index '||owner||'.'||index_name||' rebuild ;''),'

||'case when OPT_CMPR_COUNT > '||opt_cmpr_threshold||' AND ' -- index key compression

||'OPT_CMPR_PCTSAVE > '||opt_cmpr_gain_threshold||''  -- compressed gain >= 30 %

||' then '

||'''Alter index '||owner||'.'||index_name||' rebuild compress ''||OPT_CMPR_COUNT||''; -- Gain: ''||OPT_CMPR_PCTSAVE||''% => ''||trunc(OPT_CMPR_PCTSAVE*d.blocks*8192/(1024*1024),2)||''MB'''

||' else '

||'decode(SIGN((LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS)-'||dist_keys_threshold||'),1,'

||''' -- '||owner||'.'||index_name||' Candidate for Bitmap: ''||trunc((LF_ROWS-DISTINCT_KEYS)*100/ decode(LF_ROWS,0,1,LF_ROWS),2)||''%'')'

||' end )) '

||' end '

||'from index_stats d , dba_segments e , dba_tablespaces f where e.TABLESPACE_NAME = f.TABLESPACE_NAME and e.segment_name = '''||index_name||''' '

||'and name = '''||index_name||''' ' order2

       ,trunc(100*(row_number() over (order by table_name,index_name)/count(*) over ()),2) cnt_l

       ,trunc(100*sum(num_rows) over ( order by  table_name,index_name rows between unbounded preceding and current row )/sum(num_rows) over(),2) cnt_r

    from dba_indexes a , ( select owner sowner ,segment_name,sum(bytes) from dba_segments group by owner,segment_name ) b

    where owner=''||SCH||''

    and   owner=b.sowner

    and   index_name=b.segment_name

--    and table_name like 'TABLE1'

    and num_rows > 0

    --and rownum < 4

    order by table_name ,index_name;

  TYPE    Cur_Typ IS REF CURSOR;

  cur_dyn Cur_Typ;

  order2  varchar2(4000);

  order3  varchar2(4000);

  tables  varchar2(4000):='x';

  Stats   varchar2(4000):='';

begin

        dbms_output.disable;        -- *** Line added ***

        dbms_output.enable(100000); -- *** Line added ***

  dbms_output.put_line('-- ===========================');

  dbms_output.put_line('-- ANALYZE DATABASE INDEX v1.0');

  dbms_output.put_line('-- ===========================');

  dbms_output.put_line('-- Start date:'||to_char(sysdate,'DD/MM/YY HH24:MI:SS'));

  dbms_output.put_line('-- ----------------------------');

  dbms_output.put_line('--');

  dbms_application_info.set_module('ANA.IDX DB v1.0','@');

  dbms_output.put_line('--      Stats: Blevel - Del - Rows - Distinct - used - Opt – PctOpt – Idx_Sz_MB');

  for operation_rec in operation_cur loop

       Stats:='OK';

       if tables<>operation_rec.table_name then

          tables:=operation_rec.table_name;

          dbms_output.put_line('-- ');

          dbms_output.put_line('-- TABLE NAME : '||operation_rec.table_name);

          dbms_application_info.set_module('ANA.IDX DB v1.0-'||operation_rec.cnt_l||'-'||operation_rec.cnt_r,'Tbl:'||operation_rec.table_name);

       end if;

       dbms_application_info.set_client_info('Ana.Idx:'||operation_rec.index_name||'('||to_char(sysdate,'HH24:mi')||')');

       dbms_output.put_line('--      Ana.Idx: ('||to_char(sysdate,'DD/MM/YY HH24:mi:ss')||') -- '||operation_rec.index_name);

       Begin

        execute immediate operation_rec.order1;

       exception

        WHEN OTHERS THEN

          Stats:='NOK';

       end;

       dbms_application_info.set_client_info('Wri.Idx:'||operation_rec.index_name||'('||to_char(sysdate,'HH24:mi')||')');

       open cur_dyn for operation_rec.order2;

        fetch cur_dyn INTO order2;

       close cur_dyn;

       if stats='OK' then

select height||'-'||DEL_LF_ROWS||'-'||LF_ROWS||'-'||DISTINCT_KEYS||'-'||PCT_USED||'-'||OPT_CMPR_COUNT||'-'||OPT_CMPR_PCTSAVE||'-'||ceil((BTREE_SPACE)/1024/1024)

                       into order3 from index_stats;

          dbms_output.put_line('--      Stats: '||order3);

          dbms_output.put_line(order2);

       else

          dbms_output.put_line('--      '||operation_rec.index_name||' --> Resource busy NOT ANALYZED');

       end if;

  end loop;

  dbms_application_info.set_module('GENE.COMPRESS DATABASE v1.0','End date :'||to_date(sysdate,'DD/MM/YY HH24:MI:SS'));

  dbms_output.put_line('--');

  dbms_output.put_line('-- ---------------------------');

  dbms_output.put_line('-- End date:'||to_char(sysdate,'DD/MM/YY HH24:MI:SS'));

  dbms_output.put_line('-- ===========================');

  dbms_output.put_line('-- ANALYZE DATABASE INDEX v1.0');

  dbms_output.put_line('-- ===========================');

end;

/

$ nohup sqlplus system  @ana_idx_fra.sql SCH1 &

old   3:   schema        VARCHAR2 (20)   := '&1' ;

new   3:   schema        VARCHAR2 (20)   := 'SCH1' ;

-- ===========================

-- ANALYZE DATABASE INDEX v1.0

-- ===========================

-- Start date:14/06/16 15:34:03

-- ----------------------------

--

--      Stats: Blevel - Del - Rows - Distinct - used - Opt – PctOpt – Idx_Sz_MB

--

-- TABLE NAME : AM_ACTION

--      Ana.Idx: (14/06/16 15:34:03) -- IDX_AM_ACTION215

--      Stats: 3-0-690588-72828-90-1-23-12

--      Ana.Idx: (14/06/16 15:34:04) -- PK_AM_ACTION

--      Stats: 3-0-690621-690621-90-0-0-12

--

-- TABLE NAME : USERS_MON

--      Ana.Idx: (14/06/16 15:34:04) -- USERS_MON_I01

--      Stats: 3-0-6736638-6728353-46-0-0-256

Alter index ADB.USERS_MON_I01 shrink space ;-- Gain: 140MB original size: 256MB

--

-- ---------------------------

-- End date:14/06/16 15:34:09

-- ===========================

-- ANALYZE DATABASE INDEX v1.0

-- ===========================

PL/SQL procedure successfully completed.

-- ===========================

-- ANALYZE DATABASE INDEX v1.0

-- ===========================

-- Start date:18/06/14 16:26:39

-- ----------------------------

--

--      Stats: Blevel - Del - Rows - Distinct - used - Opt - PctOpt

--

-- TABLE NAME : TABLE1

--      Ana.Idx: (18/06/14 16:26:44) -- I_FK_T_MISSION0

--      Stats: 3-883093-38704947-3194090-59-1-27

Alter index SCH1.I_FK_T_MISSION0 shrink space ;-- Gain: 432MB original size: 1047MB

--      Ana.Idx: (18/06/14 16:38:53) -- I_TABLE1_TRC_HEURE

--      Stats: 4-3472774-41294628-749-42-1-41

Alter index SCH1.I_TABLE1_TRC_HEURE shrink space ;-- Gain: 1068MB original size: 1821MB

--      Ana.Idx: (18/06/14 16:57:42) -- PK_TABLE1

--      Stats: 3-853192-38675046-38675046-66-0-0

Alter index SCH1.PK_TABLE1 shrink space ;-- Gain: 317MB original size: 907MB

--

-- ---------------------------

-- End date:18/06/14 17:06:10

-- ===========================

-- ANALYZE DATABASE INDEX v1.0

-- ===========================

PL/SQL procedure successfully completed.

Elapsed: 00:39:32.31

SHRINK

set timing on

Alter index SCH1.PK_TABLE1 shrink space ;

Elapsed: 00:21:09.88

Ask again for Index Stats :

--      Stats: Blevel - Del - Rows - Distinct - used - Opt – PctOpt – Idx_Sz_MB

-- TABLE NAME : TABLE1

--      Ana.Idx: (20/06/14 10:15:04) -- PK_TABLE1

--      Stats: 3-245-37940694-37940694-97-0-0-601

!! The SHRINK Commands are very Log Consuming !!