If the cache flag field in DBQLogTbl is blank, it means that the query is NOT using a cached plan and had to go through parsing and optimization. Only the value of "T" means that the query used a cached plan and bypassed parsing and optimization.
Thanks for such a nice explaination of Peaking.
We have a sql which runs very frequently in our system, since we upgraded to 14.10 its' parsing time has been increased and we tried peaking and non-peaking options for this but we couldn't get any success except identifying that it is going for 'A' when we apply peaking by making it prametarized statement.
Original query is not parameterized sql, so we are seeing space in CacheFlag column in DBQL. Is there any way we can tweak or hit the PE to use Cache for such queries?
Below are the three execution of original sql with few seconds difference. As you can see, ParsingCPU is very higher than AMPCPUTime, in result elapse time is also high.
Total_ElapsedTime = FirstRespTime-StartTime
ParsingTime = FirstStepTime-StartTime
You are correct, there will be a space in the cache flag field if the request is nto parameterized. But even non-parameterized requests could have their plans cached, it just the circumstances that would support that are less likely.
Non-parameterized requests will not have their plans cached the first time they are executed. In order to have their plan placed in the request cache such requests have to be seen by the optimizer a second time, on the same parsing engine as the first request. The second (or subsequent) request must use identical SQL as the first execution, character-by-character, all values must be the same, no extra spaces anywhere.
Even with those criteria met, there are several reasons why such a cached plans may not be re-used:
- The cached plan may have aged out of the request cache, too much time has gone by
- The second or subsequent requests are coming in on a different PE (each PE has its own request cache)
- DDL or collect stats has been executed on components of the request, causing the plan to be flushed from the cache
There are no settings you can make or tweaking you can do to get the request cache to do what you want. It's all out of your hands and handled internally.
I am seeing lot of EMPTY SPACES in the CACHE FLAG field. I understood that we cannot do anything if the queries are non-parameterized. Do you think too many empty spaces for the queries (around 5millions sometimes) increase the parsing CPU time, Because i am seeing there is increased parser cpu time, so just trying to see if the nonparametrized sqls are causing any down side on parser cpu time.
Q2) If i want to measure how many hits are being reused in request cache, can i consider the sum of S+G+A+T for one specific day?
More the number of Sum(S+G+A+T), then the more effectively the RequestCache is being reused, is this true?
Or is there anyother way i can evaluate the effictiveness of request cache? I read your magazine article and realized that there is no 100% accurate way to measure the request cache, but to see the rough estimation on how it isbeing reused, i am trying to find a way. Please share your thoughts.
Q) MaxRequestsSaved--regarding this parameter i think the size of the cache is still same at 100MB in 14.10, but if i increase the MaxRequestsSaved parameter to max value (2000), am i going to get advantage of parser cpu utilization by making more room for the tactical queries?
Empty space in the DBQL CacheFlag field means that the request did not have parameterized SQL and therefore was not eligible for using a cached plan. All queries with space in the cache flag (queries that did not have parameterized SQL) will have undergone parsing and optimizing. They will contibute to overall CPU time spent in parsing. You can see how much CPU was used by examining the ParserCPU column in the DBQLogTbl.
Only when you see cache flag of 'T' in DBQLogTbl was a cached plan used. When you see a cache flag of 'S' or a 'G' or an 'A' that means that a query plan had to be built. So if you want to know what percent of a group of queries among the queries that are candidates for using cached plans got their plan from the request cache, only look at the ones with 'T' and compare that number to the total number of queries in the candidate group.
Increasing the number of requests that can be held in the request cache (MaxRequestsSaved parameter) will only provide value if there are plans that could or have been successfully cached, but are pushed out of the request cache, and that is the reason for cache misses. If plans are mostly small (simple requests) there could be an advantage in increasing MaxRequestsSaved. But if the plans that could have been cached and weren't, or plans there were pushed out of the request cache are large, and their combined size is equal to or greater than the size of the request cache, then increasing the max number of plans that can be cached might not help at all. There are many variables.
It is best to discuss possible changes concerning DBS Control parameters with the support center, if you are at all unsure about what to try or not try, assuming you are having issues with unacceptably long parsing times.
Thanks, - Carrie