Java.sql.SQLException: - ORA-01000: nombre maximum de curseurs ouverts dépassé

Question

Je reçois une exception SQL ORA-01000. J'ai donc quelques requêtes en rapport avec cela.

1. Les curseurs ouverts maximum sont-ils directement liés au nombre de connexions JDBC, ou sont-ils aussi liés aux objets de déclaration et de résultat que nous avons créés pour une seule connexion ? (Nous utilisons un pool de connexions)

2. Existe-t-il un moyen de configurer le nombre d'objets de déclaration/résultat dans la base de données (comme les connexions) ?

3. Est-il conseillé d'utiliser des objets de déclaration/résultat variables d'instance au lieu d'objets de déclaration/résultat locaux à la méthode dans un environnement à un seul thread ?

4. Est-ce que l'exécution d'une instruction préparée dans une boucle cause ce problème ? (Bien sûr, j'aurais pu utiliser sqlBatch) Note : pStmt est fermé une fois la boucle terminée.

   { //début du try de la méthode  
     String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
     pStmt = obj.getConnection().prepareStatement(sql);
     pStmt.setLong(1, subscriberID);
     for (String language : additionalLangs) {
       pStmt.setInt(2, Integer.parseInt(language));
       pStmt.execute();
     }
   } //fin de la méthode/du try
   
   { //début du finally
      pStmt.close()
   } //fin du finally 

5. Que se passera-t-il si conn.createStatement() et conn.prepareStatement(sql) sont appelés plusieurs fois sur un seul objet connection ?

Édition 1: 6. Est-ce que l'utilisation d'un objet de déclaration à référence faible/forte aidera à prévenir les fuites de mémoire ?

Édition 2: 1. Y a-t-il un moyen de 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'a pas été effectué) éligible à la collecte d'ordures ? Un outil disponible ? Ou dois-je l'analyser manuellement ?

Veuillez m'aider à comprendre cela.

Solution

Pour trouver le curseur ouvert dans la base de données Oracle pour le nom d'utilisateur - VELU

Allez sur la machine ORACLE et lancez sqlplus en tant que sysdba.

[oracle@db01 ~]$ sqlplus / as sysdba 

Ensuite, exécutez

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 pour mieux comprendre ma solution

Answer 1

Requête pour trouver le SQL qui a été 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

Answer 2

Ce problème se produit principalement lorsque vous utilisez le pool de connexions car lorsque vous fermez la connexion, cette connexion retourne au pool de connexions et tous les curseurs associés à cette connexion ne sont jamais fermés car la connexion à la base de données est toujours ouverte. Une alternative consiste à diminuer le temps de connexion inactif des connexions dans le pool, de sorte que chaque fois qu'une connexion reste inactive dans le pool de connexions pendant disons 10 secondes, la connexion à la base de données sera fermée et une nouvelle connexion sera créée pour être ajoutée au pool.

Answer 3

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

{ //début de la méthode try  
    String sql = "INSERT into TblName (col1, col2) VALUES(?, ?)";
    Connection conn = obj.getConnection()
    pStmt = conn.prepareStatement(sql);

    for (String language : additionalLangs) {
        pStmt.setLong(1, subscriberID);
        pStmt.setInt(2, Integer.parseInt(language));
        pStmt.execute();
        conn.commit();
    }
} //fin de la méthode try { 
    //début de la section finally
    pStmt.close()
} //fin de la section finally

Answer 4

L'utilisation du traitement par lots entraînera moins de frais généraux. Consultez le lien suivant pour des exemples: http://www.tutorialspoint.com/jdbc/jdbc-batch-processing.htm

Answer 5

Dans notre cas, nous utilisions Hibernate et nous avions de nombreuses variables faisant référence à la même entité mappée par Hibernate. Nous créions et sauvegardions ces références dans une boucle. Chaque référence ouvrait un curseur et le maintenait ouvert.

Nous avons découvert cela en utilisant une requête pour vérifier le nombre de curseurs ouverts en exécutant notre code, en pas à pas avec un débogueur et en commentant sélectivement certaines parties du code.

Quant à pourquoi chaque nouvelle référence ouvrait un autre curseur - l'entité en question avait des collections d'autres entités mappées vers elle et je pense que cela y avait quelque chose à voir (peut-être pas uniquement cela mais en combinaison avec la façon dont nous avions configuré le mode de récupération et les paramètres du cache). Hibernate lui-même a eu des problèmes de fermeture de curseurs ouverts, bien que ces problèmes semblent avoir été corrigés dans des versions ultérieures.

Puisque nous n'avions pas vraiment besoin d'avoir autant de références dupliquées vers la même entité de toute façon, la solution a été d'arrêter de créer et de conserver toutes ces références redondantes. Une fois que nous avons fait cela, le problème a disparu.