SQL/MP Installation and Management Guide

Enhancing Performance
HP NonStop SQL/MP Installation and Management Guide—523353-004
14-19
Using an SQL DEFINE to Manage PFS Utilization
SQL can enable file system double buffering only when the disk process uses virtual
sequential block buffering (VSBB) and the SELECT statement specifies browse
access. For more information about VSBB, see the SQL/MP Query Guide.
SQL automatically uses file system double buffering when this feature will enhance the
performance of the query. By default, the optimizer does not use double buffering for
scanning the inner table in a nested join or key-sequenced merge join.
This feature requires the file system to use two buffers instead of one. In certain
circumstances, this feature could potentially exceed the memory size limit for the
process file segment (PFS) assigned to the file system for the process. To avoid
memory overflow, SQL automatically disables the use of this feature for any more files
if file system memory utilization exceeds 70 percent of the PFS. The disabling of this
feature is likely to be temporary; when PFS utilization returns to a level below 70
percent, double buffering is enabled again for newly opened files.
The PFS is an area of real memory used by the file system to store operating system
information. The PFS size is dynamic; that is, the file system fills it, as needed, up to
the PFS size limit assigned by the operating system.
For example, suppose that a server process opens a large number of SQL tables; the
file system uses a portion of the PFS for each open. Suppose further that multiple
cursor operations called by the server perform sequential table scans using VSBB. The
file system temporarily allocates additional portions of the PFS for each cursor
operation; if the file system uses double buffering, it allocates twice the amount of
buffer space in PFS memory for each cursor operation. Thus, double buffering can
increase the amount of the PFS used by the file system.
If a PFS memory overflow occurs, the system is likely to display error message 31. A
file system error 31 occurs when insufficient space is available in the PFS for a file
system buffer needed to perform the specified operation. For more information about
this error message, see the “File System Errors” section of the Guardian Procedure
Errors and Messages Manual.
To avoid a PFS memory overflow, take one of these steps:
•
Use an SQL DEFINE to lower the PFS utilization threshold at which the SQL file
system automatically disables file system double buffering for additional files.
•
Increase the PFS size limit.
Using an SQL DEFINE to Manage PFS Utilization
You can change the memory utilization threshold at which SQL disables file system
double buffering by setting the DEFINE =_SQL_EXE_DOUBLE_SHUTOFF. For
example, by increasing the threshold to 90 percent, you increase the use of double
buffering but make it somewhat more likely that the file system will exceed the PFS
memory size limit. If you lower the threshold to 50 percent, you decrease the use of
double buffering but also reduce the likelihood of a PFS memory overflow.