Oracle 10g R2 et le segment advisor. Défragmenter et récupérer l'espace dans les instances en mode ASSM

Introduction

Avec Oracle 10g R2, le package DBMS_JOB est obsolète et remplacé par le package PL/SQL DBMS_SCHEDULER. Ce package permet de programmer des jobs.

Un job système est créé automatiquement avec Oracle 10g R2 pour déclencher à intervalles réguliers le conseiller de la gestion automatique des segments (Automatic Segment Advisor), conseiller très utile pour obtenir rapidement un rapport sur l'état de fragmentation, la récupération d'espace possible pour des tables, indexes etc... dans une instance Oracle dont les tablespaces sont en mode ASSM (Automatic Storage Space Management). Pour en savoir plus sur le mode ASSM : Méthodes d'allocations des segments avec Oracle 9i. Liste des blocs libres (freelists) vs ASSM (Automatic Storage Space Management)

Cet article présente ce job automatique, un exemple concret de rapport obtenu avec ce conseiller Segment Advisor grâce à dbms_space.asa_recommendations et la défragmentation des tables et indexes avec la nouvelle option Oracle 10g R2 shrink space des commandes alter table et alter index.

Le job automatique AUTO_SPACE_ADVISOR_JOB

Pour retrouver le job système AUTO_SPACE_ADVISOR_JOB automatiquement créé avec Oracle 10g R2 et qui déclenche le conseiller Segment Advisor dans une instance Oracle :

SELECT
   a.job_name,
   a.enabled,
   c.window_name,
   c.schedule_name,
   c.start_date,
   c.repeat_interval
FROM
   dba_scheduler_jobs             a,
   dba_scheduler_wingroup_members b,
   dba_scheduler_windows          c
WHERE
   job_name in ('AUTO_SPACE_ADVISOR_JOB')
   and
     a.schedule_name=b.window_group_name
   and
     b.window_name=c.window_name;
JOB_NAME                       ENABL WINDOW_NAME
------------------------------ ----- ------------------------------
SCHEDULE_NAME
--------------------------------------------------------------------------------
START_DATE
---------------------------------------------------------------------------
REPEAT_INTERVAL
--------------------------------------------------------------------------------
AUTO_SPACE_ADVISOR_JOB         TRUE  WEEKEND_WINDOW
freq=daily;byday=SAT;byhour=0;byminute=0;bysecond=0

AUTO_SPACE_ADVISOR_JOB         TRUE  WEEKNIGHT_WINDOW
freq=daily;byday=MON,TUE,WED,THU,FRI;byhour=22;byminute=0; bysecond=0

2 programmations sont réalisées (fenêtre de temps) :

  • À 22h00 les lundi, mardi, mercredi, jeudi et vendredi
  • À 00h00 les samedi

La vue DBA_SCHEDULER_PROGRAMS indique que le job AUTO_SPACE_ADVISOR_JOB déclenche la procédure stockée DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC qui ne prend aucun argument en entrée.

select a.JOB_NAME,
             a.PROGRAM_NAME,
             b.PROGRAM_TYPE,
             b.PROGRAM_ACTION,
             b.NUMBER_OF_ARGUMENTS
          from DBA_SCHEDULER_JOBS a, 
                  DBA_SCHEDULER_PROGRAMS b
          where a.PROGRAM_NAME = b.PROGRAM_NAME
          and a.JOB_NAME='AUTO_SPACE_ADVISOR_JOB'
JOB_NAME                PROGRAM_NAME             PROGRAM_TYPE        PROGRAM_ACTION                              NUMBER_OF_ARGUMENTS
----------------------  -----------------------  -----------------   ----------------------------------------    -------------------
AUTO_SPACE_ADVISOR_JOB  AUTO_SPACE_ADVISOR_PROG  STORED_PROCEDURE    dbms_space.auto_space_advisor_job_proc      0

DBMS_SPACE.AUTO_SPACE_ADVISOR_JOB_PROC déclenche le conseiller de la gestion automatique des segments (Automatic Segment Advisor), conseiller qui identifie les segments présentant de la place à récupérer et donne des recommandations à consulter avec Enterprise Manager ou les vues DBA_AUTO_SEGADV_SUMMARY et DBA_AUTO_SEGADV_CTL.

