Hello,

We have a test which sits in a loop and every iteration inserts some
records and runs some queries. When we start with an empty database
test performance degrades rapidly. However, if we stop the test and
restart it at the state of the database where it left off, performance
goes back to normal.

We spent hours trying to figure this one out and eventually realized
that the problem was with stored procedures (SP) plans. When we start
with an empty database SP plans are compiled when there is no data in
the database and hence are wrong. When we start with a pre-populated
database - the plans are more or less accurate and give us good
performance.

What are the best practices for recompiling SP plans? What's the best
way to determine that plans become outdated? What is the SQL statement
that causes SP plan recompilation?

Thanks

P.S. In our case we execute JDBC prepared statements (via Jaybird)
that create and execute stored procedures. How can we clear prepared
statement cache? I realize that this probably belongs on firebird-java
forum, but I thought I'd try it here :)