onstat Portal: onstat Utility Commands Sorted by Functional Category

Each category represents a different HCL OneDB™ feature for which onstat commands are useful for providing troubleshooting and performance enhancement information. Commands that appear in bold typeface are especially useful for providing troubleshooting information. Certain onstat commands are specific to one category, while others provide more general information and are listed in more than one category.

Category List

Determine the appropriate category from the following list, then follow the link to the onstat options for that category.

onstat Utility Archive Information Options
onstat Utility Cache Information Options
onstat Utility Compression Options
onstat Utility Debugging Options
onstat Utility Enterprise Replication Options
onstat Utility High-Availability Replication Options
onstat Utility I/O Options
onstat Utility Locks and Latches Options
onstat Utility Logs Options
onstat Utility Memory Options
onstat Utility Network Options
onstat Utility Performance Checks (First Tier)
onstat Utility Performance Checks (Second Tier)
onstat Utility Table Options
onstat Utility Thread Options
onstat Utility User/Session Options
onstat Utility Virtual Processor Options
onstat Utility Waiting Options
Other Useful onstat Utility Options

onstat Utility Archive Information Options

Use the following onstat options to display information about archives and restores.

Table 1. onstat Utility Archive Information Options
Commands	Reference
onstat -D	Prints chunk I/O activity. Prints dbspace read/write activity for monitoring restore progress. onstat -D command: Print page-read and page-write information
onstat -g arc	Prints the last committed and any ongoing backups for each dbspace. onstat -g arc command: Print archive status

onstat Utility Cache Information Options

Use the following onstat options to display information about caches and cached data, including buffer pools.

Table 2. onstat Utility Cache Information Options
Commands	Reference
onstat -b	Prints buffer pages in use. onstat -b command: Print buffer information for buffers in use
onstat -B	Prints information about used buffers. onstat -B command: Prints information about used buffers
onstat -F	Prints state of buffer queue cleaners and I/O. onstat -F command: Print counts
onstat -g cac	Prints summary and detailed information about all memory caches or about the specified cache. onstat -g cac command: Print information about caches
onstat -g dic	Prints data dictionary cache, containing system catalog data for tables. Prints one line of information for each table that is cached in the shared-memory dictionary. onstat -g dic command: Print table information
onstat -g dsc	Prints table distribution statistics for the optimizer. onstat -g dsc command: Print distribution cache information.
onstat -g prc	Prints the stored procedure (SPL) routine cache. Prints information about SPL routine cache. onstat -g prc command: Print sessions using UDR or SPL routines
onstat -g ssc	Prints the number of times that the database server reads the SQL statement in the cache. Displays the same output as onstat -g cac. For more information, see improving query performance in the HCL OneDB Performance Guide. onstat -g ssc command: Print SQL statement occurrences
onstat -g vpcache	Prints CPU virtual processor memory cache. onstat -g vpcache command: Print CPU virtual processor and tenant virtual processor private memory cache statistics
onstat -h	Prints buffer hash chain information. onstat -h command: Print buffer header hash chain information
onstat -O	Prints optical subsystem memory cache and staging-area (disk cache) blobspace for TEXT or BYTE data. onstat -O command: Print optical subsystem information
onstat -p	Prints global (server) information regarding the effectiveness of buffer pool caching. onstat -p command: Print profile counts
onstat -X	Prints threads that are waiting for buffers. onstat -X command: Print thread information

onstat Utility Compression Options

Use the following onstat options to print compression information.

Table 3. onstat Utility Compression Options
Commands	Reference
onstat -g dsk	Prints progress of currently running compression operations. onstat -g dsk command: Print the progress of the currently running compression operation
onstat -g ppd	Prints partition compression dictionary information. onstat -g ppd command: Print partition compression dictionary information

onstat Utility Debugging Options

Use the following onstat options to display information that is useful for debugging problems with the server.

