I can see that the Firebird 2.1 process (on Linux) (for our program) reaches 97% CPU load, the load may be distributed, e.g. the server can have 4 cores and 2 cores are consumed with 97% load and the remaining 2 cores are under normal load (1-10%) from the Firebird process. The bad thing is, that this 97% peak can last half hour, an hour or even longer.

As I understand, then I just need to determine the Firebird transaction and the Firebird attachment (i.e. connection) that has created this peek and then I can just ask the user/software instance, that created this connection/attachment to close his/her program and start anew. When attachment is closed, the Firebird can sense this and Firebird process stops any CPU loads and processes that were assigned to that attachment.

So, my aim is to look on the data from the monitoring tables (mon$...) and to determine the offending transaction/connection.

I came up with the select (for Firebird 2.1):

select a.mon$user, sa.*, t.*            
  from mon$transactions t           
    left join mon$io_stats s on (t.mon$stat_id=s.mon$stat_id)           
    left join mon$attachments a on (t.mon$attachment_id=a.mon$attachment_id)            
    left join mon$statements sa on (t.mon$transaction_id=sa.mon$transaction_id)         
  where s.mon$page_reads>1000000

This SQL seems to be right, but practically the results are misleading. For example, my select returns several entries with a.mon$timestamp that is 4 or even more hours old. I can not believe that there are transactions that are so old and that still are taking resources. The strange thing is that the records have no data from left-joined mon$statements. So, I have some information about long-running transactions, but I have no information about statements that case created or prolonged this transaction. I don't even understand whether such transactions are actually creating the CPU peak or if this data is obsolete.

So, how to correct this SQL (or write completely anew) to find the statements/attachments that is causing CPU % in Firebird 2.1?