For the GURUS - sysprocplan and procedure cache

I am looking for the following information: 1. I have read in past postings that the limit on the number of stored procedure plans held in cache is 50. Is this completely independent of size? Is there anything besides sheer numbers that will bump a stored proc out of cache? (ie - could a proc be bumped out of cache while there are only, say 30 plans in cache?) 2. I read in a previous posting that setting noage = 1 can affect stored procedures in cache - can someone expand upon this? 3. Has anyone successfully extended the limits of stored procedure cache by using PC_POOLSIZE and/or PC_HASHSIZE? 4. Are procedure plans reentrant? If the existing plan is in use when another user needs to execute the proc, will the second user be able to use the existing plan, or will another one be generated? If another one is generated and stored in sysprocplan, what will happen when neither plan is being accessed and the proc is executed, which plan will be used? Thank You, Nicole Guffey Nguffey@jdriscoll.com

Nicole Guffey wrote: > > I am looking for the following information: > > 1. I have read in past postings that the limit on the number of > stored procedure plans held in cache is 50. Is this completely > independent of size? It is - the size of a stored procedure is limited. > Is there anything besides sheer numbers that will > bump a stored proc out of cache? (ie - could a proc be bumped out of > cache while there are only, say 30 plans in cache?) I don't think so - possibly when you shut down your server to "quiescent" mode. > 2. I read in a previous posting that setting noage = 1 can affect > stored procedures in cache - can someone expand upon this? ???? > 3. Has anyone successfully extended the limits of stored procedure > cache by using PC_POOLSIZE and/or PC_HASHSIZE? Yes. > 4. Are procedure plans reentrant? If the existing plan is in use > when another user needs to execute the proc, will the second user be > able to use the existing plan, or will another one be generated? When you call a stored procedure, the server determines whether to reoptimize the SP or not. If it must be re-optimized, the new plan will be stored in the "sysprocplan" table, that is, during this operation the entry will be locked in exclusive mode and noone else will be able to call the stored procedure until the update in the sysprocplan table is committed. I recommend to set the LOCK MODE TO WAIT n to avoid locking problems. Normally a single update of sysprocplan takes just a few milliseconds. > If > another one is generated and stored in sysprocplan, what will happen > when neither plan is being accessed and the proc is executed, which plan > will be used? > > Thank You, > Nicole Guffey > Nguffey@jdriscoll.com Best regards, Stefan Weideneder