SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-26
Virtual Sequential Block Buffering (VSBB)
VSBB is used for UPDATE and DELETE operations if no more than one row is to be
updated out of 32 rows examined.
VSBB is not used for nonaudited tables if any of these is true:
•
The syncdepth value is greater than 0.
•
A table lock is not used.
•
The table contains alternate indexes, and a varying-length column is being
updated.
When an operation is buffered, data is transferred between the file system and the disk
process a block at a time instead of a row at a time. Consequently, virtual sequential
block buffering (VSBB) improves the performance of queries by reducing the number
of messages exchanged and the amount of data transferred between the file system
and the disk process.
To find out if the optimizer is choosing VSBB, use the EXPLAIN utility, described in
Section 6, Analyzing Query Performance.
Effects of VSBB on Concurrency
The use of VSBB can cause concurrency problems because it requires locks.
Increased lock waits and timeouts can occur for reasons described in these
subsections. If you are experiencing concurrency problems, consider disabling VSBB
(by using the CONTROL TABLE directive).
For sequential read operations, the disk process locks all rows scanned (rather than a
row at a time). Consequently, SQL operations that use VSBB, even with stable access,
can acquire more locks that remain in place longer than operations that do not use
VSBB. (The EXPLAIN utility, described in Section 6, Analyzing Query Performance,
lists VSBB and STABLE ACCESS when both are in use.) Stable locks on rows are
released only when the program is done with the block. You can disable VSBB for read
operations by specifying this directive:
CONTROL TABLE * SEQUENTIAL READ OFF
For sequential update operations, the compiler performs a sequential read before
performing the update. You can disable VSBB for update operations by specifying this
directive:
CONTROL TABLE * SEQUENTIAL UPDATE OFF
When requesting an insert into a key-sequenced table that uses a SYSKEY column or
a timestamp as the primary key, VSBB is usually chosen for the operation, because
inserts into such a table will very likely be sequential. If concurrent servers are inserting