Open Cursors

[nik_flash]

hi
I have a doubt
If I specify open cursors a small value
Can this lead to performance degradation with out any error?

[nir_s]

Hi nik,

Increasing the "init.ora" parameter 'OPEN_CURSORS' may cause Oracle to pre-allocate more PGA (SGA if using MTS) to keep track of cursor descriptors. However, if those cursors are never used, it should not have much effect on performance.

You could test this by increasing the 'OPEN_CURSORS' value, then monitor how it will affect session memory from the Oracle side (such as with "v$sesstat"),as well as paging and swapping on the host.
The way the applications utilize the increased number of open cursors available and will have much greater impact on performance.

Regards,
Nir

[nik_flash]

Thanx for the reply Nir
so does that mean that on increasing the OPEN_CURSOR improvement there can be a good improvement in the performance of the system, if it does not clash with the SGA or PGA size available.

regards nik

Originally posted by nir_s
Hi nik,

Increasing the "init.ora" parameter 'OPEN_CURSORS' may cause Oracle to pre-allocate more PGA (SGA if using MTS) to keep track of cursor descriptors. However, if those cursors are never used, it should not have much effect on performance.

You could test this by increasing the 'OPEN_CURSORS' value, then monitor how it will affect session memory from the Oracle side (such as with "v$sesstat"),as well as paging and swapping on the host.
The way the applications utilize the increased number of open cursors available and will have much greater impact on performance.

Regards,
Nir