2

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 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.

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

select b.sid, b.username, b.osuser, 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 happening so why are there open cursors?

        **UPDATE**

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?

Gloria Santin
  • 2,066
  • 3
  • 51
  • 124
  • Maybe a better candidate for https://dba.stackexchange.com/? – saritonin May 31 '19 at 20:20
  • Thanks...will try there. – Gloria Santin May 31 '19 at 20:21
  • I would start by looking at this query: `select * from v$open_cursor;`. This is likely not a database error, unless `select value from v$parameter where name = 'open_cursors'` is set ridiculously low. Usually when I've seen this problem is happens because an exception handler in the application forgot to cleanup. – Jon Heller Jun 01 '19 at 01:26
  • The result from the v$parameter table was 1000. Is there any way to determine the number of open cursors based on the application? During the close function, the API service accesses 2 other services that also hit the database. I need to determine which service is leaving open cursors. – Gloria Santin Jun 02 '19 at 05:06
  • Try this query to find the culprits: `select * from gv$open_cursor join gv$session on gv$open_cursor.inst_id = gv$session.inst_id and gv$open_cursor.sid = gv$session.sid;` – Jon Heller Jun 02 '19 at 21:35
  • `OSUSER` should never be null. But that value can be controlled/spoofed with JDBC. I wonder if you're caught in a catch-22: you're getting errors because of a bad driver, but you can't find the errors because of a bad driver. If the client isn't correctly reporting information you may need to rely on a tool outside of the database, like some sort of packet tracer. But I would still think that the SQL statements listed in `GV$OPEN_CURSOR` should give a string hint at the problem. Unless the problem happens and the session quickly closes - maybe run the query frequently? – Jon Heller Jun 04 '19 at 04:59
  • Also, inactive queries can still have open cursors cached. But those inactive queries shouldn't matter much. The limit is per session, not cumulative for the whole database. 1000 is already above the default value, and implies somebody already found this problem before and tried to workaround it without resolving the root cause. Maybe ask around, and see if anybody remembers changing that parameter in the past? – Jon Heller Jun 04 '19 at 05:02
  • This legacy code is 20 years old. Nobody will remember that. I found one of the queries that has alot of open cursors. I need to find the connection that the session calls this statement. I added an update above.. – Gloria Santin Jun 04 '19 at 12:45
  • You can find out which user first ran the SQL with this: `select parsing_schema_name from gv$sql where sql_id = 'abcd1234';` If the SQL statement is running right now, you can find session information about it like this: `select * from v$session where sql_id = 'abcd1234';`. The column PLSQL_ENTRY_OBJECT_ID, if it's populated, will identify the procedure or package that contains the SQL: `select * from dba_objects where object_id = 1234;`. If that doesn't work, try looking at `DBA_SOURCE`. – Jon Heller Jun 05 '19 at 15:08
  • THANK YOU! Will do this and get back to ya – Gloria Santin Jun 06 '19 at 11:50

0 Answers0