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
