Dynamic SQL Cache (DSC) settings
I have recently been to a bunch of shops where everyone was very happy with their Dynamic SQL Cache (DSC) settings. They all said “We have statements for up to four days in the cache and a 99.2% hit ratio so everything is green!”
They were so wrong….
The problem is, that one statement in the DSC that is executed 16,000,000 times, gives a HUGE false positive on the hit ratio, and *one* really old statement does not automatically imply that *all* statements are so long lived in the DSC.
What I have found is that the DSC flush rate per hour is a much much better metric to see if your DSC is thrashing itself to death. If you have 10,000 statements or more being flushed out of the cache, then your cache is not too healthy!
At the sites where I looked, the peak was 25,000 statements an hour being flushed! This is seriously bad when the DBAs were sure that everything “is green”…
So how do you work out your Flush/hour ???
Well, you could always buy and install the SQL WorkloadExpert that does the calculations for you, or you could run this set of SQL to also get nearly the same style of data!
First,
do an EXPLAIN STMTCACHE ALL on one of the members of your data-sharing groups or in your non-datasharing DB2 (Remember that this command only extracts the local data!!!)
EXPLAIN STMTCACHE ALL; ---------+---------+---------+---------+---------+--------- DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 0 ---------+---------+---------+---------+---------+---------
Then,
execute the following SQL:
SELECT COUNT(*) AS NUM_STMTS
, INTEGER((MAX(STMT_ID) - MIN(STMT_ID) + 1 - COUNT(*)) /
(
TIMESTAMPDIFF ( 2 , CAST(
MAX(CACHED_TS) - MIN(CACHED_TS) AS CHAR(22)
)) / 3.6E+03
) )
AS FLUSH_PER_HOUR
, MAX(STMT_ID) - MIN(STMT_ID) + 1 - COUNT(*) AS FLUSHED_STMTS
, MIN(STMT_ID) AS MIN_STMT
, MAX(STMT_ID) AS MAX_STMT
, MIN(CACHED_TS) AS MIN_STMT_TS
, MAX(CACHED_TS) AS MAX_STMT_TS
, DECIMAL(
TIMESTAMPDIFF ( 2 , CAST(
MAX(CACHED_TS) - MIN(CACHED_TS) AS CHAR(22)
)) / 3.6E+03
, 12 , 3 ) AS MAX_AGE_OF_CACHE
FROM <userid>.DSN_STATEMENT_CACHE_TABLE
;
I use here the TIMESTAMPDIFF built-in function (BIF) with 2 to get the seconds, and then divide by 3600 to get back to hours.
The output will look like:
---------+---------+---------+---------+---------+---------+---------
NUM_STMTS FLUSH_PER_HOUR FLUSHED_STMTS MIN_STMT MAX_STMT
---------+---------+---------+---------+---------+---------+---------
69 4 200 7750 8018
---------+---------+---------+---------+---------+---------+---------+-
MIN_STMT_TS MAX_STMT_TS MAX_AGE_OF_CACHE
---------+---------+---------+---------+---------+---------+---------+-
2013-04-24-09.49.55.712231 2013-04-26-09.52.46.758709 48.047
Here, on one of our small test systems you can see we have a very low use and flush rate, however, I am sure that you all will be surprised when you run this in your pre or production DB2s!
Feel free to send me your comments and ask questions.
TTFN,
Roy Boxwell
Senior Software Architect.