Hi, Bill
Please let me throw two ideas I'm sure you are already aware of:
1- DB2 caches dymanic SQL statements. The great advantage of those
parameter markers is that they help the optimizer to recognize repeating
statements even with different data.
2- If you are going to modify the application then if it's like many
other apps, a few SQL statements will be used all the time and many
other will appear from time to time. Concentrate only in the most
popular to achieve a high benefit with less work and risk.
Good luck
--
Juan Lanus
TECNOSOL
[EMAIL PROTECTED] wrote:
> Hi,
>
> First, our environment is UDB v6.1 (fixpack 5) on AIX 4.3.3.
>
> We have an application that just went into production that was written
> using purely dynamic SQL (i.e. no parameter markers, just straight dynamic
> SQL). There was no specific reason why it was written this way other than
> the fact that the developer didn't know what he was doing (he thought he
> WAS writing static SQL).
>
> 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.
>
> We're really not experiencing any problems because of this (yet) because
> the workload that this particular application is incurring on the AIX
> system is not extremely high. But the hope is that this application will
> grow by orders of magnitude over time, and my fear is that at some point
> the system CPU utilization will become stressed. I'd like to get some hard
> numbers to present to the application folks to convince them that they
> should address this issue now rather than later. Just because things
> appear to be rosy now doesn't mean that we should ignore the potential for
> future problems.
>
> What I'd like to be able to find would be some hard data on exactly how
> much more expensive this pure dynamic SQL is in terms of CPU utilization as
> opposed to static SQL (or dynamic SQL with parameter markers). I'm unable
> to find any good information in the snapshot monitors that could provide
> this level of information. I can get a breakdown of User CPU time used vs.
> System CPU time used for application ID's when I do a "get snapshot for all
> applications", but I cannot determine if this can provide me any
> information about how much overhead we are really incurring because of
> dynamic SQL.
>
> Can anybody point me in the right direction to get this level of
> information, and how I can relate this to actual CPU time used for dynamic
> SQL overhead. If not hard numbers, then is there a rule of thumb of how
> much additional CPU time is consumed for dynamic vs. static SQL execution?
>
> Any information will be appreciated.
>
> Thanks,
>
> Bill Gallagher, DBA
> Phoenix Home Life
> Enfield, CT 06083
>
>
>
> =====
> 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
