-
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?
Jurij Modic
ASCII a stupid question, get a stupid ANSI
24 hours in a day .... 24 beer in a case .... coincidence?
-
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?
ovidius over!
-
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
-
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.
-
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?
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|