Calculating the Shared Memory Size

This chapter describes how to calculate the size of the shared memory.

The shared memory in Tibero is divided into fixed memory and shared pool memory as follows:

• Fixed Memory Its size is fixed based on a parameter value set at database startup.

  – Buffer cache (database buffer)

  – Log buffer

  – Others: space for global variables and managing working threads and session information.

• Shared Pool Memory It is dynamically allocated during database operation.

  – DD cache

  – PP cache (library cache)

  – Others

---

Considerations

Consider the following when calculating the size of the shared memory.

• The size of the shared memory cannot be increased dynamically while the database is running.

• The size of the shared memory must be determined properly based on the usage patterns of the buffer cache and shared pool.

- Buffer Cache

• Determine the size according to the Buffer Cache Hit rate in TPR after executing major workloads.

• If the hit rate is lower than 90%, increase the buffer cache.

- Shared Pool Memory

• Determine the size according to the SHARED POOL MEMORY item in v$sga after executing major workloads.

• If the shared pool usage is too high (insufficient free area), increase the shared pool memory.

• At least 1 MB of the shared pool memory is required for each session.

---

Calculating the Size

The size of each area in the shared memory can be checked with v$sga.

SQL> select * from v$sga; 
NAME TOTAL USED
--------------------  ---------  ---------
SHARED MEMORY         536870912  536870912
FIXED MEMORY          430875880  430875880
SHARED POOL MEMORY    105992872   40974968
SHARED POOL ALOCATORS         1          1
Database Buffers      357892096  357892096
Redo Buffers           10485760   10485760

The size of the shared memory can be set and queried with the following initialization parameters.

Shared Memory

The following example queries the size with the TOTAL_SHM_SIZE parameter.

SQL> show param total_shm_size 
NAME               TYPE      VALUE
----------------   --------  ---------
TOTAL_SHM_SIZE     INT64     536870912

Buffer Cache

The following example queries the size with the DB_CACHE_SIZE parameter.

SQL> show param db_cache_size 
NAME               TYPE      VALUE
----------------   --------  ---------
DB_CACHE_SIZE      UINT64    357892096

• Single mode : ⅔ of TOTAL_SHM_SIZE

• TAC mode : ½ of TOTAL_SHM_SIZE

Log Buffer

The following example queries the size with the LOG_BUFFER parameter. (the default value of LOG_BUFFER is 10 MB.)

SQL> show param log_buffer 
NAME               TYPE      VALUE
----------------   --------  ---------
LOG_BUFFER         UINT32    10485760

Shared Pool Memory

The size can be calculated by subtracting the fixed memory size from the total shared memory size as follows:

TOTAL_SHM_SIZE - [Fixed Memory]

• Single mode The total shared pool size must be greater than _MIN_SHARED_POOL_SIZE.

The default value of _MIN_SHARED_POOL_SIZE: 1MB * MAX_SESSION_COUNT

• TAC mode Sufficient space for Cluster Cache Control (CCC) and Cluster Wait-lock Service (CWS) is required. About 25% of a total buffer cache size is used by shared pools.

  The shared pool space excluding space for CCC and CWS must be greater than

  _MIN_SHARED_POOL_SIZE.

[Total shared pool size] = _MIN_SHARED_POOL_SIZE + [CCC space] + [CWS space]
+ [Free shared pool size]

The free shared pool size for PP and DD caches must be greater than CCC and CWS spaces.

[Free shared pool size] > ([Total shared pool size] - _MIN_SHARED_POOL_SIZE) / 2