manage/monitor open_cursors

The open_cursors parameter is a governor, a block to prevent runaway tasks from consuming too much library cache RAM.

Any session may execute many SQL statements and the open_cursors parameter governs the total number of open cursors for any given session. 

For example, if you set open_cursors=100, Oracle will be allowed to allocate up to 100 cursor slots in the library cache.  Because the slots are only allocated as they are requested, there is no added overhead to setting this value higher than actually needed.

The starting value is set by Oracle at instance creation time.   

Just like the sessions and processes parameters, your application usage determines the value for open_cursors.

If you set open_cursors value too high, you risk having a task abort with the ORA-01000 error:

ORA-01000 maximum open cursors exceeded

 Whenever you get an ORA-01000 error you need to determine if the session has a bug or whether the cursor requests are legitimate.  You can change theopen_cursors parameter dynamically while the database is running using an alter system statement:

 alter system set open_cursors = 400 scope=both;

You can monitor your high water mark for open cursors with a query like this:

col hwm_open_cur format 99,999
col max_open_cur format 99,999

select 
   max(a.value) as hwm_open_cur, 
   p.value      as max_open_cur
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;

HWM_OPEN_CUR     MAX_OPEN_CUR

---------------- ------------

           2,350        4,096

 In sum, the open_cursors parameter default value is usually enough for any application, and it can be increased as-needed, depending upon your application.

 Monitoring open cursors

To monitor your open cursors, you have several views:

§  v$open_cursor

§  v$sesstat

select 
   stat.value, 
   sess.username, 
   sess.sid, 
   sess.serial#
from 
   v$sesstat  stat, 
   v$statname b, 
   v$session  sess
where 
   stat.statistic# = b.statistic#  
and 
   sess.sid=stat.sid
and 
   b.name = 'opened cursors current'; 

select 
   sum(stat.value) 
   total_cur, 
   avg(stat.value) avg_cur, 
   max(stat.value) max_cur, 
   sess.username, 
   sess.machine
from 
   v$sesstat   stat, 
   v$statname     b, 
   v$session    sess 
where 
   stat.statistic# = b.statistic#  
and 
   sess.sid=stat.sid
and 
   b.name = 'opened cursors current' 
group by 
   sess.username, 
   sess.machine
order by 1 desc;

Difference between CANDIDATE/FORMER and PROVISIONED DISK in ASM

Disks that were discovered but that have not yet been assigned to a disk group have a header status of either CANDIDATE or PROVISIONED.

CANDIDATE 

Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

PROVISIONED

Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.

For example, on Windows, the administrator used asmtool or asmtoolg to stamp the disk with a header, or on Linux, the administrator used ASMLib to prepare the disk for ASM.

Below are the HEADER_STATUS in the v$ASM_DISK. I have taken below status from 11gR2.

·         UNKNOWN - Automatic Storage Management disk header has not been read

·         CANDIDATE - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement

·         INCOMPATIBLE - Version number in the disk header is not compatible with the Automatic Storage Management software version.

·         PROVISIONED - Disk is not part of a disk group and may be added to a disk group with the ALTER DISKGROUP statement. The PROVISIONED header status is different from the CANDIDATE header status in that PROVISIONED implies that an additional platform-specific action has been taken by an administrator to make the disk available for Automatic Storage Management.

·         MEMBER - Disk is a member of an existing disk group. No attempt should be made to add the disk to a different disk group. The ALTER DISKGROUP statement will reject such an addition unless overridden with the FORCE option

·         FORMER - Disk was once part of a disk group but has been dropped cleanly from the group. It may be added to a new disk group with the ALTER DISKGROUP statement.

·         CONFLICT - Automatic Storage Management disk was not mounted due to a conflict

·         FOREIGN - Disk contains data created by an Oracle product other than ASM. This includes datafiles, logfiles, and OCR disks.

      When adding a disk, the FORCE option must be used if Oracle ASM   

      recognizes that the disk was managed by Oracle. Such a disk appears 

      in the V$ASM_DISK view with a status of FOREIGN.