As mentionned in the previous notes, using parameter markers in the dynamic sql will
also address the problem.
DB2 will cache both the compiled statement and the string that made it in the cache.
As the 'marked' stmts are
compiled, their strings stored, the next execution request will find an identical
string and therefore used the
compiled version of the dynamic statement as if it was a static precompiled statement.
For statements that generate single row retrievals (cust. id=?, prod. id=?), this is
very efficient as the
compiler generates the same access plan whether you use a value or a marker.
If you're getting 70% hit ratio this is not bad but your objective should be 80-85%.
This would require a larger
cache. You should increase it in slices of 8 to 12 pages and monitor.
What you want is to snapshot monitor your cache and look a t the number of cache
lookups and the number of cache
inserts (this is what the ratio is built on).
The db2batch tool will also allow you to submit a series of sql statements and find
out what their prep time and
execution time were as well as what cpu time was used totally.
HTH, Pierre.
[EMAIL PROTECTED] wrote:
> Ian,
>
> Increasing the package cache size would just alleviate the symptoms, but
> not address the problem. The problem is that the application should have
> been written using static SQL. I can increase the package cache to improve
> the hit ratio, but as the volume of data and transactions for the
> application continue to grow, the package cache will have to grow
> accordingly as well. If the application can be rewritten using static SQL,
> we should be able to keep the package cache at a reasonably sized, fixed
> size. That's my goal, and I need to be able to convince the application
> folks that that's what they need to address now as opposed to later when
> the machine starts getting constrained by CPU/memory.
>
> Bill Gallagher, DBA
> Phoenix Home Life
> Enfield, CT 06083
>
>
> "Ian D. Bjorhovde"
> <[EMAIL PROTECTED]> To: [EMAIL PROTECTED]
> Sent by: cc:
> [EMAIL PROTECTED] Subject: Re: DB2EUG: Overhead
>for Dynamic SQL in UDB/AIX?
> a.best.com
>
>
> 03/21/01 04:00 AM
> Please respond to
> db2eug
>
>
>
> From: <[EMAIL PROTECTED]>
> To: <[EMAIL PROTECTED]>
> Sent: Tuesday, March 20, 2001 2:20 PM
> Subject: DB2EUG: Overhead for Dynamic SQL in UDB/AIX?
>
> > ...
> >
> > After two weeks of monitoring, I'm seeing that we're getting about a 70%
> > hit ratio in the Package Cache, which is actually better than I expected,
> > but nowhere near what I would expect had this been written using static
> SQL
> > or at least dynamic SQL with parameter markers. I would expect that we
> > could easily see 95%-98% hit ratio or better in the Package Cache if this
> > were the case.
>
> Have you tried tuning the package cache size (PCKCACHESZ in db cfg)?
> If not, increasing it should have some effect on the hit ratio.
>
> Is the DB2 optimizer smart enough to recognize dynamic SQL, parameterize
> it and store the modified statement in the package cache? Given all
> of the features of the optimizer, I would be surprised if this wasn't
> already a feature.
>
> =====
> To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
> For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
