For the different combinations of physical memory sizes, schema sizes and IM compression levels we tested, our goal for each test case was to populate as many TPC-H-like schema tables inside the In-Memory area as possible. Ideally, we would prefer to populate all eight schema tables generated by the HammerDB TPC-H-like schema inside the In-Memory area. However, for a given schema size, the different In-Memory compression levels have different impacts on the space occupied by any particular schema table inside the In-Memory area. Hence, the following steps show the methodology used to perform the memory sizing for all the test cases described in this guide.

1. For the two schema sizes we tested with—1 TB and 3 TB TPC-H-like schemas—during the trial runs, we first determined the minimum inmemory_size parameter value needed in order to populate the entire schema (all eight tables) inside the In-Memory area. The values determined are listed in the following figure. From the onset, this step helps us to determine which IM compression level will and will not populate the entire schema size inside the In-Memory area for a given physical memory size.

Figure 24.          Minimum inmemory_size needed to populate entire schema In-Memory

2. In addition to the inmemory_size, however, we still need to provision other Oracle memory components (PGA and other components of SGA) and non-Oracle components (operating system) within the available physical memory space. Hence, for a given physical memory capacity, we next assigned percentage ranges for all the memory components that are practical and acceptable in an Oracle Database environment, as shown below.

• Physical memory capacity (100 percent)
  • Non-Oracle components (operating system): 5 to 15 percent of total physical memory size
  • Total SGA and PGA: 85 to 95 percent of total physical memory size
    • PGA Limit: 30 to 50 percent of total SGA and PGA size
      • PGA Target: 50 percent of PGA Limit size
    • SGA Target and Max: 50 to 70 percent of total SGA and PGA size
      • In-Memory area: 75 to 85 percent of SGA Target size
      • Other SGA (buffer cache): 15 to 25 percent of SGA Target size

3. Therefore, starting with the minimum inmemory_size from step 1 and adding sizes for all other memory components on top using step 2, for each test case, we were able to do the memory sizing and determine the minimum and maximum values for each memory component for a given physical memory capacity. For each test case, once we were able to determine the max size we could allocate for inmemory_size, we could then determine the maximum number of source schema tables we could populate inside the In-Memory area. As an example, the following figure shows the memory sizing done during Test 1.1.1 that had 768 GB DRAM-only capacity and Capacity High IM compression level was used to populate the 1 TB TPC-H-like schema inside the In-Memory area.

Figure 25.          Memory sizing for Test 1.1.1 with 768 GB DRAM-only, Capacity High IM compression, and 1 TB OLAP schema

As seen in the figure above, during Test 1.1.1, we were able to assign 330 GB to the In-Memory Area which still allowed enough headroom (768 GB - 330 GB = 438 GB) to allocate reasonable sizes to other memory components using the percentage-ranges methodology provided in step 2 above. Within the 330 GB (minimum size required as shown in Figure 24) we were able to populate the entire schema inside IMCS for Test 1.1.1.

Using the above memory sizing methodology, Table 9, Table 10 and Table 11 in the Oracle Database parameter settings section provides the different values set to the key memory parameters for both the use cases tested in this design guide.