Re: High Version count in V$SQLAREA

From: Anurag Varma <avdbi_at_hotmail.com>
Date: 8 Jul 2003 09:52:41 -0700
Message-ID: <7171ca2d.0307080852.7fa3c546@posting.google.com>

srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0307072253.60403427_at_posting.google.com>...
> Hi yong,
> Thanks for the reply.
> I dont think the versions are created due to change in bind lengths as
> they are issued by the same application (same part).
---snip---
> regards
> Srivenu

Oh yes they are created due to significant changes in bind length. Try this example and see it to believe it (I tested it on Oracle version 9.2.0.3):

---

var h varchar2(10)
begin :h := 'abcd'; end;
/

select /* varma */ :h from dual;

select sql_text,first_load_time,parsing_user_id,version_count from v$sqlarea
where sql_text like '%varma%'
/

var h varchar2(1000)
begin :h := rpad('abcd',990,'x'); end;
/

select /* varma */ :h from dual;

select sql_text,first_load_time,parsing_user_id,version_count from v$sqlarea
where sql_text like '%varma%'
/

var h varchar2(4000)
begin :h := rpad('abcd',3990,'x'); end;
/

select /* varma */ :h from dual;

select sql_text,first_load_time,parsing_user_id,version_count from v$sqlarea
where sql_text like '%varma%'
/

---

You should see 3 version counts in the last select.

Although a very high version count could be a result of a BUG too.

Anurag Received on Tue Jul 08 2003 - 11:52:41 CDT