Step 1) Define the INMEMORY_SIZE
Customer has to setup the correct value for a database initialization parameter , INMEMORY_SIZE. This parameter specifies the amount of memory, from the SGA, that is to be used for the In-Memory column store. This is a static pool (ie Automatic memory management cannot extend or shrink this area), which means that you have to restart the database if any changes to this parameter needs to take effect. The In-Memory area is sub-divided into two pools: a 1MB pool used to store the actual column formatted data populated into memory, and a 64K pool used to store metadata about the objects that are populated into the IM column store.
sho parameter inmemory_size
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_size big integer 500M
select pool,alloc_bytes,used_bytes,populate_status from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS
-------------------------- ----------- ---------- --------------------------
1MB POOL 418381824 0 DONE
64KB POOL 100663296 0 DONE
Step 2) Mark the performance critical objects in your database, with the attribute INMEMORY
select partition_name,bytes/(1024),inmemory,inmemory_compression from dba_segments where
owner = 'SH' and segment_name = 'SALES'
PARTITION_NAME BYTES/(1024) INMEMORY INMEMORY_COMPRESS
------------------------------ ------------ -------- -----------------
SALES_Q1_1998 8192 DISABLED
ALTER TABLE SH.SALES MODIFY PARTITION SALES_Q1_1998 INMEMORY PRIORITY CRITICAL MEMCOMPRESS FOR QUERY HIGH;
Table altered.
select partition_name,bytes/(1024),inmemory,inmemory_compression from dba_segments where
owner = 'SH' and segment_name = 'SALES' 2 ;
PARTITION_NAME BYTES/(1024) INMEMORY INMEMORY_COMPRESS
------------------------------ ------------ -------- -----------------
SALES_Q1_1998 8192 ENABLED FOR QUERY HIGH
Step 3) Populate the In-Memory datastore
Objects are populated into the In-Memory Datastore, in a prioritized list, immediately after the database is opened, or after they are scanned for the first time. There are 7 levels for the keyword PRIORITY (CRITICAL, HIGH, MEDIUM, LOW, NONE).
The IM column store is populated by a set of background processes referred to as worker processes (ora_w001_orcl). The database is fully active / accessible while this occurs.Each worker process is given a subset of database blocks from the object to populate into the IM column store. Population is a streaming mechanism, simultaneously columnizing and compressing the data. There is a new IMCO (In memory co-ordinator) background process which wakes up every 2 minutes and checks to see if there are any population tasks that need to be completed. Eg: A new object has been marked as InMemory with a PRIORITY other than None.
select v.owner,v.segment_name,v.partition_name,v.bytes orig_size,v.inmemory_size in_mem_size
OWNER SEGMENT_NA PARTITION_NAME ORIG_SIZE IN_MEM_SIZE
---------- ---------- ------------------------------ ---------- -----------
SH SALES SALES_Q1_1998 8388608 1179648
select * from
(
select /*+ full(sales) */ channel_id,count(*)
from sh.sales partition (sales_q1_1998)
group by channel_id
order by count(*) desc
)
where rownum < 6
CHANNEL_ID COUNT(*)
---------- ----------
3 32796
2 6602
4 3926
9 363
Elapsed: 00:00:00.09
select * from table (dbms_xplan.display_cursor());
PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------------------------------
SQL_ID 40pjk921r3jrc, child number 0
-------------------------------------
select * from ( select /*+ full(sales) */ channel_id,count(*) from
sh.sales partition (sales_q1_1998) group by channel_id order by
count(*) desc ) where rownum < 6
Plan hash value: 2962696457
---------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time | Pstart| Pstop |
---------------------------------------------------------------------------------------------------------
PLAN_TABLE_OUTPUT
---------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 12 (100)| | | |
|* 1 | COUNT STOPKEY | | | | | | | |
| 2 | VIEW | | 4 | 104 | 12 (34)| 00:00:01 | | |
|* 3 | SORT ORDER BY STOPKEY | | 4 | 12 | 12 (34)| 00:00:01 | | |
| 4 | HASH GROUP BY | | 4 | 12 | 12 (34)| 00:00:01 | | |
| 5 | PARTITION RANGE SINGLE | | 43687 | 127K| 9 (12)| 00:00:01 | 5 | 5 |
| 6 | TABLE ACCESS INMEMORY FULL| SALES | 43687 | 127K| 9 (12)| 00:00:01 | 5 | 5 |
---------------------------------------------------------------------------------------------------------
For much more in-depth technical details of the Oracle Database In-Memory, please see this whitepaper.