Tune OPEN_CURSORS parameter in Oracle

Tune OPEN_CURSORS parameter in Oracle

Open Cursors parameter is define the maximum open cursor a session can have open.
If you define the value of open_cursors parameter to 300 then a session can open max upto 300 cursors.

Check the value of Open Cursors parameter
Default value assigned is 300

SQL> show parameter open_cursors

NAME                TYPE        VALUE
------------------- ----------- -------
open_cursors        integer     300

Check the maximum value used by Oracle for Setting the OPEN_CURSORS parameter

Col Session_Max_open_cursor for 99999
col Parameter_Value_define for a25
select max(a.value) as Session_Max_open_cursor, p.value as Parameter_Value_define
from v$sesstat a, v$statname b, v$parameter p
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'
and p.name = 'open_cursors'
group by p.value;

Note: If value is reached to maximum limit then you can increase the value of this parameter.

Check the usage in percentage of open_cursor

select 'open_cursors' parameter,
lpad(value, 5) value,
decode(value, 0, ' n/a', to_char(100 * used / value, '990') || '%') usage
from (select max(s.value) used
from sys.v_$statname n, sys.v_$sesstat s
where n.name = 'opened cursors current'
and s.statistic# = n.statistic#),
(select value
from sys.v_$parameter
where name = 'open_cursors')

Note: If usage is above 95% percent than you can also increase the value.

Increase or Decrease the value by setting with alter command
Change this parameter need to restart the Database.

ALTER SYSTEM SET OPEN_CURSORS =500 scope=BOTH;

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.