Appendix E. Monitoring Tables

The Firebird engine can monitor activities in a database and make them available for user queries via the monitoring tables. The definitions of these tables are always present in the database, all named with the prefix MON$. The tables are virtual: they are populated with data only at the moment when the user queries them. That is also one good reason why it is no use trying to create triggers for them!

The key notion in understanding the monitoring feature is an activity snapshot. The activity snapshot represents the current state of the database at the start of the transaction in which the monitoring table query runs. It delivers a lot of information about the database itself, active connections, users, transactions prepared, running queries and more.

The snapshot is created when any monitoring table is queried for the first time. It is preserved until the end of the current transaction to maintain a stable, consistent view for queries across multiple tables, such as a master-detail query. In other words, monitoring tables always behave as though they were in SNAPSHOT TABLE STABILITY (consistency) isolation, even if the current transaction is started with a lower isolation level.

To refresh the snapshot, the current transaction must be completed and the monitoring tables must be re-queried in a new transaction context.

Access Security

SYSDBA and the database owner have full access to all information available from the monitoring tables
Regular users can see information about their own connections; other connections are not visible to them

🛑

Warning

In a highly loaded environment, collecting information via the monitoring tables could have a negative impact on system performance.

List of Monitoring Tables

Section E.1, “MON$ATTACHMENTS”: Information about active attachments to the database
Section E.3, “MON$CALL_STACK”: Calls to the stack by active queries of stored procedures and triggers
Section E.2, “MON$COMPILED_STATEMENTS”: Virtual table listing compiled statements
Section E.4, “MON$CONTEXT_VARIABLES”: Information about custom context variables
Section E.5, “MON$DATABASE”: Information about the database to which the CURRENT_CONNECTION is attached
Section E.6, “MON$IO_STATS”: Input/output statistics
Section E.7, “MON$MEMORY_USAGE”: Memory usage statistics
Section E.8, “MON$RECORD_STATS”: Record-level statistics
Section E.9, “MON$STATEMENTS”: Statements prepared for execution
Section E.10, “MON$TABLE_STATS”: Table-level statistics
Section E.11, “MON$TRANSACTIONS”: Started transactions

E.1. `MON$ATTACHMENTS`

MON$ATTACHMENTS displays information about active attachments to the database.

Column Name	Data Type	Description
`MON$ATTACHMENT_ID`	`BIGINT`	Connection identifier
`MON$SERVER_PID`	`INTEGER`	Server process identifier
`MON$STATE`	`SMALLINT`	Connection state: `0` - idle `1` - active
`MON$ATTACHMENT_NAME`	`VARCHAR(255)`	Connection string — the file name and full path to the primary database file
`MON$USER`	`CHAR(63)`	The name of the user who is using this connection
`MON$ROLE`	`CHAR(63)`	The role name specified when the connection was established. If no role was specified when the connection was established, the field contains the text `NONE`
`MON$REMOTE_PROTOCOL`	`VARCHAR(10)`	Remote protocol name
`MON$REMOTE_ADDRESS`	`VARCHAR(255)`	Remote address (address and server name)
`MON$REMOTE_PID`	`INTEGER`	Remote client process identifier
`MON$CHARACTER_SET_ID`	`SMALLINT`	Connection character set identifier (see `RDB$CHARACTER_SET` in system table `RDB$TYPES`)
`MON$TIMESTAMP`	`TIMESTAMP WITH TIME ZONE`	The date and time when the connection was started
`MON$GARBAGE_COLLECTION`	`SMALLINT`	Garbage collection flag (as specified in the attachment’s DPB): 1=allowed, 0=not allowed
`MON$REMOTE_PROCESS`	`VARCHAR(255)`	The full file name and path to the executable file that established this connection
`MON$STAT_ID`	`INTEGER`	Statistics identifier
`MON$CLIENT_VERSION`	`VARCHAR(255)`	Client library version
`MON$REMOTE_VERSION`	`VARCHAR(255)`	Remote protocol version
`MON$REMOTE_HOST`	`VARCHAR(255)`	Name of the remote host
`MON$REMOTE_OS_USER`	`VARCHAR(255)`	Name of remote user
`MON$AUTH_METHOD`	`VARCHAR(255)`	Name of authentication plugin used to connect
`MON$SYSTEM_FLAG`	`SMALLINT`	Flag that indicates the type of connection: `0` - normal connection `1` - system connection
`MON$IDLE_TIMEOUT`	`INTEGER`	Connection-level idle timeout in seconds. When `0` is reported the database `ConnectionIdleTimeout` from `databases.conf` or `firebird.conf` applies.
`MON$IDLE_TIMER`	`TIMESTAMP WITH TIME ZONE`	Idle timer expiration time
`MON$STATEMENT_TIMEOUT`	`INTEGER`	Connection-level statement timeout in milliseconds. When `0` is reported the database `StatementTimeout` from `databases.conf` or `firebird.conf` applies.
`MON$WIRE_COMPRESSED`	`BOOLEAN`	Wire compression active (TRUE) or inactive (FALSE)
`MON$WIRE_ENCRYPTED`	`BOOLEAN`	Wire encryption active (TRUE) or inactive (FALSE)
`MON$WIRE_CRYPT_PLUGIN`	`VARCHAR(63)`	Name of the wire encryption plugin used
`MON$SESSION_TIMEZONE`	`CHAR(63)`	Name of the session time zone
`MON$PARALLEL_WORKERS`	`INTEGER`	Maximum number of parallel workers for this connection, `1` means no parallel workers. Garbage Collector and Cache Writer connections may report `0`.

Retrieving information about client applications

  |SELECT MON$USER, MON$REMOTE_ADDRESS, MON$REMOTE_PID, MON$TIMESTAMP
  |FROM MON$ATTACHMENTS
  |WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

E.1.1. Using `MON$ATTACHMENTS` to Kill a Connection

Monitoring tables are read-only. However, the server has a built-in mechanism for deleting (and only deleting) records in the MON$ATTACHMENTS table, which makes it possible to close a connection to the database.

All the current activity in the connection being deleted is immediately stopped and all active transactions are rolled back
The closed connection will return an error with the isc_att_shutdown code to the application
Subsequent attempts to use this connection (i.e. use its handle in API calls) will return errors

Termination of system connections (MON$SYSTEM_FLAG = 1) is not possible. The server will skip system connections in a DELETE FROM MON$ATTACHMENTS.

Closing all connections except for your own (current):

  |DELETE FROM MON$ATTACHMENTS
  |WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION

Prev	Up	Next
D.41. `RDB$VIEW_RELATIONS`	Home	E.2. `MON$COMPILED_STATEMENTS`