V$MYSTAT Question

Can someone please explain me what does STATISTICS# and VALUE mean in the VIEW V$MYSTAT.

And please tell me are there any adverse effects by setting 'close_cached_open_cursors' = TRUE.

Appreciate your time and patience.

Join V$MYSTAT data with V$STATNAME (V$MYTAT.STATISTIC# = V$STATNAME.STATISTIC#) and you'll get meaningfull statistic names.

CLOSE_CACHED_OPEN_CURSOR is an obsolete parameter as of 8i. Which release are you using?

Thanks. I am using 8i. Is there any necessicity of this parameter in 8i. If so what is the equivalent?

The reason why I am asking this is:

I have following PL/SQL block:

declare
cursor c is select empno from emp;
crs number;
n number;
begin
select value into crs from v$mystat where statistic# = 3;
dbms_output.put_line('curr.op.cursors: ' || crs);
open c;
fetch c into n;
close c;
select value into crs from v$mystat where statistic# = 3;
dbms_output.put_line('curr.op.cursors: ' || crs);
end;

The output is as follows:
curr.op.cursors: 2
curr.op.cursors: 4




So despite the fact that I've closed the cursor, its still showing up in the count. And I understand that -- when you execute an SQL statement, there is a shared SQL area in the SGA, an instantiation in the PGA, and a statement handle in the client environment. Each of these is called a "cursor" in various contexts. In closing the cursor, the statement handles in my client environment (PL/SQL) but the corresponding instantiation in the PGA remains cached, which is what statistic# 3 is counting.

The solution I thought was to set close_cached_open_cursors = TRUE .

If this parameter is obselete in 8i, how do we control this?

Hope I'm clear.

I really do not understand WHY do you need this?

that is a feature not a bug or anything, what oracle does is it will cache the cursor even you close it to prevent soft parses

Quote:

---

Originally posted by ovidius
I really do not understand WHY do you need this?

---

So that I can close the cached cursor that remains despite the fact I've closed the cursor explicitly. Observe the DBMS_OUTPUTS of my procedure and you'll see what I am saying.

Quote:

---

Originally posted by pando
that is a feature not a bug or anything, what oracle does is it will cache the cursor even you close it to prevent soft parses

---

Could you please explain a little more. I didn't follow.

Any more pointers on this?

Since in Oracle 7, by setting the close_cached_open_cursors = TRUE, I can clear the memory, how do we incorporate the same in 8.x?