Will someone please post an example of what a dynamic
SQL statement using parameter markers looks like. I
know they are question marks and the question marks
are somehow used for substitution, but I'm having a
hard time visualizing what the statement looks like.
Thanks.
--- [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
>
__________________________________________________
Do You Yahoo!?
Get email at your own domain with Yahoo! Mail.
http://personal.mail.yahoo.com/
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
