how to find open cursors

[amolik]

HI All

Can anyone tell me how to find the No. of Cursors which are Open and their details. The no. of rows in view V$OPEN_CURSOR are more than my limit set in init.ora file so I guess its incorrect. Can Anyone tell me how to find correct no. of actuall OPEN Cursors.

Amol

[vinit]

I think the open_cursors init.ora parameter is a value set for a particular session and not a total for all sessions. The value in v$open_cursors is based on a particular session (sid, username) and it should not exceed the value set in init.ora. Can anyone pls correct me if I'm wrong ?

Cheers,

[pando]

it is per session, also v$open_cursor dooes not show all open cursors. it shows more than that, the best option to find the number of open cursors is from v$sysstat

select * from v$sysstat where statistic#=3

[vinit]

Pando,
there seems to b a lot of difference between the values in v$open_cursors and the value of open_cursors current in v$systat. Now which one is correct, I think it is from v$open_cursors.

Regards,
Vinit

[pando]

no, the one in sysstat is correct, v$open_cursor shows more than open cursors, in plsql v2 the cursors are not closed once they are used, they are kind of cached. Also v$sysstat shows cursors that are opened but not parsed whereas v$open_cursor shows cursors that are opend and parsed

[vinit]

Thanx for the clarification, Pando..!!

Cheers

[julian]

You may increase OPEN_CURSORS in order to reduce waits!