FirebirdSQL logo
  • Anglaiskeyboard_arrow_down
 System TablesSecurity tables 

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

docnext count = 3

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