Monday, October 6, 2014

Enable the In-Memory column store (IM 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

oracle@solaris:/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 donghua/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