I am working with a Firebird 2.1.1 database and I started reading about the Monitoring tables.
I am trying to see somehow in real-time what queries does each client performs in the database.
I had a look at the MON$STATEMENTS
table but it does not provide much info. I would have expected more content inside the table.
Am I looking in the wrong place or did I not understand the purpose / use of the Firebird monitoring tables?
Best Answer
There are a number of things you need to know when using the monitoring tables:
MON$STATEMENTS
) provides you a list of the active or prepared statements at the time a query was run.SYSDBA
, the databaseowner or a user with roleRDB$ADMIN
can see all information, other users can only see information about their own connections.doc/README.monitoring_tables.txt
Now as to your screenshot: all statements are from the same connection (your own):
MON$ATTACHMENT_ID = 1521
. Only one statement is currently active (MON$STATE = 1
) and participating in transactionMON$TRANSACTION_ID = 90964
and was started at 2012-12-06 10:08 (MON$TIMESTAMP
), the rest is inactive (MON$STATE = 0
) and therefor has no transaction-id and timestamp. The first statement is also the only one that currently has an actual query (MON$SQL_TEXT
), which is the query that actual produced the result in the screenshot. In Firebird statements are allocated separately from the actual query to be executed using that statement.That is not much, but if you had joined to
MON$RECORD_STATS
,MON$IO_STATS
orMON$MEMORY_USAGE
based onMON$STAT_ID
you would have received statistics on the query (ie the number of reads, records update, number of pages read or written, memory use). The monitoring tables show information at a specific moment in time, and if a query has already finished when you execute it, you don't get any info (I am not 100% sure, but it might remain available until the next execute of a statement, or until the statement is unprepared or closed).If instead you need a stream of information and statistics, than you might get more out of the Firebird Trace API. I haven't used it much myself yet, but the article Audit and Trace Services in Firebird 2.5 by Thomas Steinmauer provides a good introduction to it.
In your question you say you expected 'more content', but didn't actually specify what content you need or expected; I hope this provides sufficient info.