Re: [Firebird-devel] Autonomic Computing on FB

[Fabiano B. <fab...@gm...>]

On 8/28/07, Leonardo Machado <leo...@gm...> wrote:

> humm good ideas Fabiano...
>
> I'm just thinking about how complex would be to develop an application with
> such funcionalities...
>
> IBM created the prototype of a monitor to optimize queries based on
> estimated and executed plans... Based on what was estimated and what really
> happened, the prototype was able to make the changes, so a future query with
> same characteristics would use the correct plan, and not a new estimated
> plan, that could be wrong again... This happens in DB2 when there are too
> much joins... Not always the optimizer uses the best plan...
> To create only this funcionality, the project envolved lots of skilled IT
> professionals... and I'll develop the application alone...

I can imagine it will not be easy at all. That's why i proposed the
indices rebuilding and corruption stuff. It seems it would be easier
to achieve.

Reading LEO document superficially, it seems your application would
need more statistical stuff than what is present today in a FB
database, so you will probably need to periodically create and mantain
an external database containing statistics about the target database,
and use these statistics to decide the better way to go.

> Talking about the application to be developed: is it really reachable?
>  In Java/C++, for example, is it possible to create an application to stay
> monitoring the database and discover what's happening on it? I never worked
> with it before, but I know both c++ and java...

I _think_ it will be possible to monitor many aspects of the database,
including the executed queries, in version 2.1 and above, because this
version will provide monitoring tables.

I don't think it will be possible to 'intercept' a query and change
it's plan using an external application, but a simple log showing the
slow queries and alternative plans would be very usefull.

Regards,

Fabiano.