Re: [firebird-support] Plan evaluation in stored procedures

[Ann Harrison aharri...@ibphoenix.com [firebird-support]]

> On Aug 10, 2015, at 8:15 AM, Aldo Caruso aldo.car...@argencasas.com 
> [firebird-support] <firebird-support@yahoogroups.com> wrote:
> 
> 
> does it mean that if the database is shut down periodically the stored 
> procedure plans would be recalculated ? Shutting the database periodically 
> solves the problem ?
> 

Not Sean, but yes.  Shutting down the database will cause stored procedures to 
be reoptimized. 


Good luck,

Ann
> 
>> On 9 de agosto de 2015 20:14:46 GMT-03:00, "'Leyne, Sean' 
>> s...@broadviewsoftware.com [firebird-support]" 
>> <firebird-support@yahoogroups.com> wrote:
>> Aldo,
>> 
>> > My question is: does the optimizer evaluates the plan for the query each
>> > time the stored procedure is excecuted or is it evaluated only once, when
>> > the stored procedure is compiled to BLR.
>> > In the latter case it would be a dissadvantage because as time passes,
>> > table cardinality and indeces distribution vary, and if the plan was 
>> > frozen at
>> > the stored procedure compilation time, may be that it would't be optimal 
>> > any
>> 
>> The plan for SP is determined when the SP is invoked for the first time from 
>> disk.
>> 
>> So your worse is not as bad you think, but if you are running superserver 
>> without the database havng no active connections, the SP will remain in 
>> memory and the plan will not reflect the latets index stats.
>> 
>> Sean
> 
> 
>