-
Hi All
I have issued the following query
"select user_name , status, osuser, machine, a.sql_text
from v$session b, v$open_cursor a
where a.sid = b.sid"
I am getting same SQL in the SQL_TEXT in my system.
I have only 4 jdbc clients accessing the database but a same query is passed by them (They are accessing data through stored procedures so there is no question of similar but different query)
One of my function contains a simple query 'SELECT * FROM ACTIVEUSERS' which occurs in the result od above query in SQL_TEXT' at least 30 times. I am closing all my cursors properly and even Java programs are also well drafted everyehere using stmt.close(). Then Why so much of cursors are created and also what the status is 'INACTIVE' ?
Can oracle cache that query and use it for different clients.
Should I set the parameter CURSOR_SHARING=Y. Is there any other way where I can close/reuse the INACTIVE cursors?
Amol
-
Try
Code:
ALTER SYSTEM SET CURSOR_SHARING=FORCE;
Note: Forcing cursor sharing among similar (but not identical) statements can have unexpected results in some DSS applications and if your applications use stored outlines
Sam
-
if you want to know exact cursors open you have to check v$sysstat, statistic#3
v$open_cursor shows cached cursors
-
The count on V$SYSSTAT gives me even more number i.e. 203 and where STATISTIC#=3 gives me just 1 record.
The count of V$OPEN_CURSOR os still less i.e. 130 so what can be real numeber about my system.
I have also used ALTER SYSTEM SET CURSOR_SHARING=FORCE;
However there is still no difference. (By the how to undo forced cursor sharing)
Amol
-
you real number of open cursor is what v$sysstat shows
ALTER SYSTEM SET CURSOR_SHARING=NONE