Table 4. onstat Utility Debugging Options
Commands	Reference
onstat -g dmp	Prints raw memory at a specified address for a number of given bytes. onstat -g dmp command: Print raw memory
onstat -g src	Searches for patterns in shared memory. Note that memory is byte-swapped on Intel™ platforms. onstat -g src command: Patterns in shared memory
onstat -o	Prints shared memory contents to a file. onstat -o command: Output shared memory contents to a file.

onstat Utility Enterprise Replication Options

Use the following onstat options to track Enterprise Replication statistics and to provide troubleshooting information. For additional information about Enterprise Replication see the cdr view and cdr view profile commands that are described in the HCL OneDB Enterprise Replication Guide.

Table 5. onstat Utility Enterprise Replication Options
Commands	Reference
onstat -g cat	Prints information from the Enterprise Replication global catalog. The global catalog contains a summary of information about the defined servers, replicates, and replicate sets on each of the servers within the enterprise. onstat -g cat: Print ER global catalog information
onstat -g cdr	Prints the output for all of the Enterprise Replication statistics commands. onstat -g cdr: Print ER statistics
onstat -g cdr config	Prints Enterprise Replication configuration parameters and environment variables. onstat -g cdr config: Print ER settings
onstat -g ddr	Prints status of Enterprise Replication components that read and process log records. onstat -g ddr: Print status of ER log reader
onstat -g dss	Prints activity of individual data sync (transaction processing) threads. onstat -g dss: Print statistics for data sync threads
onstat -g dtc	Prints delete table cleaner activity. Deleted or updated rows that are placed in the delete table are purged at intervals. onstat -g dtc: Print statistics about delete table cleaner
onstat -g grp	Prints Enterprise Replication grouper statistics. The grouper evaluates the log records, rebuilds the individual log records into the original transaction, packages the transaction, and queues the transaction for transmission. onstat -g grp: Print grouper statistics
onstat -g nif	Prints network interface statistics. Shows the state of the network interface, servers, and data transfer among servers. onstat -g nif: Print statistics about the network interface
onstat -g que	Prints statistics for the high-level queue interface (which is common to all of the queues of the Enterprise Replication Queue Manager). onstat -g que: Print statistics for all ER queues
onstat -g rcv	Prints receive manager statistics. onstat -g rcv: Print statistics about the receive manager
onstat -g rep	Prints events that are in the queue for the schedule manager. onstat -g rep: Prints the schedule manager queue
onstat -g rqm	Prints statistics and contents of the low-level queues (send queue, receive queue, ack send queue, sync send queue, and control send queue) managed by the Reliable Queue Manager (RQM). onstat -g rqm: Prints statistics for RQM queues
onstat -g sync	Prints synchronization status. onstat -g sync: Print statistics about synchronization

onstat Utility High-Availability Replication Options

Use the following onstat options to monitor high-availability cluster environments and the Connection Manager.

Table 6. onstat Utility High-Availability Replication Options
Commands	Reference
onstat -g cluster	Prints high-availability cluster information. onstat -g cluster command: Print high-availability cluster information
onstat -g cmsm	Prints Connection Manager information. onstat -g cmsm command: Print Connection Manager information
onstat -g dri	Prints data-replication information. See Monitoring High-Availability Data-Replication status in the HCL OneDB Administrator's Guide. onstat -g dri command: Print high-availability data replication information.
onstat -g ipl	Prints index page logging status. onstat -g ipl command: Print index page logging status information
onstat -g laq	Prints information about log recovery apply queues. onstat -g laq command: Print log apply queues
onstat -g proxy	Prints proxy distributors for high-availability. onstat -g proxy command: Print proxy distributor information
onstat -g rss	Prints remote stand-alone server (RSS) information. onstat -g rss command: Print RS secondary server information
onstat -g sds	Prints shared disk secondary (SDS) server information. onstat -g sds command: Print SD secondary server information
onstat -g smx	Prints Server Multiplexer Group (SMX) connections in high-availability environments. Prints data transfer statistics and encryption status.Prints data transfer statistics. onstat -g smx command: Print multiplexer group information

