FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 System TablesSecurity tables 

MON$TRANSACTIONS

MON$TRANSACTIONS reports started transactions.

Column Name Data Type Description

MON$TRANSACTION_ID

BIGINT

Transaction identifier (number)

MON$ATTACHMENT_ID

BIGINT

Connection identifier

MON$STATE

SMALLINT

Transaction state:

0 - idle
1 - active

MON$TIMESTAMP

TIMESTAMP WITH TIME ZONE

The date and time when the transaction was started

MON$TOP_TRANSACTION

BIGINT

Top-level transaction identifier (number)

MON$OLDEST_TRANSACTION

BIGINT

Transaction ID of the oldest [interesting] transaction (OIT)

MON$OLDEST_ACTIVE

BIGINT

Transaction ID of the oldest active transaction (OAT)

MON$ISOLATION_MODE

SMALLINT

Isolation mode (level):

0 - consistency (snapshot table stability)
1 - concurrency (snapshot)
2 - read committed record version
3 - read committed no record version
4 - read committed read consistency

MON$LOCK_TIMEOUT

SMALLINT

Lock timeout:

-1 - wait forever
0 - no waiting
1 or greater - lock timeout in seconds

MON$READ_ONLY

SMALLINT

Flag indicating whether the transaction is read-only (value 1) or read-write (value 0)

MON$AUTO_COMMIT

SMALLINT

Flag indicating whether automatic commit is used for the transaction (value 1) or not (value 0)

MON$AUTO_UNDO

SMALLINT

Flag indicating whether the logging mechanism automatic undo is used for the transaction (value 1) or not (value 0)

MON$STAT_ID

INTEGER

Statistics identifier
Getting all connections that started Read Write transactions with isolation level above Read Commited
SELECT DISTINCT a. *
FROM mon$attachments a
JOIN mon$transactions t ON a.mon$attachment_id = t.mon$attachment_id
WHERE NOT (t.mon$read_only = 1 AND t.mon$isolation_mode >= 2)

MON$COMPILED_STATEMENTS

Virtual table listing compiled statements.

Column Name Data Type Description

MON$COMPILED_STATEMENT_ID

BIGINT

Compiled statement id

MON$SQL_TEXT

BLOB TEXT

Statement text

MON$EXPLAINED_PLAN

BLOB TEXT

Explained query plan

MON$OBJECT_NAME

CHAR(63)

PSQL object name

MON$OBJECT_TYPE

SMALLINT

PSQL object type:

2 - trigger
5 - stored procedure
15 - stored function

MON$PACKAGE_NAME

CHAR(63)

PSQL object package name

MON$STAT_ID

INTEGER

Statistics identifier

docnext count = 8

MON$CALL_STACK

MON$CALL_STACK displays calls to the stack from queries executing in stored procedures and triggers.

Column Name Data Type Description

MON$CALL_ID

BIGINT

Call identifier

MON$STATEMENT_ID

BIGINT

The identifier of the top-level SQL statement, the one that initiated the chain of calls.Use this identifier to find the records about the active statement in the MON$STATEMENTS table

MON$CALLER_ID

BIGINT

The identifier of the calling trigger or stored procedure

MON$OBJECT_NAME

CHAR(63)

PSQL object name

MON$OBJECT_TYPE

SMALLINT

PSQL object type:

2 - trigger
5 - stored procedure
15 - stored function

MON$TIMESTAMP

TIMESTAMP WITH TIME ZONE

The date and time when the call was started

MON$SOURCE_LINE

INTEGER

The number of the source line in the SQL statement being executed at the moment of the snapshot

MON$SOURCE_COLUMN

INTEGER

The number of the source column in the SQL statement being executed at the moment of the snapshot

MON$STAT_ID

INTEGER

Statistics identifier

MON$PACKAGE_NAME

CHAR(63)

Package name for stored procedures or functions in a package

MON$COMPILED_STATEMENT_ID

BIGINT

Compiled statement id
Note

Information about calls during the execution of the EXECUTE STATEMENT statement does not get into the call stack.
Get the call stack for all connections except your own
WITH RECURSIVE
  HEAD AS (
    SELECT
      CALL.MON$STATEMENT_ID, CALL.MON$CALL_ID,
      CALL.MON$OBJECT_NAME, CALL.MON$OBJECT_TYPE
    FROM MON$CALL_STACK CALL
    WHERE CALL.MON$CALLER_ID IS NULL
    UNION ALL
    SELECT
      CALL.MON$STATEMENT_ID, CALL.MON$CALL_ID,
      CALL.MON$OBJECT_NAME, CALL.MON$OBJECT_TYPE
    FROM MON$CALL_STACK CALL
      JOIN HEAD ON CALL.MON$CALLER_ID = HEAD.MON$CALL_ID
  )
SELECT MON$ATTACHMENT_ID, MON$OBJECT_NAME, MON$OBJECT_TYPE
FROM HEAD
  JOIN MON$STATEMENTS STMT ON STMT.MON$STATEMENT_ID = HEAD.MON$STATEMENT_ID
