Re: SQL with High version count...

Hi from Paris

if I can remember, even when issuing the same sql with bind variables, but with differents sql cursors, Oracle does a "soft parse", which mean it was able to reuse explain plan, etc... but had to create a new sql cursor... have a statspack report, and compare statistics "hard parse", "parse count" (difference is soft parse) and "opened cursors cumulative"... I suppose the two last will be similar

If this is the case, you application should try to use a pool of session, repeaditely executing the same sql cursor and only change bind variables (ocibindbyname)... or use "session_cached_cursors" or "cursor_space_for_time" (have a read of these parameters before changing one)

this may be a totally wrong information, but if you confirm "parse count" and "opened cursors" are closed, and the application is not re-using the same sql cursor, this may explain the root cause....

-> note that this behaviour, even if it is a contention point, is far less dangerous than doing "hard parse" (when not using bind variables for example)...

"ibodogan" <idogan_tech_at_yahoo.com> a écrit dans le message de news: 1105993262.266225.53190_at_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:30:22 CST