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.
> 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?
I think that your best bet would be to take a CLI trace on the client
when you run the application in all three scenarios (pure dynamic, dynamic
with parameter markers, and static).
Not only will this give you detailed information on what the client is
doing, but you can also get detailed timings for each operation (e.g.
how long did the statement take to execute, how much time elapsed between
each CLI call).
Unfortunately, CLI traces are verbose and parsing through them to find
the "good stuff" can be tedious.
Good luck,
ian d. bjorhovde cephalad corporation
[EMAIL PROTECTED] intelligent solutions for
http://cephalad.com systems and data management
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
