Oracle 12c Memory Innovations: Smart Flash Cache, PGA Limits & In‑Memory Column Store

Oracle Database 12c (first released in June 2013 and subsequently updated) introduced a series of memory‑related features aimed at boosting OLTP and OLAP workloads, prompting DBAs to understand their purpose, configuration, and impact.

Feature 1: Extended Database Cache – Smart Flash Cache

Oracle 12c adds a secondary cache called the Smart Flash Cache (SGA extension) that stores data blocks on fast SSD devices, reducing physical I/O and improving read performance for both OLTP and data‑warehouse workloads. The feature relies on the parameters db_flash_cache_file (path to SSD, e.g., /dev/sda) and db_flash_cache_size (typically 2‑10× the buffer cache size).

db_flash_cache_file – specifies the SSD device or file path.

db_flash_cache_size – defines the cache size, recommended 2‑10 times the buffer cache.

Effective use requires Solaris or Oracle Linux, sufficient CPU resources, and awareness of the db file sequential read wait event.

Feature 2: PGA Memory Limit – pga_aggregate_limit

The pga_aggregate_limit parameter caps the total PGA memory allocated to an instance, preventing runaway memory consumption that could otherwise cause excessive paging and instability. The default is 2 GB; when exceeded, Oracle terminates or suspends sessions that cannot be optimized for PGA usage.

Feature 3: In‑Memory Column Store

Oracle 12c introduces an In‑Memory (IM) column store that keeps data in a compressed columnar format, dramatically accelerating OLAP‑type queries. Data is loaded into two internal pools (1 MB for column data, 64 KB for metadata) and managed via the INMEMORY attribute on tablespaces, tables, partitions, or materialized views.

Key concepts:

Compression levels range from No Memcompress to high‑capacity options, with typical default Memcompress for query low.

In‑Memory scans read only required columns and apply predicates without decompression.

Automatic creation of In‑Memory indexes stores min/max values per IMCU, enabling fast range filtering.

SIMD (single‑instruction‑multiple‑data) processing evaluates multiple column values in a single CPU instruction, as illustrated by the example of counting PROMO_ID = 9999 in the SCOTT.SALES table.

In‑Memory joins leverage Bloom filters for efficient multi‑table joins.

Feature 4: Temperature‑Based Object‑Level Replacement Algorithm

Starting with 12.1.0.2, Oracle uses a temperature‑based algorithm to decide which objects stay in the large‑table cache. The algorithm considers object “temperature” (CRITICAL, HIGH, MEDIUM, LOW, NONE) and works together with the DB_BIG_TABLE_CACHE_PERCENT_TARGET parameter, which allocates a percentage of the buffer cache for large‑table scans.

Feature 5: Big Table Cache

When DB_BIG_TABLE_CACHE_PERCENT_TARGET is set (e.g., 80), 80 % of the buffer cache is reserved for scanning large tables, while the remaining 20 % serves OLTP workloads. The views v$bt_scan_cache and v$bt_scan_obj_temps expose cache usage details.

Monitoring and Managing In‑Memory

DBAs can query v$im_segments and v$im_user_segments to see which objects and columns are loaded into the IM column store, and v$im_column_level to inspect column‑level loading status.

Core initialization parameters for In‑Memory include INMEMORY_SIZE, INMEMORY_QUERY, and INMEMORY_MAX_POPULATE_SERVERS. Additional controls such as INMEMORY_CLAUSE_DEFAULT, INMEMORY_FORCE, and OPTIMIZER_INMEMORY_AWARE let DBAs fine‑tune loading behavior and optimizer cost calculations.

In summary, Oracle 12c provides a rich set of memory‑centric features—Smart Flash Cache, PGA limits, In‑Memory column store, temperature‑based replacement, and big‑table cache—that, when configured according to workload characteristics, can significantly improve query performance for both OLTP and OLAP scenarios.