| Subject | Re: [firebird-support] Plan evaluation in stored procedures |
|---|---|
| Author | Ann Harrison |
| Post date | 2015-08-10T13:08:26Z |
On Aug 10, 2015, at 8:15 AM, Aldo Caruso aldo.caruso@... [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 ?
Good luck,
Ann
On 9 de agosto de 2015 20:14:46 GMT-03:00, "'Leyne, Sean' Sean@... [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