FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 System TablesSecurity 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

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

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.

Note
Notes

  • 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

MON$TABLE_STATS

MON$TABLE_STATS reports table-level statistics.

Column Name Data Type Description

MON$STAT_ID

INTEGER

Statistics identifier

MON$STAT_GROUP

SMALLINT

Statistics group:

0 - database
1 - connection
2 - transaction
3 - statement
4 - call

MON$TABLE_NAME

CHAR(63)

Name of the table

MON$RECORD_STAT_ID

INTEGER

Link to MON$RECORD_STATS
Getting statistics at the record level for each table for the current connection
SELECT
  t.mon$table_name,
  r.mon$record_inserts,
  r.mon$record_updates,
  r.mon$record_deletes,
  r.mon$record_backouts,
  r.mon$record_purges,
  r.mon$record_expunges,
  ------------------------
  r.mon$record_seq_reads,
  r.mon$record_idx_reads,
  r.mon$record_rpt_reads,
  r.mon$backversion_reads,
  r.mon$fragment_reads,
  ------------------------
  r.mon$record_locks,
  r.mon$record_waits,
  r.mon$record_conflicts,
  ------------------------
  a.mon$stat_id
FROM mon$record_stats r
JOIN mon$table_stats t ON r.mon$stat_id = t.mon$record_stat_id
JOIN mon$attachments a ON t.mon$stat_id = a.mon$stat_id
WHERE a.mon$attachment_id = CURRENT_CONNECTION