Estimating the size of the virtual portion of shared memory

You can use a formula to estimate the initial size of the virtual portion of shared memory. You specify the initial size in the SHMVIRTSIZE configuration parameter.

The formula for estimating an initial size of the virtual portion of shared memory is as follows:
shmvirtsize = fixed overhead + shared structures +
            (mncs * private structures) +
            other buffers

To estimate an SHMVIRTSIZE value with the preceding formula:

  1. Estimate the value for the fixed overhead portion of the formula as follows:
    fixed overhead = global pool +
                     thread pool after booting

    1. Run the onstat -g mem command to obtain the pool sizes allocated to sessions.
    2. Subtract the value in the freesize field from the value in the totalsize to obtain the number of bytes allocated per session.
    3. Estimate a value for the thread pool after booting variable. This variable is partially dependent on the number of virtual processors.
  2. Estimate the value of shared structures with the following formula:
    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.)
  3. Estimate the next part of the formula, as follows:
    1. Estimate the value of 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 run 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 value of private structures, as follows:
      private structures = stack + heap +
                         session control-block structures
      Generally 32 KB but dependent on recursion in user-defined routines. You can obtain the stack size for each thread with the onstat -g sts option.
      About 15 KB. You can obtain the heap size for an SQL statement when you use the onstat -g stm option.
      session control-block structures
      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.
    3. Multiply the results of steps 3a and 3b to obtain the following part of the formula:
      mncs * private structures
  4. Estimate the value of other buffers to account for private buffers allocated for features such as lightweight I/O operations for smart large objects (about 180 KB per user).
  5. Add the results of steps 1 through 4 to obtain an estimate for the SHMVIRTSIZE configuration parameter.
Tip: When the database server is running with a stable workload, you can use onstat -g seg to obtain a precise value for the actual size of the virtual portion of shared memory. You can then use the value for shared memory that this command reports to reconfigure SHMVIRTSIZE.

To specify the size of segments that are added later to the virtual shared memory, set the SHMADD configuration parameter. Use the EXTSHMADD configuration parameter to specify the size of virtual-extension segments that are added for user-defined routines and DataBlade routines.

The following table contains a list of additional topics for estimating the size of shared structures in memory.

Table 1. 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 SPL routine executable format stored in UDR cache
SQL statement cache Enabling the SQL statement cache Monitor and tune the SQL statement cache
Other pools To see how much memory is allocated to the different pools, use the onstat -g mem command.

Copyright© 2018 HCL Technologies Limited