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 11.0.1.2276 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). |