TMP$INDICES

Go Up to System Temporary Tables

TMP$INDICES is a new system table included as part of the Performance Monitoring system tables introduced in InterBase 2020 with ODS version 18. This table tracks all loaded indices per table in the database with key metrics for monitoring. The table structure is as follows.

Note: Currently, only SQL queries support TMP$INDICES. IBConsole Performance Monitoring UI does not yet enable a TMP$INDICES tab.

Column name	Datatype for ODS >= 18, dialect 1	Datatype for ODS >= 18, dialect 3	Description
TMP$DATABASE_ID	INTEGER	INTEGER	Database identifier
TMP$RELATION_NAME	CHAR (67)	CHAR (67)	Relation name
TMP$INDEX_NAME	CHAR (67)	CHAR (67)	Index name
TMP$INDEX_TYPE	CHAR (31)	CHAR (31)	Index Type; types include PRIMARY KEY, FOREIGN KEY, UNIQUE, NON-UNIQUE, EXPRESSION
TMP$INDEX_SEGMENTS	SMALLINT	SMALLINT	Number of segments/columns in the index definition
TMP$INDEX_MAX_KEYSIZE	SMALLINT	SMALLINT	Maximum index key size allowed in the index
TMP$INDEX_DEPTH	SMALLINT	SMALLINT	Depth of the index B-tree structure. Larger the depth, more the time taken to fetch a record. Consider increasing database page size to reduce depth.
TMP$INVOCATIONS	DOUBLE PRECISION	NUMERIC (18,0)	Number of requests that have used this index for retrieval
TMP$PAGE_READS	DOUBLE PRECISION	NUMERIC (18,0)	Not used, yet.
TMP$PAGE_WRITES	DOUBLE PRECISION	NUMERIC (18,0)	Number of index page/buckets that have been written to
TMP$PAGE_FETCHES	DOUBLE PRECISION	NUMERIC (18,0)	Number of index page/buckets that have been fetched
TMP$PAGE_SPLITS	DOUBLE PRECISION	NUMERIC (18,0)	Number of index page/buckets that have been split to accommodate a new index node insertion
TMP$PAGE_REVERSE_SPLITS	DOUBLE PRECISION	NUMERIC (18,0)	Number of times 2 less-populated index page/buckets have been combined
TMP$PAGE_NAVIGATIONS	DOUBLE PRECISION	NUMERIC (18,0)	Number of index page/buckets that have been read for a navigation request; ORDER BY
TMP$RECORD_INSERTS	DOUBLE PRECISION	NUMERIC (18,0)	Number of new index nodes inserted
TMP$RECORD_UPDATES	DOUBLE PRECISION	NUMERIC (18,0)	Number of index nodes updated; updates are a combination of 1 delete (of old key) and 1 insert (of new key)
TMP$RECORD_DELETES	DOUBLE PRECISION	NUMERIC (18,0)	Number of index nodes deleted; sometimes reflect a negative number if update related delete node have not been garbage collected yet
TMP$NODE_WALKS	DOUBLE PRECISION	NUMERIC (18,0)	Number of index nodes traversed in total, in all levels (depth); include nonleaf (pointer pages) and leaf (record nodes) page nodes
TMP$NONLEAF_NODE_WALKS	DOUBLE PRECISION	NUMERIC (18,0)	Number of nonleaf index page nodes traversed/walked
TMP$LEAF_NODE_WALKS	DOUBLE PRECISION	NUMERIC (18,0)	Number of leaf index page nodes traversed/walked
TMP$EQUALITY_MATCHES	DOUBLE PRECISION	NUMERIC (18,0)	Number of index nodes/records retrieved for an equality match; "a = b", JOIN, IN list, etc.
TMP$RANGE_MATCHES	DOUBLE PRECISION	NUMERIC (18,0)	Number of index nodes/records retrieved for a range retrieval; BETWEEN, "a > b", etc.

Usage

List all indices for a specific relation/table

 
SELECT * FROM TMP$INDICES
 WHERE TMP$RELATION_NAME='foo';

List all indices with depth greater than 3

  
SELECT * FROM TMP$INDICES
 WHERE TMP$INDEX_DEPTH > 3;

List all indices with more than 10 segments

    
SELECT * FROM TMP$INDICES
 WHERE TMP$INDEX_SEGMENTS > 10;

List all indices that are index type 'FOREIGN KEY'

    
SELECT * FROM TMP$INDICES
 WHERE TMP$INDEX_TYPE='FOREIGN KEY';

List all indices with tons of delete operations leading to reverse page splits. This is a good indicator that index selectivity needs to be recalculated.

    
SELECT * FROM TMP$INDICES
 WHERE TMP$PAGE_REVERSE_SPLITS > 1000;

List all indices with tons of insert/update/delete operations; similar to above. This is a good indicator that index selectivity needs to be recalculated.

    
SELECT * FROM TMP$INDICES
 WHERE TMP$RECORD_INSERTS > 1000
    OR TMP$RECORD_UPDATES > 1000
    OR TMP$RECORD_DELETES > 1000;

List all indices that have very low activity; could indicate unnecessary index definitions, or, UNIQUE index on the same table where a PRIMARY KEY exists on the same set of columns. The SQL optimizer will typically use only one of PRIMARY KEY or UNIQUE index and seldom use the other. In this case, you may want to drop the UNIQUE index, and let the PRIMARY KEY remain.

    
SELECT * FROM TMP$INDICES
 WHERE TMP$PAGE_FETCHES < 100; /* set to your needs */

Advance To:

• System Temporary Tables