Java.SQL.SQLException: - ORA-01000: dépassement maximal des curseurs ouverts
j'obtiens une exception ORA-01000 SQL. J'ai donc quelques questions concernant.
- les curseurs ouverts maximums sont-ils exactement liés au nombre de connexions JDBC, ou sont-ils également liés aux objets statement et resultset que nous avons créés pour une seule connexion ? (Nous sommes à l'aide de pool de connexions)
- y a-t-il un moyen de configurer le nombre d'objets statement/resultset dans la base de données (comme les connexions) ?
- est-il conseillé d'utiliser l'instance variable statement/resultset object au lieu de method local statement/resultset object dans un seul environnement fileté ?
-
l'exécution d'une déclaration préparée dans une boucle cause-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 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(); } } //method/try ends { //finally starts pStmt.close() } //finally ends -
Ce qui va se passer si conn.createStatement() et conn.préparestatement (sql) sont appelé plusieurs fois sur l'objet de connexion ?
Edit 1: 6. Est-ce que l'utilisation de L'objet de l'énoncé de référence faible/doux aidera à prévenir la fuite ?
Edit2: 1. Est-ce qu'il y a un moyen, je peux trouver toutes les déclarations manquantes".close()"dans mon projet ? Je comprends ce n'est pas une fuite de mémoire. Mais je dois trouver une référence d'énoncé (où close () n'est pas effectuée) admissibles pour la collecte des ordures ? Un outil disponible ? Ou dois-je analyser manuellement ?
Aidez-moi à le comprendre.
Solution
pour trouver le curseur ouvert dans Oracle DB pour nom d'utilisateur-VELU
allez à la machine ORALCE et démarrez sqlplus comme 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 à fin.
13 réponses
ORA-01000, l'erreur maximum-open-cursors, est une erreur extrêmement courante dans le développement de base de données Oracle. Dans le contexte de Java, cela se produit lorsque l'application tente d'ouvrir plus de jeux de résultats qu'il n'y a de curseurs configurés sur une instance de base de données.
les causes communes sont:
-
erreur de Configuration
- vous avez plus de threads dans votre application en questionnant la base de données que curseurs sur le DB. Un cas est où vous avez une connexion et un pool de thread plus grand que le nombre de curseurs sur la base de données.
- vous avez beaucoup de développeurs ou d'applications connectés à la même instance DB (qui inclura probablement beaucoup de schémas) et ensemble vous utilisez trop de connexions.
-
Solution:
- augmenter le nombre de curseurs dans la base de données (si les ressources le permettent) ou
- diminuant le nombre de fils dans la demande.
-
curseur leak
- Les applications n'est pas de clôture des jeux de résultats (JDBC) ou des curseurs (dans des procédures stockées dans la base de données)
- Solution : les fuites du curseur sont des bogues; augmenter le nombre des curseurs sur le DB retarde simplement l'échec inévitable. Les fuites peuvent être trouvées en utilisant analyse de code statique , JDBC ou l'enregistrement de niveau d'application, et surveillance de base de données .
arrière-plan
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 passez directement à la section "élimination des fuites".
qu'est Ce qu'un curseur?
un curseur est une ressource sur la base de données qui détient l'état d'une requête, en particulier la position où un lecteur est dans un ensemble de résultats. Chaque instruction SELECT a un curseur, et les procédures stockées en PL/SQL peuvent s'ouvrir et utiliser autant de curseurs qu'ils nécessitent. Vous pouvez en savoir plus sur les curseurs sur Orafaq .
une instance de base de données sert généralement plusieurs différents schémas , beaucoup de différents utilisateurs chacun avec sessions multiples . 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 requête nécessite un nouveau curseur, la requête échoue avec une erreur ORA-010000.
trouver et régler le nombre de curseurs
Le numéro est normalement configuré par le DBA lors de l'installation. Le nombre de curseurs actuellement utilisés, le nombre maximum et la configuration peuvent être consultés dans les fonctions D'administrateur dans Oracle SQL Developer . De SQL il peut être réglé avec:
ALTER SYSTEM SET OPEN_CURSORS=1337 SID='*' SCOPE=BOTH;
Concernant JDBC dans la JVM pour les curseurs sur la DB
les objets JDBC ci-dessous sont étroitement liés aux concepts de base de données suivants:
- JDBC Connexion est le client de la représentation d'une base de données session et fournit la base de données transactions . Une connexion ne peut avoir qu'une seule opération ouverte à la fois (mais les transactions peuvent être imbriquées)
- JDBC ResultSet est pris en charge par un seul curseur sur la base de données. Lorsque close () est appelé sur le le curseur est libéré.
- JDBC CallableStatement appelle une "1519610920 des" procédures stockées sur la base de données, souvent écrites en PL/SQL. La procédure stockée peut créer zéro ou plus de curseurs, et peut retourner un curseur comme un jeu de résultats JDBC.
JDBC est thread-safe: Il est tout à fait OK pour passer les différents JDBC objets entre les threads.
Par exemple, vous pouvez créer la connexion en un thread; un autre thread peut utiliser cette connexion pour créer une déclaration préparée et un troisième thread peut traiter le jeu de résultats. La seule restriction majeure est que vous ne pouvez pas avoir plus d'un jeu de résultats ouvert sur une seule déclaration préparée à tout moment. Voir est-ce que Oracle DB supporte plusieurs opérations (parallèles) par connexion?
notez qu'une commit de base de données se produit sur une connexion, et donc tous les DML (INSERT, UPDATE et DELETE) sur cette connexion s'engagera ensemble. Par conséquent, si vous voulez effectuer plusieurs transactions en même temps, vous devez avoir au moins une connexion pour chaque Transaction concurrente.
fermeture d'objets JDBC
un exemple typique d'exécution D'un jeu de résultats est:
Statement stmt = conn.createStatement();
try {
ResultSet rs = stmt.executeQuery( "SELECT FULL_NAME FROM EMP" );
try {
while ( rs.next() ) {
System.out.println( "Name: " + rs.getString("FULL_NAME") );
}
} finally {
try { rs.close(); } catch (Exception ignore) { }
}
} finally {
try { stmt.close(); } catch (Exception ignore) { }
}
Notez comment la clause finally ignore tout de l'exception soulevée par le close():
- si vous fermez simplement le jeu de résultats sans essayer {} catch {}, il peut échouer et de prévenir l'Instruction en cours d'fermé
- Nous voulons permettre à toute exception soulevée dans le corps de l'essayer pour se propager à l'appelant. Si vous avez une boucle, par exemple, la création et l'exécution des Instructions, n'oubliez pas de fermer chaque Instruction dans la boucle.
en Java 7, Oracle a introduit le interface Autoclosable qui remplace la plupart du boilerplate Java 6 avec quelques nice sucre syntaxique.
Holding JDBC objects
les objets JDBC peuvent être conservés en toute sécurité dans des variables locales, des instances d'objet et des membres de classe. Il est généralement préférable de:
- utiliser une instance d'objet ou des membres de classe pour détenir des objets JDBC qui sont réutilisés plusieurs fois sur une plus longue période, tels que des connexions et des États préparés
- utiliser des variables locales pour les résultats étant donné qu'ils sont obtenu, Bouclé puis fermé typiquement dans le cadre d'une seule fonction.
il y a cependant une exception: si vous utilisez EJBs, ou un conteneur Servlet/JSP, vous devez suivre un modèle de filetage strict:
- seul le serveur D'Application crée des threads (avec lesquels il traite les requêtes entrantes)
- seul le serveur D'Application crée des connexions (que vous obtenez à partir de la connexion la piscine)
- lorsque vous sauvegardez des valeurs (état) entre les appels, vous devez être très prudent. Ne stockez jamais de valeurs dans vos propres caches ou membres statiques - ce n'est pas sûr à travers les grappes et d'autres conditions bizarres, et le serveur D'Application peut faire des choses terribles à vos données. Utilisez plutôt des haricots ou une base de données.
- En particulier, jamais hold JDBC objets (Connexions, des jeux de résultats, PreparedStatements, etc) sur différentes à distance invocations-laissez le serveur D'Application gérer cela. Le serveur D'Application ne fournit pas seulement un pool de connexion, il cache également vos données pré-compilées.
élimination des fuites
il existe un certain nombre de processus et d'outils pour aider à détecter et à éliminer les fuites JDBC:
-
pendant le développement - la détection précoce des bogues est de loin la meilleure approche:
-
pratiques de développement: les 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 sont les suivantes:
- Pair programming , pour éduquer ceux qui n'ont pas une expérience suffisante
- des revues de Code parce que beaucoup d'yeux valent mieux qu'une
- "15191700920 de tests Unitaires ce qui signifie que vous pouvez exercer tout et partie de votre base de code à partir d'un outil de test qui rend la reproduction de fuites triviale
- Utiliser bibliothèques existantes pour le raccordement de mise en commun plutôt que la construction de votre propre
-
analyse de Code statique: utilisez un outil comme l'excellent Findbugs pour effectuer une analyse de code statique. Ce ramasse beaucoup d'endroits où la fermer() n'a pas été correctement traitées. Findbugs a un plugin pour Eclipse, mais il exécute aussi standalone pour one-off, a des intégrations dans Jenkins CI et d'autres outils de construction
-
-
à l'exécution:
-
Holdability et de s'engager
- si la retenue de la série de résultats est une série de résultats.CLOSE_CURSORS_OVER_COMMIT, puis le jeu de résultats est fermé lorsque le Connexion.méthode commit() est appelée. Cela peut être réglé en utilisant la connexion.setHoldability () ou en utilisant la connexion surchargée.createStatement() la méthode.
-
Connexion au moment de l'exécution.
- mettez les bons logs dans votre code. Ceux-ci doivent être clairs et compréhensibles afin que le client, le personnel de soutien et les coéquipiers puissent comprendre sans formation. Ils doivent être concis et comprendre l'impression état / valeurs internes des variables et attributs clés afin que vous puissiez tracer la logique de traitement. Une bonne journalisation est fondamentale pour le débogage des applications, en particulier celles qui ont été déployées.
-
vous pouvez ajouter un pilote de débogage JDBC à votre projet (pour le débogage - ne pas le déployer réellement). Un exemple (Je ne l'ai pas utilisé) est log4jdbc . Vous devez ensuite faire une analyse simple de ce fichier pour voir quels exécutes n'ont pas de fermeture correspondante. Le comptage de l'ouverture et de la fermeture devrait mettre en évidence s'il y a un problème potentiel
- Suivi de la base de données. Surveillez votre application en cours d'exécution en utilisant les outils tels que la fonction SQL Developer' Monitor SQL ' ou Quest's TOAD . La surveillance est décrite dans cet article . Pendant le suivi, vous interrogez les curseurs ouverts (par exemple à partir de la table v$sesstat) et passez en revue leur SQL. Si le nombre de curseurs est en augmentation, et (le plus important) en devenant dominé par une déclaration SQL identique, vous savez que vous avez une fuite avec CE SQL. Cherchez votre code et passez en revue.
-
d'Autres pensées
pouvez-vous utiliser des références faibles pour gérer les connexions de fermeture?
les références faibles et douces sont des façons de vous permettre de faire référence à un objet d'une manière cela permet à la JVM de ramasser les ordures du référent à tout moment qu'elle juge approprié (en supposant qu'il n'y ait pas de chaîne de référence solide à cet objet).
si vous passez une ReferenceQueue dans le constructeur à la référence molle ou faible, l'objet est placé dans la ReferenceQueue quand L'objet est GC'ed quand il se produit (s'il se produit du tout). Avec cette approche, vous pouvez interagir avec la finalisation de l'objet et vous pouvez fermer ou finaliser l'objet à ce moment.
les références fantômes sont un peu plus étranges; leur but est seulement de contrôler la finalisation, mais vous ne pouvez jamais obtenir une référence à l'objet original, il va donc être difficile d'appeler la méthode close() dessus.
cependant, c'est rarement une bonne idée de tenter de contrôler quand le GC est exécuté (les références faibles, douces et fantômes vous font savoir après le fait que l'objet est recherché pour GC). En fait, si la quantité de mémoire dans la JVM est grande (eg-Xmx2000m) vous pourriez jamais GC l'objet, et vous vivrez toujours L'ORA-01000. Si la mémoire JVM est petite par rapport aux exigences de votre programme, vous pouvez trouver que les objets ResultSet et PreparedStatement sont GCE immédiatement après la création (avant que vous puissiez lire à partir d'eux), ce qui va probablement échouer votre programme.
TL; DR: le mécanisme de référence faible n'est pas une bonne façon de gérer et de fermer la Déclaration et Objects ResultSet.
j'ajoute peu de compréhension.
- curseur est seulement au sujet d'un objectif de déclaration; il n'est ni resultSet ni l'objet de connexion.
- mais nous devons tout de même fermer le jeu de résultats pour libérer un peu de mémoire oracle. Toujours si vous ne fermez pas le jeu de résultats qui ne sera pas compté pour les curseurs.
- déclaration de clôture l'objet fermera automatiquement la série de résultats objet trop.
Le curseur - sera créé pour toutes les instructions SELECT/INSERT/UPDATE/DELETE.
- chaque instance Oracle DB peut être identifiée en utilisant oracle SID; de la même manière ORACLE DB peut identifier chaque connexion en utilisant connection SID. Les deux SID sont différents.
- donc Oracle session n'est rien d'autre qu'une connexion JDBC(tcp); qui n'est rien d'autre qu'un SID.
- si nous paramétrons cursors maximum à 500 alors ce n'est que pour une session JDBC/connexion/SID.
- donc nous pouvons avoir beaucoup de connexion JDBC avec son no respectif de curseurs (déclarations).
- une fois la JVM terminée, toutes les connexions/curseurs seront fermés, ou la Jdbccconnection est fermée les curseurs par rapport à cette connexion seront fermés.
Loggin en tant que sysdba.
In Putty (Oracle login):
[oracle@db01 ~]$ sqlplus / as sysdba
En SqlPlus:
nom d'utilisateur: sys as sysdba
définit la valeur de session_cached_cursors à 0 pour qu'il n'ait pas de curseurs fermés.
alter session set session_cached_cursors=0
select * from V$PARAMETER where name='session_cached_cursors'
sélectionner la valeur OPEN_CURSORS existante définie par connexion dans DB
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;
Ci-dessous se trouve la requête pour trouver la liste SID/connections avec des 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éboguez le Code et profitez!!! :)
Corrigez votre Code comme ceci:
try
{ //method try starts
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();
}
} //method/try ends
finally
{ //finally starts
pStmt.close()
}
Êtes-vous sûr, que vous fermez vraiment vos états de service, les connexions et les résultats?
pour analyser des objets ouverts, vous pouvez implanter un modèle delegator, qui enveloppe le code autour de vos objets statemant, connection et result. Donc vous verrez, si un objet va réussir à se fermer.
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(String sql){
return delegates.prepareStatement(sql);
}
public void close(){
try{
delegates.close();
}finally{
log.debug(delegates.toString() + " was closed");
}
}
}
si votre application est une application Java EE tournant sur Oracle WebLogic en tant que serveur d'application, une cause possible pour ce problème est le paramètre Statement Cache Size dans WebLogic.
si le paramètre de taille de Cache de L'instruction pour une source de données particulière est à peu près égal ou supérieur au paramètre de nombre de curseur ouvert maximum de la base de données Oracle, alors tous les curseurs ouverts peuvent être consommés par des instructions SQL cachées qui sont tenues ouvertes par WebLogic, résultant dans L'erreur ORA-01000.
pour résoudre ce problème, réduisez le paramètre de taille de Cache de L'énoncé pour chaque source de données WebLogic qui pointe vers la base de données Oracle pour être significativement inférieur au paramètre de nombre de curseur maximum sur la base de données.
dans la Console D'administration WebLogic 10, l'instruction de paramétrage de la taille du Cache pour chaque source de données se trouve dans L'onglet Services (nav de gauche) > sources de données > (source de données individuelle) > Pool de connexion.
avez-vous mis autocommit=true? Si ce n'est pas le cas, essayez ceci:
{ //method try starts
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();
}
} //method/try ends {
//finally starts
pStmt.close()
} //finally ends
requête pour trouver sql qui s'est 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'ai eu une boucle for dans le try {}, pour exécuter une instruction SELECT dans un Oracle DB plusieurs fois, (à chaque fois changeant un paramètre), et dans le final{} j'ai eu mon code pour fermer le Resultset, le PreparedStatement et la connexion comme d'habitude. Mais dès que j'ai atteint une quantité spécifique de boucles (1000) j'ai eu l'erreur D'Oracle à propos de trop de curseurs ouverts.
basé sur le post D'Andrew Alcock ci-dessus, j'ai fait des changements de sorte que à l'intérieur de la boucle, j'ai fermé chaque jeu de résultats et chaque énoncé après avoir obtenu les données et avant de boucler à nouveau, et qui a résolu le problème.
de plus, le même problème s'est produit dans une autre boucle D'Insert Statements, dans un autre Oracle DB (ORA-01000), cette fois après 300 statements. Encore une fois, il a été résolu de la même manière, de sorte que soit la déclaration préparée ou le jeu de résultats ou les deux, comptent comme curseurs ouverts jusqu'à ce qu'ils soient fermés.
moi aussi j'avais fait face à cette question.L'exception ci-dessous a été utilisée pour venir
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 d'une façon ou d'une autre et après quelques minutes environ, elle me donnait cette exception.
après beaucoup de débogage et d'analyse minutieux, j'ai trouvé qu'il y avait un problème avec le Indexage, clé primaire et contraintes uniques dans l'une des Table étant utilisé dans le requête que j'exécutais.
mon application essayait de mettre à jour les colonnes qui étaient par erreur indexé . Donc, chaque fois que mon application tapait la requête de mise à jour sur les colonnes indexées, la base de données essayait de faire le reindexing basé sur les valeurs mises à jour. Il a eu des fuites le curseurs .
j'ai pu résoudre le problème en indexant correctement les colonnes qui ont été utilisées pour rechercher dans la requête et en appliquant des contraintes appropriées partout où cela était nécessaire.
L'Utilisation du traitement par lots réduira les 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 hibernation et nous avions de nombreuses variables se référant à la même entité cartographiée hibernation. Nous avons créé et enregistré ces références dans une boucle. Chaque référence ouvrait un curseur et le gardait ouvert.
nous l'avons découvert en utilisant une requête pour vérifier le nombre de curseurs ouverts pendant l'exécution de notre code, en passant par un débogueur et en commentant sélectivement les choses.
pourquoi chaque nouvelle la référence a ouvert un autre curseur - l'entité en question avait des collections d'autres entités associées à elle et je pense que cela avait quelque chose à voir avec elle (peut-être pas seulement cela mais en combinaison avec la façon dont nous avions configuré le mode fetch et les paramètres de cache). Hibernate lui-même a eu bugs autour de ne pas fermer curseurs ouverts, bien qu'il semble que ceux-ci ont été corrigés dans les versions ultérieures.
Puisque nous n'avons pas vraiment besoin d'avoir autant de doublons références à la même entité de toute façon, la solution était d'arrêter de créer et de s'accrocher à toutes ces références redondantes. Une fois que nous avons fait cela le problème quand loin.
ce problème se produit principalement lorsque vous utilisez la mise en commun des connexions car lorsque vous fermez la connexion, celle-ci retourne à la piscine de connexion et tous les curseur associés à cette connexion ne se ferment jamais car la connexion à la base de données est toujours ouverte. Ainsi une alternative est de diminuer le temps de connexion inactif des connexions dans la piscine, donc peut chaque fois que la connexion est inactif dans la connexion pour dire 10 secondes , la connexion à la base de données sera fermée et nouvelle connexion créée pour mettre en piscine.
j'ai eu ce problème avec ma source de données dans WildFly et Tomcat, se connectant à un Oracle 10g.
j'ai trouvé que dans certaines conditions la déclaration n'était pas fermée même quand la déclaration.close() a été invoquée. Le problème était avec le pilote Oracle que nous utilisions: ojdbc7.pot. Ce pilote est destiné à Oracle 12c et 11g, et il semble avoir quelques problèmes quand il est utilisé avec Oracle 10g, donc je rétrograde à ojdbc5.jar et maintenant tout va bien.
j'ai affronté le même problème parce que je questionnais db pour plus de 1000 itérations. J'ai utilisé try et finalement dans mon code. Mais était encore en train d'erreur.
pour résoudre ce que je viens de me connecter dans Oracle db et ran ci-dessous requête:
ALTER SYSTEM SET open_cursors = 8000 SCOPE=BOTH;
Et cela a résolu mon problème immédiatement.