Managing Storage Space - Segment Shrink

 Ongi volta che viene effettuato un'update, una delete su oggetti di un tablespace, possono essere creati degli spazio vuoti che non sono sufficentmente grandi da contenere nuovi dati.

Questi spazi vuoti vengono chiamati fragmented free space. 

Gli oggetti che hanno queste sacche di spazio vuoto possono avere un impatto negativo sulle performance del database, pertanto esistono delle tecniche che individuano questi spazi vuoti, li deframmentano e li recalamano: si parla di online segment shrink. 

Nel Data Dictionary oltre ad essere tracciato l'allocazione degli extent è tracciato lo spazio usato rispetto a quello allocato, tramite il parametro HIGH WATER MARK.
Questo parametro indica l'ultima posizione nell'ultimo extent usato all'interno di un segment associato ad una tabella (ad esempio): lo spazio al di sopra di HWM è libero, al di sotto è occupato.

Il segment shrink consiste nel recuperare spazio al di sotto delll'hwm (liberando spazio e resettando il valore di hwm). 

Poiché vengono mosse le righe e cambiano i ROWIDs occorre abilitare la tabella alla modalità "row movement" e il tablespace di appartenenza deve eesere gestito in modalità ASSM (automatic segment space management).

SQL > alter table .... enable row movement;

Inoltre occorre disabilitare trigger, dml e query basati sul rowid.
Può essere eseguita on line, pertanto durante lo shrink vengono bloccate le dml che operano su quei segmenti mentre gli indici risultano utilizzabili.

Segment Advisor è la procedura che permette di individuare gli oggetti che beneficiano del segment shrink.

Può essere applicata solo ai segment che appartengono a tablespace creati con le caratteristiche "extent mangement local " e "segment space management auto".

Il Segment Advisor può essere automatizzato o manuale e in questo caso si usa il package DBMS_ADVISOR.

Colleghiamoci ad un pluggable database in stato read e write.

show con_name;
CON_NAME 
------------------------------
PDBTS

show user;
USER è "PDBTSADM"

Creiamo un tablespace da mettere sotto monitororaggio.

create tablespace tbsalert
datafile '/u01/app/oracle/oradata/ORCL/pdbts/tbsalert.dbf'
size 50M logging
extent management local
segment space management auto;

La percentuale di spazio libero del tbs è 98%.

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes,
fs.bytes*100/ df.bytes PCT_FREE
FROM DBA_DATA_FILES df , DBA_FREE_SPACE fs
WHERE df.tablespace_name = fs.tablespace_name
AND df.tablespace_name = 'TBSALERT';

Creiamo una tabella e popoliamola:

create table test5 ( x  number , y varchar2(100) ) tablespace TBSALERT;

begin 

for i in 1..1000000

loop

insert into test5 values (i,'test');

end loop;

commit;

end;

/

Vediamo nuovamente lo spazio occupato nel tbs.

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 

fs.bytes*100/ df.bytes PCT_FREE

FROM DBA_DATA_FILES df ,DBA_FREE_SPACE fs

WHERE df.tablespace_name = fs.tablespace_name

AND df.tablespace_name = 'TBSALERT';

TABLESPACE                           FREE      BYTES   PCT_FREE

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

TBSALERT                         37748736   52428800         64

Cancelliamo i dati e veifichiamo lo spazio coccupato:

SQL> delete from test5;

SQL> commit;

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 

fs.bytes*100/ df.bytes PCT_FREE

FROM DBA_DATA_FILES df ,DBA_FREE_SPACE fs

WHERE df.tablespace_name = fs.tablespace_name

AND df.tablespace_name = 'TBSALERT';

TABLESPACE                           FREE      BYTES   PCT_FREE

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

TBSALERT                         37748736   52428800         64

Vuol dire che sono stati eliminati i dati dai blocchi ma gli stessi blocchi non sono ancora liberi per  essere occupati da nuovi dati.

1) Creiamo un task Segment Advisor

 DECLARE

 tname VARCHAR2(128) := 'my_seg_task3';

 tname_desc VARCHAR2(128) := 'Get shrink advice for segments in TBSALERT';

 task_id NUMBER;

 object_id NUMBER;

 objectname VARCHAR2(100);

 objecttype VARCHAR2(100);

 BEGIN

 dbms_advisor.create_task('Segment Advisor', task_id,tname,tname_desc,NULL);---select * from DBA_ADVISOR_DEFINITIONS

 dbms_advisor.create_object(tname,'TABLESPACE','TBSALERT',' ',' ',NULL, ' ',object_id) ;

 dbms_advisor.set_task_parameter(tname,'RECOMMEND_ALL','TRUE');

 END;

2) Esegui il task.

DECLARE

tname VARCHAR2(128) := 'my_seg_task3';

BEGIN

dbms_advisor.EXECUTE_TASK(tname);

END;

Per verificare che l'esecuzione del task è terminata correttamente:

SELECT  description

FROM     DBA_ADVISOR_TASKS

WHERE  task_name ='my_seg_task3';

DESCRIPTION                       

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

Get shrink advice for segments in TBSALERT

3) Eseguendo questa query, Oracle dice cosa fare.

SELECT  attr1, attr2, message

FROM    DAB_ADVISOR_FINDINGS f,  DBA_ADVISOR_OBJECTS o

WHERE  f.task_name = o.task_name 

AND       f.object_id = o.object_id 

AND       f.task_name = 'my_seg_task3';

ATTR1                     ATTR2                      MESSAGE 

 --------------------------------- --------------------------------------------------------------------------------------
PDBTSADM          TEST5          Abilitare lo spostamento delle righe della tabella PDBTSADM.TEST5 ed eseguire la riduzione, risparmio stimato di 17787682 byte.

ALTER TABLE test5 ENABLE ROW MOVEMENT;

ALTER TABLE test5 SHRINK SPACE;

Rieseguendo la query vediamo che lo spazio libero è aumentato

SELECT df.tablespace_name tablespace, fs.bytes free, df.bytes, 

fs.bytes*100/ df.bytes PCT_FREE

FROM dba_data_files df ,dba_free_space fs

WHERE df.tablespace_name = fs.tablespace_name

AND df.tablespace_name = 'TBSALERT';

TABLESPACE                           FREE      BYTES   PCT_FREE

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

TBSALERT                         51314688   52428800     97,875