Oninit Logo
The Down System Specialists
+1-913-674-0360
+44-2081-337529
Introduction Health Check Standby Admin Remote Admin Remote Monitoring Backup and Restore Custom Support Down Systems
Unix SQL Advanced SQL 4GL Applications Advanced 4GL IDS Admin Migrating to V14 IDS Performance Managing & Optimising SPL and Triggers IDS V14 Upgrading Very Old Versions Custom Training
Onstat Guide Error Code Guide Reference Datablades The Dumb DBA Sysmaster Fault Finder
Co-Location Web Hosting Managed Servers Web Development
BenchMark Tookkit RegExp Datablade
Partnerships Contact
Onstat
-g act -g afr -g all -g amx -g aqt -g arc -g ath -g btc -g buf -g cac -g cdr -g cluster -g cdr config -g cfg -g cmsm -g con -g cpu -g dbc -g ddr -g dic -g dis -g dll -g dmp -g dri -g dsc -g dsk -g dss
-g env -g ffr -g glo -g grp -g imc -g ioa -g iob -g iof -g iog -g ioq -g iov -g ipl -g lap -g laq -g lmm -g lmx -g lsc -g mem -g mgm -g nbm -g nif -g nsc -g nsd -g nss -g nta -g ntd -g ntm -g ntt -g ntu
-g opn -g osi -g pgp -g pos -g ppd -g ppf -g pqs -g prc -g proxy -g qst -g rah -g ras -g rbm -g rcv -g rep -g rea -g rss -g sch -g scn -g sds -g seg -g ses -g sle -g smb -g smx -g spi -g sql -g src -g ssc -g stk -g stm -g stq -g sts -g sym -g sync
-g rwm -g tgp -g tpf -g ufr -g wai -g vpcache -g wmx -g wst
-b/-B -F -g cac -g dic -g dsc -g prc -g ssc -g vpcache -h -p -X
-t/-T -g buf -g lap -g opn -g ppf -g scn
-g cat -g cdr -g cdrconfig -g ddr -g dss -g dtc -g grp -g nif -g que -g rcv -g rep -g rqm -g sync
-g cluster -g cmsm -g dri -g ipl -g laq -g proxy -g rss -g sds -g smx
-R -g afr -g ffr -g lmm -g lsc -g mem -g mgm -g nbm -g rbm -g seg -g ses -g stm -g stq -g ufr
-g imc -g nsc -g nsd -g nss -g nta -g ntd -g ntm -g ntt -g ntu
-G -u -x -g env -g his -g pqs -g ses -g sql
-g act -g ath -g cpu -g rea -g sle -g stk -g sts -g tpf -g wai -g wst
-a -b/-B -C -c -d/-D -F -f -G -h -i -j -k/-K -L -l -m -o -P -p -R -r -s -t/-T -u -X -x -z

onstat -b/-B Active Buffer Usage / All Buffers

The onstat -b command provides a list of the buffers in the buffer pool that are currently being modified (i.e., in use) according to information contained in the buffer headers. The onstat -B command displays all allocated buffers in memory. The buffers displayed are those created by the BUFFERS parameter in the configuration file. 

Buffers
address  userthread flgs pagenum memaddr nslots pgflgs xflgs owner waitlist

Buffer pool page size: 2048
 468 modified, 3000000 total, 4194304 hash buckets, 2048 buffer size

 Buffer pool page size: 16384
 785b2970  0  20247 136:22400  1f2564000   3  8890   10    0  0
 785b8ff0  0  20247 136:22336  1f27f4000   3  8890   10    0  0
 785c8090  0  20247 136:22368  1f2df8000   3  8890   10    0  0
 785cd810  0  20247 136:22408  1f3028000   3  8890   10    0  0
 785cddb0  0  20247 136:22376  1f304c000   3  8890   10    0  0
....
Buffer pool page size: 2048
 2968 modified, 3000000 total, 4194304 hash buckets, 2048 buffer size

Buffer pool page size: 16384
 20295 modified, 500000 total, 524288 hash buckets, 16384 buffer size

Output Description

