Hi Tom,
Two questions:
1. I run you query listed here
select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and a.statistic#= 3;
The result is the same in any databases I have (9 in total) at any time.
VALUE NAME
--------- ------------------------
1 opened cursors current

I think this is not the case.

2. When I run
select count(*) from v$open_cursor;
I get 1996 as the result. However, my parameter file setting is:
Open cursors = 1000.
How can that be? You have explained above that v$open_cursor is not the currently open cursors. Should the number returned from v$open_cursor smaller than the setting in parameter file, 1000 in my case, even though the number is not the actually openning cursors? The database has no problem.
Thank you very much.

1) and ... why do you believe that?  care to explain why you think it is wrong (i don't think it is wrong)


SQL*Plus: Release 8.1.7.0.0 - Production on Tue Jun 25 15:35:20 2002
(c) Copyright 2000 Oracle Corporation.  All rights reserved.
Connected to:
Oracle8i Enterprise Edition Release 8.1.7.3.0 - Production
With the Partitioning option
JServer Release 8.1.7.3.0 - Production

ops$tkyte@ORA817DEV.US.ORACLE.COM> select a.value, b.name 
  2              from v$mystat a, v$statname b 
  3             where a.statistic# = b.statistic# 
  4               and a.statistic#= 3;

     VALUE NAME
---------- ------------------------------
         1 opened cursors current

2) select sid, count(*) from v$open_cursor group by sid

open_cursor is by session.  You are looking across sessions. 

I looked all over for this information. I am trying to set up alerting before the ORA-1000 error is posted in the alert log. I am now changing my code to count the maximum number of open cursors for a session, and check it against a threshold value, instead of counting all open cursors for a session

Tom

After testing would I be right in saying then that the query:

SELECT a.VALUE, b.NAME
FROM v$mystat a, v$statname b
WHERE a.statistic# = b.statistic#
AND a.statistic#= 3

Returns only those open cursors for your current session? If this is so, how do you get all the open cursors for the database? Would you use v$open_cursors then?
Thanks
R.

you would use v$sesstat.

I did a bad thing here too -- i should have coded using the NAME not the number:


ops$tkyte@ORA9I> select a.value, b.name
  2    from v$mystat a, v$statname b
  3   where a.statistic# = b.statistic#
  4     and b.name = 'opened cursors current'
  5  /

     VALUE NAME
---------- ------------------------------
         1 opened cursors current

ops$tkyte@ORA9I> select sum(a.value), b.name
  2    from v$sesstat a, v$statname b
  3   where a.statistic# = b.statistic#
  4     and b.name = 'opened cursors current'
  5   group by b.name
  6  /

SUM(A.VALUE) NAME
------------ ------------------------------
          94 opened cursors current

 

Sorry about that Tom,
a little more investigation leads me to believe that if you have a 1000 set as max number of open cursors you are allowed a 1000 per session, for all sessions. Is this correct.

Also I replaced v$mystat with v$sesstat to get all the session open cursors. Is this the correct view? If so is it misleading that it gives me the same number of open cursors as v$open_cursors does?

Cheers
R.

Hi Tom - 
I tried this - 

SQL> select sum(a.value), b.name
      from v$sesstat a, v$statname b
     where a.statistic# = b.statistic#
       and b.name = 'opened cursors current'
     group by b.name
  2    3    4    5    6  /

SUM(A.VALUE) NAME
------------ ------------------------------------------------------------
         515 opened cursors current

SQL> select count(1) from v$open_Cursor;

  COUNT(1)
----------
       377

SQL>

How can v$open_cursor contain less than your query? 

How can v$open_cursor be only my own cursors? It has a sid column.

never mind, restart....(geez, i even said myself:

...
2) select sid, count(*) from v$open_cursor group by sid

open_cursor is by session.  You are looking across sessions. 
......

above) thats what I get for firing off an answer too fast.  thanks for checking back.


but anyway, on a busy system -- one would expect there to be differences -- cursors come and cursors go.  All you need is one session to exit between calls and bamm -- there go hundreds of cursors potentially.

Now, since V$ views are *not* read consistent -- they are 100% point in time pictures -- at the point in time you look at them -- you'll see this.  

Even if you try a single query like so:

ops$tkyte@ORA9I> select sum(a.value), b.name
  2  from v$sesstat a, v$statname b
  3  where a.statistic# = b.statistic# and b.name = 'opened cursors current'
  4  group by b.name
  5  union all
  6  select count(*), 'v$open_cursor' from v$open_cursor
  7  /

SUM(A.VALUE) NAME
------------ ------------------------------
         100 opened cursors current
         129 v$open_cursor

ops$tkyte@ORA9I> /

SUM(A.VALUE) NAME
------------ ------------------------------
          85 opened cursors current
         129 v$open_cursor

ops$tkyte@ORA9I> /

SUM(A.VALUE) NAME
------------ ------------------------------
         110 opened cursors current
         175 v$open_cursor

ops$tkyte@ORA9I> /

