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.
Printable View
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
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 ovidius
I really do not understand WHY do you need this?
Could you please explain a little more. I didn't follow.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
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?