Discussion :

[Apsolu]

Bonjour

Je suis développeur d'une application qui permet de consulter une table contenant beaucoup de données via une interface qui ressemble à un tableau Excel.
Sur chaque colonne du tableau (c'est à dire de la table) l'utilisateur peut appliquer un tri et/ou un filtre sur une valeur donnée (ex. : n'afficher que les lignes pour lesquelles le montant vaut 100).
L'utilisateur peut créer des tris et des filtres sur autant de colonnes qu'il le désire (au plus 1 critère de filtre et un critère de tri sur une colonne donnée).
De plus, les colonnes affichées ou non sont également au bon vouloir de l'utilisateur.

La requête sous-jacente est donc complètement dynamique et n'a rien de fixé : ni la liste des colonnes du SELECT, ni la clause WHERE ni la clause ORDER BY !

Du coup, je ne sais pas comment je pourrais optimiser quoi que ce soit et surtout éviter que ces requêtes (toujours différentes de l'une à l'autre) s'accumulent dans la SHARED_POOL, ce qui produit très rapidement un message du style

ORA-04031 : unable to allocate 8716 bytes of shared memory ("large pool", "unknown object","cursor work he","kllqas:kllsltba")

N'étant pas un spécialiste Oracle, pardon par avance si mon exposé n'est pas très clair ou qu'il y a confusion de plusieurs notions.

Votre aide me serait infiniment précieuse !
Merci d'avance !

[LeoAnderson]

Il est techniquement possible de vider la shared pool :

Code :

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

ALTER SYSTEM FLUSH SHARED POOL;

mais je vous déconseille vivement de l'utiliser !!!

Par ailleurs, il est indispensable que vous précisiez la version d'Oracle avec laquelle vous travaillez, les réponses fournies peuvent changer selon les moteurs !

Cependant, voici un début d'explication sur votre erreur :

ORA-04031: unable to allocate string bytes of shared memory ("string","string","string","string")
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 initialization parameters SHARED_POOL_RESERVED_SIZE and SHARED_ POOL_SIZE. If the large pool is out of memory, increase the initialization parameter LARGE_POOL_SIZE.

[Pomalaix]

Bonjour !

Votre description est fort pertinente, puisque vous présentez les éléments essentiels du problème.

La fragmentation mémoire, dont l'erreur ORA-04031 est un symptome, peut effectivement se produire lorsque de très nombreuses requêtes différentes sont exécutées.
Si ces requêtes ne différaient que par les paramètres de leur clause WHERE, on pourrait envisager de "factoriser" ces requêtes en utilisant des variables de liaison.
Mais comme vous précisez d'emblée que ces requêtes divergent même quant aux colonnes utilisées, cette solution ne sera d'aucun secours.

Dans votre cas, la seule issue semble être l'augmentation de LARGE_POOL_SIZE.
Pouvez-vous cependant nous donner sa taille actuelle ?

L'ORA-04031 peut aussi provenir d'une bogue, donc il serait utile que vous nous indiquiez votre version exacte.

[Apsolu]

Bonjour,

Merci beaucoup pour vos messages, je ne savais plus à quel saint me vouer !

Faute de bien comprendre les mécanismes sous-jacents, j'envisageais justement sans conviction un appel systématique à la directive ALTER SYSTEM FLUSH SHARED_POOL avant de lancer chaque nouvelle requête (déjà tenté APRÈS apparition du message d'erreur, mais ça semblait être "trop tard" et ne changeait rien, on restait planté). Quel est exactement le problème avec cette directive ? (Pour ma culture générale, et si cela ne nous entraîne pas trop loin du sujet ?)

Voici les tailles récupérées au niveau de V$PARAMETER :
shared_pool_size : 31457280
shared_pool_reserved_size : 1572864
large_pool_size : 614400

Bien que ces tailles ne m'évoquent rien, est-ce qu'en les augmentant (chose qu'il faudrait aussi imposer aux clients de notre logiciel) on ne risquerait pas de ne gagner qu'un sursis ?

Je redoute en effet que le problème vienne de notre façon "naïve" d'envoyer des textes de requêtes les uns après les autres et de fragmenter ainsi la mémoire de façon dramatique.

J'aimerais maîtriser cette notion de "factorisation" que vous évoquez, mais ça paraît inadapté à notre problématique. Quoique… ? Vous semblez dire que si les colonnes sélectées restaient toujours les mêmes ce serait peut-être jouable (?). Or elles constituent en effet le paramètre le moins variable : on peut quasiment les considérer comme "fixes" (quitte à rapatrier des colonnes non demandées et ne pas les afficher).

Encore merci pour votre aide !

[Pomalaix]

Bonjour

Très brièvement (guère le temps de détailler pour l'heure) :
Votre LARGE_POOL_SIZE est bien petite : 600 Ko.
Passez-la par exemple à 8 Mo, ça devrait réduire nettement la fréquence de vos problèmes.
Car même si vos requêtes sont dynamiques et toutes différentes, il serait étonnant que vous en ayez des dizaines de milliers...

Et ne faites pas un FLUSH SHARED_POOL avant chaque requête, ce n'est vraiment pas une bonne idée.
D'ailleurs, je ne suis même pas sûr que cette commande agisse sur le LARGE POOL, qui semble ici en cause si l'on en croit votre message d'erreur initial.

[Apsolu]

Bonjour !

Merci beaucoup pour votre aide. Le problème de taille vous a sauté aux yeux alors que ça ne m'aurait rien évoqué. En passant la LARGE_POOL_SIZE à 8Mo comme vous le disiez, il semble que le problème ait disparu !

En espérant que ce n'est pas reculer pour mieux sauter et que je ne reviendrai pas vous embêter dans quelques jours !

Merci encore à tous deux !

[facosh]

Est - ce que ces problèmes peuvent etre dues à des connections a la base qui ne sont pas fernées ??

Cordialement