Re: High Version count in V$SQLAREA

From: Anurag Varma <avdbi_at_hotmail.com>
Date: Fri, 11 Jul 2003 00:43:05 GMT
Message-ID: <sKnPa.5034$Av.1678@news02.roc.ny>

"Yong Huang" <yong321_at_yahoo.com> wrote in message news:b3cb12d6.0307100917.4b76c791_at_posting.google.com...
> srivenu_at_hotmail.com (srivenu) wrote in message news:<1a68177.0307092113.619f62a0_at_posting.google.com>...
> > I really appreciate everyone who has answered and especially yong.
> > I think, yong, you are right.
> > The high version count is due to Bind Mismatch.
> > I gave the following query and got the answer for my question.
> >
> > sys_at_OMPROD> l
> > 1 select substr
> ...
> > 25 from v$sql a,v$sql_shared_cursor b
> > 26 where a.address=b.kglhdpar
> > 27 and sql_text like '%DATE_ENTERED,NOTES_ID%'
> > 28* order by 2
> > sys_at_OMPROD> /
> >
> > Par Loa
> > First sing Reason For ded
> > Open Kept Users Users
> > Child Load User Cursor Not Vers
> > Vers Vers Ope Execu
> > SUBSTR(SQL_TEXT,1,25) Number Time id Being Shared ions
> > ions ions ning ting
> > ------------------------- ------ ---------- ----- --------------- ----
> > ---- ---- ----- -----
> > INSERT INTO ASAP.NOTES ( 0 2003-06-21 0 -Bind Metadata 0
> > 0 0 0 0
> > /02:00:34 Mismatch-
> >
> > INSERT INTO ASAP.NOTES ( 1 2003-06-21 0 -Bind Metadata 0
> > 0 0 0 0
> > /02:00:34 Mismatch-
> >
> ...
> >
> >
> > 32 rows selected.
> >
> > Now these statements are coming from an application.
> > Can someone tell me how i can rectify this ?
> > Thanks in advance

>

> Hi, Srivenu,

>

> Anurag suggests setting _sqlexec_progression_cost to 0. Can you try
> that?

>

> BTW, you didn't explain why you want to thank me more than others in
> this thread. All I did out of the thread is ask James Morle, and he
> replied that my interpretation of his book is correct and he strongly
> suspects bind threshold alone may explain why you have so many
> versions (before he sees your result from v$sql_shared_cursor).
>
> Yong Huang

The _sqlexec_progression_cost might not work in 8.1.7. Check metalink if that BUG is still present on your version. Anyway that BUG might not have to do anything with your case.

My guess would be to have the application call a sql package to do its insert. That might be easier said than done. However, I base my guess on the short test I posted. If I define the variable fixed (i.e. in all cases set var h varchar2(4000)) then the versions
did not increase. I guess oracle in that case preallocated the same amount of memory to the bind variables and hence the version number did not increase.
If this is true then different inserts in the application might be using different sets of memory allocation and oracle might be inferring its allocation based on the application (just a guess). If I remember reading correctly somewhere: Oracle is refining this bind memory allocation in its subsequent releases. It probably works like a bucket allocation. I guess there is a balance where further increase in size of bucket does not perform good overall.

Thus what I'm hinting towards is that probably there is no solution like setting some hidden parameter to alleviate this issue. You might need to start doing all (single) inserts/updates/deletes via a central package ... or probably standardize the way the application
does the inserts. Apart from that the solutions might be to upgrade or live with it (If it causes 4031 errors .. then increase the Shared pool area
and/or flush shared pool at a relatively idle time every other day/week/..).

Also: We still see version counts unto 20-30 (10 - 20 of such cases) on our current 9.2.0.3 version.

.. and yes talk to Oracle Support. They might have other/better ideas.

HTH Anurag Received on Thu Jul 10 2003 - 19:43:05 CDT