onstat -t/-T Active TBLSpaces / All TBLSpaces

The onstat -t command displays TBLSpaces (system and user) that are currently being accessed by a userthread (i.e., ucnt > 0). The onstat -T command displays all TBLSpaces that have been opened.

Tblspaces 
 n address  flgs ucnt tblnum   physaddr npages nused  npdata nrows  nextns resident
 1 14a957c0 0    1    100001   10000e   350    350    0      0      3      0  
 4 14a95ac0 0    1    200001   200004   50     2      0      0      1      0 
 5 14a553f0 0    1    300001   300004   50     6      0      0      1      0 
10 14a55d30 0    1    400001   400004   450    442    0      0      9      0 
11 14ac3a70 0    2    400002   400005   24     21     10     156    3      0 
44 142cc840 0    1    400042   4002b5   24     18     17     17     3      0
....
 37 active, 164 total

Output Description

Heading	Description	Format	See Also
n	A sequential counter incremented for each table as it is opened.	Dec	onstat -g opn
address	The in-memory address of the tblspace.	Hex
flgs	Describes the status of the tblspace using the following hexadecimal values: 0x00000000 No modifications 0x00000001 Partition structure being initialized 0x00000002 Partition has been modified 0x00000004 Partition is being dropped 0x00000008 Pseudo table partition 0x00000010 Partition being altered (add/drop index) 0x00000020 Partition being altered by ALTER TABLE 0x00000040 Partition dropped with structs active 0x00000080 Dropping this partition while dbspace is down 0x00000100 Don't delete blobspace blobs when table dropped (used by alter fragment) 0x00000200 Partition alter page count updated 0x00000400 Pages altered to latest schema 0x00000800 System temp table 0x00001000 User temp table 0x00002000 Resident partition 0x00004000 Index operations deferred during recovery 0x00008000 Partition is being truncated 0x00010000 Partition is partially truncated	Hex
ucnt	The number of userthreads currently accessing the table.	Dec
tblnum	The tblspace number in hexadecimal format.	Hex	TBLSpace number
physaddr	The physical address of the tblspace on disk.	Hex
npages	The total number of pages allocated to the tblspace.	Dec
nused	The number of pages used of those allocated.	Dec
npdata	The number of used pages that are data pages.	Dec
nrows	The number of rows contained in the tblspace.	Dec
nextns	The number of extents for this tblspace.	Dec
resident	Indicates this tblspace is set for residency (0 or 0x2000).	Hex	onstat -P

Notes

TBLSpace 0x100001 is the root DBSpace TBLSpace partition. The first TBLSpace in each DBSpace will be a TBLSpace partition.

TBLSpace 0x100002 is the database TBLSpace.

Upon startup, the Informix engine opens the TBLSpace partition in each DBSpace.

The residency of a table must be re-established each time the Informix server is started.

Monitoring and Tuning

If there appears to be a sudden performance hit on the system, the onstat -t command can be used to monitor what tables are currently being accessed. This information can be used in conjunction with information provided by onstat -g sql to identify a potential problem table. An analysis of the nextns (number of extensions) column might identify a table with a large number of extensions.

One method for identifying tables with an excessive number of extents is to execute the following query against the sysmaster database:

SELECT dbsname, tabname, nextns
FROM systabnames t, sysptnhdr p
WHERE t.partnum = p.partnum
AND p.nextns > 10
ORDER BY 3 DESC;

37 active, 164 total