I don’t know about the rest of you, but I have had a lot ofproblems truly determining if my SQL Cache settings are efficient. If my UserDefined Routine (UDR) cache, for example, always seems to be 50% full, does that mean thecache only uses 50% and so the settings are to high, or are they at 50% becauseI’m constantly cleaning the pools, and thus I’m configured too low. Beginning in Cheetah you now have a means to diagnose thattype of information, and it’s located in the sysmaster database. The table name is syssqlcacheprof, and it contains profileinformation for each of the caches.
Below is a sample output from this table:
As you can see in the above you can now quickly identify how often the cache is removing entries (orcleaning), and what the efficiency is of your cache, in terms of a hit rate. Infact a very simple query that could be used for analyzing the hit ratio for the caches would be:
select *, (hits/(hits+misses)) hit_ratio from syssqlcacheprof
This table also provides youthe benefit of quickly seeing how much memory each cache is actually using,something that before Cheetah was a bit awkward to calculate. While not a major feature of Cheetah, this new sysmaster table is a valuable new asset in performance tuning.
Mark Jamison[Read More]