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

2015-08-10 Thread 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

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

2015-08-09 Thread 'Leyne, Sean' s...@broadviewsoftware.com [firebird-support] 
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