SQL with High version count...

From: ibodogan <idogan_tech_at_yahoo.com>
Date: 17 Jan 2005 13:03:39 -0800
Message-ID: <1105993262.266225.53190@f14g2000cwb.googlegroups.com>

I have an alert-system that generates the following report about SQLs with high number of versions:

VER : version count
LVER: loaded versions
UOP : users opening
EXEC: executions
PARS: parses
LOAD: loads
HVCR: high version count reason

TOP 10 SQL WITH HIGHEST VERSION COUNT (01/17/05 14:55) ***

HASH VALUE ADDRESS VER LVER UOP EXEC PARS LOAD SQL TEXT

~~~~~~~~~ ~~~~~~~~~~~~~~~~~ ~~~~ ~~~~ ~~~~ ~~~~~~~ ~~~~~~~ ~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

113822022 0700000005CF7E00 158 1 10 13116 13115 315

INSERT INTO DEAnswer ( ElementID, CourseTryID, QuestionTry, AnsweredTime, Archetype, ShortAnswer, ShortCorrectAnswer, FullAnswer, Judgement )
VALUES (
:ph0, :ph1, :ph2, :ph3, :ph4, :ph5, :ph6, :ph7,:ph8 )

1004316362 0700000005C147D8 145 1 11 26974 24808 379 INSERT INTO DRAuditLog
( eventTime, perpUserID, eventString, victimID, description ) VALUES (
:ph0, :ph1, :ph2, :ph3, :ph4 )

1778100833 07000000056B5090 83 1 13 18716 18714 153

....
HASH VALUE ADDRESS MULTI VERSION REASONS

~~~~~~~~~ ~~~~~~~~~~~~~~~~~~~~
~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
113822022  0700000005CF7E00	BIND_MISMATCH,
1004316362 0700000005C147D8	BIND_MISMATCH,
1778100833 07000000056B5090	BIND_MISMATCH,

...

As seen in report, "INSERT INTO DEAnswer.." SQL has 158 different versions. Report also tells that the reason is BIND_MISMATCH (used V$SQL_SHARED_CURSOR to list the reasons)..but as seen in the report, the SQL is using bind variables (Actually apps don't use bind variables i set cursor_sharing to SIMILAR) so i couldn't figure out the root cause..

is this high version count caused by log string variables or something else?

thanks,
i.d. Received on Mon Jan 17 2005 - 15:03:39 CST