Juan,
Two very good points. I wouldn't have a problem with the application using
dynamic SQL with parameter markers to take better advantage of the caching.
But if they have to make changes to the application to convert the most
frequently executed SQL statements from purely dynamic SQL, they might as
well go all the way to static SQL as opposed to dynamic SQL with parameter
markers. From what they've told me about the application, they have no
specific need to use dynamic SQL at all. The only reason they wrote it
using dynamic SQL was because they THOUGHT they were writing static SQL,
i.e. they really didn't know what they were doing or how to do it.
Bill Gallagher, DBA
Phoenix Home Life
Enfield, CT 06083
Juan Lanus
<[EMAIL PROTECTED] To: [EMAIL PROTECTED]
> cc:
Sent by: Subject: Re: DB2EUG: Overhead for
Dynamic SQL in UDB/AIX?
[EMAIL PROTECTED]
a.best.com
03/21/01 11:32 AM
Please respond to
db2eug
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
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
