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
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