Home > ASE > Procedure Cache and Query Plan

Procedure Cache and Query Plan

ASE doesn’t contain any table which stores the Query Plans of stored procedure. Instead, Query Plans gets stored in procedure cache that is the part of max memory.

ASE maintains MRU/LRU (most recently used/least recently used) algorithm. Stored procedures generally preferred over separate SQL statements because when users execute stored procedure, Adaptive server search procedure cache for existing query plan. If it is available then execution begins.

If Query plan is not available or all copies are in use, if multiple users are executing same stored procedure at a time then multiple copies of query plan will be available in procedure cache until size of cache is supporting, then query tree for the procedure is read from the sysprocedures table  .  Then query tree is then optimized, based on the parameters passed to the procedures and converted into query plan and then execution begins.

 

Check the Sybase Wiki @ sybasewiki.com
Categories: ASE Tags:
  1. May 16th, 2012 at 00:36 | #1

    I think it might have done good to “general” public to have demystified the procedure cache a bit. There is quite a lot of uncertainty around it – not the least because you may hit 701 error out of the blue – with the only way to monitor it being sp_monitorconfig + in 15.x monProcedureCacheMemory/ModuleUsage. Not long ago I have learned (through a lot of pain) that the sizing of the procedure cache has to take into consideration general workload of the server (number of user connections, number of engines) and this is because procedure cache is “chunked” per engine into engine local caches. I’d love to get some well-verbed documentation on procedure cache. Miss it a lot.

  1. No trackbacks yet.