Discussion :

[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 :

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

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 :

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

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 :

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

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 :

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

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?

[bouyao]

Code :

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

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.

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]

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 :

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

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)

[Pomalaix]

Code :

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

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 ???

Peut-être votre SHARED_POOL est-elle tellement fragmentée qu'il n'existe pas de fragment libre faisant au moins 34 Ko (mais une multitude de fragments plus petits).

La requête suivante (à lancer en SYSDBA, et juste après l'erreur) vous permettra de connaître la taille du plus gros fragment libre.

Code :

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

select max(ksmchsiz)  plus_gros_fragment_libre from x$ksmsp where ksmchcls = 'free';

[Pomalaix]

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?

Ce sujet est secondaire vis à vis de votre problème, mais en bref :
le LOG_BUFFER est un composant qui nécessite très peu de mémoire. La plupart des bases fonctionnent avec un LOG_BUFFER de moins de 512 Ko (je dis bien Ko).
La doc Oracle indique en outre qu'une valeur supérieure à 1M est de toute façon inutile.
Ce plafond absolu de 1 Mo est parfois remis en cause par certains experts, et en 10g on voit fréquemment un LOB_BUFFER entre 12 à 15 Mo.

Pour votre 9i, si vous mettez 1Mo, c'est déjà énorme.