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

http://sqlanywhere-forum.sap.com/questions/1298/add-the-possibility-to-declare-constants-within-blocks/1303#1303

... the optimizer cannot use variable values when optimizing queries define within a stored procedure, ...

http://sqlanywhere-forum.sap.com/questions/1287/cursor-predicates-arent-re-evaluated-on-the-fly-are-they/1289#1289

... When a statement within a stored procedure is executed multiple times, it is eligible for plan caching. When plan caching is used, the optimizer builds a special plan that doesn't pay attention to the current values of variables. Instead, the value is retrieved when the plan starts to execute (in the "prepare" phase of the root). With plan caching, the optimizer can not use the variable values (they may change during subsequent uses of the statement). ...

http://dcx.sybase.com/index.html#1200en/dbusage/plan-caching-queryopt.html

Plan caching

... For queries that are executed frequently, the cost of query optimization can outweigh the benefits of optimizing at execution time. To reduce the cost of optimizing these statements repeatedly, the SQL Anywhere server considers caching plans for:

• All statements performed inside stored procedures, user-defined
  functions, and triggers. ...

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?

ibid.

... The execution plan is not cached when the benefit of optimizing on each execution outweighs the savings from avoiding optimization. ...

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?