Si le lancement manuel du conseiller de la gestion automatique des segments est préféré au déclenchement automatique par DBMS_SCHEDULER, ce job est désactivé avec la procédure disable du package DBMS_SCHEDULER :

exec dbms_scheduler.disable('AUTO_SPACE_ADVISOR_JOB');

Les vues DBA_AUTO_SEGADV_SUMMARY et DBA_AUTO_SEGADV_CTL

La vue DBA_AUTO_SEGADV_SUMMARY

La vue DBA_AUTO_SEGADV_SUMMARY fournit un résumé des dernières exécutions du conseiller sur la gestion des segments et notamment le nombre de segments analysés lors d'une exécution :

select segments_processed, recommendations_count, end_time from dba_auto_segadv_summary order by 3;
SEGMENTS_PROCESSED RECOMMENDATIONS_COUNT END_TIME
------------------ --------------------- ----------------------------------
                90                    42 23/01/12 22:07:41,373682
                68                    38 24/01/12 22:06:23,982241
                68                    40 25/01/12 22:06:45,724990
                65                    40 26/01/12 22:07:00,389867
                15                    89 27/01/12 22:24:41,568701
                43                    24 28/01/12 06:03:06,207762

La vue DBA_AUTO_SEGADV_CTL

La vue DBA_AUTO_SEGADV_CTL indique la raison technique pour laquelle un segment a été choisi pour analyse :

select segment_owner||'.'||segment_name as object, 
        status, 
        reason, 
        end_time 
from dba_auto_segadv_ctl
order by 4
OBJECT                    STATUS      REASON          END_TIME
------------------------  ----------  --------------  -----------------------
RISK.CDO_NETTING          COMPLETE    SCAN            24/01/12 22:03:01,658848
RISK.AUDIT_MVT            COMPLETE    IO              28/01/12 06:00:42,159814
RISK.INDICATEUR           COMPLETE    SPACE_USAGE     28/01/12 06:02:37,608511
RISK.GRECQUE_TAUX         COMPLETE    SPACE_USAGE     28/01/12 06:02:53,240061

Le rapport du segment advisor : dbms_space.asa_recommendations()

La fonction asa_recommendations du package dbms_space retourne toutes les recommandations du segment advisor lancé automatiquement par le système ou manuellement :

exec dbms_space.asa_recommendations (
   all_runs      IN VARCHAR2 DEFAULT := TRUE,
   show_manual   IN VARCHAR2 DEFAULT := TRUE,
   show_findings IN VARCHAR2 DEFAULT := 'FALSE')
RETURN asa_reco_row_tb PIPELINED;

Dans les paramètres de cette fonction :

  • all_runs : TRUE (défaut), retourne les recommandations et éléments détectés pour tous les déclenchements du segment advisor. Lorsque ce paramètre est à FALSE, seuls les éléments détectés et recommandations du dernier déclenchement du segment advisor sont affichés.
  • show_manual : TRUE (défaut), retourne les résultats uniquement pour les invocations manuelles du segment advisor. Les résultats des invocations automatiques systèmes du segment advisor sont également affichés lorsqu'il est à FALSE.
  • show_findings : FALSE (défaut), affiche les éléments trouvés ainsi que les recommandations. Lorsque ce paramètre est positionné à TRUE, les éléments trouvés sont affichés sans les recommandations.

Cette fonction retourne un objet asa_reco_row_tb de type RECORD :

CREATE TYPE asa_reco_row_tb IS RECORD (
  tablespace_name   VARCHAR2(30),
  segment_owner     VARCHAR2(30),
  segment_name      VARCHAR2(30),
  segment_type      VARCHAR2(18),
  partition_name    VARCHAR2(30),
  allocated_space   NUMBER,
  used_space        NUMBER,
  reclaimable_space NUMBER,
  chain_rowexcess   NUMBER,
  recommendations   VARCHAR2(1000), 
  c1                VARCHAR2(1000),
  c2                VARCHAR2(1000),
  c3                VARCHAR2(1000),
  task_id           NUMBER,
  mesg_id           NUMBER));

La requête ci-dessous permet d'obtenir un rapport des recommandations (état de fragmentation, récupération d'espace...) sur toutes les exécutions manuelles et automatiques du segment advisor :

