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 </div>
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 priorities9.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 </div>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 </div> |
Hex | |
| xflgs | Describes the type of access currently being applied to the buffer using the following hexadecimal values:
0x10 Shared Lock 0x80 Exclusive Lock </div> |
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.
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.
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 </div>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.