I was asked the other day about the request cache, speficially, how to keep plans in the cache so the query does not have to undergo repetitive parsing and optimizing. A request cache exists on each parsing engine (PE) and will hold generic plans that have been identified as potentially re-usable. For very short queries, request cache hits can reduce your elapsed time and provide more consistent response time. So the longer those re-usable plans stay in the cache, the better.
The Big Flush
You’re probably familiar with the way that plans get removed from the request cache periodically. Each PE flushes both its dictionary and request cache every 4 hours. The dictionary cache is flushed first, and always completely; the request cache is only flushed partially. In-use plans, or plans not dependent on statistics, such as primary index queries, are retained in the request cache. This flushing event is staggered so that no 2 PEs undergo flushing at the same time.
Other Factors That Push Plans out of the Cache
Some of the other things I have seen that can cause entries to be removed from the request cache include:
Operations performed on objects: Collecting Statistics , ALTER or DROP, INSERT/SELECTs that change more than 10% of the rows will cause specific objects to be removed from the request cache. To lesson the impact on the request cache, try to restrict these types of operations against objects used by the tactical apps during the times those tactical queries are running.
The limit on number of cache entries has been exceeded: If request cache entries per PE would exceed the MaxRequestsSaved number (default is 600), then plans will be released from the cache. I’ve talked to several sites that have changed this setting to the maximum of 2000, and all of them saw more cache hits as a result.
A really big request: Total request cache size is not allowed to exceed 100 MB by the caching of a new plan. To get big plans in, sometimes smaller plans will have to go. There’s no option to increase this setting, but you might consider targeting sessions where you expect large plans and sessions where you expect small plans to different groups of PEs.
Available swap space on the node has fallen below some minimum healthy number. When the system is tight on memory, a minimal number of entries will be automatically purged from the request cache, with the least-recently-used to go first. This is not a common experience among Teradata sites, but it’s still a good idea to keep on eye on what’s happening with your memory.
The application is spread across a large number of PEs, and other non-tactical plans are pushing out the tactical plans. Consolidating the tactical application to a small number of PEs can help to keep the plans for the tactical applications in the request cache longer. The more frequently the plans are used, the less likely they are to be aged out.
A Surprise Factor
In our exploratory tactical query testing last year we discovered that a MultiLoad job can cause plans to be flushed from the request cache when the tactical query references the table undergoing loading. (Yes, the tactical queries were using access locking.) This happens because a utility lock must be set and unset as part of the MuliLoad job. Utility locks cause the table header to change, and when the table header changes, all plans releated to that table get flushed from the request cache. We were doing lots of short mini-batch loads against the same table we were issuing tactical queries against, so we were able to see this effect. Increasing MaxRequestsSaved reduced this impact significantly, which is the first thing I’d suggest you do if you find this happening to you.
How to Monitor Success
You can see how consistently your cached plans are being used by examining the CacheFlag value in DBQLogTbl. If the plan used was from the request cache, you’ll see a ‘T’ in that field. For more background on how cached plans are managed see my article posted on Dev X last August called “To Peek or Not to Peek”.
You must be a registered user to add a comment. If you've already registered, sign in. Otherwise, register and sign in.