Bill,
I also have an environment that uses only dynamic SQL. If you want to see the
CPU times for "Prepare" I would suggest you setup an event monitor. Here is the
sample SQL to setup a monitor:
drop event monitor aa_monfeb2001;
create event monitor aa_monfeb2001
for statements where auth_id like 'SG%'
write to file '/db/app/db2/mirp1/events/aa_monfeb2001'
autostart
maxfiles 1
maxfilesize 8192
buffersize 32;
The "file" allocation, in the above example, is a directory where the event
records are written.
You will need to use a DB2 utility called "db2evmon" to produce a report from
the event monitor. Here is the sample syntax.
db2evmon -db mirprod -evm aa_monmar2001
If you decide to go down this path I would strongly recommend you install APAR
IY14503. I believe it is part of fixpack 7 for V6.1 (we are running on
Solaris).
Good luck,
Roy
[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
