Discussion :

[Ikebukuro]

Bonjour,

De temps en temps notre TBS Undo explose car il est sous-dimensionné par rapport à des traitements en cours : nous avons une erreur "ORA-30036: unable to extend segment by 8 in undo tablespace".

On m'a demandé d'identifier la transaction qui pose problème; on ne veut pas dans l'immédiat augmenter la taille du TBS car le problème serait alors masqué alors qu'il faut le résoudre.

Voici ma démarche mais j'ai besoin de votre aide pour valider le process (pour info je travaille avec Toad 9.7.2.5 sans le pack DBA ni aucun outil de monitoring de la base).

Etape 1 : Identifier la taille max du TBS UNDO

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT TABLESPACE_NAME, SUM(DECODE(AUTOEXTENSIBLE, 'YES', MAXBYTES, BYTES))/(1024*1024) “MAX_SIZE in Mo”
FROM   DBA_DATA_FILES
WHERE TABLESPACE_NAME = 'UNDOTBS'
GROUP BY TABLESPACE_NAME;
 
TABLESPACE_NAME                MAX_SIZE in Mo
------------------------------ --------------
UNDOTBS                                 31744
1 row selected.

Etape 2 : Créer une table de test et la remplir avant de modifier ses champs et voir combien de blocs Undo sont utilisés. J'ai inséré un million d'enregistrements. La table n'a aucun index.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
 
 
CREATE TABLE GSA.ZZZ_TEST_DDU
(
  ID   NUMBER(12),
  LIB  VARCHAR2(200 BYTE)
)
TABLESPACE TBS_DATA
PCTUSED    0
PCTFREE    10
INITRANS   1
MAXTRANS   255
STORAGE    (
            INITIAL          64K
            NEXT             1M
            MINEXTENTS       1
            MAXEXTENTS       UNLIMITED
            PCTINCREASE      0
            BUFFER_POOL      DEFAULT
           )
LOGGING 
NOCOMPRESS 
NOCACHE
NOPARALLEL
MONITORING;
 
desc zzz_test_ddu
TABLE zzz_test_ddu
 Name                                      Null?    Type                        
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(12,)                 
 LIB                                                VARCHAR2(200)

Etape 3 : vérifier avant ma modif la taille occupée par les transactions en cours. Mon user est X097933 et je ne suis pas présent.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
SELECT         S.USERNAME AS "SESSION USERNAME", 
S.OSUSER AS "OS USERNAME", 
 (T.USED_UBLK * (select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'UNDOTBS'))/(1024*1024) AS "Taille UNDOTBS used Mo"
FROM V$SESSION S, V$TRANSACTION T
WHERE S.SADDR = T.SES_ADDR;
 
SESSION USERNAME               OS USERNAME                   Taille UNDOTBS used Mo
------------------------------------------------------------------------------------------------
SIR                                      X120341                            .0078125

Etape 4 : modification de ma table

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

update zzz_test_ddu set lib = lib||'2';

Etape 5 : vérifier après ma modif la taille occupée par les transactions en cours
Il s'agit bien de mon user X097933 MAIS la taille des segments UNDO prise me semble excessif par rapport à ma table.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT         S.USERNAME AS "SESSION USERNAME", 
S.OSUSER AS "OS USERNAME", 
 
 (T.USED_UBLK * (select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'UNDOTBS'))/(1024*1024) AS "Taille UNDOTBS used Mo"
FROM V$SESSION S, V$TRANSACTION T
WHERE S.SADDR = T.SES_ADDR ;
 
SESSION USERNAME               OS USERNAME                   Taille UNDOTBS used Mo
-----------------------------------------------------------------------------------------------
GSA                            X097933                                    173.65625

Etape 6 : Vérifier la taille de ma table en base.
Attention pour les VARCHAR(2) : un caractère peut-être stocké sur N octets ! On fera donc une approximation en estimant : 1 caractère = 1 octet.
J'obtiens 21 mégas alors que ma transaction occupe 174 mégas dans le TBS Undo : est-ce normal ce rapport de 7? Il me semble excessif.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
SELECT  COUNT(*), 
        ROUND(AVG(LENGTH(ID))) AS "LONGUEUR ID", 
        ROUND(AVG(LENGTH(LIB))) AS "LONGUEUR LIB",  
        ROUND(COUNT(*) * (ROUND(AVG(LENGTH(ID))) + ROUND(AVG(LENGTH(LIB))))/ (1024*1024), 2) AS "TAILLE TABLE EN MO" 
FROM ZZZ_TEST_DDU;
 
COUNT(*) LONGUEUR ID LONGUEUR LIB TAILLE TABLE EN MO
---------- ----------- ------------ ------------------
    999999           8           14              20.98
1 row selected.

Etape 7 : je commit ma transaction. Les données du user X097933 ont bien disparu de la vue des transactions.
Attention, si je suis en mode Flashback query je sais que le TBS UNDO n'est pas vidé des données de mes transactions mais je n'ai pas trouvé les infos liées à un user et son espace disque occupé dans le TBS UNDO donc je me suis rapatrié sur les données de la vue des trnsactions.

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
commit;
 
SELECT         S.USERNAME AS "SESSION USERNAME", 
S.OSUSER AS "OS USERNAME", 
 
 (T.USED_UBLK * (select BLOCK_SIZE from dba_tablespaces where tablespace_name = 'UNDOTBS'))/(1024*1024) AS "Taille UNDOTBS used Mo"
FROM V$SESSION S, V$TRANSACTION T
WHERE S.SADDR = T.SES_ADDR ;
 
SESSION USERNAME               OS USERNAME                   Taille UNDOTBS used Mo
------------------------------ -----------------------------------------------------------------
SIR                            X120341                                       .0078125

[mnitu]

la taille des segments UNDO prise me semble excessif par rapport à ma table.

Ajoutez les instructions de création du jeu d'essai (table, index, et inserts).

Etape 6 : Vérifier la taille de ma table en base

Il y a une procédure de Tom Kyte show_space basée sur dbms_space que vous pouvez utiliser.

Ensuite la transaction qui plante c'est "la transaction qui pose problème".
Souvent il s'agit des requêtes faisant un (semi)produit cartésien.

[Ikebukuro]

mnitu,

J'ai rajouté la commande de création de la table récupérée sous Toad; elle n'a pas d'index ni de contrainte d'intégrité.
Pour les insert j'ai pris des données existantes d'une table en faisant un Insert ... Select from...

[fouedgr]

J'ai pas pris trop le temps de tout lire en detail, mais si ca peut aider : verifie au niveau de V$UNDOSTAT
(quote : Contains statistics for monitoring and tuning undo space. Use this view to help estimate the amount of undo space required for the current workload. The database also uses this information to help tune undo usage in the system. This view is meaningful only in automatic undo management mode.)

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
select distinct
s.sid,
s.osuser,
s.process,
s.sql_id,
round((u.undoblks*32768) / (1024*1024)) undoblks_MB,
u.tuned_undoretention
from v$undostat u, v$session s
group by
s.sid,
s.osuser,
s.process,
s.sql_id,
u.undoblks,
u.tuned_undoretention
order by s.sid;

[Ikebukuro]

Hello fouedgr,

Merci pour l'info, je vais regarder cette table :-)