That’s No Way to Treat Your Best Friend!
The Dynamic Statement Sache (DSC) can, and should, be your best friend for helping SQL run nice and fast on your machine. If you are good, you have a latency (how long the statements stay in the DSC) of about two days, and everything looks hunky-dory at first glance.
However, it could be that your best friend is not all he’s cracked up to be!
The first inkling that something somewhere is not right, is when you see your DSC flush rate soaring upwards (i.e. how many statements are being flushed per hour). Statements are flushed all the time through RUNSTATS, or security changes, etc. But when you see thousands of statements grabbing their coats and heading for the door you know something is wrong – and it can’t just be the music!
1,000’s of INSERTS statements are taking a “slot” from the DSC for no purpose!
What I have seen is that lots of people concentrate on Dynamic SQLs that are SELECT or UPDATE or predicated DELETEs and MERGEs, but no-one bothers about simple INSERT statements… and guess what I had seen? Yep, 1,000’s are INSERTS using literals, each of which takes a “slot” from the DSC for no purpose whatsoever!!! These are really, really nasty indeed…
How to list and fix the “bad guys” (the INSERTS Statements)
To find them,
– I use a snap of the DSC.
– Then I select only the INSERT Statements,
– and then simply exclude any that have parameter markers.
What you are left with, is the list of “bad guys” that, if excessive in number, must be fixed as soon as possible!
Here’s how I do this using our SQL PerformanceExpert (SPX):
1 First I snap the DSC and show only the INSERTS:
Here you can see in our DB2 10 NF test system that there are 2,673 statements.
2 Identifying the “good guys”
After filtering on “INSERT”, we get the next Panel:
Here I can see only 200 INSERTS are in the cache. Note that some have multiple executions.
These are the “good guys” – Multiply executed but singly prepared.
3 Now I use the primary command PR to just dump these statements in a file for ISPF usage:
Here all of the SQL text is shown, (it carries on over to the right hand side, of course!)
Now I can see some INSERTS with Parameter Markers, but I want to see the ones without.
4 INSERTS without Parameter Markers
Simply doing these ISPF commands enables me to see that view too:
X ALL
F ALL ?
F ALL INSERT 1
Then I simply page down looking for INSERTS with no matching question marks.
Of course you can also do this with a small REXX, etc
Now I can quickly see that there appears to be a long set of INSERTS – all with literals.
All of these should be changed to be Parameter Markers to help boost overall system Performance!
Naturally I use the above SQL data for much more than just Parameter Marker usage. You can imagine the fun to be had just conducting text searches to see which SQL uses which Built-In-Function, for example. CHAR9 usage anyone?
The possibilities are endless!
Now I get to spend lots of time with my best friend.
Happy Tuning!
As usual any questions or comments are welcome,
TTFN Roy Boxwell
Senior Software Architect