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 4OBJECT 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 descVoici 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 commandealter 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 spacedes commandesalter tableetalter 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é. Dimensionner le tablespace d’UNDO pour ces opérations dans un environnement de pré-production ou de qualification avec une volumétrie équivalente à la production si cela est possible.
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 compatibleNAME 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.