Erreur ORA-04031 sans issu

[ilalaina]

Bonjour à tous.
J'ai un gros gros problème :
J'utilise Oracle 8i, et mes procédures PL/SQL Web me retournent cette erreur :

Code :

ORA-04031: impossible d'affecter 33512 octets de mémoire partagée ("shared pool","HTP","pl/sql source","pl/sql source")

et depuis ma base est très lente.
Je sais bien que des sujets similaires à ceci ont déjà été postés sur le forum, j'ai creusé partout, mais aucun des solutions n'a résolu mon problème.
J'ai essaié d'augmenter progressivement le shared pool en allant de shared_pool_size = 80M jusqu'à 512M, ainsi que le shared_pool_reserved_size mais j'ai toujours cette erreur. Voici quelques informations qui pourraient vous aider à diagnostiquer mon cas :

OS : Windows 2000
CPU : 2.66 Ghz
RAM : 3 Go

Environ 100 utilisateurs connectés simmultanément.

Dans gestionnaire des tâches Windows :

Mémoire Virtuelle : 1 742 428 Ko

utilisation mémoire : 1 432 944 ko

Show sga donne :

Code :

1
2
3
4
5
Zone globale systÞme (SGA) totale de                       1740535836 octets
Fixed Size                                          75804 octets
Variable Size                                   139079680 octets
DATABASE Buffers                               1433600000 octets
Redo Buffers                                    167780352 octets

Si quelqu'un pourrait m'aider alors son aide sera pour moi très précieuse.
Merci beaucoup.

[ilalaina]

J'ai passé toute la journée à chercher la solution, là je suis vraiment à court d'idées or ça me bloque totalement.
Personne n'aurait une petite idée de ce que je dois faire?
Merci d'avance.

[LeoAnderson]

Vous êtes en 8.1.7.4 ?

Quand l'erreur survient-elle ?
pouvez-vous le reproduire ?

Quel est le message complet de l'alert.log ?
Est-ce qu'une trace est générée ? pouvez-vous l'uploader ?

[ilalaina]

Je suis sur 8.1.7.0.0.
Bizarrement il n'y a aucune erreur dans alert.log.
Voici le contenu du fichier trace :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
*** 2007-11-21 17:48:30.230
*** SESSION ID:(32.13) 2007-11-21 17:48:30.230
*********START PLSQL RUNTIME DUMP************
***Got ORA-4031 while running PLSQL***
PACKAGE BODY SYS.HTP:
library unit=12e43684 line=965 opcode=17 static link=2521274 scope=1
Current frame Registers:
FP=24fa5d0 PC=13ba17ad Page=2 AP=2521274 ST=24fb6c8
DL0=13a67ef0 GF=13a67f44 DL1=13a67f14 DPF=13a67f3c DS=13b9b400
MCODE content IN current Page:
SYS.HTP: 08192: CVTNI      AP[8], FP+44
SYS.HTP: 08197: MOVI       FP+44, FP+36
SYS.HTP: 08202: CMP3I      FP+28, FP+36, PC+86 =08288:=
SYS.HTP: 08212: BRGT       PC+81 =08293:=

Quand j'ai fait purger le shared_pool avec

Code :

ALTER system FLUSH shared_pool

l'erreur n'apparaissait plus d'un coup, mais après environ 20 mn elle réapparait.
Je prie le ciel que vous pourriez m'aider, merci d'avance.

[LeoAnderson]

Quand l'erreur survient-elle ?
pouvez-vous le reproduire ?

Avez-vous bien augmenté le paramètre shared_pool puis arrêter/relancer la base ?
jusqu'à quelle valeur êtes-vous monté ?
quels changement y-a-t-il eu entre le moment où ça marchait et maintenant ?

quel type d'activité a la base ?
y-a-t-il beaucoup d'utilisateurs ?

[ilalaina]

