onstat -g scn command: Print scan information

Use the onstat -g scn command to display the status of a current scan and information about the scan.

If you have a long-running scan, you might want to use this command to check the progress of the scan, to determine how long the scan will take before it completes, and to view information about the scan. For tables, the onstat -g scn command output identifies whether a scan is a light or bufferpool scan.

Figure 1: Syntax:

Example Output

Figure 2: onstat -g scn output showing table information


Light Scan Info
descriptor  address           next_lpage  next_ppage        ppage_left  bufcnt  look_aside

RSAM batch sequential scan info

SesID Thread Partnum  Rowid    Rows Scan'd Scan Type Lock Mode  Notes
48    68     10016e   12bb09   43146       Light     Table      Look aside, 
40    47     100106   101      0           Buffpool  +Test      Must copy

Information about an index scan is valid when a scan is running.

Figure 3: onstat -g scn output showing index scan information

RSAM batch index scan info

SesID Thread Partnum  Scan Type Lock Mode  Notes
136   156    100197             SLock+Test
       Start Key   GT   :-2147483648:
       Stop Key    EQ   :1500:
       Current key      :170:
       Current position: buffp 0x10a4bc0c8 pagenum 2 slot 17 rowoff 4 flags 0

Output Description

descriptor (decimal): Light scan ID
address (hex): Memory address of the light scan descriptor
next_lpage (hex): Next logical page address to scan
next_ppage (hex): Next physical page address to scan
ppage_left (decimal): Number of physical pages left to scan in the current extent
bufcnt: Number of light scan buffers used for this light scan
look_aside: Whether look aside is needed for this light scan (Y = yes, N = no). Look asides occur when a thread needs to examine the buffer pool for existing pages to obtain the latest image of a page being light scanned.

SesID

Session ID

Thread

Thread ID

Partnum

Partition number

Rowid

Current® row ID

Rows Scan'd

Number of rows that have been scanned

Scan Type

For tables, either:

Bufferpool
Light (light scan)

For indexes, either:

key only
No value if the scan is not a key-only scan

Lock Mode

The type of acquired lock or no lock:

Table (table-level lock acquired)
Slock (share locks acquired)
Ulock (update locks acquired)
blank (no locks acquired)

This column can also show one of the following values:

+Test (The scan tested for a conflict with the specified lock type; the lock was not acquired.)
+Keep (The acquired locks will be held until end of session instead of the end of the transaction.)

Notes

This column can show one of the following values:

Look aside
The light scan is performing look aside.
The light scan reads blocks of pages directly from disk into large buffers, rather than getting each page from the buffer manager. In some cases, this process requires the light scan to check the buffer pool for the presence of each data page that it processes from one of its large buffers; this process is called look aside. If the page is currently in the buffer pool, the light scan will use that copy instead of the one in the light scan large buffer. If the page is not in the buffer pool, the light scan will use the copy that the light scan read from disk into its large buffer. If the light scan is performing look aside, the performance of the scan is slightly reduced.
In many cases, the light scan can detect that it is impossible for the buffer pool to have a newer version of the page. In these situations, the light scan will not check the buffer pool, and the look aside note will be absent.
Forward row lookup
The server is performing a light scan on a table that has rows that span pages. The light scan must access and use the buffer pool to get the remainder pieces of any rows that are not completely on the home page.

Start key

Start key of the scan

Stop key

End key of the scan

Current key

The current key in the scan

Current position

The current location of the scan in the index, for example, the page, slot, and offset