• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 819
  • Last Modified:

Oracle open_cursor

We are using apex with Oracle 11g in our application

We are facing number of open_cursor value exceeds.

Can we see which part of the code is causing problem?

Sachin
0
sachin_dba
Asked:
sachin_dba
  • 3
  • 2
1 Solution
 
sonicefuCommented:
you can increase the open_cursors by using the following statement

ALTER SYSTEM SET open_cursors=300 SCOPE=SPFILE; -- >> you can use different value instead
0
 
sachin_dbaAuthor Commented:
I want to understand which module (procedure/functions) or sessions are opening more cursors.
0
 
sonicefuCommented:
Following query may help you
SELECT c.sid AS "Oracle Session ID",
         c.address AS "SQL Address",
         c.hash_value AS "SQL Address Hash",
         COUNT (c.saddr) AS "Cursor Copies",
         c.SQL_ID,
         c.SQL_TEXT
    FROM v$open_cursor c
GROUP BY c.sid,
         c.address,
         c.hash_value,
         c.SQL_ID,
         c.SQL_TEXT
  HAVING COUNT (c.saddr) > 2
ORDER BY 3 DESC;

Open in new window

0
 
sachin_dbaAuthor Commented:
All the apex sessions have more values. ie

SID                Program                            No. of Open Cursors
425      JDBC Thin Client      347
275      JDBC Thin Client      96
334      JDBC Thin Client      80

All the sessions are in waiting stage from long time.

Why apex sessions requires so may cursors?
0
 
sachin_dbaAuthor Commented:
Thanks
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now