--- In firebird-support@yahoogroups.com, Arn <odth@...> wrote:

>
> Hi All.
>
> I have some recursive SP.
> They don't move the OAT, so after some minutes the gap from OAT
> and new transaction is over 500 or 600.

Stored procedures are run WITHIN transactions and unlike some other
dbms they have no say in when transactions start or finish (nor should
they IMO). A transaction is active until it is committed (or rolled
back). If you want to know how it really works, then the link is a
good place to begin.

http://www.ibphoenix.com/main.nfs?a=ibphoenix&page=ibp_expert4

So the fact a recursive procedure is used means nothing to the OAT
(except that running a procedure 1000 times is going to take longer
than running it once). But until the transaction that you called the
stored procedure from commits, and all transactions that are
interested in record versions created by your transaction (or an
earlier transaction), the garbage collection must wait.

> I have set SweepInterval to 50, but no results.

The garbage collection will happen, but it can't collect these old
record versions because they are still needed.

>
> Somebody can help here?

Appending the primary key to the end of your indexed field in an index
allows the same functionality, but helps the garbage collection.
Otherwise, FB 2 solves a lot of this problem.

Adam