Enable the In-Memory column store in database


SQL> select banner from v$version where banner like '%Database%';
BANNER 
-------------------------------------------------------------------------------- 
Oracle Database 12c Enterprise Edition Release 12.1.0.2.0 - 64bit Production
SQL> select * from v$sgainfo where name='In-Memory Area Size';
NAME BYTES RES CON_ID 
-------------------------------- ---------- --- ---------- 
In-Memory Area Size 0 No 0
SQL> show parameter inmemory
NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
inmemory_clause_default string 
inmemory_force string DEFAULT 
inmemory_max_populate_servers integer 0 
inmemory_query string ENABLE 
inmemory_size big integer 0 
inmemory_trickle_repopulate_servers_ integer 1 
percent 
optimizer_inmemory_aware boolean TRUE
SQL> conn / as sysdba 
Connected. 
SQL> alter system set inmemory_size=50M scope=spfile;
System altered.
SQL> shutdown immediate 
Database closed. 
Database dismounted. 
ORACLE instance shut down. 
SQL> startup 
ORA-64353: in-memory area size cannot be less than 100MB
$ cd /u01/app/oracle/product/12.1.0/dbhome_1/dbs
$ grep inmemory initorclcdb.ora
*.inmemory_size=100M
SQL> create spfile from pfile;
File created.
SQL> startup 
ORACLE instance started.
Total System Global Area 1258291200 bytes 
Fixed Size 3003176 bytes 
Variable Size 905972952 bytes 
Database Buffers 218103808 bytes 
Redo Buffers 13770752 bytes 
In-Memory Area 117440512 bytes 
Database mounted. 
Database opened.
SQL> show parameter inmemory
NAME TYPE VALUE 
------------------------------------ ----------- ------------------------------ 
inmemory_clause_default string 
inmemory_force string DEFAULT 
inmemory_max_populate_servers integer 1 
inmemory_query string ENABLE 
inmemory_size big integer 112M 
inmemory_trickle_repopulate_servers_ integer 1 
percent 
optimizer_inmemory_aware boolean TRUE
SQL> conn anar/password@pdb1 
Connected.
SQL> select * from v$sgainfo where name='In-Memory Area Size';
NAME BYTES RES CON_ID 
-------------------------------- ---------- --- ---------- 
In-Memory Area Size 117440512 No 0
SQL> select * from v$inmemory_area;
POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID 
---------- ----------- ---------- --------------- ---------- 
1MB POOL 82837504 0 DONE 3 
64KB POOL 16777216 0 DONE 3
SQL> select sum(alloc_bytes)/1024/1024 from v$inmemory_area;
SUM(ALLOC_BYTES)/1024/1024 
-------------------------- 
 95

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: