Implement in-memory in your RAC database

 

Oracle has provided In-memory feature at base_level for free. You use 16GB allocation limit for this feature to be used without the licence.

I will take you step by step on how to implement in-memory in your RAC database for the table that require HIGH I/Os.

1. Check the size of the Table

SELECT segment_name, segment_type, bytes/1024/1024 AS “Size(MB)” FROM dba_segments WHERE segment_name = ‘<Table_Name>’;
SEGMENT_NAME SEGMENT_TYPE Size(MB)
—————————— —————— ———-
TABLE_NAME TABLE 1665

 

2. Set this parameter so you may dont accidentally cross the limits for in-memory feature usage in your system

alter system set inmemory_force=base_level scope=spfile sid=’*’;

3. Set the size of the inmemory, please note this will be taken from the SGA as inmemory is the part of SGA so whatever you are planning to set this value to should be added in the SGA.

alter system set inmemory_size=5G scope=spfile sid=’*’;

4. Restart the Database

5. Enable the Table to be used for inmemory

ALTER TABLE <Table_Name> INMEMORY PRIORITY CRITICAL;

6. Check the status of the Table using the below query

COLUMN table_name FORMAT A20
SELECT table_name,
inmemory,
inmemory_priority,
inmemory_distribute,
inmemory_compression,
inmemory_duplicate
FROM dba_tables
WHERE owner='<owner>’ and table_name='<table>’
ORDER BY table_name;

 

7. Check the status of the Table if it has populated in the memory, it will be ditributed among the RAC instances by default

COLUMN segment_name FORMAT A20
select segment_name,inmemory_size,bytes_not_populated from gv$im_segments;

8. If want to load it manually use the below command

exec dbms_inmemory.populate(‘owner’,’table’);

 

9. Check again

select inst_id,segment_name,inmemory_size,bytes_not_populated,populate_status,INMEMORY_DISTRIBUTE from gv$im_segments;