TOP をテンプレートにして作成
ホーム
バックアップ
一覧
検索
最終更新
ヘルプ
ログイン
開始行
Administrative Features
Table of Contents
Monitoring Tables
More Context Information
Firebird is gradually adding new features to assist in th...
Monitoring Tables
Dmitry Yemanov
The Concept
Scope and Security
Metadata
Usage
Cancel a Running Query
Performance Guidelines for Using MON$ Under Load
Firebird 2.1 introduces the ability to monitor server-sid...
The word “virtual” means that the table data is not mater...
Note
Virtual monitoring tables exist only in ODS 11.1 (and hig...
The Concept
The key term of the monitoring feature is an activity sna...
A snapshot is created the first time any of the monitorin...
In other words, the monitoring tables always behave like ...
To refresh the snapshot, the current transaction should b...
Scope and Security
Access to the monitoring tables is available in both DSQL...
Complete database monitoring is available to SYSDBA and t...
Regular users are restricted to the information about the...
Monitor Multiple Attachments
D. Yemanov
Under v.2.1.0 and 2.1.1, a non-SYSDBA user with more than...
Metadata
MON$DATABASE (connected database)
- MON$DATABASE_NAME (database pathname or alias)
- MON$PAGE_SIZE (page size)
- MON$ODS_MAJOR (major ODS version)
- MON$ODS_MINOR (minor ODS version)
- MON$OLDEST_TRANSACTION (OIT number)
- MON$OLDEST_ACTIVE (OAT number)
- MON$OLDEST_SNAPSHOT (OST number)
- MON$NEXT_TRANSACTION (next transaction number)
- MON$PAGE_BUFFERS (number of pages allocated in th...
- MON$SQL_DIALECT (SQL dialect of the database)
- MON$SHUTDOWN_MODE (current shutdown mode)
0: online
1: multi-user shutdown
2: single-user shutdown
3: full shutdown
- MON$SWEEP_INTERVAL (sweep interval)
- MON$READ_ONLY (read-only flag)
- MON$FORCED_WRITES (sync writes flag)
- MON$RESERVE_SPACE (reserve space flag)
- MON$CREATION_DATE (creation date/time)
- MON$PAGES (number of pages allocated on disk)
- MON$BACKUP_STATE (current physical backup state)
0: normal
1: stalled
2: merge
- MON$STAT_ID (statistics ID)
MON$ATTACHMENTS (connected attachments)
- MON$ATTACHMENT_ID (attachment ID)
- MON$SERVER_PID (server process ID)
- MON$STATE (attachment state)
0: idle
1: active
- MON$ATTACHMENT_NAME (connection string)
- MON$USER (user name)
- MON$ROLE (role name)
- MON$REMOTE_PROTOCOL (remote protocol name)
- MON$REMOTE_ADDRESS (remote address)
- MON$REMOTE_PID (remote client process ID)
- MON$REMOTE_PROCESS (remote client process pathname)
- MON$CHARACTER_SET_ID (attachment character set)
- MON$TIMESTAMP (connection date/time)
- MON$GARBAGE_COLLECTION (garbage collection flag)
- MON$STAT_ID (statistics ID)
columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains no...
column MON$REMOTE_PROCESS can contain a non-pathname valu...
column MON$GARBAGE_COLLECTION indicates whether GC is all...
MON$TRANSACTIONS (started transactions)
- MON$TRANSACTION_ID (transaction ID)
- MON$ATTACHMENT_ID (attachment ID)
- MON$STATE (transaction state)
0: idle (state after prepare, until execution b...
1: active (state during execution and fetch. I...
returns after cursor is closed)
- MON$TIMESTAMP (transaction start date/time)
- MON$TOP_TRANSACTION (top transaction)
- MON$OLDEST_TRANSACTION (local OIT number)
- MON$OLDEST_ACTIVE (local OAT number)
- MON$ISOLATION_MODE (isolation mode)
0: consistency
1: concurrency
2: read committed record version
3: read committed no record version
- MON$LOCK_TIMEOUT (lock timeout)
-1: infinite wait
0: no wait
N: timeout N
- MON$READ_ONLY (read-only flag)
- MON$AUTO_COMMIT (auto-commit flag)
- MON$AUTO_UNDO (auto-undo flag)
- MON$STAT_ID (statistics ID)
MON$TOP_TRANSACTION is the upper limit used by the sweepe...
MON$AUTO_UNDO indicates the auto-undo status set for the ...
MON$STATEMENTS (prepared statements)
- MON$STATEMENT_ID (statement ID)
- MON$ATTACHMENT_ID (attachment ID)
- MON$TRANSACTION_ID (transaction ID)
- MON$STATE (statement state)
0: idle
1: active
- MON$TIMESTAMP (statement start date/time)
- MON$SQL_TEXT (statement text, if appropriate)
- MON$STAT_ID (statistics ID)
column MON$SQL_TEXT contains NULL for GDML statements
columns MON$TRANSACTION_ID and MON$TIMESTAMP contain vali...
The execution plan and the values of parameters are not a...
MON$CALL_STACK (call stack of active PSQL requests)
- MON$CALL_ID (call ID)
- MON$STATEMENT_ID (top-level DSQL statement ID)
- MON$CALLER_ID (caller request ID)
- MON$OBJECT_NAME (PSQL object name)
- MON$OBJECT_TYPE (PSQL object type)
- MON$TIMESTAMP (request start date/time)
- MON$SOURCE_LINE (SQL source line number)
- MON$SOURCE_COLUMN (SQL source column number)
- MON$STAT_ID (statistics ID)
column MON$STATEMENT_ID groups call stacks by the top-lev...
columns MON$SOURCE_LINE and MON$SOURCE_COLUMN contain lin...
MON$IO_STATS (I/O statistics)
- MON$STAT_ID (statistics ID)
- MON$STAT_GROUP (statistics group)
0: database
1: attachment
2: transaction
3: statement
4: call
- MON$PAGE_READS (number of page reads)
- MON$PAGE_WRITES (number of page writes)
- MON$PAGE_FETCHES (number of page fetches)
- MON$PAGE_MARKS (number of pages with changes pend...
MON$RECORD_STATS (record-level statistics)
- MON$STAT_ID (statistics ID)
- MON$STAT_GROUP (statistics group)
0: database
1: attachment
2: transaction
3: statement
4: call
- MON$RECORD_SEQ_READS (number of records read sequ...
- MON$RECORD_IDX_READS (number of records read via ...
- MON$RECORD_INSERTS (number of inserted records)
- MON$RECORD_UPDATES (number of updated records)
- MON$RECORD_DELETES (number of deleted records)
- MON$RECORD_BACKOUTS (number of records where a ne...
record version or a change to an existing prim...
version is backed out due to rollback or savep...
- MON$RECORD_PURGES (number of records where record...
chain is being purged of versions no longer ne...
or younger transactions)
- MON$RECORD_EXPUNGES (number of records where reco...
chain is being deleted due to deletions by tra...
older than OAT)
Note
Textual descriptions of all “state” and “mode” values can...
Usage
Creation of a snapshot is usually quite a fast operation,...
A valid database connection is required in order to retri...
The system variables CURRENT_CONNECTION and CURRENT_TRANS...
Examples
Retrieve IDs of all CS processes loading CPU at the moment
SELECT MON$SERVER_PID
FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION
AND MON$STATE = 1
Retrieve information about client applications
SELECT MON$USER, MON$REMOTE_ADDRESS,
MON$REMOTE_PID,
MON$TIMESTAMP
FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION
Get isolation level of the current transaction
SELECT MON$ISOLATION_MODE
FROM MON$TRANSACTIONS
WHERE MON$TRANSACTION_ID = CURRENT_TRANSACTION
Get statements that are currently active
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
Retrieve call stacks for all connections
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
Cancel a Running Query
Runaway and long-running queries can now be cancelled fro...
There is no API function call directed at this feature. I...
Example
As a very rough example, the following statement will kil...
delete from mon$statements
where mon$attachment_id <> current_connection
Performance Guidelines for Using MON$ Under Load
The monitoring is implemented around two cornerstones: sh...
Shared Memory
All server processes share some region of memory where th...
Collection of the monitoring information and population o...
Notifications
Every server process has a flag that indicates its capabi...
Once every notified process has finished its update, the ...
Any processes that have been idle since the last monitori...
The “ready” flag is set as soon as something significant ...
Locking
While coordinating the write/read operations, the request...
Multiple simultaneous monitoring requests are serialized.
Limitations and Known Issues
In a heavily loaded system running Classic, monitoring re...
Note
Improved in v2.1.3.
Sometimes, an out-of-memory condition can cause monitorin...
In V.2.1.x, every blob occupies <page size> bytes of memo...
Another possible source of this memory exhaustion could b...
Note
There is no relief for this condition in v.2.1.3. However...
More Context Information
More context information about the server and database ('...
Example
SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION')
FROM RDB$DATABASE
For detailed information about using these context calls,...
最終行:
Administrative Features
Table of Contents
Monitoring Tables
More Context Information
Firebird is gradually adding new features to assist in th...
Monitoring Tables
Dmitry Yemanov
The Concept
Scope and Security
Metadata
Usage
Cancel a Running Query
Performance Guidelines for Using MON$ Under Load
Firebird 2.1 introduces the ability to monitor server-sid...
The word “virtual” means that the table data is not mater...
Note
Virtual monitoring tables exist only in ODS 11.1 (and hig...
The Concept
The key term of the monitoring feature is an activity sna...
A snapshot is created the first time any of the monitorin...
In other words, the monitoring tables always behave like ...
To refresh the snapshot, the current transaction should b...
Scope and Security
Access to the monitoring tables is available in both DSQL...
Complete database monitoring is available to SYSDBA and t...
Regular users are restricted to the information about the...
Monitor Multiple Attachments
D. Yemanov
Under v.2.1.0 and 2.1.1, a non-SYSDBA user with more than...
Metadata
MON$DATABASE (connected database)
- MON$DATABASE_NAME (database pathname or alias)
- MON$PAGE_SIZE (page size)
- MON$ODS_MAJOR (major ODS version)
- MON$ODS_MINOR (minor ODS version)
- MON$OLDEST_TRANSACTION (OIT number)
- MON$OLDEST_ACTIVE (OAT number)
- MON$OLDEST_SNAPSHOT (OST number)
- MON$NEXT_TRANSACTION (next transaction number)
- MON$PAGE_BUFFERS (number of pages allocated in th...
- MON$SQL_DIALECT (SQL dialect of the database)
- MON$SHUTDOWN_MODE (current shutdown mode)
0: online
1: multi-user shutdown
2: single-user shutdown
3: full shutdown
- MON$SWEEP_INTERVAL (sweep interval)
- MON$READ_ONLY (read-only flag)
- MON$FORCED_WRITES (sync writes flag)
- MON$RESERVE_SPACE (reserve space flag)
- MON$CREATION_DATE (creation date/time)
- MON$PAGES (number of pages allocated on disk)
- MON$BACKUP_STATE (current physical backup state)
0: normal
1: stalled
2: merge
- MON$STAT_ID (statistics ID)
MON$ATTACHMENTS (connected attachments)
- MON$ATTACHMENT_ID (attachment ID)
- MON$SERVER_PID (server process ID)
- MON$STATE (attachment state)
0: idle
1: active
- MON$ATTACHMENT_NAME (connection string)
- MON$USER (user name)
- MON$ROLE (role name)
- MON$REMOTE_PROTOCOL (remote protocol name)
- MON$REMOTE_ADDRESS (remote address)
- MON$REMOTE_PID (remote client process ID)
- MON$REMOTE_PROCESS (remote client process pathname)
- MON$CHARACTER_SET_ID (attachment character set)
- MON$TIMESTAMP (connection date/time)
- MON$GARBAGE_COLLECTION (garbage collection flag)
- MON$STAT_ID (statistics ID)
columns MON$REMOTE_PID and MON$REMOTE_PROCESS contains no...
column MON$REMOTE_PROCESS can contain a non-pathname valu...
column MON$GARBAGE_COLLECTION indicates whether GC is all...
MON$TRANSACTIONS (started transactions)
- MON$TRANSACTION_ID (transaction ID)
- MON$ATTACHMENT_ID (attachment ID)
- MON$STATE (transaction state)
0: idle (state after prepare, until execution b...
1: active (state during execution and fetch. I...
returns after cursor is closed)
- MON$TIMESTAMP (transaction start date/time)
- MON$TOP_TRANSACTION (top transaction)
- MON$OLDEST_TRANSACTION (local OIT number)
- MON$OLDEST_ACTIVE (local OAT number)
- MON$ISOLATION_MODE (isolation mode)
0: consistency
1: concurrency
2: read committed record version
3: read committed no record version
- MON$LOCK_TIMEOUT (lock timeout)
-1: infinite wait
0: no wait
N: timeout N
- MON$READ_ONLY (read-only flag)
- MON$AUTO_COMMIT (auto-commit flag)
- MON$AUTO_UNDO (auto-undo flag)
- MON$STAT_ID (statistics ID)
MON$TOP_TRANSACTION is the upper limit used by the sweepe...
MON$AUTO_UNDO indicates the auto-undo status set for the ...
MON$STATEMENTS (prepared statements)
- MON$STATEMENT_ID (statement ID)
- MON$ATTACHMENT_ID (attachment ID)
- MON$TRANSACTION_ID (transaction ID)
- MON$STATE (statement state)
0: idle
1: active
- MON$TIMESTAMP (statement start date/time)
- MON$SQL_TEXT (statement text, if appropriate)
- MON$STAT_ID (statistics ID)
column MON$SQL_TEXT contains NULL for GDML statements
columns MON$TRANSACTION_ID and MON$TIMESTAMP contain vali...
The execution plan and the values of parameters are not a...
MON$CALL_STACK (call stack of active PSQL requests)
- MON$CALL_ID (call ID)
- MON$STATEMENT_ID (top-level DSQL statement ID)
- MON$CALLER_ID (caller request ID)
- MON$OBJECT_NAME (PSQL object name)
- MON$OBJECT_TYPE (PSQL object type)
- MON$TIMESTAMP (request start date/time)
- MON$SOURCE_LINE (SQL source line number)
- MON$SOURCE_COLUMN (SQL source column number)
- MON$STAT_ID (statistics ID)
column MON$STATEMENT_ID groups call stacks by the top-lev...
columns MON$SOURCE_LINE and MON$SOURCE_COLUMN contain lin...
MON$IO_STATS (I/O statistics)
- MON$STAT_ID (statistics ID)
- MON$STAT_GROUP (statistics group)
0: database
1: attachment
2: transaction
3: statement
4: call
- MON$PAGE_READS (number of page reads)
- MON$PAGE_WRITES (number of page writes)
- MON$PAGE_FETCHES (number of page fetches)
- MON$PAGE_MARKS (number of pages with changes pend...
MON$RECORD_STATS (record-level statistics)
- MON$STAT_ID (statistics ID)
- MON$STAT_GROUP (statistics group)
0: database
1: attachment
2: transaction
3: statement
4: call
- MON$RECORD_SEQ_READS (number of records read sequ...
- MON$RECORD_IDX_READS (number of records read via ...
- MON$RECORD_INSERTS (number of inserted records)
- MON$RECORD_UPDATES (number of updated records)
- MON$RECORD_DELETES (number of deleted records)
- MON$RECORD_BACKOUTS (number of records where a ne...
record version or a change to an existing prim...
version is backed out due to rollback or savep...
- MON$RECORD_PURGES (number of records where record...
chain is being purged of versions no longer ne...
or younger transactions)
- MON$RECORD_EXPUNGES (number of records where reco...
chain is being deleted due to deletions by tra...
older than OAT)
Note
Textual descriptions of all “state” and “mode” values can...
Usage
Creation of a snapshot is usually quite a fast operation,...
A valid database connection is required in order to retri...
The system variables CURRENT_CONNECTION and CURRENT_TRANS...
Examples
Retrieve IDs of all CS processes loading CPU at the moment
SELECT MON$SERVER_PID
FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION
AND MON$STATE = 1
Retrieve information about client applications
SELECT MON$USER, MON$REMOTE_ADDRESS,
MON$REMOTE_PID,
MON$TIMESTAMP
FROM MON$ATTACHMENTS
WHERE MON$ATTACHMENT_ID <> CURRENT_CONNECTION
Get isolation level of the current transaction
SELECT MON$ISOLATION_MODE
FROM MON$TRANSACTIONS
WHERE MON$TRANSACTION_ID = CURRENT_TRANSACTION
Get statements that are currently active
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
Retrieve call stacks for all connections
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
Cancel a Running Query
Runaway and long-running queries can now be cancelled fro...
There is no API function call directed at this feature. I...
Example
As a very rough example, the following statement will kil...
delete from mon$statements
where mon$attachment_id <> current_connection
Performance Guidelines for Using MON$ Under Load
The monitoring is implemented around two cornerstones: sh...
Shared Memory
All server processes share some region of memory where th...
Collection of the monitoring information and population o...
Notifications
Every server process has a flag that indicates its capabi...
Once every notified process has finished its update, the ...
Any processes that have been idle since the last monitori...
The “ready” flag is set as soon as something significant ...
Locking
While coordinating the write/read operations, the request...
Multiple simultaneous monitoring requests are serialized.
Limitations and Known Issues
In a heavily loaded system running Classic, monitoring re...
Note
Improved in v2.1.3.
Sometimes, an out-of-memory condition can cause monitorin...
In V.2.1.x, every blob occupies <page size> bytes of memo...
Another possible source of this memory exhaustion could b...
Note
There is no relief for this condition in v.2.1.3. However...
More Context Information
More context information about the server and database ('...
Example
SELECT RDB$GET_CONTEXT('SYSTEM', 'ENGINE_VERSION')
FROM RDB$DATABASE
For detailed information about using these context calls,...
ページ名:
新規
名前変更
ホーム
一覧
検索
最終更新
バックアップ
ヘルプ
最終更新のRSS