Cursor is a pointer to a memory area which is opened
to execute an SQL statement issued by the end user/application. It means that
every SQL (SELECT/DML) you want to execute would require utilizing this memory
area to execute the SQL.
OPEN_CURSORS
To limit the memory used by a session to open cursors for the execution of SQLs, the parameter OPEN_CURSORS is used. This parameter sets the limits of simultaneously opened cursors for each session. No session can open more cursors than specified in this parameter. I have checked different websites/blogs and have found out that experts suggest setting its value to around 1000 and this should be enough for most of the applications. If you still see high number of cursors being opened by sessions (you should have alerts set for open cursor matrix in the OEM or in any other monitoring tool that you use) or if you see ORA-01000, it means that your application needs the value of OPEN_CURSORS to be increased. You can dynamically change value of this parameter.
To limit the memory used by a session to open cursors for the execution of SQLs, the parameter OPEN_CURSORS is used. This parameter sets the limits of simultaneously opened cursors for each session. No session can open more cursors than specified in this parameter. I have checked different websites/blogs and have found out that experts suggest setting its value to around 1000 and this should be enough for most of the applications. If you still see high number of cursors being opened by sessions (you should have alerts set for open cursor matrix in the OEM or in any other monitoring tool that you use) or if you see ORA-01000, it means that your application needs the value of OPEN_CURSORS to be increased. You can dynamically change value of this parameter.
Increasing the value of this parameter and keep
increasing might not be very good, so you should involve application team to
confirm if cursors being opening by the code are also closed timely after work
done by the cursor completes.
After you set this parameter to a sufficiently high
value, you should not see sessions failing with ORA-01000 error message.
Following is a query that can show you current open
cursors utilization by each session.
|
select
a.username,a.sid,a.program, b.value "opened_cursors" from v$session
a, v$sesstat b, v$statname c where a.sid=b.sid and b.statistic#=c.statistic# and c.name = 'opened cursors current' and
a.username is not null order by b.value;
USERNAME SID PROGRAM
opened_cursors
------------------------------
---------- ----------------------------------------------------------------
--------------
C##SALMAN 129 sqlplus.exe
12
C##SALMAN 19 sqlplus.exe
3
|
No comments:
Post a Comment