Re: Same statement in SQL plan cache multiple times??

[CRPence <CRPbottle@xxxxxxxxx>]

  Code the statement with the parameter markers since it appears 
the original statement is being compared [for a matching statement 
in the cache] rather than comparing the statement which had since 
been converted to have parameter markers.

Regards, Chuck

Charles Wilt wrote:
> The statements in question are being issues as only as part of an
>  interactive RPG program.
>
> They are being prepared dynamically (yes I know this is open to
> SQL injection), with literal comparison values.  However, in the
> SQL plan cache, the literals were replaced by parameter makers.
> But given that many of the statement have a Total Times Run of
> greater than 0, and there's not enough duplicates for every
> dynamic run to result in a new entry in the plan cache, I'm
> hoping somebody might know the circumstances in which a duplicate
> entry is added to the plan cache.
>
>
> On Wed, Apr 28, 2010 at 11:08 AM, CRPence wrote:
> > Charles Wilt wrote:
> > > Trying to do some performance tuning, noticed that the SQL
> > > plan cache often has the same statement shown multiple
> > > times...which of course throws off the "Total Times Run".
> >
> > I guess that would be the total times the [same] statement has 
> > been run _with the associated plan_ then.? :-)
> >
> > > Anybody know why this happens?  or some way to work around
> > > it? (he says as he looks at Elvis ;-)
> > Get all invocations of that statement to implement with the
> > same plan.? ;-)
> >
> > Seriously though... I seem to recall seeing that, for example, 
> > the /same/ statement run interactive [*firstio implicit 
> > optimization] versus batch [with *allio implicit optimization]
> > would appear separately; the statements /explained/ with that
> > difference. For that scenario, adding the OPTIMIZE FOR ALL ROWS
> > clause on the SELECT request would make the one statement
> > appear only once, after having cleared the cache.