Oracle Point, Oracle Life.

1. Note:2060096.6 Monitoring Memory Use

• describe couple of Unix commands which DBA may need to use frequently, such as ipcs, ps, pstat, vmstat, sar, and top

2. Note:174555.1 UNIX: Determining the Size of an Oracle Process

• ps, ipcs, pmap ? which one is good for presenting accurate statistics? It’s pmap.
• HOW MUCH MEMORY IS ORACLE ACTUALLY USING? Issue,
• select sum(value) from (select value, n.name|| ‘(’||s.statistic#||’)’ , sid
  from v$sesstat s , v$statname n
  where s.statistic# = n.statistic#
  and n.name like ‘%ga memory%’
  order by value);
• do heapdump to diagnose memory problem, such as memory leak.
• (script of “Dispaly Usage of UGA and PGA for all sessions, including background process”)
• referene book: Oracle Expert one-on-one chapter 2: Architechture

3. Note:17094.1 Unix Virtual Memory, Paging & Swapping explained

• The physical memory in Unix is devided to three uses: OS Kernel, I/O subsystem, and Page Pool.
• Unix process is devided into two portions: Text and Data.
• a UNIX process is divided into six segments: text, stack, heap, BSS, initialized data, and shared
  memory.
• desribed usage of ps, pstat, vmstat, sar
• This is a very good recource to know Unix memory internal use (and with Oracle).

4. Note:15566.1 Unix Semaphores and Shared Memory Explained

• Oracle has 3 different possible models for the SGA - one-segment, contiguous multi-segment, and non-contiguous multi-segment.
• The entire SGA must fit into shared memory, so the total amount of shared memory allocated under any model will be equal to the size of the SGA.
• SHMMAX > size of SGA
• SHMMAX - kernel parameter controlling maximum size of one shared memory
  segment
  SHMMNI - kernel parameter controlling maximum number of shared memory segments
  in the system
  SHMSEG - kernel parameter controlling maximum number of shared memory segments
  a process can attach
  SEMMNS - kernel parameter controlling maximum number of semphores in
  the system
  SEMMNI - kernel parameter controlling maximum number of semaphore
  sets. Semphores in Unix are allocated in sets of 1 to SEMMSL.
  SEMMSL - kernel parameter controlling maximum number of semaphores in a
  semphore set.
  SHMLBA - kernel parameter controlling alignment of shared memory
  segments; all segments must be attached at multiples of this value.
  Typically, non-tunable.

5. Note:68281.1 DETERMINING WHICH INSTANCE OWNS WHICH SHARED MEMORY & SEMAPHORE SEGMENTS

• This is helpful when in recovery situations where the instance may not have released the shared memory or semaphores on database shutdown.
• It’s helpful to remove share memory segments and semaphore segments

6. Note:153961.1 Semaphores and Shared Memory - An Overview

• Semaphores and shared memory are two very distinct sets of Operating System resources. Semaphores are a system resource that Oracle utilizes for interprocess communication and they occupy a relatively small memory space, while shared memory is utilized to contain the SGA and can garner a large portion of physical memory.
• The most common circumstance in which these errors occur is during the creation of a new database.
• the list of Semaphores related error
• Semaphore related troubleshooting: Note:115235.1 ,

Popularity: 4% [?]

The Metalink Note:396940.1 offers an experimental suggestion on setting up ASMM (Automatic Shared Memory Management).

The rule is:

• Monitor the size of the managed pools
• Observe trends and find the right values for a minimum size for the various pools.
• Use these hard-coded values for the pool parameter settings in your initialization file.
• Set SGA_TARGET to 25% above the sum of the fixed and hard-coded settings.
• Set SGA_MAX_SIZE to 25% above the setting for SGA_TARGET.

NOTE:  There have been a number of issues filed on 10.2.x with ASMM and ORA-4031.   Many customers do not set minimum sizes for the various auto-tuned pools instead relying on sga_target and MMAN to move memory around as is needed.   Internal tests and from discussions with development, it is better to find a minimum setting for these pool and manually set that in the spfile or init parameter file.   As a starting point, review data in v$sga_dynamic_components and manually assign values to the pools at 70 or 75% of the current_size.   If there are indications of failed attempts to shrink the shared pool below that number over time, then decrease the default setting by another 10% and monitor that the new value is a better minimum setting.   This will help with decreasing chances of seeing excessive pinging of memory between the buffer cache and the various pools.

Popularity: 3% [?]