HOW TO ENABLE INMEMORY IN ORACLE DATABASE
Check the free available OS memory either linux or any other OS. There is 11GB free total memory and out of it, 9.1 GB is free
[oracle@IMTEST /]$ free -h
total used free shared buff/cache available
Mem: 11Gi 710Mi 9.1Gi 315Mi 1.6Gi 10Gi
Swap: 9.9Gi 0B 9.9GiCheck the meminfo file for all the memory page informations
[oracle@IMTEST /]$ cat /proc/meminfo
MemTotal: 11967576 kB
MemFree: 9534100 kB
MemAvailable: 10773244 kB
Buffers: 3276 kB
Cached: 1661212 kB
SwapCached: 0 kB
Active: 1062896 kB
Inactive: 1126672 kB
Active(anon): 757888 kB
Inactive(anon): 101080 kB
Active(file): 305008 kB
Inactive(file): 1025592 kB
Unevictable: 12368 kB
Mlocked: 12368 kB
SwapTotal: 10338300 kB
SwapFree: 10338300 kB
Dirty: 16 kB
Writeback: 0 kB
AnonPages: 537448 kB
Mapped: 449216 kB
Shmem: 323312 kB
KReclaimable: 41856 kB
Slab: 87372 kB
SReclaimable: 41856 kB
SUnreclaim: 45516 kB
KernelStack: 5552 kB
PageTables: 64024 kB
NFS_Unstable: 0 kB
Bounce: 0 kB
WritebackTmp: 0 kB
CommitLimit: 16322088 kB
Committed_AS: 7569444 kB
VmallocTotal: 34359738367 kB
VmallocUsed: 32872 kB
VmallocChunk: 0 kB
Percpu: 692 kB
HardwareCorrupted: 0 kB
AnonHugePages: 0 kB
ShmemHugePages: 0 kB
ShmemPmdMapped: 0 kB
FileHugePages: 0 kB
FilePmdMapped: 0 kB
CmaTotal: 0 kB
CmaFree: 0 kB
HugePages_Total: 0
HugePages_Free: 0
HugePages_Rsvd: 0
HugePages_Surp: 0
Hugepagesize: 2048 kB
Hugetlb: 0 kB
DirectMap4k: 124864 kB
DirectMap2M: 12457984 kB
Check the total SGA allocated in the database. Here ASMM is used. So check for SGA parameters.
[oracle@IMTEST /]$ sqlplus / as sysdba
SQL> show parameter sga
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
allow_group_access_to_sga boolean FALSE
lock_sga boolean FALSE
pre_page_sga boolean TRUE
sga_max_size big integer 2912M
sga_min_size big integer 0
sga_target big integer 2912M
unified_audit_sga_queue_size integer 1048576
Check the fixed and variable sizes in the sga
SQL> show sga
Total System Global Area 3053450504 bytes
Fixed Size 9139464 bytes
Variable Size 620756992 bytes
Database Buffers 2415919104 bytes
Redo Buffers 7634944 bytes
Check the inmemory parameters. “inmemory_size” is the parameter which determines the total size of inmemory.
SQL> show parameter inmemory
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_adg_enabled boolean TRUE
inmemory_automatic_level string OFF
inmemory_clause_default string
inmemory_expressions_usage string ENABLE
inmemory_force string DEFAULT
inmemory_max_populate_servers integer 0
inmemory_optimized_arithmetic string DISABLE
inmemory_prefer_xmem_memcompress string
inmemory_prefer_xmem_priority string
inmemory_query string ENABLE
inmemory_size big integer 0
NAME TYPE VALUE
------------------------------------ ----------- ------------------------------
inmemory_trickle_repopulate_servers_ integer 1
percent
inmemory_virtual_columns string MANUAL
inmemory_xmem_size big integer 0
optimizer_inmemory_aware boolean TRUE
Increase the value for SGA with 5G and enable INMEMORY with a startup size of 4G. Then reboot the database, since these parameters are static.
SQL> alter system set sga_max_size=5G scope=spfile;
System altered.
SQL> alter system set sga_target=4400M scope=spfile;
System altered.
SQL> alter system set inmemory_size=4G scope=spfile;
System altered.
SQL> startup force;
ORACLE instance started.
Total System Global Area 5242879440 bytes
Fixed Size 9190864 bytes
Variable Size 897581056 bytes
Database Buffers 37748736 bytes
Redo Buffers 3391488 bytes
In-Memory Area 4294967296 bytes <========= This line
Database mounted.
Database opened.
Well done! You have enabled INMEMORY feature in the database