SUM(A.VALUE) NAME
------------ ------------------------------
          91 opened cursors current
          97 v$open_cursor

ops$tkyte@ORA9I> /

SUM(A.VALUE) NAME
------------ ------------------------------
         144 opened cursors current
         137 v$open_cursor

ops$tkyte@ORA9I> /

SUM(A.VALUE) NAME
------------ ------------------------------
         146 opened cursors current
          95 v$open_cursor


v$sesstat is queried at a different point in time then v$open_cursor.  Both answers are *right* at their respective points in time.  If you do this on a "single user" system -- you'll see much more predicable responses.

 

Is there a way to monitor open_cursors usage? I want to get noted if open_cursors parameter is too small.

My monitoring "sofware" uses this query:

select value-(select max(value) from v$sesstat where STATISTIC#='3') from v$parameter where name='open_cursors';

...but sometimes, some databases it gives negative value, so my logic must be wrong or there is a bug or something.

SQL> select STATISTIC#, name from v$sysstat where NAME='opened cursors current';

STATISTIC# NAME
---------- ----------------------------------------------------------------
         3 opened cursors current

SQL> 


SQL> select value-(select max(value) from v$sesstat where STATISTIC#='3') from 
v$parameter where name='open_cursors';   

VALUE-(SELECTMAX(VALUE)FROMV$SESSTATWHERESTATISTIC#='3')
--------------------------------------------------------
                                                    2677

SQL> show parameter open_cursors

NAME                                 TYPE    VALUE
------------------------------------ ------- ------------------------------
open_cursors                         integer 3000

SQL> select value-(select max(value) from v$sesstat where STATISTIC#=3) from v$parameter where name='open_cursors';

VALUE-(SELECTMAX(VALUE)FROMV$SESSTATWHERESTATISTIC#=3)
------------------------------------------------------
                                                  2677


...but sometimes it gives negative values!?
 

Great! Now will this show me ref cursors as well as "regular" cursors? I don't think it does (after running a quick test). So I can find out whether my web app has a ref cursor leak (ref cursors being opened but never closed). We use connection pooling so a session may stay active for quite some time.

ref cursors ARE regular cursors.

yes, it'll show you.


ops$tkyte@ORA920LAP> select name, value
  2   from v$mystat a, v$statname b
  3   where a.statistic# = b.statistic#
  4    and b.name = 'opened cursors current'
  5  /

NAME                                VALUE
------------------------------ ----------
opened cursors current                  1

ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> variable x refcursor
ops$tkyte@ORA920LAP> variable y refcursor
ops$tkyte@ORA920LAP> variable z refcursor
ops$tkyte@ORA920LAP>
ops$tkyte@ORA920LAP> exec open :x for select * from dual;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> /

NAME                                VALUE
------------------------------ ----------
opened cursors current                  2

ops$tkyte@ORA920LAP> exec open :y for select * from dual;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> /

NAME                                VALUE
------------------------------ ----------
opened cursors current                  3

ops$tkyte@ORA920LAP> exec open :z for select * from dual;

PL/SQL procedure successfully completed.

ops$tkyte@ORA920LAP> /

NAME                                VALUE
------------------------------ ----------
opened cursors current                  4

 

I think I see what's going on here.
<pre>
declare
type typ_ref is ref cursor;
v_ref typ_ref;
v_sum number;
v_name varchar2(100);
begin
select name, value
into v_name, v_sum
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current';

dbms_output.put_line(v_name || ': ' || v_sum);

open v_ref for select 1 from dual;
open v_ref for select 'test' from dual;
open v_ref for select user from dual;
open v_ref for select sysdate from dual;
open v_ref for select * from dual;

select name, value
into v_name, v_sum
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current';

dbms_output.put_line(v_name || ': ' || v_sum);
end;
/

opened cursors current: 7
opened cursors current: 9
</pre>

My cursor space is being reused. I was expecting Oracle to store all the different queries in memory.

Thanks!

SQL> select STATISTIC#, name from v$sysstat where NAME='opened cursors current';

STATISTIC# NAME
---------- ----------------------------------------------------------------
         3 opened cursors current


SQL> select max(VALUE) from v$sesstat where STATISTIC#=3;
MAX(VALUE)
----------
381

SQL> show parameter open_cursors
NAME TYPE VALUE
------------------------------------ ------- ------------------------------
open_cursors integer 150

SQL> select value-(select max(value) from v$sesstat where STATISTIC#='3')
2 from v$parameter where name='open_cursors';
VALUE-(SELECTMAX(VALUE)FROMV$SESSTATWHERESTATISTIC#='3')
--------------------------------------------------------
-231
 

any chance someone did an alter system?


ops$tkyte@ORA920> select STATISTIC#, name from v$sysstat where NAME='opened cursors current';
 
STATISTIC# NAME
---------- ------------------------------
         3 opened cursors current
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> select max(VALUE) from v$sesstat where STATISTIC#=3;
 
MAX(VALUE)
----------
        10
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> show parameter open_cursors
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     500
ops$tkyte@ORA920>
ops$tkyte@ORA920> select value-(select max(value) from v$sesstat where STATISTIC#='3')
  2    from v$parameter where name='open_cursors';
 
VALUE-(SELECTMAX(VALUE)FROMV$SESSTATWHERESTATISTIC#='3')
--------------------------------------------------------
                                                     490
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> alter system set open_cursors = 5;
 
System altered.
 
ops$tkyte@ORA920>
ops$tkyte@ORA920> show parameter open_cursors
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
open_cursors                         integer     5
ops$tkyte@ORA920>
ops$tkyte@ORA920> select value-(select max(value) from v$sesstat where STATISTIC#='3')
  2    from v$parameter where name='open_cursors';
 
VALUE-(SELECTMAX(VALUE)FROMV$SESSTATWHERESTATISTIC#='3')
--------------------------------------------------------
                                                      -5
 
ops$tkyte@ORA920>
 

I have Oracle 9i R2 application and 9i R2 database server. The open_curosrs were default on db server. The application server stopped responding so it was suggested by the application developer that open_cursors should be increased. Now let me know,

1- Is there any calculations method for open_cursors?
2- If application is not using bind variables so is this the reason that we have to increase open_cursors?
3- If the open_cursors are increased then what will be the performance impact on the db server and the memory usage?

Thanks for your time.

Ya you are right that it should be only increased when the error hits. I would like to know that what kind of bug do you think may be in the application,

2) no, you have a bug in the code that the java developers MUST fix though.

No use of baind variables may be one of the reasons?

Hi Tom, 

I have the following PL/SQL block: 

SQL> declare
  2    procedure x is
  3      cursor c is select ename from emp;
  4      v_ename emp.ename%TYPE;
  5    begin
  6      open c;
  7      fetch c into v_ename;
  8      dbms_output.put_Line(v_ename);
  9      -- Let's not bother to close c
 10    end;
 11  begin
 12    for i in 1..5 loop
 13      x;
 14    end loop;
 15* end;
SQL> /
SMITH
SMITH
SMITH
SMITH
SMITH

I would like to know, if it would hog the memory (especially if I increase the counter) ? It does not show these as open cursors though !

Can you please throw some light on this ..

Thanks 

SQL> set serveroutput on size 1000000
SQL> variable myref refcursor
SQL> DECLARE
  2  TYPE emp_cur_t is REF CURSOR return SCOTT.EMP%ROWTYPE;
  3  v_cur      emp_cur_t;
  4  emp_rec     scott.emp%rowtype;
  5  procedure x is
  6  begin
  7  open v_cur for select * from scott.emp where rownum <= 10;
  8  fetch v_cur into emp_rec;
  9  DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
 10  :myref := v_cur;
 11  end;
 12  begin
 13  for i in 1..5 loop
 14  x;
 15  end loop;
 16  end;
 17  /
SMITH
SMITH
SMITH
SMITH
SMITH

PL/SQL procedure successfully completed.

SQL> print myref

     EMPNO ENAME      JOB              MGR HIREDATE         SAL       COMM
---------- ---------- --------- ---------- --------- ---------- ----------
    DEPTNO
----------
      7499 ALLEN      SALESMAN        7698 20-FEB-81       1600        300
        30

      7521 WARD       SALESMAN        7698 22-FEB-81       1250        500
        30

      7566 JONES      MANAGER         7839 02-APR-81       2975
        20

      7654 MARTIN     SALESMAN        7698 28-SEP-81       1250       1400
        30

      7698 BLAKE      MANAGER         7839 01-MAY-81       2850
        30

      7782 CLARK      MANAGER         7839 09-JUN-81       2450
        10

      7788 SCOTT      ANALYST         7566 19-APR-87       3000
        20

      7839 KING       PRESIDENT            17-NOV-81       5000
        10

      7844 TURNER     SALESMAN        7698 08-SEP-81       1500          0
        30


9 rows selected.

SQL> 

What does this prove to you ? Was the cursor closed after the RETURN happened ? :)) Even if the cursor variable was available at the SQL*Plus prompt, the cursor should have been closed (as per your theory). So, when you print the value of cursor variable, you should'nt get anything (infact you should get an error, because the handle of cursor that was set in the cursor variable, has been closed. Refer the code below, where we would get the error, if we close the cursor explicitly)

Now, let us close the cursor explicitly :

SQL> set serveroutput on size 1000000
SQL> variable myref refcursor
SQL> DECLARE
  2  TYPE emp_cur_t is REF CURSOR return SCOTT.EMP%ROWTYPE;
  3  v_cur      emp_cur_t;
  4  emp_rec     scott.emp%rowtype;
  5  procedure x is
  6  begin
  7  open v_cur for select * from scott.emp where rownum <= 10;
  8  fetch v_cur into emp_rec;
  9  DBMS_OUTPUT.PUT_LINE(emp_rec.ename);
 10  :myref := v_cur;
 11  close v_cur; -- CLOSED EXPLICITLY
 12  end;
 13  begin
 14  for i in 1..5 loop
 15  x;
 16  end loop;
 17  end;
 18  /
SMITH
SMITH
SMITH
SMITH
SMITH

PL/SQL procedure successfully completed.

SQL> print myref
ERROR:
ORA-24338: statement handle not executed


SP2-0625: Error printing variable "myref"
SQL> 

 

Hi Tom,

Sorry for not being explicit.

I was trying to mention that if we do a close within the procedure (where we open the cursor), the cursor actually is closed. But, If we don't put a close in the procedure, the cursor is not closed (even though the procedure has finished).
Cursor is actually defined and opened in the procedure, so when the procedure returns the control to the PL/SQL block, and then to SQL*Plus, you would expect the cursor to be closed. But that does'nt happen.
Also, if you could please let me know why do I get the other 9 records from the table, when I just do one fetch (5 times) ?

that is a REF CURSOR only.  

in your first example "C", a local cursor to that anonymous block is *closed*

in your second example, :myref, a cursor handle owned by sqlplus, is closed only when

a) you close it in plsql before giving it back to sqlplus
b) sqlplus hits "end of fetch" and closes it.

cursor is NOT defined in plsql in your second example:

SQL> variable myref refcursor

it was clearly defined in SQLPlus -- sqlplus owned that cursor.  You would not expect the cursor to be closed -- else there would be NO WAY to return a result set from a stored procedure.

I don't know what you mean by that last paragraph at all???  You fetched a row in plsql -- sqlplus fetched the rest?   

And ... in the second PL/SQL, you can see that if I do the print (cursor variable), I cannot print it. Which proves that the cursor is not opened. Whereas in case of first PL/SQL, it prints the other records from the cursor, even after the PL/SQL block has executed. I would assume, that since the PLSQL has finished, the cursor should not be open/available for print. I can be sure that it is showing records from the cursor, because it shows only the 10 records that I selected while defining the cursor.

I am just trying to understand what is happening here. Please excuse my inexperience :)

I am a bit confused here. What has happend when we assign a local declared ref cursor to a global cursor i,e
:myref := v_cur;

If they both represent different cursor then surely closing the local cursor v_cur should not have any impact on the global one. Hence we should be able to print the values from the global cursor at the SQL prompt.

Have I got something wrong here ?

Hi Tom,

Here is the query that I ran on my database as the developer complained about getting ORA-1000, but he is fixing his code and closing the cursors.

But, the results of the queries are so different, can you explain why...

select sum(a.value), b.name
from v$sesstat a, v$statname b
where a.statistic# = b.statistic# and b.name = 'opened cursors current'
group by b.name
union all
select count(*), 'v$open_cursor' from v$open_cursor
;
SUM(A.VALUE) NAME
------------ ----------------------------------------------- 5081 opened cursors current
53 v$open_cursor

OPEN_CURSOR is set at 700.

Tom,

How can i know how much memory is used by each cursor.

Thanks.

We have a typical situation here, wherein the application is throwing TOO MANY CURSORS. We know that we have to fix the application, but in the interim we would like to increase the cursors open to 2000. But, this is the feedback we got :

"Each open cursor consume 2 to 5 MB of memory, depend upon execution work. Setting 2000 cursor means we are reserving memory of 4000MB. You system may crash at some point. We can set the limit to 2000, provided the server can handle this much of memory."

I know this is not true, but how to really determine how much each cursors is taking space in memory.

Hi Tom,
Could you please explain why I see cursors in the V$OPEN_CURSOR view and in v$sesstat even though they have been explicitly closed in the code.
With each call to this function the number of open cursors increased and leads to ORA-01000 error.
Here is the part of the code used for cursor handling.

EXEC SQL PREPARE S FROM :gSQLStatement;
EXEC SQL DECLARE C CURSOR FOR S;
EXEC SQL OPEN C;
.
.
.
EXEC SQL DESCRIBE SELECT LIST FOR S INTO selda;
.
.
.
EXEC SQL FOR :array_size FETCH C USING DESCRIPTOR selda;
.
.
.
sqlclu(selda);
EXEC SQL CLOSE C;
Thanks,
Swain

Hi, Tom,

I found that your query:

select a.value, b.name
from v$mystat a, v$statname b
where a.statistic# = b.statistic#
and b.name = 'opened cursors current'

still returns a result that includes 'cached cursors' that has been closed.

Here are my test procs and results - should be general enough for any other oracle dbs:

SQL> CREATE OR REPLACE    PROCEDURE OPENCursorTest1
  2      IS
  3  
  4       CURSOR Test_cur
  5       IS
  6    SELECT * FROM global_name
  7    ;
  8  
  9   V_NUM_OPENCURSOR NUMBER;
 10  
 11      BEGIN
 12  
 13   OPEN Test_cur;
 14   CLOSE Test_cur;
 15  
 16   IF Test_cur%ISOPEN THEN
 17  
 18           select a.value INTO V_NUM_OPENCURSOR
 19           from v$mystat a, v$statname b
 20    where a.statistic# = b.statistic#
 21                and b.name = 'opened cursors current';
 22  
 23          DBMS_OUTPUT.PUT_LINE('Test_cur IS OPEN AND NUM OPEN CURSORS ='||TO_CHAR(V_NUM_OPENCURSO
R));
 24    CLOSE Test_cur;
 25          ELSE
 26  
 27           select a.value INTO V_NUM_OPENCURSOR
 28               from v$mystat a, v$statname b
 29              where a.statistic# = b.statistic#
 30                and b.name = 'opened cursors current';
 31          DBMS_OUTPUT.PUT_LINE('Test_cur IS CLOSED AND NUM OPEN CURSORS 
 32  ='||TO_CHAR(V_NUM_OPENCURSOR));
 33  
 34   END IF;
 35  
 36  
 37      END OPENCursorTest1;
 38  /

Procedure created.

SQL> 
SQL> show error;
No errors.

SQL> 
SQL> exec OPENCursorTest1
Test_cur IS CLOSED AND NUM OPEN CURSORS
=3

PL/SQL procedure successfully completed.

SQL> 
SQL> CREATE OR REPLACE    PROCEDURE OPENCursorTest2
  2      IS
  3  
  4       CURSOR Test_cur
  5       IS
  6    SELECT * FROM global_name
  7    ;
  8  
  9   V_NUM_OPENCURSOR NUMBER;
 10  
 11      BEGIN
 12  
 13   OPEN Test_cur;
 14   --CLOSE Test_cur;
 15  
 16   IF Test_cur%ISOPEN THEN
 17  
 18           select a.value INTO V_NUM_OPENCURSOR
 19           from v$mystat a, v$statname b
 20    where a.statistic# = b.statistic#
 21                and b.name = 'opened cursors current';
 22  
 23          DBMS_OUTPUT.PUT_LINE('Test_cur IS OPEN AND NUM OPEN CURSORS ='||TO_CHAR(V_NUM_OPENCURSO
R));
 24    CLOSE Test_cur;
 25          ELSE
 26  
 27           select a.value INTO V_NUM_OPENCURSOR
 28               from v$mystat a, v$statname b
 29              where a.statistic# = b.statistic#
 30                and b.name = 'opened cursors current';
 31          DBMS_OUTPUT.PUT_LINE('Test_cur IS CLOSED AND NUM OPEN CURSORS 
 32  ='||TO_CHAR(V_NUM_OPENCURSOR));
 33  
 34   END IF;
 35  
 36  
 37      END OPENCursorTest2;
 38  /

Procedure created.

SQL> 
SQL> show error;
No errors.

SQL> 
SQL> exec OPENCursorTest2
Test_cur IS OPEN AND NUM OPEN CURSORS =3

PL/SQL procedure successfully completed.

SQL> 

The SQL_TEXTs for the two of the three open cursors are the same for the two cases:
SID    SQL_TEXT

51    SELECT A.VALUE FROM V$MYSTAT A, V$STATNAME B WHERE A.STATIST
51    SELECT * FROM GLOBAL_NAME 

The question is now back to Connie's:
How do we know the "real" open cursors that developers forget to explicitly close in the app codes?

Thanks Tom!

Roger
 

Thanks for having this out here

my process does not have any Open Cursor or Open REF Cursor. there is a call to DBMS_XML.get_xml and it returns out put of it in CLOB to Java program. when I am load testing the app for 300 simultaneous users, I get ora-01000 error. Database version is 10gR1 and parameter is set to 300.

since we are not opening any cursors explictly, why would we get this error ??

I have a situation where I believe my app is leaking cursors, but it would help me a lot to see the SQL text of only the open (as reported by the session_open_cursors function) cursors.

Is this possible?

Thanks.

Sorry, I just realised that is a function we wrote. It just returns the value for the statistic 'opened cursors current'.

As you say above ...

"V$OPEN_CURSOR represents a set of cached cursors the server
has for you. The query [snip] gives you the actual number of truely open cursors."

What I am trying to get is the text for truly open cursors.

I am facing a problem in our production. There are some cursors which are not getting closed.

Using following query I am able to get the count of open cursors:

SELECT a.SID, a.VALUE, b.NAME
FROM v$sesstat a, v$statname b, v$session s
WHERE a.statistic# = b.statistic#
AND a.statistic# = 3
AND a.SID = s.SID
AND a.VALUE <> 0;

But I am not able to get the sql queries of those open cursors anyhow.

Can you please help me?

Thanks in Advance.

Hi Tom

How does open_cursor and session_cached_cursor relted to. What is the difference between these two.

In the top of this article you had mentioned
<<V$OPEN_CURSOR represents a set of cached cursors the server
has for you. >>

so what is session_cached_cursor will do. a bit confused..

Thanks & Rgds
Anil

Could you please let me know how to forcibly close the open cursors that are listed in the table V$OPEN_CURSOR

Hi tom,

Could you please explain the reuslts.
First example Database 9.2.0.8 ( session_cached_cursors=0)
Second example 10.2.0.3 session_cached_cursors=20
Third example 10.2.0.3 session_cached_cursors=0

All examples i just connected to the database:

1)
JServer Release 9.2.0.8.0 - Production

select * from V$MYSTAT where rownum < 2;

SID STATISTIC# VALUE
---------- ---------- ----------
2617 0 1

Transcurrido: 00:00:00.00
variable x refcursor
exec open :x for select * from dual;

Procedimiento PL/SQL terminado correctamente.

Transcurrido: 00:00:00.00
select name, value
from v$mystat a, v$statname b 2
where a.statistic# = b.statistic#
and b.name = 3 4 'opened cursors current';

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 4

Transcurrido: 00:00:00.01
select sql_text from V$OPEN_CURSOR where sid=2617;

SQL_TEXT
------------------------------------------------------------
select sql_text from V$OPEN_CURSOR where sid=2617
SELECT * FROM DUAL

Transcurrido: 00:00:00.05
select name, value
from v$mystat a, v$statname b 2
where a.statistic# = b.statistic#
and b.name = 3 4 'opened cursors current';

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 4

Transcurrido: 00:00:00.00

EXAMPLE 2)
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

select * from V$MYSTAT where rownum < 2;

SID STATISTIC# VALUE
---------- ---------- ----------
1019 0 1

variable x refcursor
exec open :x for select * from dual;

Procedimiento PL/SQL terminado correctamente.

select name, value
from v$mystat a, v$statname b 2
where a.statistic# = b.statistic#
and b.name = 3 4 'opened cursors current';

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 2

select sql_text from V$OPEN_CURSOR where sid=1019;

SQL_TEXT
------------------------------------------------------------
select f.file#, f.block#, f.ts#, f.length from fet$ f, ts$ t
SELECT DECODE('A','A','1','2') FROM DUAL
SELECT USER FROM DUAL
BEGIN open :x for select * from dual; END;
SELECT * FROM DUAL
select * from V$MYSTAT where rownum < 2
select name, value from v$mystat a, v$statname b where a.sta
BEGIN DBMS_APPLICATION_INFO.SET_MODULE(:1,NULL); END;
SELECT CHAR_VALUE FROM SYSTEM.PRODUCT_PRIVS WHERE (UPPER('
DECLARE v_ID NUMBER; v_user VARCHAR2(15); v_program
SELECT ATTRIBUTE,SCOPE,NUMERIC_VALUE,CHAR_VALUE,DATE_VALUE F
select sql_text from V$OPEN_CURSOR where sid=1019

12 filas seleccionadas.

select name, value
from v$mystat a, v$statname b 2
where a.statistic# = b.statistic#
and b.name = 3 4 'opened cursors current';

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 2

Third example:

Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options

alter session set session_cached_cursors=0;

Sesión modificada.

select * from V$MYSTAT where rownum < 2;

SID STATISTIC# VALUE
---------- ---------- ----------
975 0 1

variable x refcursor
exec open :x for select * from dual;

Procedimiento PL/SQL terminado correctamente.

select name, value
from v$mystat a, v$statname b 2
where a.statistic# = b.statistic#
and b.name = 3 4 'opened cursors current';

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 2

select sql_text from V$OPEN_CURSOR where sid=975;

SQL_TEXT
------------------------------------------------------------
update sys.col_usage$ set equality_preds = equality_pre
SELECT * FROM DUAL
select sql_text from V$OPEN_CURSOR where sid=975

select name, value
from v$mystat a, v$statname b 2
where a.statistic# = b.statistic#
and b.name = 3 4 'opened cursors current';

NAME VALUE
---------------------------------------------------------------- ----------
opened cursors current 2


My questions are :

1) Does oracle treat in a different way opened cursors on different versions ¿?
2) Why does oracle detect 4 opened cursors on oracle 9 ¿?
It makes more sense to me the behaviour of oracle 10.

Sir,

I am trying to understand about the 'open_cursors' and 'session_cached_cursors' and to understand them clearly - I want to understand the cursors how they are managed by the client,pl/sql in the above example ..

I have a 3GL application which calls a pl/sql procedure

Any 3GL application
============================
declare a cursor C- CLIENT CURSOR
main()
C=call_plsql_procedure();
end;
==========================

see below the pl/sql procedure

create or replace call_plsql_procedure
as
declare a ref_cursor R - EXPLICIT cursor - 1
curor d is select emp_no from emp; -EXPLICIT CURSOR -2 (Pl/sql)
begin
open d;
fetch d into x
close c ;
update emp set emp_no=emp_no+20 -- IMPLICIT 1
select x from y into z --IMPLICIT 2

end;


Here
Client Cursor - 1
Pl/sql Explicit cursors -2
Pl/sql Implicit cursors - 3

Could you please explain the sequence of the cursors caching/closing above by pl/sql and the client ?

Thanks for your time

Hard Parse :
cursor creation,allocate some memory in uga/pga to store the information about SQL,variables etc ,syntax,semantic check,bindng,create a LINK to SGA ,Optimizer will develop the best execution plan and store in SGA .
Soft Parse:
again we do -cursor creation,allocate some memory in uga/pga to store the information about SQL,variables etc ,syntax,semantic check,bindng,HERE WE FIND SAME SQL in SGA and so NO NEED to create an execution plan - again we have to create a link to that SQL ... so everything is same as hard parse excpet that 'execution plan' is not again built in this case.

Softer Soft parse :(curose caching by pl/sql or any 3gl)
nothing we do here ...becuase memory already exists,link to SGA exists , exection plan is there ... just 'execute' it

Is my understaning correct Tom ? Thank you

Sir,
Let us say - it is 'dedicated connection'
When the client(3gl) submits a SQL statement for first time - It first creates a pointer an handle -(cursor) on its side ,associates the SQL with it - allocates 'sql area' to hold the information about that sql in pga/uga and make a parse call to server - the client is done now ...

Server parses it , get the execution plan and store them in SGA , now there is a pointer to the SQL in the SGA . so there are 'two' pointers now - one is from client to PGA/uga and second is from PGA/UGA to SGA


Lets say the statement is executed 3 times (open, close, open-close,open -close) - then Server may decide to 'cache' it - in this case -

1.Server 'caches' the cursor - means what happens internally? I mean - here the cursor is closed but the pointer from pga/uga still points to the SQL in SGA though the cursor is closed ....Correct ? but the SQL can be aged out from shared pool as per LRU logic.

2.Will the Client (3gl) also 'cache' cursor as Server does above - (or) does it depend on client side programming ? How it actually 'caches' internally ?

3.Lets say in the above case - the SQL statement is from pl/sql block -
Pl/SQl 'caches' the cursor though the cursor is closed - means what happens ? Here the cursor is still 'open' so everything remains same as if it is open - correct ?
Here the SQL can NOT be aged out from shared pool as the cursor is still open.

Sir,
Let us say - it is 'dedicated connection'
When the client(3gl) submits a SQL statement for first time - It first creates a pointer an
handle -(cursor) on its side ,associates the SQL with it - allocates 'sql area' to hold the
information about that sql in pga/uga and make a parse call to server - the client is done now ...

Server parses it , get the execution plan and store them in SGA , now there is a pointer to the SQL
in the SGA . so there are 'two' pointers now - one is from client to PGA/uga and second is from
PGA/UGA to SGA


Lets say the statement is executed 3 times (open, close, open-close,open -close) - then Server may
decide to 'cache' it - in this case -

1.Server 'caches' the cursor - means what happens internally? I mean - here the cursor is closed
but the pointer from pga/uga still points to the SQL in SGA though the cursor is closed
....Correct ? but the SQL can be aged out from shared pool as per LRU logic.

2.Will the Client (3gl) also 'cache' cursor as Server does above - (or) does it depend on client
side programming ? How it actually 'caches' internally ?

3.Lets say in the above case - the SQL statement is from pl/sql block -
Pl/SQl 'caches' the cursor though the cursor is closed - means what happens ? Here the cursor is
still 'open' so everything remains same as if it is open - correct ?
Here the SQL can NOT be aged out from shared pool as the cursor is still open.

Many thanks Tom for your excellent explanation -

Sorry but one more doubt

there is a slight difference between any 3GL cached cursors and PL/SQL cached cursors ?

I was under impression if PL/SQL caches a curosr - the SQL can not be aged out(removed) from shared pool but for 3GL(client) cursors -Oracle will age out the SQL though the cursor is cached ?

Is it wrong assumption ?

Sorry , I was wrong in saying '3gl cached cursors' - I meant to say any 'API' client which interacts with Database .

I want to know if there is difference between the caching mechanism between an API client caching and Pl/SQL caching .

PL/SQL will automatically 'caches' if the cursor is opened few times (say 3 times) but the API client - it depends on the API and user programming (the API code)- the client will decide whether to cache or not ,…

Is it correct ?

Many Many thanks Tom for the excellent explanation ..
I am learning a lot of things *clearly* from you .

Sir , I have one question for you

Why do you revise and update your book 'Effective Oracle by design' ?

I have your book first edition (Expert Oracle architecture 9i and 10g) and got the alpha version of your second edition (Expert Oracle architecture 9i,10g and 11g ) and compared both of them - I may be wrong - what I have understood is you added just few 11g features of those concepts to the same old book .
But the one who ** understands clearly your first edition** can easily understands 11g features just by going through the concepts guide or any other book
Because that first book has basic, solid and great explanations for every concept- I don’t see very much benefit for going for second edition of the book while I have the first edition .

What thought came to mind is - if you rewrite the "Effective Oracle by design for 11g" - that would *rock and roll*..
Can we expect this in future ?

Sir, Please don’t mind - this is purely my personal opinion and this is what I felt in my mind.

Hi Tom,

In case I declare a cursor in my package and I dont close it, what happens? Is it closed after some time automatically?

If not, I would like to know the way we can close the cursors that are open which I see through your query:

select sum(a.value), b.name
      from v$sesstat a, v$statname b
     where a.statistic# = b.statistic#
       and b.name = 'opened cursors current'
     group by b.name

Does the same logic applies for the CFL (Cursor For-Loop) also? as we dont fetch there, I hope.

Please suggest.

ops$tkyte%ORA11GR2> create or replace package my_pkg
  2  as
  3          procedure p;
  4  end;
  5  /

Package created.

ops$tkyte%ORA11GR2> 
ops$tkyte%ORA11GR2> create or replace package body my_pkg
  2  as
  3  
  4          cursor always_closed_for_me is select * from all_users;
  5  
  6          cursor I_must_close_this is select * from all_users;
  7  
  8  procedure p
  9  is
 10          cursor this_will_be_closed_for_me is select * from all_users;
 11          l_rec all_users%rowtype;
 12  begin
 13          for x in (select * from all_users THIS_IS_ALWAYS_CLOSED_FOR_ME)
 14          loop
 15                  null;
 16      end loop;
 17  
 18          for y in ALWAYS_CLOSED_FOR_ME
 19          loop
 20                  null;
 21          end loop;
 22  
 23          open I_must_close_this;
 24          loop
 25                  fetch I_must_close_this into l_rec;
 26                  exit when I_must_close_this%notfound;
 27          end loop;
 28          close I_must_close_this;
 29  
 30          open this_will_be_closed_for_me;
 31          loop
 32                  fetch this_will_be_closed_for_me into l_rec;
 33                  exit when this_will_be_closed_for_me%notfound;
 34          end loop;
 35          /* sloppy but I don't have to close it */
 36  end;
 37  
 38  end;
 39  /

Package body created.

Dear Sir,


Nice example!!!


for x in (select * from all_users THIS_IS_ALWAYS_CLOSED_FOR_ME) ------- implicit cursor
14 loop
15 null;
16 end loop;
17
18 for y in ALWAYS_CLOSED_FOR_ME ----- declare in package body
19 loop
20 null;
21 end loop;


(1) so does it mean that explicit or imlicit cursor when loop through FOR LOOP will close implicitly i.e. automatically?


(2) Also when cursor is close implicitly OR explicitly does oracle still keep it open under the hood in PL/SQL cache ?


Thanks

Hi Tom, (Oracle 10.1.0.5, open_cursors=300, session_cached_cursors=0, cursor_sharing=EXACT)

I stumbled upon something really strange today. It relates to open cursors. I was trying to determine how many open cursors were being used by each user (across multiple sessions), so I used the following SQL:

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

This seemed to work quite well. I tested it by logging in and opening some cursors and closing some cursors etc. This seemed to work as expected.

However, while running this in my production environment, there was suddenly a single session that jumped to the top of the list with a number for total_cur that was so big, it just showed ############ as a value. So I quickly changed the query to try and get the total open cursors for the single user:

select a.value total_cur,
s.username, s.machine, s.program
from v$sesstat a, v$statname b, v$session s 
where a.statistic# = b.statistic#  and s.sid=a.sid
and b.name = 'opened cursors current'
AND S.USERNAME = 'HDM4' 

Using toad to view the output, I got a result of 1.84467440737096E19

And now, I am totally confused. I don't get it... How is this possible? There was only 1 session for the given schema (schema named 'HDM4'). A bit more info:
a) I also noticed that this was a dbms_job process.
b) The session seemed to be connected for perhaps 5-10 minutes.

Sanity check: is my SQL wrong?
How is it possible that the session opens more than open_cursors? And, is this query really indicating "currently open cursors"?

Thanks Tom

select * 
  from v$sesstat
where sid in (select sid from v$session where username = 'HDM4')
and statistic# = (select statistic# from v$statname where name = 'opened cursors current' )

Hi Tom,
have been asking questions here since last 8 yrs, some one above made a comment that you should write a new version of effective design for 11g. My opinion is you should write a book which tells more details..which help the mid-level experienced folks in oracle ( have 4-8 yrs of experience) mostly in consultiing work on oracle. reasons are :
1)There are so many un-answered queries in our mind - they never get answered - partly we don;t have source of information as we are not oracle employees - one such exmaple is - how to read AWR reports and make sense out of it. there are few things we know, we learn from asktom or we learn from some other books .
2) how to really approach a complex performance issue...

sometime i do feel that you just oversimplify the answers you gave here...I do unserstand that you speak from your experience and we will probably never reach to that level but there is fair amount of queroisity to know the things - how things actually work in oracle..to become more comptent than what we are.

recently i read a book "troubleshooting oracle performance" - something like that may be...if comes from you as well -would be a great assist .

Regards
Ajeet

Hi Tom!

Are there any way to know if a cursor still have any row to be fetched? I need to kill some inactive sessions with opened cursors, but I don´t know if one of these cursors will be fetched by the user later.

Thanks!
Victor