Ma base utilise des procédures PL/SQL Web (GPAO) qui font des select et updates sur des tables de 6 000 000 d'enregistrements.

quel type d'activité a la base ?
y-a-t-il beaucoup d'utilisateurs ?

J'ai environ 400 utilisateurs mais seulement 100 connectés simmultanément.

Avez-vous bien augmenté le paramètre shared_pool puis arrêter/relancer la base ?
jusqu'à quelle valeur êtes-vous monté ?

J'ai monté la valeur de shared_pool_size de 80M à 512M et redemarré la base.

quels changement y-a-t-il eu entre le moment où ça marchait et maintenant ?

J'ai modifié les db_block buffers et la shared_pool_size à des valeurs trop elevées et j'ai eu une erreur ORA-27102 : out of memory en démarrant la base, alors je suis revenu aux valeurs précédents de shared_pool_size et db_block buffers.
Le comble c'est que je ne peux pas redémarrer la machine serveur.
Pensez vous que cela pourrait être bénéfique de redémarrer la machine?

[LeoAnderson]

shared pool à 80 M, ça me semble effectivement faiblard...

mais ça a déjà marché ou pas ?

[ilalaina]

Ca a toujours marché, c'est ce qui m'intrigue.

[LeoAnderson]

et depuis que ça ne marche plus, aucun autre paramètre n'a été modifié ? aucune modification du code n'a été livrée ?

[ilalaina]

En fait seulement une procédure me retournait cette erreur auparavant, c'est pourquoi j'ai voulu augmenter la SGA. Maintenant plusieurs procédures sont KO.
Quelle solution me proposez-vous?

[ilalaina]

Je n'ai plus rien modifié depuis que cela ne marche plus.

[ilalaina]

