SQL/MP Query Guide

Improving Query Performance With Environmental
Options
HP NonStop SQL/MP Query Guide—524488-003
4-25
Virtual Sequential Block Buffering (VSBB)
VSBB reduces the number of messages between the file system and the disk process.
Further, it reduces the amount of data transferred between the file system and the disk
process.
The disk process returns a block of rows to the file system, but the block contains only
the requested columns from rows that satisfy the predicate (because the disk process
does the projection and restriction of data). So, only the columns ITEM_NAME and
RETAIL_PRICE are returned to the file system.
Therefore, the disk process returns 2160 bytes of data (90 rows at 24 bytes per row) to
the file system. Because the answer to the query can be contained in one 4 kilobyte
page, the file system sends only one message to the disk process.
VSBB is also used for insert and update operations. It significantly reduces the number
of messages passed between the file system and the disk process.
Guidelines for Choosing VSBB
For a sequential scan, VSBB is chosen when one or more of these is true:
•
Less than two-thirds of a row is retrieved or examined.
•
Most records examined do not satisfy all of the predicates. (The difference
between the table and indexes selectivities is large.)
•
More than one-third of the table is on a remote node.
VSBB is used for audited tables and cursor stability if less than 1 out of 16 rows
examined is to be returned and if any of the preceding conditions are met.
Figure 4-4. Virtual Sequential Block Buffering (VSBB)
11
20
12
25
70
PEAR
AP PL E
CHERRY
ORANGE
SOUP
File System
Get Next Virtua l Block Where
RETAIL_PRICE > 10
Disk Process
Ret urns One Virtual Block of Ro ws
(With Projection and Restriction)
5
11
20
12
PEACH
PEAR
A
PPLE
...
CHERRY
10
11
12
18
...
...
...
...
...
...
...
25
30
50
O
RANGE
L
EMON
CHEESE
...
19
20
21
...
...
50
70
...
FLOWER
SO UP
99
10 0
0
17
...
VST0404.vsd