Virtual Portion

The virtual portion of shared memory for the database server includes the following components:

The SHMVIRTSIZE configuration parameter in the database server configuration file provides the initial size of the virtual portion. As the need for additional space in the virtual portion arises, the database server adds shared memory in increments that the SHMADD configuration parameter specifies, up to a limit on the total shared memory allocated to the database server, which the SHMTOTAL parameter specifies.

The size of the virtual portion depends primarily on the types of applications and queries that you are running. Depending on your application, an initial estimate for the virtual portion might be as low as 100 kilobytes per user or as high as 500 kilobytes per user, plus an additional 4 megabytes if you intend to use data distributions. For guidelines on creating data distributions, see the discussion of UPDATE STATISTICS in Creating Data Distributions.

The basic algorithm for estimating an initial size of the virtual portion of shared memory is as follows:

To estimate SHMVIRTSIZE with the preceding formula

Use the following formula to estimate the fixed overhead:
```
fixed overhead = global pool +
                 thread pool after booting
```
Use the onstat -g mem command to obtain the pool sizes allocated to sessions. Subtract the value in the freesize field from the value in the totalsize to obtain the number of bytes allocated per session.

The thread pool after booting variable is partially dependent on the number of virtual processors.

Use the following formula to estimate shared structures:

shared structures = AIO vectors + sort memory +
            dbspace backup buffers +
            data-dictionary cache size +
            size of user-defined routine cache +
            histogram pool +
            STMT_CACHE_SIZE (SQL statement cache) +
            other pools (See onstat display.)

Table 4 lists the location of more information on estimating the size of these shared structures in memory.

Table 4. Finding Information for Shared-Memory Structures
Shared-Memory Structure	More Information
Sort memory	Estimating Memory Needed for Sorting
Data-dictionary cache	Data-Dictionary Configuration
Data-distribution cache (histogram pool)	Data-Distribution Configuration
User-defined routine (UDR) cache	UDR Cache
SQL statement cache	Enabling the SQL Statement Cache SQL Statement Cache
Other pools	To see how much memory is allocated to the different pools, use the onstat -g mem command.

To estimate the next part of the formula, perform the following steps:
1. Estimate mncs (which is the maximum number of concurrent sessions) with the following formula:
```
mncs = number of poll threads *
      number connections per poll thread
```
  The value for number of poll threads is the value that you specify in the second field of the NETTYPE configuration parameter.
  
  The value for number of connections per poll thread is the value that you specify in the third field of the NETTYPE configuration parameter.
  
  You can also obtain an estimate of the maximum number of concurrent sessions when you execute the onstat -u command during peak processing. The last line of the onstat -u output contains the maximum number of concurrent user threads.
2. Estimate the private structures with the following formula:
```
private structures = stack + heap +
                   session control-block structures
```
  stack
  
  is generally 32 kilobytes but dependent on recursion in user-defined routines. You can obtain the stack size for each thread with the onstat -g sts option.
  
  heap
  
  is about 15 kilobytes. You can obtain the heapsize for an SQL statement when you use the onstat -g stm option.
  
  session control-block structures
  
  is the amount of memory used per session. The onstat -g ses option displays the amount of memory, in bytes, in the total memory column listed for each session id.
  
  For more information on the onstat -g stm option, see Session Memory.
3. Multiply the results of steps 3a and 3b to obtain the following part of the formula:
```
mncs * private structures
```
Estimate other buffers to account for private buffers allocated for features such as lightweight I/O operations for smart large objects (about 180 kilobytes per user).
Add the results of steps 1 through 4 to obtain an estimate for SHMVIRTSIZE.