Light scans

Some sequential scans of tables can use light scans to read the data. A light scan bypasses the buffer pool by utilizing session memory to read directly from disk.

Light scans can provide performance advantages over use of the buffer pool for sequential scans and skip scans of large tables. These advantages include:
  • Bypassing the overhead of the buffer pool when many data pages are read
  • Preventing frequently accessed pages from being forced out of the buffer pool when many sequential pages are read for a single query.
Light scans occur under these conditions:
  • The optimizer chooses a sequential scan or a skip-scan of the table.
  • The amount of data in the table exceeds one MB.
  • The query meets one of the following locking conditions:
    • The isolation level is Dirty Read (or the database has no transaction logging).
    • The table has at least a shared lock on the entire table and the isolation level is not Cursor Stability.
      Note: A sequential scan in Repeatable Read isolation automatically acquires a share lock on the table.

Tables that cannot be accessed by light scans

Light scans are only performed on user tables whose data rows are stored in tblspaces. Light scans are not used to access indexes, or to access data stored in blobspaces, smart blob spaces, or partition blobs. Similarly, light scans are not used to access data in the system catalog tables, nor in the tables and pseudotables of system databases like sysadmin, sysmaster, sysuser, and sysutils.

Configuration settings that affect light scans

If the BATCHEDREAD_TABLE configuration parameter or the IFX_BATCHEDREAD_TABLE session environment option to the SET ENVIRONMENT statement is set to 0, light scans are not used to access tables that have variable length rows, or tables where the row length is greater than the pagesize of the dbspace in which the table is contained. A variable length row includes tables that have a variable length column, such as VARCHAR, LVARCHAR or NVARCHAR, as well as tables that are compressed.

You can use the IFX_BATCHEDREAD_TABLE session environment option of the SET ENVIRONMENT statement, or the onmode -wm command, to override the setting of the BATCHEDREAD_TABLE configuration parameter for the current session. You can use the onmode -wf command to change the value of BATCHEDREAD_TABLE in the ONCONFIG file.

Example of onstat output during a light scan

If you have a long-running scan, you can view output from the onstat -g scn command to check the progress of the scan, to determine how long the scan will take before it completes, and to see whether the scan is a light scan or a bufferpool scan.

The following example shows some of the output from onstat -g scn for a light scan. The word Light in the Scan Type field identifies the scan as a light scan.

SesID  Thread Partnum  Rowid  Rows Scan'd  Scan Type  Lock Mode  Notes
17     48     300002   207    15           Light                 Forward row lookup