Re: OPEN_CURSORS

In article <8o665g$ttc$1_at_nnrp1.deja.com>,   kishen <kishenp_at_my-deja.com> wrote:
> Hi All,
> Currently we have the OPEN_CURSORS set as default 50.
> In some particular cases when certain SQL's are executed, 28-30
 cursors
> get openend. Due to which i get the maximum OPEN_CURSORS limit
 exceeded.
> Is there a way to find out how many cursors get opened for a specific
> SQL ? Will increasing the number OPEN_CURSORS solve my problem ?
>
> TIA
> Kishen
>

Interesting, I would think one sql statement would result in one cursor. The only way I would expect to see multiple cursors for the same sql statement for one session is if an explicit cursor was used in a loop and it was not closed before the loop was reexecuted.

You can see all the open cursors in the instance in v$open_cursor which includes the sid so you can look at only the cursors for a session of interest or group by address and hash value to find shared sql.

It is not unusual for a screen such as an sqlforms screen to have 50 - 70 open cursors so trying to use 50 as the limit may not be practical.

Here is an sql that should, if I did not do something stupid, show duplicate cursors held by one session:

OPS2> l
  1 select sid, sql_text, count(*)
  2 from v$open_cursor
  3 group by sid, sql_text
  4* having count(*) > 1

Obviously since I got hits I need to research this more when I get time. I hope this helps you with your problem.

I got a logon screen when I tried to post so if this shows up twice I apologize.

--
Mark D. Powell  -- The only advice that counts is the advice that
 you follow so follow your own advice --


Sent via Deja.com http://www.deja.com/
Before you buy.