onstat Utility I/O Options

Use the following onstat options to track input and output (read and write) activity.

Table 7. onstat Utility I/O Options
Commands	Reference
onstat -D	Prints chunk I/O activity. onstat -D command: Print page-read and page-write information
onstat -g cpu	Prints runtime statistics for each thread. onstat -g cpu: Print runtime statistics
onstat -g ioa	Prints combined information from onstat -g ioq (queues), onstat -g iov (virtual processors), and onstat -g iob (big buffer). onstat -g ioa command: Print combined onstat -g information
onstat -g iob	Prints the big buffer usage summary. onstat -g iob command: Print big buffer use summary
onstat -g iof	Prints I/O statistics by file or chunk. This option is similar to the onstat -D option, but also displays information about non-chunk, temporary, and sort-work files. onstat -g iof command: Print asynchronous I/O statistics
onstat -g iog	Prints AIO global information. onstat -g iog command: Print AIO global information
onstat -g ioq	Prints queue read/write statistics and queue length. onstat -g ioq command: Print I/O queue information. Also see the HCL OneDB Performance Guide.
onstat -g iov	Prints asynchronous I/O statistics by virtual processor. onstat -g iov command: Print AIO VP statistics
onstat -p	Prints global disk activity, including sequential scans. onstat -p command: Print profile counts

onstat Utility Locks and Latches Options

Use the following onstat options to display information about locks.

Table 8. onstat Utility Locks and Latches Options
Commands	Reference
onstat -k	Prints information about active locks. onstat -k command: Print active lock information
onstat -L	Prints the number of locks on a lock free list. onstat -L command: Print the number of free locks
onstat -p	Prints global statistics on lock requests, lock waits, and latch waits. onstat -p command: Print profile counts
onstat -s	Prints latch (mutex) information. onstat -s command: Print latch information

onstat Utility Logs Options

Use the following onstat options to monitor logical and physical logs.

Table 9. onstat Utility Logs Options
Commands	Reference
onstat -g ipl	Prints index page logging information in high-availability environments. onstat -g ipl command: Print index page logging status information
onstat -l	Prints status of physical and logical logs, and log buffering. onstat -l command: Print physical and logical log information

onstat Utility Memory Options

Use the following onstat options to monitor the various aspects of server memory allocation and use.

Table 10. onstat Utility Memory Options
Commands	Reference
onstat -g afr	Prints allocated memory fragments for a specified session or shared-memory pool. To obtain the pool name, see the onstat -g mem option. onstat -g afr command: Print allocated memory fragments
onstat -g ffr (`pool name session ID`)	Prints free fragments for a session or shared memory pool. onstat -g ffr command: Print free fragments
onstat -g lmm	Prints information about automatic low memory management settings and recent activity: onstat -g lmm command: Print low memory management information
onstat -g mem	Prints session or pool virtual shared memory statistics. onstat -g mem command: Print pool memory statistics
onstat -g mgm	Prints Memory Grant Manager (parallel and sort operations) resource information. onstat -g mgm command: Print MGM resource information. Also see the HCL OneDB Performance Guide.
onstat -g nbm	Prints block map for non-resident segments. onstat -g nbm command: Print a block bit map
onstat -g rbm	Prints block map for resident segment. onstat -g rbm command: Print a block map of shared memory
onstat -g seg	Prints memory segment statistics. onstat -g seg command: Print shared memory segment statistics. Also see the HCL OneDB Administrator's Guide.
onstat -g ses	Prints session information, including memory breakdown. For detailed information, use: onstat -g ses `session_id` onstat -g ses command: Print session-related information Also see the HCL OneDB Performance Guide
onstat -g stm	Prints SQL statement memory use. onstat -g stm command: Print SQL statement memory usage
onstat -g stq	Prints stream queue buffers. onstat -g stq command: Print queue information
onstat -g ufr	Prints memory pool fragments for a session or shared memory pool in use. onstat -g ufr command: Print memory pool fragments
onstat -R	Prints buffer pool queues and their status. onstat -R command: Print LRU, FLRU, and MLRU queue information