J'ai essayé ce script que j'ai trouvé sur developpez.com (http://www.developpez.net/forums/sho...ight=ora-04031

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
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
SET serveroutput ON size 10000
SET verify off
ACCEPT MTS_mode PROMPT 'MTS ou non ? (Y/N) ' DEFAULT N
declare 
     object_mem     number; 
      shared_sql     number; 
      cursor_mem     number; 
      mts_mem        number; 
      used_pool_size number; 
      free_mem       number; 
      pool_size      varchar2(512); 
   begin 
      -- Stored objects (packages, views) 
      SELECT   sum(sharable_mem) 
      INTO     object_mem 
      FROM     V$DB_OBJECT_CACHE; 
 
      -- Shared SQL 
      -- need to have additional memory if dynamic SQL used 
      SELECT   sum(sharable_mem) 
      INTO     shared_sql 
      FROM     V$SQLAREA; 
 
      -- User Cursor Usage 
      -- run this during peak usage. 
      --  assumes 250 bytes per open cursor, for each concurrent user. 
      SELECT   sum(250*users_opening) 
      INTO     cursor_mem 
      FROM     V$SQLAREA; 
 
      -- For a test system 
      -- get usage for one user, multiply by # users 
      -- select (250 * value) bytes_per_user 
      -- from v$sesstat s, v$statname n 
      -- where s.statistic# = n.statistic# 
      -- and n.name = 'opened cursors current' 
      -- and s.sid = 25; 
      -- where 25 is the sid of the process 
      -- MTS memory needed to hold session information for shared server users 
      -- This query computes a total for all currently logged on users (run 
      --  during peak period). Alternatively calculate for a single user and 
      --  multiply by # users. 
      SELECT   sum(value) INTO mts_mem 
      FROM     V$SESSTAT s, V$STATNAME n 
      WHERE    s.statistic# = n.statistic# and 
               n.name = 'session uga memory max'; 
 
      -- Free (unused) memory in the SGA: gives an indication of how much memory 
      -- is being wasted out of the total allocated. 
      SELECT   sum(bytes) 
      INTO     free_mem 
      FROM     V$SGASTAT 
      WHERE    name = 'free memory'; 
 
      SELECT used_pool_size
      INTO   used_pool_size
      FROM   (
	      -- For non-MTS add up object, shared sql, cursors and 20% overhead. 
	      SELECT round(1.2*(object_mem+shared_sql+cursor_mem)) used_pool_size
	      FROM   dual
	      WHERE  '&MTS_mode' = 'N'
	      UNION
	      -- For MTS mts contribution needs to be included (comment out previous line) 
	      -- used_pool_size := round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem)); 
	      SELECT round(1.2*(object_mem+shared_sql+cursor_mem+mts_mem)) used_pool_size
	      FROM   dual
	      WHERE  '&MTS_mode' = 'Y'
	     );
 
      SELECT   value 
      INTO     pool_size 
      FROM     V$PARAMETER 
      WHERE    name = 'shared_pool_size'; 
 
      -- Display results 
      dbms_output.put_line ('Obj mem                         :  '|| to_char(object_mem, '999G999G999G999') || ' bytes'); 
      dbms_output.put_line ('Shared sql                      :  '|| to_char(shared_sql, '999G999G999G999') || ' bytes'); 
      dbms_output.put_line ('Cursors                         :  '|| to_char(cursor_mem, '999G999G999G999') || ' bytes'); 
      IF '&MTS_MODE' = 'Y' THEN
      	dbms_output.put_line ('MTS session                     : '||to_char(mts_mem, '999G999G999G999') || ' bytes'); 
      END IF;
      dbms_output.put_line ('Free memory                     :  '|| to_char(free_mem, '999G999G999G999') || ' bytes ' || '('  || to_char(round(free_mem/1024/1024,2)) || 'MB)'); 
      dbms_output.put_line ('Shared pool utilization (total) :  '|| to_char(used_pool_size, '999G999G999G999') || ' bytes ' || '(' ||  to_char(round(used_pool_size/1024/1024,2)) || 'MB)'); 
      dbms_output.put_line ('Shared pool allocation (actual) :  '|| to_char(to_number(pool_size), '999G999G999G999') ||' bytes ' || '(' || to_char(round(pool_size/1024/1024,2)) || 'MB)'); 
      dbms_output.put_line ('Percentage Utilized             :  '|| to_char(round(used_pool_size/pool_size*100)) || '%'); 
   end; 
/

et voici le résultat :

Code :

1
2
3
4
5
6
7
Obj mem                         :         8.419.646 bytes
Shared sql                      :         5.343.142 bytes
Cursors                         :             4.000 bytes
Free memory                     :        85.699.076 bytes (81,73MB)
Shared pool utilization (total) :        16.520.146 bytes (15,75MB)
Shared pool allocation (actual) :        80.000.000 bytes (76,29MB)
Percentage Utilized             :  21%

Je me demande bien pourquoi l'utilisation de la shared pool est à seulement 15,75MB, alors que des erreurs mémoire surviennent.

[Lebubar]

voila ce que ja'i trouvé :
04031, 00000, "unable to allocate %s bytes of shared memory (\"%s\",\"%s\",\"%s\",\"%s\")"
// *Cause: More shared memory is needed than was allocated in the shared
// pool.
// *Action: If the shared pool is out of memory, either use the
// dbms_shared_pool package to pin large packages,
// reduce your use of shared memory, or increase the amount of
// available shared memory by increasing the value of the
// INIT.ORA parameters "shared_pool_reserved_size" and
// "shared_pool_size".
// If the large pool is out of memory, increase the INIT.ORA
// parameter "large_pool_size".

J'ai des commandes mes je ne suis aps sur qu'elles passent en Oracle 8 :

SQL>SELECT * FROM V$SGASTAT WHERE NAME = 'free memory' AND POOL = 'shared pool';

On peut ainsi calculer le Hit Ration du Library Cache (nombre de requêtes qui n'ont pas besoin d'être reparssées, car déjà présente en Library Cache) :

