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