java.sql.SQLException: – ORA-01000: dépassement des curseurs ouverts maximum

Je reçois une exception SQL ORA-01000. J’ai donc quelques questions à ce sujet.

  1. Les curseurs ouverts maximum sont-ils exactement liés au nombre de connexions JDBC ou sont-ils également liés aux objects de déclaration et de jeu de résultats créés pour une connexion unique? (Nous utilisons un pool de connexions)
  2. Existe-t-il un moyen de configurer le nombre d’objects instruction / resultset dans la firebase database (comme les connexions)?
  3. Est-il conseillé d’utiliser l’object instruction / resultet de la variable d’instance à la place de l’object instruction / resultset local de la méthode dans un seul environnement threadé?
  4. L’exécution d’une instruction préparée dans une boucle provoque-t-elle ce problème? (Bien sûr, j’aurais pu utiliser sqlBatch) Note: pStmt est fermé une fois la boucle terminée.

    { //method try starts Ssortingng sql = "INSERT into TblName (col1, col2) VALUES(?, ?)"; pStmt = obj.getConnection().prepareStatement(sql); pStmt.setLong(1, subscriberID); for (Ssortingng language : additionalLangs) { pStmt.setInt(2, Integer.parseInt(language)); pStmt.execute(); } } //method/try ends { //finally starts pStmt.close() } //finally ends 
  5. Que se passera-t-il si conn.createStatement () et conn.prepareStatement (sql) sont appelés plusieurs fois sur un seul object de connexion?

Edit1: 6. L’utilisation de l’object de référence Weak / Soft aidera-t-elle à prévenir les fuites?

Edit2: 1. Y a-t-il un moyen, je peux trouver tous les “statement.close ()” manquants dans mon projet? Je comprends que ce n’est pas une fuite de mémoire. Mais j’ai besoin de trouver une référence à une déclaration (où close () n’est pas effectuée) éligible pour le nettoyage de la mémoire? Tout outil disponible? Ou dois-je l’parsingr manuellement?

S’il vous plaît, aidez-moi à le comprendre.

Solution

Pour trouver le curseur ouvert dans Oracle DB pour le nom d’utilisateur -VELU

Accédez à la machine ORALCE et démarrez sqlplus en tant que sysdba.

 [oracle@db01 ~]$ sqlplus / as sysdba 

Puis courir

 SELECT A.VALUE, S.USERNAME, S.SID, S.SERIAL# FROM V$SESSTAT A, V$STATNAME B, V$SESSION S WHERE A.STATISTIC# = B.STATISTIC# AND S.SID = A.SID AND B.NAME = 'opened cursors current' AND USERNAME = 'VELU'; 

Si possible, veuillez lire ma réponse à la fin.

ORA-01000, l’erreur maximum-open-cursors, est une erreur extrêmement courante dans le développement de bases de données Oracle. Dans le contexte de Java, cela se produit lorsque l’application tente d’ouvrir plus de ResultSets que de curseurs configurés sur une instance de firebase database.

Les causes communes sont:

  1. Erreur de configuration

    • Vous avez plus de threads dans votre application interrogeant la firebase database que les curseurs de la firebase database. Un cas est celui où vous avez une connexion et un pool de threads supérieurs au nombre de curseurs de la firebase database.
    • Vous avez de nombreux développeurs ou applications connectés à la même instance de firebase database (qui inclura probablement de nombreux schémas) et vous utilisez ensemble trop de connexions.
    • Solution:

      • Augmenter le nombre de curseurs dans la firebase database (si les ressources le permettent) ou
      • Diminuer le nombre de threads dans l’application.
  2. Fuite de curseur

    • L’application ne ferme pas les ResultSets (en JDBC) ou les curseurs (dans les procédures stockées sur la firebase database)
    • Solution : Les fuites de curseur sont des bogues. L’augmentation du nombre de curseurs sur la firebase database retarde simplement la défaillance inévitable. Des fuites peuvent être trouvées en utilisant l’parsing de code statique , la journalisation JDBC ou au niveau de l’application et la surveillance de firebase database .

Contexte

Cette section décrit une partie de la théorie derrière les curseurs et comment JDBC devrait être utilisé. Si vous n’avez pas besoin de connaître le contexte, vous pouvez sauter ceci et passer directement à la rubrique “Élimination des fuites”.

Qu’est-ce qu’un curseur?

Un curseur est une ressource de la firebase database contenant l’état d’une requête, en particulier la position où se trouve un lecteur dans un ResultSet. Chaque instruction SELECT a un curseur et les procédures stockées PL / SQL peuvent s’ouvrir et utiliser autant de curseurs que nécessaire. Vous pouvez en savoir plus sur les curseurs sur Orafaq .

Une instance de firebase database sert généralement plusieurs schémas différents, de nombreux utilisateurs différents comportant chacun plusieurs sessions . Pour ce faire, il dispose d’un nombre fixe de curseurs disponibles pour tous les schémas, utilisateurs et sessions. Lorsque tous les curseurs sont ouverts (en cours d’utilisation) et que la demande nécessite un nouveau curseur, la requête échoue avec une erreur ORA-010000.

Trouver et définir le nombre de curseurs

Le numéro est normalement configuré par l’administrateur de firebase database lors de l’installation. Le nombre de curseurs actuellement utilisés, le nombre maximal et la configuration sont accessibles dans les fonctions d’administrateur d’ Oracle SQL Developer . À partir de SQL, il peut être défini avec:

 ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH; 

Relier JDBC dans la machine virtuelle Java aux curseurs de la firebase database

Les objects JDBC ci-dessous sont étroitement liés aux concepts de firebase database suivants:

  • JDBC Connection est la représentation client d’une session de firebase database et fournit des transactions de firebase database. Une connexion ne peut avoir qu’une seule transaction ouverte à la fois (mais les transactions peuvent être nestedes)
  • Un ResultSet JDBC est pris en charge par un seul curseur sur la firebase database. Lorsque close () est appelée sur le ResultSet, le curseur est relâché.
  • Un JDBC CallableStatement appelle une procédure stockée sur la firebase database, souvent écrite en PL / SQL. La procédure stockée peut créer zéro ou plusieurs curseurs et peut renvoyer un curseur en tant que JDBC ResultSet.

JDBC est thread-safe: Il est tout à fait correct de passer les différents objects JDBC entre les threads.

Par exemple, vous pouvez créer la connexion dans un thread; un autre thread peut utiliser cette connexion pour créer un PreparedStatement et un troisième thread peut traiter le jeu de résultats. La seule ressortingction majeure est que vous ne pouvez pas avoir plusieurs ResultSet ouverts sur un seul PreparedStatement à tout moment. Voir Est – ce que Oracle DB prend en charge plusieurs opérations (parallèles) par connexion?

Notez qu’une validation de firebase database se produit sur une connexion et que tous les fichiers DML (INSERT, UPDATE et DELETE) de cette connexion seront validés ensemble. Par conséquent, si vous souhaitez prendre en charge plusieurs transactions simultanément, vous devez disposer d’au moins une connexion pour chaque transaction simultanée.

Fermeture d’objects JDBC

Un exemple typique d’exécution d’un ResultSet est:

 Statement stmt = conn.createStatement(); try { ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" ); try { while ( rs.next() ) { System.out.println( "Name: " + rs.getSsortingng("FULL_NAME") ); } } finally { try { rs.close(); } catch (Exception ignore) { } } } finally { try { stmt.close(); } catch (Exception ignore) { } } 

Notez que la clause finally ignore toute exception déclenchée par close ():

  • Si vous fermez simplement ResultSet sans try {} catch {}, cela pourrait échouer et empêcher la fermeture de l’instruction
  • Nous voulons permettre à toute exception déclenchée dans le corps de l’essai de se propager à l’appelant. Si vous avez une boucle, par exemple en créant et en exécutant des instructions, n’oubliez pas de fermer chaque instruction dans la boucle.

Dans Java 7, Oracle a introduit l’ interface AutoClosable qui remplace la majeure partie de Java 6 par un bon sucre syntaxique.

Détenir des objects JDBC

Les objects JDBC peuvent être conservés en toute sécurité dans des variables locales, une instance d’object et des membres de classe. Il est généralement préférable de:

  • Utilisez l’instance d’object ou les membres de classe pour contenir des objects JDBC réutilisés plusieurs fois sur une période plus longue, tels que Connections et PreparedStatements
  • Utilisez des variables locales pour ResultSets car celles-ci sont obtenues, mises en boucle puis fermées généralement dans le cadre d’une fonction unique.

Il existe toutefois une exception: si vous utilisez des EJB ou un conteneur Servlet / JSP, vous devez suivre un modèle de thread ssortingct:

  • Seul le serveur d’applications crée des threads (avec lesquels il traite les demandes entrantes)
  • Seul le serveur d’applications crée des connexions (obtenues à partir du pool de connexions)
  • Lorsque vous enregistrez des valeurs (état) entre les appels, vous devez faire très attention. Ne stockez jamais de valeurs dans vos propres caches ou membres statiques – ceci n’est pas sûr entre les clusters et dans d’autres conditions étranges, et le serveur d’applications peut faire des choses terribles sur vos données. Utilisez plutôt des beans stateful ou une firebase database.
  • En particulier, ne détenez jamais d’ objects JDBC (Connexions, ResultSets, PreparedStatements, etc.) sur différentes invocations distantes – laissez le serveur d’applications gérer cela. Le serveur d’applications ne fournit pas seulement un pool de connexions, il met également en cache vos PreparedStatements.

Éliminer les fuites

Un certain nombre de processus et d’outils sont disponibles pour détecter et éliminer les fuites JDBC:

  1. Pendant le développement, attraper les bogues rapidement est de loin la meilleure approche:

    1. Pratiques de développement: De bonnes pratiques de développement devraient réduire le nombre de bogues dans votre logiciel avant qu’il ne quitte le bureau du développeur. Les pratiques spécifiques comprennent:

      1. Programme de jumelage , pour éduquer ceux qui n’ont pas suffisamment d’expérience
      2. Révisions du code car beaucoup d’exemples valent mieux qu’un
      3. Test unitaire, ce qui signifie que vous pouvez exercer toute votre base de code à partir d’un outil de test qui facilite la reproduction des fuites
      4. Utilisez les bibliothèques existantes pour regrouper les connexions plutôt que de créer vos propres
    2. Analyse de code statique: Utilisez un outil comme l’excellent Findbugs pour effectuer une parsing de code statique. Cela ramasse de nombreux endroits où close () n’a pas été correctement géré. Findbugs a un plugin pour Eclipse, mais il fonctionne également de manière autonome, intègre le Jenkins CI et d’autres outils de construction.

  2. Lors de l’exécution:

    1. Holdability et commettre

      1. Si la propriété ResultSet est ResultSet.CLOSE_CURSORS_OVER_COMMIT, le ResultSet est fermé lorsque la méthode Connection.commit () est appelée. Cela peut être défini en utilisant Connection.setHoldability () ou en utilisant la méthode Connection.createStatement () surchargée.
    2. Enregistrement à l’exécution.

      1. Mettez de bonnes instructions de journal dans votre code. Celles-ci doivent être claires et compréhensibles pour que le client, le personnel de soutien et les coéquipiers puissent comprendre sans formation. Ils doivent être laconiques et inclure l’impression des valeurs d’état / interne des variables et atsortingbuts clés afin de pouvoir suivre la logique de traitement. Une bonne journalisation est fondamentale pour les applications de débogage, en particulier celles qui ont été déployées.
      2. Vous pouvez append un pilote JDBC de débogage à votre projet (pour le débogage – ne le déployez pas réellement). Un exemple (je ne l’ai pas utilisé) est log4jdbc . Vous devez ensuite faire une parsing simple sur ce fichier pour voir quelles exécutions n’ont pas de clôture correspondante. Compter l’ouverture et la fermeture devrait mettre en évidence s’il y a un problème potentiel

        1. Surveillance de la firebase database Surveillez votre application en cours d’exécution à l’aide d’outils tels que la fonction SQL Monitor ‘Monitor SQL’ ou Quest TOAD . La surveillance est décrite dans cet article . Pendant la surveillance, vous interrogez les curseurs ouverts (par exemple depuis la table v $ sesstat) et examinez leur code SQL. Si le nombre de curseurs augmente et (surtout) devient dominé par une même instruction SQL, vous savez que vous avez une fuite avec ce SQL. Rechercher votre code et votre avis.

D’autres pensées

Pouvez-vous utiliser WeakReferences pour gérer les connexions de fermeture?

Les références faibles et souples permettent de référencer un object de manière à permettre à la JVM de collecter le référent à n’importe quel moment (en supposant qu’il n’y ait pas de chaînes de référence fortes pour cet object).

Si vous transmettez une ReferenceQueue dans le constructeur à la référence logicielle ou faible, l’object est placé dans la ReferenceQueue lorsque l’object est GC ‘lorsqu’il se produit (s’il se produit). Avec cette approche, vous pouvez interagir avec la finalisation de l’object et vous pouvez fermer ou finaliser l’object à ce moment-là.

Les références fantômes sont un peu plus étranges. Leur but est uniquement de contrôler la finalisation, mais vous ne pouvez jamais obtenir de référence à l’object d’origine, il sera donc difficile d’appeler la méthode close ().

Cependant, il est rarement recommandé d’essayer de contrôler le lancement du GC (Weak, Soft et PhantomReferences vous permettent de savoir après que l’object est mis en queue pour GC). En fait, si la quantité de mémoire dans la machine virtuelle Java est importante (par exemple -Xmx2000m), vous risquez de ne jamais GC l’object, et vous rencontrerez toujours le ORA-01000. Si la mémoire JVM est petite par rapport aux exigences de votre programme, vous pouvez constater que les objects ResultSet et PreparedStatement sont GCed immédiatement après la création (avant que vous puissiez les lire), ce qui va probablement échouer votre programme.

TL; DR: Le mécanisme de référence faible n’est pas un bon moyen de gérer et de fermer les objects Statement et ResultSet.

J’ajoute peu plus de compréhension.

  1. Le curseur concerne uniquement un objecct de déclaration; Ce n’est ni resultSet ni l’object de connexion.
  2. Mais nous devons encore fermer le jeu de résultats pour libérer de la mémoire oracle. Toujours si vous ne fermez pas le jeu de résultats qui ne sera pas pris en compte pour CURSORS.
  3. L’object de clôture fermera automatiquement l’object du jeu de résultats.
  4. Le curseur sera créé pour toutes les instructions SELECT / INSERT / UPDATE / DELETE.
  5. Chaque instance de firebase database ORACLE peut être identifiée à l’aide du SID oracle. de même, ORACLE DB peut identifier chaque connexion en utilisant la connexion SID. Les deux SID sont différents.
  6. Donc, la session ORACLE n’est rien d’autre qu’une connexion jdbc (tcp); qui n’est rien d’autre qu’un SID.
  7. Si nous définissons un maximum de 500 curseurs, il ne s’agit que d’une seule session / connexion / SID JDBC.
  8. Nous pouvons donc avoir plusieurs connexions JDBC avec leurs no de curseurs respectifs (déclarations).
  9. Une fois la JVM terminée, toutes les connexions / curseurs seront fermés, OU JDBCConnection est fermée. LES CURSEURS concernant cette connexion seront fermés.

Loggin comme sysdba.

Dans Putty (Oracle login):

  [oracle@db01 ~]$ sqlplus / as sysdba 

Dans SqlPlus:

UserName: sys as sysdba

Définissez la valeur session_cached_cursors sur 0 pour qu’elle ne ferme pas les curseurs.

  alter session set session_cached_cursors=0 select * from V$PARAMETER where name='session_cached_cursors' 

Sélectionnez le jeu de valeurs OPEN_CURSORS existant par connexion dans la firebase database

  SELECT max(a.value) as highest_open_cur, p.value as max_open_cur FROM v$sesstat a, v$statname b, v$parameter p WHERE a.statistic# = b.statistic# AND b.name = 'opened cursors current' AND p.name= 'open_cursors' GROUP BY p.value; 

Vous trouverez ci-dessous la requête pour trouver la liste SID / connections avec les valeurs de curseur ouvertes.

  SELECT a.value, s.username, s.sid, s.serial# FROM v$sesstat a, v$statname b, v$session s WHERE a.statistic# = b.statistic# AND s.sid=a.sid AND b.name = 'opened cursors current' AND username = 'SCHEMA_NAME_IN_CAPS' 

Utilisez la requête ci-dessous pour identifier les sql dans les curseurs ouverts

  SELECT oc.sql_text, s.sid FROM v$open_cursor oc, v$session s WHERE OC.sid = S.sid AND s.sid=1604 AND OC.USER_NAME ='SCHEMA_NAME_IN_CAPS' 

Maintenant déboguer le code et profiter !!! 🙂

Corrigez votre code comme ceci:

 try { //method try starts Ssortingng sql = "INSERT into TblName (col1, col2) VALUES(?, ?)"; pStmt = obj.getConnection().prepareStatement(sql); pStmt.setLong(1, subscriberID); for (Ssortingng language : additionalLangs) { pStmt.setInt(2, Integer.parseInt(language)); pStmt.execute(); } } //method/try ends finally { //finally starts pStmt.close() } 

Etes-vous sûr que vous fermez vraiment vos déclarations, vos connexions et vos résultats?

Pour parsingr des objects ouverts, vous pouvez implémenter un modèle de délégué, qui encapsule le code autour de vos objects statemant, connection et result. Vous verrez donc si un object sera fermé avec succès.

Un exemple pour: pStmt = obj. getConnection () .prepareStatement (sql);

  class obj{ public Connection getConnection(){ return new ConnectionDelegator(...here create your connection object and put it into ...); } } class ConnectionDelegator implements Connection{ Connection delegates; public ConnectionDelegator(Connection con){ this.delegates = con; } public Statement prepareStatement(Ssortingng sql){ return delegates.prepareStatement(sql); } public void close(){ try{ delegates.close(); }finally{ log.debug(delegates.toSsortingng() + " was closed"); } } } 

Si votre application est une application Java EE exécutée sur Oracle WebLogic en tant que serveur d’applications, le paramètre Taille du cache des instructions dans WebLogic peut être à l’origine de ce problème.

Si le paramètre Taille du cache des instructions pour une source de données particulière est à peu près égal ou supérieur au paramètre Nombre maximal de curseurs ouvert de la firebase database Oracle, tous les curseurs ouverts peuvent être utilisés par les instructions SQL mises en cache par WebLogic. dans l’erreur ORA-01000.

Pour résoudre ce problème, réduisez le paramètre Taille du cache des instructions pour chaque source de données WebLogic qui pointe vers la firebase database Oracle de manière à ce qu’il soit nettement inférieur au paramètre de nombre maximal de curseurs de la firebase database.

Dans WebLogic 10 Admin Console, le paramètre Taille du cache des instructions pour chaque source de données se trouve dans Services (navigation à gauche)> Sources de données> (source de données individuelle)> onglet Pool de connexions.

requête pour trouver SQL qui a ouvert.

 SELECT s.machine, oc.user_name, oc.sql_text, count(1) FROM v$open_cursor oc, v$session s WHERE oc.sid = s.sid and S.USERNAME='XXXX' GROUP BY user_name, sql_text, machine HAVING COUNT(1) > 2 ORDER BY count(1) DESC 

J’ai fait face au même problème (ORA-01000) aujourd’hui. J’avais une boucle for dans try {}, pour exécuter une instruction SELECT dans une firebase database Oracle plusieurs fois, (chaque fois que je changeais un paramètre), et finalement {}, mon code fermait Resultset, PreparedStatement et Connection . Mais dès que j’ai atteint une quantité spécifique de boucles (1000), j’ai eu l’erreur Oracle sur trop de curseurs ouverts.

Basé sur le post d’Andrew Alcock ci-dessus, j’ai apporté des modifications pour que dans la boucle, je ferme chaque jeu de résultats et chaque instruction après avoir récupéré les données et avant de les boucler à nouveau, ce qui résout le problème.

De plus, le même problème est survenu dans une autre boucle d’insertions dans une autre firebase database Oracle (ORA-01000), cette fois après 300 déclarations. Encore une fois, il a été résolu de la même manière, soit le PreparedStatement, soit le ResultSet ou les deux, sont considérés comme des curseurs ouverts jusqu’à ce qu’ils soient fermés.

Moi aussi, j’avais été confronté à ce problème.

 java.sql.SQLException: - ORA-01000: maximum open cursors exceeded 

J’utilisais Spring Framework avec Spring JDBC pour la couche dao.

Mon application avait l’habitude de faire fuir les curseurs et après quelques minutes environ, elle me donnait cette exception.

Après beaucoup de débogage et d’parsing approfondis, j’ai constaté qu’il y avait un problème avec l’ indexation, la clé primaire et les contraintes uniques dans l’une des tables utilisées dans la requête que j’exécutais.

Mon application essayait de mettre à jour les colonnes indexées par erreur. Ainsi, chaque fois que mon application frappait la requête de mise à jour sur les colonnes indexées, la firebase database tentait de procéder à la réindexation en fonction des valeurs mises à jour. Il y avait des fuites dans les curseurs .

J’ai été capable de résoudre le problème en effectuant une indexation correcte sur les colonnes utilisées pour rechercher dans la requête et en appliquant les contraintes appropriées chaque fois que nécessaire.

Avez-vous défini autocommit = true? Sinon, essayez ceci:

 { //method try starts Ssortingng sql = "INSERT into TblName (col1, col2) VALUES(?, ?)"; Connection conn = obj.getConnection() pStmt = conn.prepareStatement(sql); for (Ssortingng language : additionalLangs) { pStmt.setLong(1, subscriberID); pStmt.setInt(2, Integer.parseInt(language)); pStmt.execute(); conn.commit(); } } //method/try ends { //finally starts pStmt.close() } //finally ends 

L’utilisation du traitement par lots entraînera une réduction des frais généraux. Voir le lien suivant pour des exemples: http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

Dans notre cas, nous utilisions Hibernate et nous avions beaucoup de variables référençant la même entité mappée Hibernate. Nous avons créé et enregistré ces références dans une boucle. Chaque référence a ouvert un curseur et l’a maintenu ouvert.

Nous avons découvert cela en utilisant une requête pour vérifier le nombre de curseurs ouverts lors de l’exécution de notre code, en passant par un débogueur et en commentant de manière sélective les choses.

En ce qui concerne la raison pour laquelle chaque nouvelle référence ouvrait un autre curseur – l’entité en question avait des collections d’autres entités et je pense que cela avait quelque chose à voir (peut-être pas seulement avec la configuration du mode de récupération et parameters de cache). Hibernate lui-même a eu des bugs autour de l’échec de la fermeture des curseurs ouverts, bien qu’il semble que ceux-ci aient été corrigés dans les versions ultérieures.

Étant donné que nous n’avions pas vraiment besoin d’avoir autant de références en double à la même entité, la solution consistait à cesser de créer et de conserver toutes ces références redondantes. Une fois qu’on a fait ça le problème quand on est parti.

Ce problème se produit principalement lorsque vous utilisez le regroupement de connexions, car lorsque vous fermez la connexion, cette connexion revient au pool de connexions et tous les curseurs associés à cette connexion ne sont jamais fermés car la connexion à la firebase database est toujours ouverte. Une alternative consiste donc à réduire le temps de connexion inactif des connexions dans le pool. Ainsi, chaque fois que la connexion rest inactive pendant une connexion de 10 secondes, la connexion à la firebase database sera fermée et une nouvelle connexion créée dans le pool.

J’ai eu ce problème avec ma source de données dans WildFly et Tomcat, se connectant à un Oracle 10g.

J’ai constaté que, sous certaines conditions, l’instruction n’était pas fermée même lorsque le fichier statement.close () était invoqué. Le problème était avec le pilote Oracle que nous utilisions: ojdbc7.jar. Ce pilote est destiné à Oracle 12c et 11g, et il semble y avoir des problèmes avec Oracle 10g. Je suis donc passé à ojdbc5.jar et tout fonctionne correctement.

J’ai été confronté au même problème car je demandais plus de 1000 itérations à db. J’ai utilisé try et enfin dans mon code. Mais recevait toujours une erreur.

Pour résoudre ce problème, je me suis simplement connecté à oracle db et j’ai couru sous la requête suivante:

ALTER SYSTEM SET open_cursors = 8000 SCOPE = BOTH;

Et cela a résolu mon problème immédiatement.