Introduction
A set of reporting and analytic queries against a large, frequently scanned table were taking longer than expected, with a large share of the response time going into repeated buffer cache and disk I/O for full and range scans rather than actual computation.
Why We Need to Do This
The Oracle buffer cache stores data row by row, which is efficient for OLTP style single row access but is not ideal for analytic queries that scan and aggregate large numbers of rows across a few columns. Oracle Database In-Memory addresses this by keeping a compressed, columnar copy of selected tables in a dedicated memory area, so scan and aggregate heavy queries can be answered largely from memory instead of walking row based blocks.
Steps to enable In-memory
Step 1 : Free up memory for the In-Memory (IM) column store by reducing sga_target
SQL> ALTER SYSTEM SET sga_target=<calculated_value> SCOPE=SPFILE SID=’*’;
Step 2 : Allocate the IM pool. This is a static parameter and requires a restart to take effect
SQL> ALTER SYSTEM SET inmemory_size=<calculated_value> SCOPE=SPFILE SID=’*’;
Step 3 : Set the number of background population workers, based on available CPU cores
SQL> ALTER SYSTEM SET inmemory_max_populate_servers=4 SCOPE=SPFILE SID=’*’;
Step 4 : Restart the database to apply the static parameters
SQL> SHUTDOWN IMMEDIATE;
SQL> STARTUP;
Step 5 : Verify the IM pool has been allocated
SELECT pool, alloc_bytes/1024/1024 AS alloc_mb,
used_bytes/1024/1024 AS used_mb,
populate_status
FROM v$inmemory_area;
Step 6 : At the PDB level, enable In-Memory on the target table with the desired compression and priority
ALTER SESSION SET CONTAINER = <pdb_name>;
ALTER TABLE <schema>.<table_name>
INMEMORY MEMCOMPRESS FOR QUERY LOW PRIORITY HIGH;
Step 7 : Confirm the table is enabled for In-Memory and check its population status
SELECT owner, segment_name, segment_type, inmemory_size,
bytes, populate_status
FROM v$im_segments;
SELECT owner, table_name, inmemory, inmemory_priority,
inmemory_compression
FROM dba_tables
WHERE table_name = ‘<TABLE_NAME>’
AND owner = ‘<SCHEMA>’;
Step 8 : Population behavior depends on the priority level assigned. Tables with priority CRITICAL, HIGH, MEDIUM, or LOW populate automatically, while priority NONE requires a manual population. Even for CRITICAL priority, automatic population can take a few minutes; if the data is needed immediately, populate it manually
EXEC DBMS_INMEMORY.POPULATE(‘<SCHEMA>’, ‘<TABLE_NAME>’);
Conclusion
Allocating a dedicated In-Memory column store and enabling it on the target table let scan and aggregate heavy queries read a compressed, columnar copy of the data straight from memory instead of relying on row based buffer cache and disk access. This gave a noticeable improvement in query response times for our reporting workload, and the priority setting gives us control over which tables populate automatically versus on demand.
