ORA-01555 caused by SQL statement below

14 déc

Bonjour ami(e)s DBA

 

Mon alerte_log me remonte ce  code d’erreur ORA-01555 sur une base de production d’un client:

on peut aussi avoir ce message lors d’un export datapump

Analyse :

En consultant le fichier je remarque plusieurs requêtes récurrentes  select * from tab(‘val’,'val2′) , ces requêtes ont un temps d’exécution supérieur à la valeur actuel du undo_retention qui est de  7200

Explication :

En effet, à partir de Oracle9i, les rollback segments sont rebaptisés « journaux d’annulations ».

Traditionnellement les informations d’annulation des transaction sont stockés dans des rollback segments jusqu’à ce qu’une instruction COMMIT ou ROLLBACK soit exécutée, et à partir de ce moment, ces segments sont disponibles pour être réutilisés.

En plus, avec la gestion automatique des annulations (apparue en 10g), le DBA peut spécifier une durée de conservation supplémentaire des informations d’annulation après le COMMIT, afin d’éviter les erreurs de type « snapshot too old » sur les longues instructions.

Ceci est fait en définissant le paramètre UNDO_RETENTION. Sa valeur par défaut étant de 900 secondes (5 minutes), vous pouvez le modifier afin de garantir qu’Oracle conserve les journaux d’annulations pour des périodes plus longues. Vous devrez peut-être aussi garantir que cette durée de conservation soit respectée, et ceci en activant l’option « GARANTI » du tablespace UNDO

 

Question  quelle est la valeur juste pour le parametre undo_retention ?

Pour moi la valeur correct doit correspondre  à la valeur max  sur un mois

voici la requête

SQL> select max(MAXQUERYLEN)
from v$undostat
where begin_time between to_date(’11/01/2016 22:30:00′,’MM/DD/YYYY HH24:MI:SS’)
and to_date(’11/29/2016 09:30:00′,’MM/DD/YYYY HH24:MI:SS’)
order by begin_time;

MAX(MAXQUERYLEN)
—————-
8510

 

Toutefois, il convient d’ajuster les paramètres importants suivants :

1 – La taille du tablespace UNDO

2 – Le paramètres d’initialisation UNDO_RETENTION

voici la formule qui permet d’avoir une taille optimal

optmal undo retention = actual undo size /(db_block_size * undo_block_per_sec)

Dans mon cas le tablespace est en autoextend yes avec une valeur à unlimited (  préco de l’éditeur du logiciel , suis pas tres d’accord mais bon …)

calcule de la taille du tablespace undo optimal qui supportera l’activité de la base  :

 

 

SQL> SELECT d.undo_size/(1024*1024) « ACTUAL UNDO SIZE [MByte]« ,
2 SUBSTR(e.value,1,25) « UNDO RETENTION [Sec]« ,
3 (TO_NUMBER(e.value) * TO_NUMBER(f.value) *
4 g.undo_block_per_sec) / (1024*1024)
5 « NEEDED UNDO SIZE [MByte]« 
6 FROM (
7 SELECT SUM(a.bytes) undo_size
8 FROM v$datafile a,
9 v$tablespace b,
10 dba_tablespaces c
11 WHERE c.contents = ‘UNDO’
12 AND c.status = ‘ONLINE’
13 AND b.name = c.tablespace_name
14 AND a.ts# = b.ts#
15 ) d,
16 v$parameter e,
17 v$parameter f,
18 (
SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’

 

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] NEEDED UNDO SIZE [MByte]
———————— ————————- ————————
10592              7200                              2012.39063

 

 

La taille du undo calculer (2012.4) est inférieur à la taille actuelle rien à faire .

 

la requête suivant permet de calculer la durée optimal pour le undo retention

 

SQL> SELECT d.undo_size/(1024*1024) « ACTUAL UNDO SIZE [MByte]« ,
2 SUBSTR(e.value,1,25) « UNDO RETENTION [Sec]« ,
3 ROUND((d.undo_size / (to_number(f.value) *
4 g.undo_block_per_sec))) « OPTIMAL UNDO RETENTION [Sec]« 
5 FROM (
6 SELECT SUM(a.bytes) undo_size
7 FROM v$datafile a,
8 v$tablespace b,
9 dba_tablespaces c
10 WHERE c.contents = ‘UNDO’
11 AND c.status = ‘ONLINE’
12 AND b.name = c.tablespace_name
13 AND a.ts# = b.ts#
14 ) d,
15 v$parameter e,
16 v$parameter f,
17 (
18 SELECT MAX(undoblks/((end_time-begin_time)*3600*24))
19 undo_block_per_sec
FROM v$undostat
) g
WHERE e.name = ‘undo_retention’
AND f.name = ‘db_block_size’ 20 21 22 23 ;

ACTUAL UNDO SIZE [MByte] UNDO RETENTION [Sec] OPTIMAL UNDO RETENTION [Sec]
———————— ————————- —————————-
10592             7200                    37896

 

La durée est de 37896 seconde soit  10h

Solution :

alter system set undo_retention = 37896

 

oraclementvotre

 

 

 

Pas encore de commentaire

Laisser une réponse

Unblog.fr | Créer un blog | Annuaire | Signaler un abus