Avec Oracle 10gR2, 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.
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 :
SQL> 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) :
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.
SQL > 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');
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 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
La fonction asa_recommendations du package dbms_space retourne toutes les recommandations du segment advisor lancé automatiquement par le système ou manuellement :
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 :
Cette fonction retourne un objet asa_reco_row_tb de type RECORD :
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.
Pour récupérer l'espace des tables et indexes : 2 options
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)
»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; ...
COMMIT et utiliser la commande
ALTER TABLE ... MOVELa 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
É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;
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.
| Version | Date | Commentaires |
|---|---|---|
| 1.0 | 01/2012 | Version initiale |
SQLPAC : Méthodes
d'allocations des segments avec Oracle 9i. Liste des blocs libres (freelists)
vs ASSM (Automatic Storage Space Management)
SQLPAC : Oracle 9i et 10g -
Défragmentation des tables et indexes (critères et méthodes)