Sizing True Cache

Use Automatic Workload Repository (AWR) data from the primary database to estimate a starting memory size for True Cache.

Note: These sizing guidelines provide an initial estimate. Test the configuration with a representative workload before you use it in production.

Prerequisites

Before you size True Cache, do the following:

• Collect an AWR report from the primary database while it’s handling a representative, steady-state workload.

  For details, see Using the Automatic Workload Repository for True Cache.

• Enable the database cache advisory on the primary database and on True Cache.

  ALTER SYSTEM SET db_cache_advice = ON;

  For details, see DB_CACHE_ADVICE.

True Cache Memory Sizing Guidelines

• Set the SGA_TARGET to be large enough for the database buffer cache, shared pool, and other configured memory pools.

• If you use automatic memory management, set DB_CACHE_SIZE so that automatic management does not shrink the buffer cache too far.

• Keep the primary database and True Cache memory settings consistent where possible.

• For OLTP workloads, target a steady-state buffer cache hit ratio of 98.5% or higher.

Calculate the Read-Only Percentage

Use the following formula with values from the primary database AWR report:

Read-Only Percentage = Consistent Gets / (Consistent Gets + DB Block Gets)

Example:

• Consistent Gets = 3,137,126,201
• DB Block Gets = 1,754,840,286
• Read-Only Percentage = 64.12%

3,137,126,201 / (3,137,126,201 + 1,754,840,286) = 64.12%

Estimate the True Cache Buffer Cache

Estimate the initial True Cache buffer cache size as follows:

True Cache Buffer Cache Size = Primary Buffer Cache Size x Read-Only Percentage + 10% metadata overhead

Example:

• Primary Buffer Cache Size = 236 GB
• Read-Only Percentage = 64.12%
• True Cache Buffer Cache Size = 166 GB

236 GB x 64.12% = 151 GB

151 + (151 x 10%) = 166 GB

Estimate the SGA Allocation

Use the remaining SGA_TARGET capacity for the buffer cache after accounting for the shared pool and other memory pools as follows:

Buffer Cache = SGA_TARGET - Shared Pool - Other Memory Pools

Example:

• SGA_TARGET = 262 GB
• Shared Pool = 62 GB
• Non-Uniform Memory Access (NUMA) Pool = 34 GB
• Buffer Cache = 166 GB

262 GB - 62 GB - 34 GB = 166 GB

Validate the Size

After you configure True Cache, run a representative workload and do the following:

• Monitor the buffer cache hit ratio.

• Review the AWR buffer pool advisory.

• Confirm that the buffer cache hit ratio stays at or above 98.5%.

Use the Buffer Pool Advisory

Use the AWR buffer pool advisory to see how increasing the buffer cache affects physical reads. As the cache gets larger, the reduction in physical reads becomes smaller, even when you double the cache size.

Size True Cache Flash Cache (Spool File)

Example:

• Server Memory: 1 TB
• PGA_AGGREGATE_TARGET: 100 GB
• SGA_TARGET: 800 GB
• DB_CACHE_SIZE: 500 GB
• Flash Cache Size = 4 TB

You can configure a flash cache of about eight times DB_CACHE_SIZE.

500 GB x 8 = 4 TB

Flash Cache metadata uses DRAM. For this example, assume 200 bytes of metadata per block. Estimate its requirement with this formula:

Metadata = Flash Cache Size x (200 / 8192)

For a 4 TB flash cache, the metadata needs about 97 GB of DRAM.

4 TB x (200 / 8192) = 97 GB

View the Flash Cache Usage

Use this query to compare DRAM and flash buffers for a pluggable database (PDB):

ALTER SESSION SET CONTAINER = pdb_name;

SELECT *
FROM
  (SELECT COUNT(*) AS DRAM_BUFFERS FROM v$bh WHERE status IN ('xcur', 'mrec')),
  (SELECT COUNT(*) AS FLASH_BUFFERS FROM v$bh WHERE status = 'flashcur');

For flash cache usage at the container database (CDB) level, see Monitoring True Cache with the V$TRUE_CACHE_STAT View.

Calculate the Flash Cache Hit Ratio

You can find the True Cache hit ratios in the V$TRUE_CACHE_STAT view.

To calculate the hit ratios manually, use the following formulas:

Flash Cache Hit Ratio = (Physical Read Flash Cache Hits / Physical Reads Cache) x 100%

True Cache Hit Ratio = (Consistent Gets from Cache + DB Block Gets from Cache + Physical Read Flash Cache Hits) / (Consistent Gets from Cache + DB Block Gets from Cache + Physical Reads Cache) x 100%

Note: A high usage ratio with a low hit ratio can indicate that True Cache is too small for the workload.

Related Topics

• Managing Memory in Database Administrator’s Guide
• Configuring the Database Buffer Cache in Database Performance Tuning Guide