DBA Notes: 2011/10/14

Cheng Li

 

Manage Oracle Databases Memory in Linux

 

Oracle database level

 

At Oracle database level, we use AMM to manage the memory automatically. For this example: The CMSPRD instance was last opened on 26-APR-10 and no parameter change  had been made since then.

 

 

SQL> select * from v$instance;

 

INSTANCE_NUMBER INSTANCE_NAME

--------------- ----------------

HOST_NAME

----------------------------------------------------------------

VERSION           STARTUP_T STATUS       PAR    THREAD# ARCHIVE LOG_SWITCH_WAIT

----------------- --------- ------------ --- ---------- ------- ---------------

LOGINS     SHU DATABASE_STATUS   INSTANCE_ROLE      ACTIVE_ST BLO

---------- --- ----------------- ------------------ --------- ---

              1 CMSPRD

ctmcmsdb.ctm.plc.cwintra.com

11.1.0.6.0        26-APR-10 OPEN         NO           1 STARTED

ALLOWED    NO  ACTIVE            PRIMARY_INSTANCE   NORMAL    NO

 

SQL> show sga

 

Total System Global Area 5344731136 bytes

Fixed Size                  2144184 bytes

Variable Size            3892316232 bytes

Database Buffers         1409286144 bytes

Redo Buffers               40984576 bytes

SQL> show parameter target

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

archive_lag_target                   integer     0

db_flashback_retention_target        integer     1440

fast_start_io_target                 integer     0

fast_start_mttr_target               integer     0

memory_max_target                    big integer 5G

memory_target                        big integer 5G

pga_aggregate_target                 big integer 0

sga_target                           big integer 0

 

 

       AMM Parameters

    Automatic memory management is configured using two new initialization parameters:

 

MEMORY_TARGET: The amount of shared memory available for Oracle to use when dynamically controlling the SGA and PGA. This parameter is  dynamic, so the total amount of memory available to Oracle can be increased or decreased, provided it does not exceed the  MEMORY_MAX_TARGET limit. The default value is "0".

 

For CMSPRD,

memory_target                        big integer 5G

 

MEMORY_MAX_TARGET: This defines the maximum size the MEMORY_TARGET can be increased to without an instance restart.

                   If the  MEMORY_MAX_TARGET is not specified, it defaults to MEMORY_TARGET setting.

 

When using automatic memory management, the SGA_TARGET and PGA_AGGREGATE_TARGET act as minimum size settings for their respective memory  areas. To allow Oracle to take full control of the memory management, these parameters should be set to zero.

 

For CMSPRD,

pga_aggregate_target                 big integer 0

sga_target                           big integer 0

 

Linux OS level

 

From OS level, we can apply Linux/Unix command to check and manage memory usage.

 

[oracle@ctmcmsdb dbalog]$ free

             total       used       free     shared    buffers     cached

Mem:      16439076   16227192     211884          0     231184   14516416

-/+ buffers/cache:    1479592   14959484

Swap:      2031608     357324    1674284

 

We can see that most of the memory use is for buffers and cache since Linux always tries to use RAM to the fullest extent to speed up disk operations. So, we can safely say the memory DB box is enough.

 

 

The physical memory usages are shown in the buff and cache columns of VMSTAT outputs, which is matching with the eHealth monitored usages.

 

[oracle@ctmcmsdb dbalog]$ vmstat 2 10

procs -----------memory----------   ---swap-- -----io---- --system-- -----cpu------

 r  b   swpd   free   buff  cache     si   so    bi    bo   in   cs  us sy id wa st

 8  0 357324 213720 231068 14515852    0    0   492   201    0    0   2  0 95  3  0

 3  1 357324 210448 231072 14515876    0    0     0   538 1135 1587  79  1 16  4  0

 1  0 357324 213944 231072 14515880    0    0     0   590 1141 1636  51  1 31 18  0

 1  0 357324 213944 231072 14515904    0    0     0     0 1052 1274  32  0 68  0  0

 0  0 357324 213952 231076 14515900    0    0     0   136 1036 1278  28  0 72  0  0

 0  0 357324 213952 231076 14515904    0    0     0    16 1055 1337   1  0 99  0  0

 1  0 357324 213944 231084 14515900    0    0     0    36 1012 1228  0  0 100  0  0

 0  1 357324 214092 231084 14515912    0    0     0    20 1006 1265  0  0 100  0  0

 0  0 357324 214092 231084 14515912    0    0     0    22 1007 1165  0  0 100  0  0

 0  0 357324 214100 231092 14515904    0    0     0    34 1008 1106  0  0 100  0  0

 

Memory

·         swpd: the amount of virtual memory used. That is the portion of swap partition or swap file in use, to have this used is not bad at all, and it is kind of normal.

·         free: the amount of idle memory. Your physical free memory, the same that the free command will output, if you constantly have this in a number close to 0 is bad.

·         buff: the amount of memory used as buffers. Is the memory that is being used as a "virtual" disk, when data is read from the disk, the kernel tries to keep it in the memory in case of future need, this will be done only in physical memory is available, to speed up processes. (That is why if you open OpenOffice and close it and open it again the second time it goes faster.

·         cache: the amount of memory used as cache. The cache is where Linux puts the data that is intended to go to the disk, this is once again to speed up processes, as memory is faste that the disk the data is first put in this cache, and the flushed to the disk in the background, this is also useful if the process that wrote this info to the disk (cache) needs it again, it will be read from the cache.

·         inact: the amount of inactive memory. (-a option)

·         active: the amount of active memory. (-a option)

 

 

The SI and SO columns show that theere is NO swapping.

 

Swap

·                si: Amount of memory swapped in from disk (/s).

·                so: Amount of memory swapped to disk (/s).

 

 

So, where do you need to look at to detect if you have memory issues in your Linux system.

A good place to start is free memory, but consider that also buffered and cached memory is somehow "free" memory, so do not run so fast to buy more memory yet, maybe the most important place to check is si and so, they show the data transferred in and out from the memory to the disk, if you find these counters always changing mean that you system is constantly paging the memory to the disk, which of course mean that you need more memory, or less processes running at a time

 

On CTMCMSDB, there is NO paging  of memory to disk therefore there is also NO memory issue at Linux level.

 

 

For virtual memory,

 

[oracle@ctmcmsdb dbalog]$ free

             total       used       free     shared    buffers     cached

Swap:      2031608     357324    1674284

 

·         swpd: the amount of virtual memory used. That is the portion of swap partition or swap file in use, to have this used is not bad at all, and it is kind of normal.

 

Reference:

http://www.puschitz.com/TuningLinuxForOracle.shtml

http://www.oracle-base.com/articles/11g/AutomaticMemoryManagement_11gR1.php

http://linuxconfig.org/Linux_lvm_-_Logical_Volume_Manager