1

I am trying to troubleshoot an issue that presents as different database errors such as, ORA-01000: maximum open cursors exceeded or Unable to create DB Connection. I have reviewed the PLSQL to determine if cursors were left open and all are closed even if there is an error.

The java application and background are as follows: The original application was a 3-tiered system:

GUI app. -> server app -> 11g Oracle database

The enhancement was to add an API service in a Pivotal Cloud Foundry (PCF) environment. So this architecture was like this:

Close Function: GUI app -> Server App -> API service -> Database.

All other Functions: GUI app -> Server App -> Database.

This was put into production and run for a week without any database issues described above. Then another enhancement was added in which the API Service communicates with several other services all in PCF in which 2 communicate with the same oracle database. Now during heavy volume we are getting these database errors.

It seems to me that the Oracle database cannot keep up with the requests from these additional services. But how can I demonstrate that. We have AppD configured for the servers but not the database. Are there queries that I can run in the prod env. that shows that these PCF applications are causing the issue? Or should I look in another area?

Thanks,

UPDATE I created a query that shows the total open cursor by sessionID This is the query:

select b.sid, b.username, b.osuser, b.status, sum(a.value) total_opened_current_cursors
from sys.v_$statname c,  
     sys.v_$sesstat a,
     sys.v_$session b    
 where a.statistic#=c.statistic# and 
      b.sid=a.sid and 
      c.name in ('opened cursors current') and 
      (b.username is not null or b.username <> '' )   
group by b.sid, b.username, b.osuser
order by total_opened_current_cursors desc

Now, I need to link the sessionID with the application that has this session. The osuser for the top ones is NULL. Also, most of the sessions' status are INACTIVE How to identify the application to the session? Secondly, is the session is inactive, which I thought meant that no query is happending so why are there open cursors?

UPDATE SORRY I ADDED THIS UPDATE TO THE ANSWER...SHOULD HAVE ADDED IT HERE So, I wrote a query that returns the top 10 sessions with the highest open cursors

select * 
FROM 
(
select b.sid, b.username, b.osuser, b.status, sum(a.value) total_opened_current_cursors
from sys.v_$statname c,  
     sys.v_$sesstat a,
     sys.v_$session b    
 where a.statistic#=c.statistic# and 
      b.sid=a.sid and 
      c.name in ('opened cursors current') and 
      (b.username is not null or b.username <> '' )   
group by b.sid, b.username, b.osuser,  b.status
order by total_opened_current_cursors desc
)
WHERE ROWNUM <= 10;

I found the SQL_TEXT that accounts for most of the open cursors...by far! (87%) So, how do I find the query that calls this SQL? There are at least 5 services that hit the database. Some of the services call PLSQL stored procedures some call raw SQL text. The query that accounts for the open cursors is listed as a SELECT statement. Does that mean it is NOT a stored procedure? Or can this SELECT be called within the stored procedures.
How do I find the connection that uses this session?

  • the max number of open cursors is set at a session level, so you have a session opening more than the limit. What is the current limit (the Oracle default is low, by the way). There’s no real harm in upping the parameter significantly temporarily while you continue to investigate. You can find the problematic sessions using the v$sesstat view and v$open_cursor. – Philᵀᴹ Jun 1 at 9:22
1

ORA-01000: maximum open cursors exceeded is almost always an application error. I'll put a large wager down that some part of the Java middle tier is failing to close a ResultSet in every case. That causes it to leak cursors and will eventually cause this error. You can increase the max_open_cursors setting in Oracle so that the cursor leak hits the limit more slowly. You may want to combine that with more aggressive restarts of the middle tier and/or recycling of the connection pool as a temporary measure. But the right answer would be to figure out where the application is leaking cursors and plug the leak.

Do you have any additional logging around the unable to create a connection error? It is possible that you're hitting the maximum number of connections specified by your sessions setting (and the processes setting on which it depends). If you really need to support more connections, you can always bump that up or move some of the connections over to shared server connections if they're using dedicated server connections today. But I'd be concerned given the cursor error that the middle tier is also leaking connections.

  • Is there a way to determine the number of open cursors based on the application? During a close function there can be as many as 3 applications that query the database. – Gloria Santin Jun 2 at 5:23
  • I looked at the legacy application and the result sets are closed. The other 3 PCF applications use Spring Boot to connect to the database. From my understanding, closing connections and result sets do not have to be explicitly closed. JDBCTemplate closes these connections/result sets. The PLSQL added has an additional cursor which is closed on success and exception. – Gloria Santin Jun 2 at 7:23
  • @GloriaSantin - max_open_cursors controls the number of cursors that are open per session (i.e. per connection). The default of 50 should be plenty though you can up that to 65535 if you want. You should be able to use v$open_cursors and v$sesstat to track down what cursors are being left open which hopefully lets you track it down to a particular application. asktom.oracle.com/pls/asktom/… AskTom has a much more detailed discussion. – Justin Cave Jun 2 at 14:08
  • Thanks for the insight. Just a basic question. These queries must take place in realtime of the max open cursor error, correct? Are there any tables that I can query for errors that happened in the past? – Gloria Santin Jun 2 at 21:13
  • @GloriaSantin - I don't know of a history table. When you're looking for a cursor leak, though, you should see the number of open cursors increasing slowly over time in a session (assuming your sessions are relatively long-lived which is the normal case). So you don't need to be getting the error to watch in real time as different sessions leak cursors to figure out which statements are getting leaked. – Justin Cave Jun 2 at 21:58

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.