Solved

Oracle open_cursor

Posted on 2010-11-15
5
725 Views
Last Modified: 2012-05-10
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
Comment
Question by:sachin_dba
  • 3
  • 2
5 Comments
 
LVL 13

Expert Comment

by:sonicefu
ID: 34134811
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
 

Author Comment

by:sachin_dba
ID: 34134979
I want to understand which module (procedure/functions) or sessions are opening more cursors.
0
 
LVL 13

Accepted Solution

by:
sonicefu earned 500 total points
ID: 34135029
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
 

Author Comment

by:sachin_dba
ID: 34247044
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
 

Author Closing Comment

by:sachin_dba
ID: 36876560
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

Working with Network Access Control Lists in Oracle 11g (part 1) Part 2: http://www.e-e.com/A_9074.html So, you upgraded to a shiny new 11g database and all of a sudden every program that used UTL_MAIL, UTL_SMTP, UTL_TCP, UTL_HTTP or any oth…
I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that use…
This video shows how to Export data from an Oracle database using the Datapump Export Utility.  The corresponding Datapump Import utility is also discussed and demonstrated.
This video shows how to Export data from an Oracle database using the Original Export Utility.  The corresponding Import utility, which works the same way is referenced, but not demonstrated.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now