onstat Utility Network Options

Use the following onstat options to monitor shared memory and network connection services.

Table 11. onstat Utility Network Options
Commands	Reference
onstat -g nsc	Prints shared-memory status by `client id`. If `client id` is omitted, all client status areas are displayed. This command prints the same status data as the nss command. onstat -g nsc command: Print current shared memory connection information
onstat -g nsd	Prints network shared-memory data for poll threads. onstat -g nsd command: Print poll threads shared-memory data
onstat -g nss	Prints network shared-memory status by `session id`. If `session id` is omitted, all session status areas are displayed. This command prints the same status data as the onstat -g nsc command. onstat -g nss command: Print shared memory network connections status
onstat -g ntd	Prints network statistics by service. onstat -g ntd command: Print network statistics
onstat -g ntm	Prints network mail statistics. onstat -g ntm command: Print network mail statistics
onstat -g ntt	Prints network user times. onstat -g ntt command: Print network user times
onstat -g ntu	Prints network user statistics. onstat -g ntu command: Print network user statistics

onstat Utility Performance Checks (First Tier)

Use the following onstat options to monitor performance and to check for performance impediments. Use the second-tier onstat options (and other onstat commands) to further narrow the problem.

Table 12. onstat Utility Performance Checks (First Tier)
Commands	Reference
onstat -c	Prints server configuration. onstat -c command: Print ONCONFIG file contents
onstat -D	Prints chunk I/O. onstat -D command: Print page-read and page-write information
onstat -g ath	Prints status and statistics for all threads. The sqlexec thread is a client session thread. The rstcb value corresponds to the user field of the onstat -u command. onstat -g ath command: Print information about all threads. For information about using onstat -g ath to print Enterprise Replication threads, see the HCL OneDB Enterprise Replication Guide.
onstat -g ckp	Prints checkpoint history and display configuration recommendations. onstat -g ckp command: Print checkpoint history and configuration recommendations
onstat -g cpu	Prints runtime statistics for each thread. onstat -g cpu: Print runtime statistics
onstat -g ioq	Prints pending I/O operations for the `queue name`. onstat -g ioq command: Print I/O queue information
onstat -p	Prints global server performance profile. onstat -p command: Print profile counts
onstat -u	Prints status and statistics for user threads. If a thread is waiting for a resource, this command identifies the type (flags field) and address (wait field) of the resource. onstat -u command: Print user activity profile

onstat Utility Performance Checks (Second Tier)

Use the following onstat options to identify performance impediments.

Table 13. onstat Utility Performance Checks (Second Tier)
Commands	Reference
onstat -b	Prints active buffers. onstat -b command: Print buffer information for buffers in use
onstat -g act	Prints active threads. onstat -g act command: Print active threads
onstat -g glo	Prints virtual processors and their operating system processes (oninit processes). Prints virtual processor CPU use. On Windows™, the virtual processors are operating system threads, and the values in the pid field are thread IDs. onstat -g glo command: Print global multithreading information
onstat -g mgm	Prints Memory Grant Manager resource information. onstat -g mgm command: Print MGM resource information
onstat -g rah	Prints read-ahead request information onstat -g rah command: Print read-ahead request statistics
onstat -g rea	Prints threads in the ready queue that are waiting for CPU resources. onstat -g rea command: Print ready threads
onstat -g seg	Prints shared-memory-segment statistics. This option shows the number and size of shared-memory segments that are allocated to the database server. onstat -g seg command: Print shared memory segment statistics.
onstat -g wai	Prints waiting threads; all threads that are waiting for mutex or condition, or yielding. onstat -g wai command: Print wait queue thread list
onstat -k	Prints active locks. onstat -k command: Print active lock information

onstat Utility Table Options

Use the following onstat options to display information about table status and table statistics.

