Querying System Temporary Tables
Go Up to Monitoring with System Temporary Tables
Clients can query these tables using SELECT statements, just as they would query any other table. By querying these tables, a rich collection of data about server performance and user behavior is available.
You cannot create or redefine temporary tables yourself.
For frequent monitoring, the best transaction control is to start the transaction as
READ_COMMITTED, READ_ONLY. Then commit it with COMMIT_RETAINING. This has the least impact on the system.Contents
Refreshing the Temporary Tables
To refresh the rows in the temporary tables, commit your transaction and perform the SELECT from the temporary tables again. InterBase automatically deletes the rows stored in temporary tables on a commit.
Listing the Temporary Tables
To display a list of these temporary tables, issue the following command in isql:
SHOW SYSTEM
The temporary tables are listed at the end of the system tables. To see the metadata for a particular table, issue:
SHOW TABLE tablename
The
SHOW SYSTEM command is available only in command-line isql, not in InterBase Windows isql.Security
Unlike system tables, which have a default access privilege of SELECT for PUBLIC users, the temporary tables have no default access by PUBLIC. The display and manipulation of this runtime information is restricted to SYSDBA and the database owner. These two users have the option of using the GRANT statement to allow access to other users. The statement can grant only SELECT privileges.
Examples of Querying System Temporary Tables
To illustrate the richness of the possibilities afforded by these temporary tables, here are some examples how you might query them.
Top ten SQL statements by execution :
SELECT a.tmp$user, s.tmp$timestamp, s.tmp$sql, s.tmp$quantum FROM TMP$STATEMENTS s, TMP$ATTACHMENTS a WHERE a.TMP$ATTACHMENT_ID = s.TMP$ATTACHMENT_ID ORDER BY s.TMP$QUANTUM DESC ROWS 10;
Top ten oldest transaction snapshots:
SELECT a.TMP$USER, t.TMP$TIMESTAMP, t.TMP$TRANSACTION_ID, t.TMP$SNAPSHOT FROM TMP$ATTACHMENTS a, TMP$TRANSACTIONS t WHERE a.TMP$ATTACHMENT_ID = t.TMP$ATTACHMENT_ID ORDER BY t.TMP$SNAPSHOT ROWS 10;
Top ten tables with the most garbage to clean up:
SELECT TMP$RELATION_NAME, TMP$GARBAGE_COLLECT_PAGES FROM TMP$RELATIONS ORDER BY TMP$GARBAGE_COLLECT_PAGES DESC ROWS 10;
Top ten most executed stored procedures:
SELECT TMP$PROCEDURE_NAME, TMP$INVOCATIONS FROM TMP$PROCEDURES ORDER BY TMP$INVOCATIONS DESC ROWS 10;
Is database sweep active and what's its progress?:
SELECT TMP$SWEEP_RELATION, TMP$SWEEP_RECORDS FROM TMP$DATABASE WHERE TMP$SWEEP_ACTIVE = 'Y';
Pool memory allocations grouped by pool type:
SELECT TMP$TYPE, SUM(TMP$POOL_MEMORY) TMP$TOTAL_MEMORY, SUM(TMP$FREE_MEMORY) TMP$TOTAL_FREE FROM TMP$POOLS GROUP BY TMP$TYPE ORDER BY 2 DESC;