SQL>select sum(pinhits) / sum(pins) from v$librarycache ;

[ilalaina]

J'ai déjà essaié tout ça mais çà n'a pas marché.

[Pomalaix]

Envoyé par ilalaina

J'ai déjà essaié tout ça mais çà n'a pas marché.

L'erreur ORA-04031 peut avoir diverses causes, y compris des bogues.
Le plus efficace pour vous sera de vous référer à Metalink.
Vous y trouverez différentes notes dédiées à cette erreur, par exemple 396940.1.

[bouyao]

Envoyé par ilalaina

Code :

Si quelqu'un pourrait m'aider alors son aide sera pour moi très précieuse.
Merci beaucoup.

Diminue le database buffer , il est enorme au lieu de 1,4 Go essaye 1 go ou 800 Mo.
c 'est quoi la valeur de : DB_BLOCK_BUFFERS et DB_BLOCK_SIZE ?

[Pomalaix]

Envoyé par bouyao

Diminue le database buffer , il est enorme au lieu de 1,4 Go essaye 1 go ou 800 Mo.
c 'est quoi la valeur de : DB_BLOCK_BUFFERS et DB_BLOCK_SIZE ?

Dans le même genre, un LOG_BUFFER de plus de 160 Mo, c'est aberrant...
Mais on ne peut pas en faire la cause directe de l'erreur ORA-04031. Tout au plus, c'est de l'espace perdu qu'il serait peut-être utile d'ajouter à SHARED_POOL_SIZE.
Néanmoins, pour ne pas tâtonner de manière désordonnée, je réitère mon conseil : metalink !

[ilalaina]

Bonjour.
Tout d'abord j'aimerais vous remercier pour toutes vos réponses.

c 'est quoi la valeur de : DB_BLOCK_BUFFERS et DB_BLOCK_SIZE ?

DB_BLOCK_BUFFERS est de 175000
et DB_BLOCK_SIZE est de 8192

Dans le même genre, un LOG_BUFFER de plus de 160 Mo, c'est aberrant...

Mon LOG_BUFFER est de 167772160 o.
Est-ce que vous pourriez m'expliquer pourquoi un LOG_BUFFER de plus de 160 Mo est-il abérrant? Je devrais donc diminuer sa valeur à combien?
Je vais m'inscrire à Metalink mais en attendant je dois résoudre ce problème c'est urgent, je dois trouver une solution provisoire.

Je me souviens aussi avoir déjà lu sur un site que pousser le shared pool à une valeur trop grande pourrait engendrer sa fragmentation. Pourriez-vous confirmer ceci? Et si oui existe-t-il un moyen de défragmenter le shared pool?

Merci d'avance.

[ilalaina]

Excusez moi de vouloir insister mais il y a vraiment quelque chose qui m'échappe :
Voilà j'ai dupliqué la base toute entière sur un base de test qui a exactement les mêmes paramétrages et caractéristiques que le serveur de prod.
J'ai donc fait en sorte de diminuer le Database Buffer Cache à 819,2 Mo, le Log Buffer à 128 Mo, et j'ai augmenté le Shared Pool Size à 768 Mo.
Ensuite je redémarre la base, et après je fais un Flush Shared Pool.
Logiquement il devrait y avoir beaucoup de mémoires libres dans la Shared Pool à cet instant.
Je lance alors la procédure stockée et là on me renvoie encore l'erreur :

Code :

impossible d'affecter 33512 octets de mémoire partagée ("shared pool","HTP","pl/sql source","pl/sql source")

Ma question est donc pourquoi Oracle se plaint de 34 Ko de mémoires alors que le Shared Pool est à 768 Mo ???

[LeoAnderson]

Comportement qui semble incompréhensible => bug ? => METALINK comme vous le signalais Pomalaix !

Essayez d'upgrader la base de test en 8.1.7.4 pour voir (toujours intérêt à être en terminal release)