Table 14. onstat Utility Table Options
Commands	Reference
onstat -g buf	Prints buffer pool profile information. onstat -g buf command: Print buffer pool profile information
onstat -g lap	Prints information about the status of currently active light appends (writes bypassing the buffer pool). onstat -g lap command: Print light appends status information
onstat -g opn	Prints open partitions (tables). onstat -g opn command: Print open partitions
onstat -g ppf	Prints partition profile (activity data) for the specified partition number or prints profiles for all partitions. onstat -g ppf command: Print partition profiles
onstat -g scn	Prints information about the progress of a scan, based on rows scanned on compressed tables, tables with rows that are larger than a page, and tables with VARCHAR, LVARCHAR, and NVARCHAR data, and identifies whether a scan is a light or bufferpool scan. onstat -g scn command: Print scan information
onstat -P	Prints table and B-tree pages in the buffer pool, listed by partition (table). onstat -P command: Print partition information
onstat -t onstat -T	Prints basic tblspace (partition) information for active (t) or all (T) tblspaces. onstat -t and onstat -T commands: Print tblspace information

onstat Utility Thread Options

Use the following onstat options to display the status and activity of threads.

Table 15. onstat Utility Thread Options
Commands	Reference
onstat -g act	Prints active threads. This output is included in onstat -g ath output. onstat -g act command: Print active threads
onstat -g ath	Prints all threads. onstat -g ath command: Print information about all threads. For information about using onstat -g ath to print Enterprise Replication threads, see the HCL OneDB Enterprise Replication Guide.
onstat -g bth	Displays the dependencies between blocking and waiting threads. onstat -g bth and -g BTH: Print blocked and waiting threads
onstat -g BTH	Displays session and stack information for the blocking threads. onstat -g bth and -g BTH: Print blocked and waiting threads
onstat -g cpu	Prints runtime statistics for each thread. onstat -g cpu: Print runtime statistics
onstat -g rea	Prints ready threads (threads that are waiting for CPU resources). This output is included in the onstat -g ath output. onstat -g rea command: Print ready threads.
onstat -g sle	Prints information about threads that are sleeping for a specified time. Does not include threads that are sleeping forever. onstat -g sle command: Print all sleeping threads
onstat -g stk	Prints the stack of a specified thread or prints stacks for all threads. onstat -g stk command: Print thread stack
onstat -g sts	Prints maximum and current stack use per thread. onstat -g sts command: Print stack usage for each thread
onstat -g tpf	Prints thread activity statistics. onstat -g tpf command: Print thread profiles
onstat -g wai	Prints waiting (idle, sleeping, and waiting) threads. Included in onstat -g ath output. onstat -g wai command: Print wait queue thread list
onstat -g wst	Prints wait statistics for threads. onstat -g wst command: Print wait statistics for threads

onstat Utility User/Session Options

Use the following onstat options to display information about the user environment and active sessions.

Table 16. onstat Utility User/Session Options
Commands	Reference
onstat -g env	Prints the values of environment variables the database server is using. onstat -g env command: Print environment variable values
onstat -g his	Prints SQL tracing information. onstat -g his command: Print SQL trace information
onstat -g pqs	Prints operators that are used in currently running SQL queries. onstat -g pqs command: Print operators for all SQL queries
onstat -g ses	Prints summary information for all active sessions or detailed information for individual sessions. onstat -g ses command: Print session-related information
onstat -g spf	Prints prepared statement profiles for all active sessions. onstat -g spf: Print prepared statement profiles
onstat -g sql	Prints SQL information for all active sessions or detailed SQL information for individual sessions. onstat -g sql command: Print SQL-related session information
onstat -G	Prints global transactions. onstat -G command: Print TP/XA transaction information
onstat -u	Prints status of user threads and their global read/write statistics. onstat -u command: Print user activity profile
onstat -x	Prints information about transactions. onstat -x command: Print database server transaction information

onstat Utility Virtual Processor Options

Use the following onstat options to display information and statistics for virtual processors.

