Vue matéralisée et saturation du tablespace UNDO

[Bktero]

Bonjour,

Tout d'abord, je dois dire que je me vraiment pas un expert en administration Oracle. Mais je viens d'arriver sur un projet et on me refile à problème détecté en production et on m'a demandé d'investiguer. Si je dis des trucs louches, n'hésitez donc pas à me reprendre.

Le problème : la production nous dit qu'une vue matérialisée (récemment mise en place) ne s'est jamais rafraichie depuis (la MAJ tombant en timeout). De plus, elle sature le UNDO tablespace et les autres processus renvoient souvent des erreurs ORA 30036.


1 )L'erreur ORA est effectivement due à une saturation du tablespace UNDO, il faudrait donc en augmenter la capacité. La question est de combien...

2) Parallèlement, je me pose la question : la saturation du UNDO est-elle liée à la non mise à jour de la vue matérialisée ?

J'ai envie de répondre "oui" à cette question vus les éléments que j'ai pu trouvé sur le net. La VM est en refresh COMPLETE, donc le SGBD essaye de tout mettre dans le UNDO, c'est pas assez grand, il attend au cas où de la place se libère, il finit par tomber en timeout mais pendant ce temps les autres processus se choppent des ORA 30036 car pas de place pour eux dans le UNDO.

Pour faire mes tests, j'ai une copie de l'environnement de prod (au moins partielle). J'ai essayé hier soir de dropper et de recréer la table, ça a marché, elle contient bien 189 millions de lignes comme avant d'avoir été droppé. J'ai effectué quelques requêtes pour voir :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
 
/* Espace alloue dans les tablespaces */
SELECT TABLESPACE_NAME
, SUM(BLOCKS) AS alloue
FROM dba_data_files
WHERE TABLESPACE_NAME = 'APPS_UNDOTS1'
GROUP BY TABLESPACE_NAME
;
 
 
/* Espace occupe dans les tablespaces */
SELECT TABLESPACE_NAME
, SUM(BLOCKS) AS occupe
FROM dba_segments
WHERE TABLESPACE_NAME = 'APPS_UNDOTS1'
GROUP BY TABLESPACE_NAME
;
 
 
/* Espace libre dans les tablespaces */
SELECT TABLESPACE_NAME
, SUM(BLOCKS) AS libre
FROM dba_free_space
WHERE TABLESPACE_NAME = 'APPS_UNDOTS1'
GROUP BY TABLESPACE_NAME
;
 
 
/* Liste des tables avec la taille en blocs pour l'utilisateur XXCN */
SELECT OWNER
, TABLE_NAME
, TABLESPACE_NAME
, NUM_ROWS
, BLOCKS
, EMPTY_BLOCKS
, LAST_ANALYZED 
FROM  DBA_TABLES
WHERE TABLE_NAME = 'XXCN_DETAILPAIEMENTLOT_VM'
;

Ce qui donne :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
TABLESPACE_NAME                ALLOUE                 
------------------------------ ---------------------- 
APPS_UNDOTS1                   4731392                
 
TABLESPACE_NAME                OCCUPE                 
------------------------------ ---------------------- 
APPS_UNDOTS1                   4698472                
 
TABLESPACE_NAME                LIBRE                  
------------------------------ ---------------------- 
APPS_UNDOTS1                   32768  
 
OWNER                          TABLE_NAME                     TABLESPACE_NAME                NUM_ROWS               BLOCKS                 EMPTY_BLOCKS           LAST_ANALYZED             
------------------------------ ------------------------------ ------------------------------ ---------------------- ---------------------- ---------------------- ------------------------- 
XXCN                           XXCN_DETAILPAIEMENTLOT_VM      APPS_TS_TX_DATA                                                                                                               
APPS                           XXCN_DETAILPAIEMENTLOT_VM      APPS_TS_TX_DATA

Auriez-vous des éléments pour valider ma théorie ou alors m'aiguiller sur une autre piste SVP ?

Merci d'avance !

[Bktero]

PS : et aussi

Code :

1
2
3
4
5
6
7
8
9
10
11
12
SELECT OWNER
, MVIEW_NAME
, REFRESH_MODE
, REFRESH_METHOD
, LAST_REFRESH_TYPE
, LAST_REFRESH_DATE
, FAST_REFRESHABLE
, STALENESS
, STALE_SINCE
FROM DBA_MVIEWS
WHERE MVIEW_NAME = 'XXCN_DETAILPAIEMENTLOT_VM'
      AND OWNER = 'XXCN';

Code :

1
2
3
OWNER                          MVIEW_NAME                     REFRESH_MODE REFRESH_METHOD LAST_REFRESH_TYPE LAST_REFRESH_DATE         FAST_REFRESHABLE   STALENESS           STALE_SINCE               
------------------------------ ------------------------------ ------------ -------------- ----------------- ------------------------- ------------------ ------------------- ------------------------- 
XXCN                           XXCN_DETAILPAIEMENTLOT_VM      DEMAND       COMPLETE       COMPLETE          10/05/11 19:07:02         DIRLOAD_DML        UNUSABLE

Le champ STALENESS à UNUSUABLE ne veut-il pas dire que la VM ne s'est pas si bien que ça créée ?