SQL/MP Installation and Management Guide

Enhancing Performance

HP NonStop SQL/MP Installation and Management Guide—523353-004

14-21

Additional DEFINEs for Managing Double Buffering

Two other DEFINEs allow you to manage other aspects of file system double buffering:

•

The DEFINE =_SQL_CMP_DOUBLE_SBB_OFF disables file system double

buffering for any query that is SQL compiled while this DEFINE is in effect.

•

The DEFINE =_SQL_CMP_DOUBLE_SBB_ON enables (turns on) file system

double buffering for scanning the inner table in a nested join or key-sequenced

merge join. (The default setting is to disable this feature for an inner table of a join

operation.)

The preceding DEFINEs influence SQL compilation; that is, they affect the optimizer’s

selection of double buffering for a query execution plan.

The EXPLAIN plan shows whether the optimizer has chosen file system double

buffering for a query execution plan. The EXPLAIN plan reads as follows:

SBB for reads : Virtual, double buffer

Also, when the optimizer requests double buffering for a given plan, the executor does

not necessarily use double buffering at run time. Its use depends on memory utilization

for the PFS, as described in the preceding paragraphs.

Using Generic Locks

Generic locking is an application-related feature that allows control over the granularity

of locking. A generic lock is a lock held by a process on a subset of the rows in a table.

Lock granularity is the size of a lockable unit. Generic locking can provide:

•

Improved performance, because the application acquires fewer locks while

performing operations

•

Ability to lock large portions of a table with a single lock without acquiring a table

lock

•

Reduced risk of a program exceeding the maximum number of locks

Figure 14-1 on page 14-22 illustrates generic locking used in a banking application with

the tables CUSTOMER and ACCOUNT. NAME is the primary key for the CUSTOMER

table. NAME and ACCOUNT_NO make up the concatenated primary key for the

ACCOUNT table.