Table 17. onstat Utility Virtual Processor Options
Commands	Reference
onstat -g glo	Prints global multithreading information and global statistics for virtual processor classes and individual virtual processors. On Windows, the virtual processors are operating system threads, and the values in the pid field are thread IDs. onstat -g glo command: Print global multithreading information
onstat -g sch	Prints the number of semaphore operations, spins, and busy waits for each virtual processor. On Windows, the virtual processors are operating system threads, and the values in the pid field are thread IDs. onstat -g sch command: Print VP information

onstat Utility Waiting Options

Use the following onstat options to display information about wait conditions for threads.

Table 18. onstat Utility Waiting Options
Commands	Reference
onstat -g con	Prints IDs of threads that are waiting for conditions. onstat -g ath to print thread information. See onstat -g con command: Print condition and thread information
onstat -g lmx	Prints all locked mutexes. onstat -g lmx command: Print all locked mutexes
onstat -g qst	Prints queue-wait statistics for mutex and condition queues. onstat -g qst command: Print wait options for mutex and condition queues
onstat -g rwm	Prints read/write mutexes. onstat -g rwm command: Print read and write mutexes
onstat -g spi	Prints spin locks with long spins and spin lock statistics. onstat -g spi command: Print spin locks with long spins
onstat -g wai	Prints waiting threads; all threads that are waiting for mutex or condition, or yielding. onstat -g wai command: Print wait queue thread list
onstat -g wmx	Prints all mutexes with waiters. onstat -g wmx command: Print all mutexes with waiters

Other Useful onstat Utility Options

Table 19. Other Useful onstat Utility Options
Commands	Reference
onstat -	Prints onstat header; includes engine version, status (online, Quiescent, and so on), elapsed time since initialization, and memory footprint. onstat - command: Print output header
onstat	Prints onstat usage options. onstat -- command: Print onstat options and functions
onstat `options infile`	Print onstat output using a shared memory dump (infile) as input. Running onstat Commands on a Shared Memory Dump File
onstat -a	Prints collective onstat outputs. onstat -a command: Print overall status of the database server
onstat -c	Prints the server configuration file. onstat -c command: Print ONCONFIG file contents
onstat -C	Prints B-tree index scanner information (shows statistics about index cleaning). onstat -C command: Print B-tree scanner information
onstat -d	Prints chunk information. onstat -d command: Print chunk information
onstat -f	Prints dbspaces configured for dataskip. onstat -f command: Print dbspace information affected by dataskip
onstat -g all	Prints diagnostic information. onstat -g all command: Print diagnostic information
onstat -g cfg	Prints a list of configuration parameters with their current values. onstat -g cfg command: Print the current values of configuration parameters
onstat -g dbc	Prints statistics about dbScheduler and dbWorker threads. onstat -g dbc command: Print dbScheduler and dbWorker thread statistics
onstat -g dis	Prints a list of database servers, their status, directory location, configuration information, and host name. onstat -g dis command: Print database server information
onstat -g dll	Prints a list of dynamic libraries that are loaded. onstat -g dll command: Print dynamic link library file list
onstat -g osi	Prints information about operating system resources and parameters. onstat -g osi: Print operating system information
onstat -g pos	Prints values from $ONEDB_HOME/etc/.infos.servernum file, which are used by clients such as onmode for shared memory connections to the server. onmode -R rebuilds the $ONEDB_HOME/etc/.infos.servernum file. onstat -g pos command: Print file values
onstat -g smb	Prints detailed information about sbspaces. onstat -g smb command: Print sbspaces information
onstat -g sym	Prints symbol table information for the oninit utility. onstat -g sym command: Print symbol table information for the oninit utility
onstat -i	Changes onstat mode to interactive. onstat -i command: Initiate interactive mode
onstat -m	Prints message log contents. onstat -m command: Print recent system message log information
onstat -O	Prints Optical subsystem cache information. onstat -O command: Print optical subsystem information
onstat -r	Prints repetitive onstat execution. onstat -r command: Repeatedly print selected statistics
onstat -z	Resets the accumulated statistics to zero. onstat -z command: Clear statistics