set pagesize 1000;
set linesize 1200;

SELECT segment_owner||'.'||segment_name||' ('||segment_type||')' as object,
           round( allocated_space/1024/1024,1 ) alloc_mb,
           round( used_space/1024/1024, 1 ) used_mb,
           round( reclaimable_space/1024/1024) reclaim_mb,
           round( reclaimable_space/allocated_space*100,0 ) pctsave,
           recommendations
      FROM TABLE(dbms_space.asa_recommendations())
     where segment_owner in ('RISK','RMS')
order by 4 desc

Voici un exemple de rapport obtenu avec la requête plus haut :

OBJECT                               ALLOC_MB   USED_MB    RECLAIM_MB  PCTSAVE   
RECOMMENDATIONS
------------------------------------ ---------- ---------- ---------- ----------
-------------------------------------------------------------
RISK.I_FHP (INDEX)                       5124,3     4062,1       1062         21 
Perform shrink, estimated savings is 1113752402 bytes.

RMS.RM_INSTRUMENT_ST_SV_PK (INDEX)       3934,5     3140,4        794         20 
Perform shrink, estimated savings is 832621752 bytes.

RISK.SCENARIO_LOG (TABLE)                   900      168,4        732         81 
Perform shrink, estimated savings is 767122502 bytes.

RISK.I_INDICATEUR_ARCH_PK (INDEX)        1910,6     1274,8        636         33 
Perform shrink, estimated savings is 666781140 bytes.

RMS.RM_INSTRUMENT_ST_SV_IDX01 (INDEX)    2721,9     2177,1        545         20 
Perform shrink, estimated savings is 571294855 bytes.

RISK.AUDIT_MVT (TABLE)                     1914     1527,1        387         20 
Perform shrink, estimated savings is 405666688 bytes.

RISK.INDICATEUR_ARCH (TABLE)               3077     2818,7        258          8 
Perform shrink, estimated savings is 270808541 bytes.

RISK.TITRES (TABLE)                         346      191,5        155         45 
Perform shrink, estimated savings is 162056809 bytes.

RISK.CDS_PRICING_EXTRACT (TABLE)            704      549,5        154         22 
Perform shrink, estimated savings is 161976139 bytes.

RISK.FUND_HISTORY_POSITIONS (TABLE)      8404,3     8269,2        135          2 
Perform shrink, estimated savings is 141747282 bytes.

RISK.RESIDUAL_EXPO_OTC_IDX2 (INDEX)       418,5      307,2        111         27 
Perform shrink, estimated savings is 116704091 bytes.

RISK.AUDIT_GR_POINTS_INDEX (INDEX)        357,9      249,9        108         30 
Perform shrink, estimated savings is 113332735 bytes.

RISK.PK_AUDIT_GR_POINTS (INDEX)           362,2      257,1        105         29 
Perform shrink, estimated savings is 110248533 bytes.

RISK.DS_UPDATE_ERRORS (TABLE)               157       53,1        104         66 
Perform shrink, estimated savings is 108927277 bytes.

RISK.AUDIT_TITRES (TABLE)                7921,5       7817        104          1 
Perform shrink, estimated savings is 109559997 bytes.

Exploiter les résultats du segment advisor

Récupération de l'espace

Pour récupérer l'espace des tables et indexes : 2 options

  • Déplacer les tables d'un tablespace à l'autre avec la commande alter table ... move . Reconstruire les indexes avec la commande alter index schema.index rebuild nologging. Pour l'utilisation de ces commandes : Oracle 9i et 10g - Défragmentation des tables et indexes (critères et méthodes)
  • Utiliser la nouvelle option 10g shrink space des commandes alter table et alter index. Ces options ne sont disponibles que si les tablespaces sont en mode ASSM (Automatic Storage Space Management).

C'est l'option 2 qui est retenue ici pour utiliser les nouvelles clauses 10g shrink space. Le résultat de la commande dbms_space.asa_recommendations() peut être exploité dynamiquement afin de générer les commandes alter table ... shrink space et alter index ... shrink space

set pagesize 50000;
set linesize 1200;

spool shrink.sql;