WHERE STMT.MON$ATTACHMENT_ID <> CURRENT_CONNECTION

MON$CONTEXT_VARIABLES

MON$CONTEXT_VARIABLES displays information about custom context variables.

Column Name Data Type Description

MON$ATTACHMENT_ID

BIGINT

Connection identifier.It contains a valid value only for a connection-level context variable.For transaction-level variables it is NULL.

MON$TRANSACTION_ID

BIGINT

Transaction identifier.It contains a valid value only for transaction-level context variables.For connection-level variables it is NULL.

MON$VARIABLE_NAME

VARCHAR(80)

Context variable name

MON$VARIABLE_VALUE

VARCHAR(32765)

Context variable value
Retrieving all session context variables for the current connection
SELECT
  VAR.MON$VARIABLE_NAME,
  VAR.MON$VARIABLE_VALUE
FROM MON$CONTEXT_VARIABLES VAR
WHERE VAR.MON$ATTACHMENT_ID = CURRENT_CONNECTION

MON$DATABASE

MON$DATABASE displays the header information from the database the current user is connected to.

Column Name Data Type Description

MON$DATABASE_NAME

VARCHAR(255)

The file name and full path of the primary database file, or the database alias

MON$PAGE_SIZE

SMALLINT

Database page size in bytes

MON$ODS_MAJOR

SMALLINT

Major ODS version, e.g., 11

MON$ODS_MINOR

SMALLINT

Minor ODS version, e.g., 2

MON$OLDEST_TRANSACTION

BIGINT

The number of the oldest [interesting] transaction (OIT)

MON$OLDEST_ACTIVE

BIGINT

The number of the oldest active transaction (OAT)

MON$OLDEST_SNAPSHOT

BIGINT

The number of the transaction that was active at the moment when the OAT was started — oldest snapshot transaction (OST)

MON$NEXT_TRANSACTION

BIGINT

The number of the next transaction, as it stood when the monitoring snapshot was taken

MON$PAGE_BUFFERS

INTEGER

The number of pages allocated in RAM for the database page cache

MON$SQL_DIALECT

SMALLINT

Database SQL Dialect: 1 or 3

MON$SHUTDOWN_MODE

SMALLINT

The current shutdown state of the database:

0 - the database is online
1 - multi-user shutdown
2 - single-user shutdown
3 - full shutdown

MON$SWEEP_INTERVAL

INTEGER

Sweep interval

MON$READ_ONLY

SMALLINT

Flag indicating whether the database is read-only (value 1) or read-write (value 0)

MON$FORCED_WRITES

SMALLINT

Indicates whether the write mode of the database is set for synchronous write (forced writes ON, value is 1) or asynchronous write (forced writes OFF, value is 0)

MON$RESERVE_SPACE

SMALLINT

The flag indicating reserve_space (value 1) or use_all_space (value 0) for filling database pages

MON$CREATION_DATE

TIMESTAMP WITH TIME ZONE

The date and time when the database was created or was last restored

MON$PAGES

BIGINT

The number of pages allocated for the database on an external device

MON$STAT_ID

INTEGER

Statistics identifier

MON$BACKUP_STATE

SMALLINT

Current physical backup (nBackup) state:

0 - normal
1 - stalled
2 - merge

MON$CRYPT_PAGE

BIGINT

Number of encrypted pages

MON$OWNER

CHAR(63)

Username of the database owner

MON$SEC_DATABASE

CHAR(7)

Displays what type of security database is used:

Default - default security database, i.e. {secdb}
Self - current database is used as security database
Other - another database is used as security database (not itself or {secdb})

MON$CRYPT_STATE

SMALLINT

Current state of database encryption

0 - not encrypted
1 - encrypted
2 - decryption in progress
3 - encryption in progress

MON$GUID

CHAR(38)

Database GUID (persistent until restore/fixup)

MON$FILE_ID

VARCHAR(255)

Unique ID of the database file at the filesystem level

MON$NEXT_ATTACHMENT

BIGINT

Current value of the next attachment ID counter

MON$NEXT_STATEMENT

BIGINT

Current value of the next statement ID counter

MON$REPLICA_MODE

SMALLINT

Database replica mode

0 - not a replica
1 - read-only replica
2 - read-write replica

MON$IO_STATS

MON$IO_STATS displays input/output statistics.The counters are cumulative, by group, for each group of 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$PAGE_READS

BIGINT

Count of database pages read

MON$PAGE_WRITES

BIGINT

Count of database pages written to

MON$PAGE_FETCHES

BIGINT

Count of database pages fetched

MON$PAGE_MARKS

BIGINT

Count of database pages marked

MON$MEMORY_USAGE

MON$MEMORY_USAGE displays memory usage 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 - operator
4 - call

MON$MEMORY_USED

BIGINT

The amount of memory in use, in bytes.This data is about the high-level memory allocation performed by the server.It can be useful to track down memory leaks and excessive memory usage in connections, procedures, etc.

MON$MEMORY_ALLOCATED

BIGINT

