Yesterday We faced below error message on our 10.2.0.5 RAC database.

Open cursors take up space in the shared pool, in the library cache. To keep a renegade session from filling up the library cache, or clogging the CPU with millions of parse requests, we set the parameter OPEN_CURSORS.

OPEN_CURSORS sets the maximum number of cursors each session can have open, per session. For example, if OPEN_CURSORS is set to 1000, then each session can have up to 1000 cursors open at one time. If a single session has OPEN_CURSORS # of cursors open, it will get an ora-1000 error when it tries to open one more cursor.

The default is value for OPEN_CURSORS is 50, but Oracle recommends that you set this to at least 500 for most applications. Some applications may need more, eg. web applications that have dozens to hundreds of users sharing a pool of sessions. Tom Kyte recommends setting it around 1000.

I have been checked my current settings as below:

SQL> show parameter cursor
Name       Type      Value
processes  integer   1500
SQL> show parameter session
Name       Type      Value
processes  integer   1655
SQL> show parameter process
Name       Type      Value
processes  integer   1500

So the question is How To Determine the SQL Expression That May Be Causing ORA-01000 on our system. There is metalink note avaliable for this question’s answer.

Here is the steps:

1. Find top 10 sessions which are currently opening most cursors.(In our example my open_cursors has been set to 1500)

# sqlplus / as sysdba
SQL> select * from ( select ss.value, sn.name, ss.sid
 from v$sesstat ss, v$statname sn
 where ss.statistic# = sn.statistic#
 and sn.name like '%opened cursors current%'
 order by value desc) where rownum < 11 ;

You may also go with below query:

SQL> select c.sid as "OraSID",
c.address||':'||c.hash_value as "SQL Address",
COUNT(c.saddr) as "Cursor Copies"
from v$open_cursor c
group by
c.sid,
c.address||':'||c.hash_value
having COUNT(c.saddr) > 2
order by 3 DESC ;

Ps: Do not forget. Those sql for standalone database, If you have RAC you need to use gv$ instead of v$ views.

2. Identify session and SQL text by using below queries:

SQL> select A.SID as sid, a.status as status, A.EVENT as event, a.seconds_in_wait as wait, a.blocking_session as blk_sesn , a.prev_sql_id as SQL_ID from v$session a where a.sid='xxx'; << If you use first query
SQL> select SQL_FULLTEXT from v$sql where ADDRESS ||':'||HASH_VALUE = '<SQL Address>' ; << If you use second query

3. So now you have 2 option to can solve this error

a. Kill session if its INACTIVE status,
b. Increase this parameter dynamicly.

If you go wiht option b than issue is:

SQL> ALTER SYSTEM SET open_cursors = 3000 SCOPE=BOTH;  << If its standalone database
SQL> ALTER SYSTEM SET open_cursors = 3000 SCOPE=BOTH SID='*';  << If its RAC database

You can use event 1000 for additional diagnosis to can solve this problem. For details please check reference guide

Referance:
http://www.orafaq.com/node/758
How To Determine the SQL Expression That May Be Causing ORA-01000 [ID 1333600.1]

Advertisements