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.9Gi

Check 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

Leave a Reply