The amount of memory allocated by the operating system, in bytes.This data is about the low-level memory allocation performed by the Firebird memory manager — the amount of memory allocated by the operating system — which can allow you to control the physical memory usage.

MON$MAX_MEMORY_USED

BIGINT

The maximum number of bytes used by this object

MON$MAX_MEMORY_ALLOCATED

BIGINT

The maximum number of bytes allocated for this object by the operating system
Note

Counters associated with database-level records MON$DATABASE (MON$STAT_GROUP = 0), display memory allocation for all connections.In the Classic and SuperClassic zero values of the counters indicate that these architectures have no common cache.

Minor memory allocations are not accrued here but are added to the database memory pool instead.
Getting 10 requests consuming the most memory
SELECT
  STMT.MON$ATTACHMENT_ID,
  STMT.MON$SQL_TEXT,
  MEM.MON$MEMORY_USED
FROM MON$MEMORY_USAGE MEM
NATURAL JOIN MON$STATEMENTS STMT
ORDER BY MEM.MON$MEMORY_USED DESC
FETCH FIRST 10 ROWS ONLY

MON$RECORD_STATS

MON$RECORD_STATS displays record-level statistics.The counters are cumulative, by group, for each group of 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$RECORD_SEQ_READS

BIGINT

Count of records read sequentially

MON$RECORD_IDX_READS

BIGINT

Count of records read via an index

MON$RECORD_INSERTS

BIGINT

Count of inserted records

MON$RECORD_UPDATES

BIGINT

Count of updated records

MON$RECORD_DELETES

BIGINT

Count of deleted records

MON$RECORD_BACKOUTS

BIGINT

Count of records backed out

MON$RECORD_PURGES

BIGINT

Count of records purged

MON$RECORD_EXPUNGES

BIGINT

Count of records expunged

MON$RECORD_LOCKS

BIGINT

Number of records locked

MON$RECORD_WAITS

BIGINT

Number of update, delete or lock attempts on records owned by other active transactions.Transaction is in WAIT mode.

MON$RECORD_CONFLICTS

BIGINT

Number of unsuccessful update, delete or lock attempts on records owned by other active transactions.These are reported as update conflicts.

MON$BACKVERSION_READS

BIGINT

Number of back-versions read to find visible records

MON$FRAGMENT_READS

BIGINT

Number of fragmented records read

MON$RECORD_RPT_READS

BIGINT

Number of repeated reads of records

MON$RECORD_IMGC

BIGINT

Number of records processed by the intermediate garbage collector

MON$STATEMENTS

MON$STATEMENTS displays statements prepared for execution.

Column Name Data Type Description

MON$STATEMENT_ID

BIGINT

Statement identifier

MON$ATTACHMENT_ID

BIGINT

Connection identifier

MON$TRANSACTION_ID

BIGINT

Transaction identifier

MON$STATE

SMALLINT

Statement state:

0 - idle
1 - active
2 - stalled

MON$TIMESTAMP

TIMESTAMP WITH TIME ZONE

The date and time when the statement was prepared

MON$SQL_TEXT

BLOB TEXT

Statement text in SQL

MON$STAT_ID

INTEGER

Statistics identifier

MON$EXPLAINED_PLAN

BLOB TEXT

Explained execution plan

MON$STATEMENT_TIMEOUT

INTEGER

Connection-level statement timeout in milliseconds.When 0 is reported the timeout of MON$ATTACHMENT.MON$STATEMENT_TIMEOUT for this connection applies.

MON$STATEMENT_TIMER

TIMESTAMP WITH TIME ZONE

Statement timer expiration time

MON$COMPILED_STATEMENT_ID

BIGINT

Compiled statement id

The STALLED state indicates that, at the time of the snapshot, the statement had an open cursor and was waiting for the client to resume fetching rows.

Display active queries, excluding those running in your connection
SELECT
  ATT.MON$USER,
  ATT.MON$REMOTE_ADDRESS,
  STMT.MON$SQL_TEXT,
  STMT.MON$TIMESTAMP
FROM MON$ATTACHMENTS ATT
JOIN MON$STATEMENTS STMT ON ATT.MON$ATTACHMENT_ID = STMT.MON$ATTACHMENT_ID
WHERE ATT.MON$ATTACHMENT_ID <> CURRENT_CONNECTION
AND STMT.MON$STATE = 1

Using MON$STATEMENTS to Cancel a Query

Monitoring tables are read-only.However, the server has a built-in mechanism for deleting (and only deleting) records in the MON$STATEMENTS table, which makes it possible to cancel a running query.

Note
Notes

  • If no statements are currently being executed in the connection, any attempt to cancel queries will not proceed

  • After a query is cancelled, calling execute/fetch API functions will return an error with the isc_cancelled code

  • Subsequent queries from this connection will proceed as normal

  • Cancellation of the statement does not occur synchronously, it only marks the request for cancellation, and the cancellation itself is done asynchronously by the server
Example

Cancelling all active queries for the specified connection:

DELETE FROM MON$STATEMENTS
  WHERE MON$ATTACHMENT_ID = 32