I am concerned about plan caching, and the funky relationship it seems to have with variable references in queries.
Let's say ( for the sake of argument :) that I have an application where 99% of the business logic resides within SQL Anywhere 22.214.171.1246 web services, stored procedures, events, triggers and the like.
Let's also say that these blocks of SQL contain many queries, some of them Queries From Hell, and that local variables are occasionally referenced in important WHERE clause predicates; e.g., clustered-index-column BETWEEN @timestamp1 AND @timestamp2.
Here's some background, followed by some questions...
Is plan caching performed across multiple calls to a stored procedure, or just for queries executed multiple times within each execution of the stored procedure?
Does it make any difference is the variable is a parameter to the stored procedure, versus a local DECLARE variable? I remember once receiving this advice, for some other RDBMS: push the query down into another procedure and pass the host variable values via parameters, to make it go faster.
Is the following statement reliable, or is it possible that a Query From Hell could use a Cached Plan From Hell on occasion?
How do I flush the plan cache? Does SET TEMPORARY OPTION max_plans_cached = 0 and then back to 20 do it?
Should I just turn off plan caching (SET OPTION PUBLIC.max_plans_cached = 0) and go back to sleep?
Or is that just something to keep in the toolkit, next to the dead chicken one occasionally waves over the keyboard when nothing else seems to be working?
While setting the option to 0 is convenient, as Mark says by doing so you'll incur the optimization cost every time a query within a stored procedure or trigger is executed. What you can do instead is use the new query hint syntax
to ensure that this particular query is always optimized (and its plan never cached).
answered 02 Nov '10, 19:01
Lots of questions, hopefully I can answer them all!
Yes, plans are cached across multiple calls to a stored procedure but the plans are cached on a per-connection basis. I.e. each database connection gets its own cache of plans.
AFAIK there is no difference if the variable is a parameter to the stored procedure or the variable is global or the variable is declared locally within the procedure - as far as the database server is concerned, a variable is a variable.
I do not know where your quote regarding benefits of optimizing each execution, but I suspect it is referring to simple queries. When a query is classified as "simple" (.e.g select * from T where pk = constant) then the cost of doing a full all-out optimization is far greater than the cost of executing the query so the server will bypass the optimizer and execute the simple query using a plan that is generated using heuristic methods.
If I am reading the code correctly, setting any option on the connection will release all cached plans on the connection. The reason this is done is because there are several options that can affect how plans are optimized - this is perhaps a little heavy handed but that is what is done today (subject to change in the future).
Setting the public option max_plans_cache to zero will perhaps let you get more sleep, but you may lose sleep when you realize that you are not getting the benefits of plan caching on your query-from-hell that takes forever to optimize every time that the query is executed :-)
answered 02 Nov '10, 13:24