The SHMVIRTSIZE configuration parameter and memory utilization

The SHMVIRTSIZE parameter specifies the size of the virtual portion of shared memory to allocate when you start the database server. The virtual portion of shared memory holds session- and request-specific data as well as other information.

Although the database server adds increments of shared memory to the virtual portion as needed to process large queries or peak loads, allocation of shared memory increases time for transaction processing. Therefore, you should set SHMVIRTSIZE to provide a virtual portion large enough to cover your normal daily operating requirements. The size of SHMVIRTSIZE can be as large the SHMMAX configuration parameter allows.

The maximum value of SHMVIRTSIZE, which must be a positive integer, is:
  • 4 terabytes on a 64-bit database server
  • 2 gigabytes on a 32-bit database server
For an initial setting, it is suggested that you use the larger of the following values:
  • 8000
  • connections * 350

The connections variable is the number of connections for all network types that are specified in the sqlhosts information by one or more NETTYPE configuration parameters. (The database server uses connections * 200 by default.)

Once system utilization reaches a stable workload, you can reconfigure a new value for SHMVIRTSIZE. As noted in Freeing shared memory with onmode -F, you can instruct the database server to release shared-memory segments that are no longer in use after a peak workload or large query.