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