Configure Oracle Database 12c In-Memory Option :-
The In-Memory Column store:
- Database In-Memory uses an In-Memory column store, which is a new component of the System Global Area (SGA), called the In-Memory Area. Data in the IM column store does not reside in the traditional row format, instead it uses a new column format. The IM column store does not replace the buffer cache, but acts as a supplement, so that data can now be stored in memory in both a row and a column format
- The In-Memory area is a static pool within the SGA, whose size is controlled by the initialization parameter INMEMORY_SIZE (default 0). The current size of the In-Memory area is visible in V$SGA
Enabling In-Memory option in Oracle database :
SQL> show parameter COMPATIBLE; NAME TYPE VALUE ---------- ------- ------ compatible string 12.2.0 SQL> show parameter sga_target; NAME TYPE VALUE ---------- ----------- ------ sga_target big integer 4G SQL> show parameter INMEMORY_SIZE; NAME TYPE VALUE ------------- ----------- ------ inmemory_size big integer 0 SQL> ALTER SYSTEM SET INMEMORY_SIZE=2G scope=spfile; System altered. SQL> SHUTDOWN IMMEDIATE; Database closed. Database dismounted. ORACLE instance shut down. SQL> STARTUP; ORACLE instance started. After startup "In-Memory Area" has allocated. Total System Global Area 4294967296 bytes Fixed Size 8628936 bytes Variable Size 603981112 bytes Database Buffers 1526726656 bytes Redo Buffers 8146944 bytes In-Memory Area 2147483648 bytes Database mounted. Database opened. SQL> SHOW PARAMETER INMEMORY NAME TYPE VALUE ------------------------------------ ----------- -------- inmemory_adg_enabled boolean TRUE inmemory_clause_default string inmemory_expressions_usage string ENABLE inmemory_force string DEFAULT inmemory_max_populate_servers integer 1 inmemory_query string ENABLE inmemory_size big integer 2G inmemory_trickle_repopulate_servers_ integer 1 percent inmemory_virtual_columns string MANUAL optimizer_inmemory_aware boolean TRUE
IM Column store sub divided into two pools:
- 1 MB pool—Store the actual column formatted data
- 64k Pool— Store the metadata about the objects.
We can see the amount memory available in each pool using below query
SQL> select * from v$inmemory_area; POOL ALLOC_BYTES USED_BYTES POPULATE_STATUS CON_ID -------- ------------- ---------- ---------------- ---------- 1MB POOL 1710227456 0 DONE 0 64KB POOL 419430400 0 DONE 0
Disabling In-Memory option :
Alter system set inmemory_size=0 SCOPE=SPFILE; or Alter system reset inmemory_size SCOPE=SPFILE;
Catch Me On:- Hariprasath Rajaram Telegram:https://t.me/joinchat/I_f4DhGF_Zifr9YZvvMkRg LinkedIn:https://www.linkedin.com/in/hari-prasath-aa65bb19/ Facebook:https://www.facebook.com/HariPrasathdba FB Group:https://www.facebook.com/groups/894402327369506/ FB Page: https://www.facebook.com/dbahariprasath/? Twitter: https://twitter.com/hariprasathdba
