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

[fblangref-appx05-monattach]: Information about active attachments to the database
[fblangref-appx05-moncallstk]: Calls to the stack by active queries of stored procedures and triggers
[fblangref-appx05-moncompst]: Virtual table listing compiled statements
[fblangref-appx05-contxtvars]: Information about custom context variables
[fblangref-appx05-mondb]: Information about the database to which the CURRENT_CONNECTION is attached
[fblangref-appx05-iostats]: Input/output statistics
[fblangref-appx05-memusage]: Memory usage statistics
[fblangref-appx05-recstats]: Record-level statistics
[fblangref-appx05-statements]: Statements prepared for execution
[fblangref-appx05-tablestats]: Table-level statistics
[fblangref-appx05-transacs]: Started transactions

`MON$ATTACHMENTS`

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

Column Name Data Type Description

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`.

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