Solved

Oracle open_cursor

Posted on 2010-11-15
5
748 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.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
query in Oracle forms Builder 2 51
Check if there are any  duplicate claims paid in a year for each member 25 76
su - oracle could not open session 6 76
exp/imp 25 74
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…
Checking the Alert Log in AWS RDS Oracle can be a pain through their user interface.  I made a script to download the Alert Log, look for errors, and email me the trace files.  In this article I'll describe what I did and share my script.
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.
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

778 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