DBAsupport.com Forums - Powered by vBulletin
Results 1 to 9 of 9

Thread: V$MYSTAT Question

  1. #1
    Join Date
    Mar 2002
    Posts
    171
    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.


  2. #2
    Join Date
    Dec 2000
    Location
    Ljubljana, Slovenia
    Posts
    4,439
    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?

  3. #3
    Join Date
    Mar 2002
    Posts
    171
    Thanks. I am using 8i. Is there any necessicity of this parameter in 8i. If so what is the equivalent?

  4. #4
    Join Date
    Mar 2002
    Posts
    171
    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.

  5. #5
    I really do not understand WHY do you need this?
    ovidius over!

  6. #6
    Join Date
    Jun 2000
    Location
    Madrid, Spain
    Posts
    7,447
    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

  7. #7
    Join Date
    Mar 2002
    Posts
    171
    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.

  8. #8
    Join Date
    Mar 2002
    Posts
    171
    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.

  9. #9
    Join Date
    Mar 2002
    Posts
    171
    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
  •  


Click Here to Expand Forum to Full Width