Quand réorganiser les tables et les index ? C’est à cette question que répond le « Segment Advisor ». Dans l’exemple ci-dessous, nous allons illustrer comment il fonctionne.

Création d’un schéma
Dans le schéma ci-dessous, la table DEMO#7 est volontairement remplie puis vidée pour examiner les conseils du Segment Advisor. Le script ci-dessous permet de créer les objets du schéma.

create table DEMO#7 (col1 number primary key, col2 number, col3 varchar2(4000)) tablespace users;

begin
for i in 1..10000 loop
for j in 1..10 loop
insert into DEMO#7 values ((j-1)*10000+i, j,
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’||
‘AAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAAA’);
end loop;
end loop;
commit;
end;
/

delete from demo#7 where col2>1;

commit;

exec dbms_stats.gather_table_stats(USER, ‘DEMO#7’, cascade=>true,-
method_opt=>’FOR ALL COLUMNS SIZE AUTO’);

Utilisation du Segment Advisor
Le segment advisor est utilisé à partir du package DBMS_ADVISOR. les vues DBA_ADVISOR_OBJECTS et DBA_ADVISOR_FINDINGS permettent d’afficher les conseils. Dans le cas qui suit, l’Advisor indique que la table peut être réduire au moyen d’un SHRINK et que l’espace gagné serait de 101 Mo environ.

VARIABLE ID NUMBER;
BEGIN
DECLARE
TASK_ID NUMBER;
NAME VARCHAR2(100) ;
DESCR VARCHAR2(500) ;
OBJID NUMBER;
BEGIN
NAME := ‘DEMO#7’;
DESCR := ‘SEGMENT ADVISOR ON A TABLE DEMO#7’;
DBMS_ADVISOR.CREATE_TASK(‘Segment Advisor’, :ID, NAME, DESCR, NULL);
DBMS_ADVISOR.CREATE_OBJECT(NAME, ‘TABLE’, USER,’DEMO#7′, NULL, NULL, OBJID);
DBMS_ADVISOR.SET_TASK_PARAMETER(NAME, ‘RECOMMEND_ALL’, ‘TRUE’);
DBMS_ADVISOR.EXECUTE_TASK(NAME);
END;
END;
/

set lines 120
col task_name format A10
col segname format A10
col type format A10
select af.task_name, ao.attr2 segname, ao.type, af.message
from dba_advisor_findings af, dba_advisor_objects ao
where ao.task_id = af.task_id
and ao.object_id = af.object_id
and ao.attr2=’DEMO#7′;

TASK_NAME SEGNAME TYPE
———- ———- ———-
MESSAGE
—————————————
DEMO#7 DEMO#7 TABLE
Activez le déplacement de lignes de la table SYSTEM.DEMO#7 et effectuez une
réduction ; ceci devrait permettre d’économiser 101809859 octets.

Taille du segment et SHRINK
Dans le script qui suit, nous allons vérifier la taille du SEGMENT DEMO#7 puis effectuer le SHRINK et valider que l’espace promis est gagné. Pour pouvoir effectuer le shrink, il faut que le tablespace dans lequel il est effectué soit Locally Managed. Il faut également que la table ait « ROW MOVEMENT ENABLED ».

select BYTES/1024/1024 SIZE_MB
from dba_segments
where owner=user and SEGMENT_NAME=’DEMO#7′;

SIZE_MB
———-
120

alter table demo#7 enable row movement;
alter table demo#7 shrink space;

select BYTES/1024/1024 SIZE_MB
from dba_segments
where owner=user and SEGMENT_NAME=’DEMO#7′;

SIZE_MB
———-
11,4375

Pour terminer, supprimer la tâche associée au Segment Advisor et la table DEMO#7 :

exec DBMS_ADVISOR.DELETE_TASK(‘DEMO#7’)
drop table DEMO#7 purge;

GarK!