Heading Description Format See Also
address In-memory address of the buffer header. Hex onstat -g dmp
userthread The address of the userthread currently modifying this buffer header. This address corresponds to the address field given by the onstat -u command. Hex onstat -u
flgs Describes the current status of the buffer using a combination of the following hexadecimal values: 
0x01    Modified Data
0x02    Data
0x04    LRU
0x08    Error
0x10    Resident
0x20    LRU AIO write in progress
0x40    Chunk write in progress
0x80    Buffer is/will be result of read-ahead
0x100   Cleaner assigned to LRU
0x200   Buffer should avoid bf_check calls
0x400   Do log flush before writing page
0x800   Buffer has been 'buff-checked'
0x8000  Buffer has been pinned
0x10000 Buffer modified by fuzzy operation
0x20000 Use aging of buffer on LRU
0x40000 Do not use buffer priorities
9.40+ 
0x01       Modified Data
0x02       Data
0x04       LRU
0x08       Error
0x10       Resident
0x20       LRU AIO write in progress
0x40       Chunk write in progress
0x80       Buffer is/will be result of read-ahead
0x100      Cleaner assigned to LRU
0x200      Buffer should avoid bf_check calls
0x400      Do log flush before writing page
0x800      Buffer has been 'buff-checked'
0x8000     Buffer has been pinned
0x10000    Buffer modified by fuzzy operation
0x00020000 The page was already in log. Used only during recovery
0x00040000 This page has all deleted items which have been committed.
0x00080000 Page is in temp dbspace
This information in held in the sysbufhdr table and is believed to be the rbuf internal structure
Hex  
pagenum The physical page number from disk that currently resides in this buffer pool slot; 0xffffff if empty. Hex onstat -g dmp
memaddr The in-memory address of the buffer within the buffer pool. Hex onstat -g dmp
nslots The number of slot table entries (i.e., rows or row portions) on this page. Dec  
pgflgs Describes the page type using the following hexadecimal values: 
0x01   Data Page
0x02   Partition Page
0x04   Bitmap Page
0x08   Chunk Free List Page
0x09   Remainder Data Page
0x0b   Partition Resident BLOB Page
0x0c   BLOBSpace Resident BLOB Page
0x0d   BLOB Chunk Free List Bitmap Page
0x0e   BLOB Chunk BLOB Map Page
0x10   B+ Tree Node Page
0x20   B+ Tree Root Node Page
0x40   B+ Tree Branch (Twig) Node Page
0x80   B+ Tree Leaf Node Page
0x100  Logical Log Page
0x200  Last Page of Logical Log
0x400  Sync Page of Logical Log
0x800  Physical Log Page
0x1000 Root Reserved Page
0x2000 No Physical Logging Required
0x4000 No Physical Logging Required (load-time)
0x8000 B-Tree Leaf with deleted entries
0x20   Secondary partition header (w/0x02)
0x40   Alter description partition header (w/0x02)
0x80   Key descriptor partition page (w/0x02)
0x80   Generic page
0x4000 Do not allocate a row without checking
Hex  
xflgs Describes the type of access currently being applied to the buffer using the following hexadecimal values: 
0x10 Shared Lock
0x80 Exclusive Lock
Hex  
owner The userthread that placed the xflgs values on the buffer page. Hex onstat -u
waitlist The first userthread (on a possible list of many) that is waiting for this particular buffer. Hex onstat -u onstat -X

Summary Definitions

Label Description See Also
modified The number of buffers in the pool that have been modified since the last buffer flush (e.g., from a checkpoint) onstat -R
resident The number of buffers containing a page that is marked as resident.  
total The number of buffers that were allocated according to the BUFFERS parameter in the configuration file. onstat -R
hash buckets The number of hash buckets created to track the pages placed into the buffer pool. onstat -h
buffer size The size of an individual buffer page. onstat -R

Notes

    Information output by onstat -b are pages currently being modified by a thread. Don't confuse this with a page that is simply dirty (used).

    The page size is the smallest unit of I/O in the system and is determined at port time.

    Currently, the userthread field is always 0.

    The number of hash buckets is the smallest power of two greater than the number of buffers.

    Buffer hash buckets point to buffer headers. Each buffer header contains a pointer to a page in the buffer pool plus maintains information about the status of pages read and written to that buffer pool page. There will be one buffer header for each buffer in the buffer pool.

    Calls are made to determine the validity of information contained on the buffer page each time an I/O is performed.

    The number of pages modified becomes zero when the buffer pool is flushed as with a checkpoint or when page cleaners flush pages.

    Individual pages within the buffer pool can be displayed by executing the onstat -g dmp command with the memaddr value and the pagesize (e.g. onstat -g dmp 0xa088000 2048).

    Don't confuse the locking of a buffer pool page with the locking of an actual database page.

    Pages within the buffer pool will be latched and released as pages (of any type) are read from disk and placed into the buffer pool.

Monitoring and Tuning

    The number of buffers in the buffer pool directly affects the performance of an OLTP system. Normally, the action involved with locking, modifying and releasing a buffer is very rapid (using latches). The onstat -b command will usually show output only when there is a heavy amount of I/O occurring against the buffer pool. A constant number of buffers displayed by the onstat -b command might indicate a problem, such as an insufficient number of buffers in the buffer pool or high read aheads. Use this command in conjunction with onstat -p to analyze potential tuning areas.

    CASE STUDY A specific row was updated in the customer table within a transaction, followed by a wait. Below is the output from the onstat -B command with a standard UNIX pipe to egrep for the page containing the row that was modified both before and after a flush of the buffer pool. The page number for a row can be acquired by using the oncheck -pp command. 

    onstat -b | egrep "(addr|999999)"

address  userthread  flgs pagenum  memaddr nslots  pgflgs   xflgs owner waitlist
xxxxxxx      0       407  123456   999999   14      2001      0    0      0 

onmode -c

onstat -b | egrep "(addr|999999)
address  userthread  flgs pagenum  memaddr nslots  pgflgs   xflgs owner waitlist
xxxxxxx      0         6  123456   999999   14      2001      0    0      0
    When the session modified the row in the customer table (contained on page 0x123456), the buffer flags (flgs) became 0x407. An examination of the value, 0x400 + 0x04 + 0x02 + 0x01 = 0x407, illustrates that we are dealing with a modified (0x01), LRU (0x04), data page (0x02), that requires a flush of the log pages before the page is written to disk (0x400). After the forced checkpoint (onmode -c) the system changed the flags to 0x06 (0x04 + 0x02), indicating the modified page was flushed to disk.

To discuss how Oninit ® can assist please call on +1-913-674-0360 or alternatively just send an email specifying your requirements.