Subject Re: Re[2]: [firebird-support] SP query plan
Author Nick Upson
Nick Upson

On 20 April 2012 19:10, Dmitry Kuzmenko <kdv@...> wrote:

> **
> Hello, Nick!
> Friday, April 20, 2012, 7:55:09 PM, you wrote:
> NU> so the plan is not stored anywhere in the database that I can access?
> it is stored only in server memory when procedure being prepared first
> time.
> NU> what happens is this regard when some clients reconnect and others
> don't,
> NU> different processes could have different plans?
> in InterBase, you can watch tmp$procedures, that are loaded into
> memory.
> When all users, that called that procedure, disconnects, it's
> being freed and new user who called it again will result
> procedure load and new plan for it.
> Why do you ask about this? you have lot of indices that being updated
> much for a short time? Btw, you can check how much index statistics
> is being changed by using IBAnalyst.
> (you asked)

I have a process (in C) which suddenly went from around 5 mins for a
specific SP call to an hour and has continued to get worse everytime its
run (daily)
The data is substantially the same so I'm trying to understand the cause.
This is on a live system where there are restrictions on what I can do.
restoring a backup onto elsewhere and it runs in 5 mins again but I can't
justify a backup/restore of the live system with the several hours downtime
an explaination of what has happened and how we can avoid it happening

This is firebird 2.1.4, 32 bit, centos 5.5 with a database almost 20Gb in

[Non-text portions of this message have been removed]