Hi,
We have a project with a large amount of SQL. Most of it humms along nicely but once in a while a customer experiences a slow query - a query that runs fast for everyone else. It seems that sometimes (in rare cases) the query analyser probably orders the joins in a different way and the amount of work increases dramatically.
A recent example was where a query was returning 130 rows, and for most customers (checking via the query analyser tool in IBExpert) there were roughly the same number of database reads. But for one customer, there were 13,000 reads and performance was a shocker. We used that little bit of black magic, adding "+0" after the first join and hey presto - fast, and very few database reads. As we understand it, the "+0" provides the query optimiser a hint as to which join comes first, right?
Anyway, that's the background. What I'm getting to is that we'd like to implement some monitoring code to try to notify us if these sorts of things are discovered in the future. (We have far too much SQL to analyse manually, and some of it is generated.) Eg for a query that returns 130 rows, raise a warning if the number of database reads is 100x the number of rows in the result set. That's a simplification... sounds like a bit of a hack, but could be useful.

Apparently in FB2.5 there are monitoring tables? Are they used to get this sort of info about queries that have just run? Or is there some other way to query the database engine to analyse this sort of info? Are there solutions in FB2.5, or things that'll also work in earlier versions of FB?

Additionally most of our clients are on FB 1.5, but once 2.5 is shipping we'll start migrating them (we can hardly wait!). Our application base is Java and we connect to FB via the jaybird JDBC driver, in case that is relevant...

Thanks in advance!
Phil