cancel
Showing results for 
Search instead for 
Did you mean: 

Should I SET OPTION PUBLIC.max_plans_cached = 0?

Breck_Carter
Participant
2,883

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

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

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

Accepted Solutions (0)

Answers (2)

Answers (2)

Former Member

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

OPTION ( FORCE OPTIMIZATION )

to ensure that this particular query is always optimized (and its plan never cached).

MarkCulp
Participant

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 🙂

MCMartin
Participant
0 Kudos

"plans are cached on a per-connection basis": This seems not so optimal to me, especially when thinking of .net apps which need to use a lot of connections due to the .net architecture. Why should a plan which is good for one connection should not be suitable for another connection?

MarkCulp
Participant
0 Kudos

There are many connection level options and settings (including the logged in user) that can affect the query plan that is chosen. We have discussed (and have experimented with) cross connection plan caching but it is hard to get it right so this feature has not (yet) made it into the released product.