onstat -I command: Print general statistics about index operations
Use the onstat -I command to display instance-wide information about index operations.
B-Tree Structure
A b-tree is a common way of organizing index pages into a structure that is self-balancing and efficient for multiple processes to search simultaneously.
In an Informix b-tree each page is called a node, and each node contains one or more index items. Each item is associated with a key value and points to a data row. Nodes are linked to each other, often in multiple directions, and in the abstract the picture formed is one of an upside down tree, with a single root node on top and a layer of leaf nodes on the bottom. In between may be one or more layers of twig nodes.
The number of levels and nodes in the tree depend on the number of rows in the table and the size of the index key. As the number of rows increases or decreases the number of nodes and levels in the tree will grow or shrink accordingly. Use the oncheck -pT command to see the number of levels in an individual index and statistics about each level.
The root node is the parent of the nodes in the level below it. Each of those nodes is the parent of some number of nodes in the level below that, and so on. Because leaf nodes are in the lowest level they cannot be parents.
B-Tree Operations
A b-tree merge operation is one in which two index nodes in the same level are combined into one, with all the index items from the right-hand node being moved onto left-hand node. The empty node is then unlinked from the tree and the page is marked free in the partition’s bitmap.
A b-tree split operation is the opposite of a merge. A new node is added to the right of a nearly-full node and about half of the index items are moved to the new node.
A b-tree shuffle operation balances the number of items between two nodes, moving some from the right-hand node to the left-hand node, without removing either node from the tree. A shuffle operation now occurs only when an index is being externally compressed using the SQL administration API’s index compress argument.
An internal b-tree compress operation predates the externally-triggered index compression feature mentioned above, and is unrelated to it. It is simply an attempted merge operation. It may or may not result in a change to the index structure.
A b-tree copyback operation is an update to the parent node when merges or splits on the level below render one of the parent’s key values out of date.
Whereas a b-tree search moves downward through the tree, a scan moves sideways through one of the levels, normally proceeding from left to right (lower values to higher values). A b-tree scan that moves from right to left is called a reverse scan.
Example output
The output provides an overview of index operations.
Index statistics
search additem delitem retry rev_scans rs_retries
60320 75783 8153 6 23 0
splits page slot root copyback
980 938 21 184 0
compress merges shuffles root
38 3 0 0
Output description
- search
- Number of times a b-tree has been searched. This number may be significantly higher than the number of queries that have used indexes.
- additem
- Number of times an item has been inserted into a b-tree.
- delitem
- Number of times an item has been either removed from a b-tree, or marked deleted.
- retry
- Number of times a search down the tree has had to start over from a higher position due to volatility on the levels below.
- rev_scans
- Number of reverse scans.
- rs_retries
- Number of times a reverse scan has had to start over due to contention.
- splits
- Total number of times a node has been split.
- page
- Splits of a node that contained multiple key values.
- slot
- Splits of a node that contained only one key value and many duplicates.
- root
- Splits of a root node, which occur temporarily when a new b-tree level is added below the root level due to index growth.
- copyback
- Number of times a parent node has been updated with a new key value due to splits or merges on the level below.
- compress
- Number of times a merge has been attempted.
- merges
- Number of successful merges.
- shuffles
- Number of shuffles, which occur only during an index compress operation.
- root
- Root node shrinks, which are the opposite of splits. They occur when the number of index levels is reduced due to item deletions.