SELECT (CASE
    WHEN segment_type='INDEX'
      THEN
        'ALTER INDEX '||segment_owner||'.'||segment_name||' shrink space;'
    WHEN segment_type='TABLE'
      THEN
        'ALTER TABLE '||segment_owner||'.'||segment_name||' enable row movement;'||chr(10)||'ALTER TABLE '||segment_owner||'.'||segment_name||' shrink space;'
        END
      )
FROM TABLE(dbms_space.asa_recommendations())
WHERE segment_owner in ('RISK','RMS')
...
ALTER INDEX RISK.IINFOS_HISTO shrink space;
ALTER INDEX RISK.IINFOS_HISTO2 shrink space;
ALTER INDEX RISK.IINFOS_HISTO3 shrink space;
ALTER INDEX RISK.IINFOS_HISTO4 shrink space;
 ...
ALTER TABLE RISK.TITRES enable row movement;
ALTER TABLE RISK.TITRES shrink space;
 ...
Attention aux plus grosses tables dans la génération réalisée ici. En fonction de la volumétrie, le tablespace UNDO peut être saturé. Dans ce cas de figure, lancer régulièrement la commande COMMIT et utiliser la commande ALTER TABLE ... MOVE

Restrictions pour l'utilisation de la nouvelle clause 10g shrink space

Paramètre compatible

La nouvelle clause shrink space dans les commandes alter table et alter index n'est disponible que si le paramètre compatible de l'instance est à 10.0.0 minimum. Pour vérifier ce paramètre :

show parameter compatible
NAME          TYPE        VALUE
------------- ----------- ------------------------------
compatible    string      10.2.0

Dans le cas contraire, l'erreur ORA-00406 est levée :

alter table RISK.SCENARIO_LOG shrink space;
alter table RISK.SCENARIO_LOG shrink space
*
ERROR at line 1:
ORA-00406: COMPATIBLE parameter needs to be 10.0.0.0.0 or greater

Option enable row movement

Étrangeté propre à Oracle, la commande alter table .... shrink space ne peut pas être lancée sur une table pour laquelle l'option enable row movement n'est pas active. L'erreur ORA-10636 est générée dans ce cas de figure :

alter table RISK.SCENARIO_LOG shrink space;
alter table RISK.SCENARIO_LOG shrink space
*
ERROR at line 1:
ORA-10636: ROW MOVEMENT is not enabled

L'option enable row movement doit être activée au préalable sur la table, si ce n'est pas le cas

alter table RISK.SCENARIO_LOG enable row movement;
alter table RISK.SCENARIO_LOG shrink space;

Indexes fonctions (function based indexes)

La commande alter table .... shrink space ne peut pas être lancée sur une table comportant un index fonction (function based index). L'erreur ORA-10631 est levée dans ce cas de figure :

alter table RISK.SCENARIO_LOG shrink space;
alter table RISK.SCENARIO_LOG shrink space
*
ERROR at line 1:
ORA-10631: SHRINK clause should not be specified for this object

Pour retrouver les tables dans un schéma (RISK ici) qui ne peuvent pas être candidates à la commande alter table ... shrink space à cause de la présence d'indexes fonctions :

SELECT dt.owner, dt.table_name,
    (CASE
        WHEN NVL(ind.cnt, 0) < 1 THEN 'Y'
        ELSE 'N'
    END) AS can_shrink
FROM dba_tables dt,
    (SELECT table_name, COUNT(*) cnt
         FROM dba_indexes di
         WHERE index_type LIKE 'FUNCTION-BASED%'
         GROUP BY table_name) ind
WHERE dt.table_name = ind.table_name(+)
  AND dt.table_name NOT LIKE 'AQ$%'
  AND dt.table_name NOT LIKE 'BIN$%'
  AND dt.owner = 'RISK'
  ORDER BY 1, 2;
OWNER                          TABLE_NAME                     C
------------------------------ ------------------------------ -
RISK                           SCENARIO_LOG                   N
RISK                           AUDIT_COLLATERAL_CONTRACT      Y
RISK                           AUDIT_CR_DIVIDENDE             Y
RISK                           AUDIT_CV_DIVIDENDE             Y
RISK                           AUDIT_EOD_DIVIDENDE            Y

Il faut supprimer l'index fonction avant le lancement de la commande alter table ... shrink space puis le recréer.