Mike Matrigali <[email protected]> writes: > On 11/21/2012 6:58 AM, Knut Anders Hatlen wrote: >> "Bergquist, Brett" <[email protected]> writes: >> >>> Yes, the statement cache size has been increased to 50K statements so >>> that might be an issue. Maybe the PermGen space will need to be >>> increased because of that. The documentation is not clear which type > I am not an expert in this area, is there any case where we expect the > re-execution of the same query to need to generate a different entry > in the statement cache?
I think what's flooding the statement cache here is whatever gets executed by the table function, which I understand is some dynamically generated SQL statements. This is also why I don't understand how changing from a view to a direct table function call should change anything, as the top-level statement should only have one entry in the cache, and the statements executed inside the table function should be the same. Two possible explanations: 1) Changing between view and direct call changes the plan picked by the optimizer, so that the table function call one time ends up as the inner table in a join, and another time as the outer table. This could change the number of times the table function is called per query. If each call to the table function generates truly unique SQL statements, calling it more often will fill the cache quicker. 2) If it is a restricted table function, the actual restriction/projection pushed down to the table function may vary depending on which plan the optimizer picks. And this could affect what kind of SQL is generated by the table function. Perhaps sometimes it generates statements that are likely to be identical across invocations, needing fewer entries in the cache, and other times it generates statements that are less likely to be identical. Following up on that last thought, if the queries generated by the table function would be something like select * from t where x < N where N varies between invocations, it's better for the statement cache if a parameter marker is used, like select * from t where x < ? rather than inlining the actual constant select * from t where x < 5 select * from t where x < 42 ... Even though the table function itself doesn't execute the query more than once, using parameter markers increases the likelihood of finding a match in the statement cache. Not sure if this affects Brett's table function. Just throwing out ideas... -- Knut Anders
