Light Scans

In some circumstances, the database server does not use the buffer pool and bypasses the LRU queues for better performance when it performs a sequential scan. Such a sequential scan is termed a light scan.

Light scans can occur on STATIC tables at any isolation level and on other table types in the following isolation levels:

• In Dirty Read isolation level, light scans can occur for all tables, including nonlogging tables.
• In Repeatable Read isolation level, light scans can occur if the table has a shared or exclusive lock.
• In Committed Read isolation level, light scans can occur if the table has a shared or exclusive lock.
• In Cursor Stability isolation level, light scans can occur only on STATIC tables.

The performance of light scans is based on the following factors:

• Up-to-date table statistics
• The value of RA_PAGES and RA_THRESHOLD
• The size of the virtual shared-memory segment

In the onstat -g scn output, the ScanType column shows whether each thread is using a light scan or is using the buffer pool. The following onstat - scn output indicates that the thread is using the buffer pool to scan the table:

RSAM sequential scan info
SesID Thread Partnum  Rowid    Rows Scan'd Scan Type Lock Mode  Notes
16    994    30002    1cc4e    61583       Buffpool  SLock+Test 

The Scan Type column might also display Light, for a light scan; Keyonly, for an index scan; and Rids, for an index scan that returns row identifiers. If an index scan is sending row identifiers to a light scan, Skip scan appears in the Notes column.

Similar information appears for light index scans. Light index scans can occur if the index meets these requirements:

• Only one index fragment must be scanned after unnecessary index fragments are eliminated.
• The WHERE clause of the query does not contain an IN clause or OR operator.
• The query does not contain any subqueries.
• The index fragment cannot be a Generalized Key (GK) or bitmap index.