Oracle Memory Areas Interview Questions and Answers

FAQ: Oracle memory areas

1. What Oracle memory areas are there?
There is a distinction between shared memory areas that can be called by all Oracle processes, and process-local memory, which is assigned to exactly one process in each case.

System Global Area (SGA, shared memory)

Buffer Pool (or "Data buffer", "Cache"): Buffer for the data blocks.
Shared Pool : Buffer for parsed SQL statements ("Shared SQL Area" or "Shared Cursor Cache" or "Library Cache") and Oracle DDIC information ("Dictionary Cache" or "Row Cache")
Java Pool: Java buffer
Large Pool : Buffer for special data (for example, when using Multi-threaded server, RMAN with several I/O slaves, or activating PARALLEL_AUTOMATIC_TUNING).
Streams Pool (Oracle 10g or later): Pool for Oracle streams
Redo Buffer: Buffer for redo log data.

Program global area (PGA, process-local memory)
This is a buffer for sortings, Hash Joins, bitmap operations and other temporary local memory requirements (for example, during the parsing of SQL statements); if the buffer is no longer sufficient, the PSAPTEMP temporary tablespace is also used.

Operating system process memory
More memory is needed for the administration of processes at operating system level. While the text section (which contains the executable program) only exists once and is used by all processes, there are other areas locally, such as data or stack, for every process. You must generally expect up to 6 MB operating system-sided memory consumption for each Oracle process (WINDOWS: Oracle thread).

2. What parameters define the memory area sizes?
Unless otherwise specified, parameters are specified in "bytes".

SGA: The size is indirectly determined by the size of the memory areas contained.

Buffer Pool: DB_BLOCK_BUFFERS (unit: Blocks) or DB_CACHE_SIZE when you use the dynamic SGA as described in Note 617416.
Shared Pool : SHARED_POOL_SIZE
Java Pool: JAVA_POOL_SIZE
Large Pool : LARGE_POOL_SIZE
Streams Pool (Oracle 10g or later): STREAMS_POOL_SIZE
Redo Buffer: LOG_BUFFER

In addition, in the context of the dynamic SGA (Note 617416), you can define the parameter SGA_MAX_SIZE which sets an upper limit for the total size of the SGA. In general, you can only increase the size of parameters, such as DB_CACHE_SIZE or SHARED_POOL_SIZE, up to the size defined by SGA_MAX_SIZE .

As of Oracle 10g, parameter SGA_TARGET is also available. You can set this instead of parameters DB_CACHE_SIZE, SHARED_POOL_SIZE, JAVA_POOL_SIZE, LARGE_POOL_SIZE and STREAMS_POOL_SIZE. If necessary, Oracle configures and then adjusts the individual SGA components within SGA_TARGET. In this context, when you stop the database, double-underscore parameters are also written to the Oracle profile to make a note of the current size of the individual memory areas ( for example, __SHARED_POOL_SIZE, __DB_CACHE_SIZE). However, this dynamic adjustment of the areas can be problematic in some cases, if one of the areas increases significantly in size and, as a result, other areas decrease significantly in size. Therefore, SAP only recommends that you use SGA_TARGET (if you use it at all) if DB_CACHE_SIZE and SHARED_POOL_SIZE are set to sufficiently large values as well. In connection with SGA_TARGET, these parameters specify lower limits for the area values, below which the values cannot fall.

PGA: The PGA allocation is dynamic and can be affected by the parameters SORT_AREA_SIZE, HASH_AREA_SIZE, BITMAP_MERGE_AREA_SIZE and CREATE_BITMAP_AREA_SIZE or PGA_AGGREGATE_TARGET when you use the automatic PGA administration (see Note 619876).

Operating system memory: The sizes of the areas are predefined by the executable. However, adjustments are possible in individual cases:

WINDOWS: You can use ORASTACK to reduce the stack size (Note 130140).
AIX: CONST pointers can be moved into the shared area to reduce the local process data (Note 750205).

3. What are useful sizes for the individual memory areas?
Buffer Pool:

The buffer pool size generally has the greatest influence on database performance. A larger buffer pool means that the system needs to carry out fewer time-consuming disk-accesses. Ensure that the data buffer quality is greater than 94%, as described in Note 618868. The following applies to the buffer pool: the larger, the better (provided that sufficient physical memory is available). Note that the following side effects may occur with a large buffer pool:

Oracle 9i or lower: The BEGIN BACKUP times are proportional to the size of the buffer pool (see Note 875477).
Oracle 10.2.0.3 or higher: The runtimes of operations that involve a large data amount (for example, imports) may be much longer with large buffer pools due to block list scans in Oracle (see Note 1028099).

Shared Pool

The shared pool should be at least 400MB in size. In addition, you should fulfill the approximations contained in Note 618868. Also take into account the recommendations given in Note 690241.

You should not configure the shared pool to a size much greater than that recommended in Note 690241, as this can cause serious performance problems. Also see Notes 767414 and 619188 ("row cache lock").

Java Pool

If no Java components are used, the size of the Java pool can be 0. Java is not used in the R/3 standard.

Large Pool

You do not have to configure the large pool explicitly. Memory consumption is small in comparison with the other memory areas.

Streams Pool (Oracle 10g or later)

The streams pool can be defined with size 0 since streams are, by default, not used in the SAP environment
Streams are used as part of Data Pump (Note 1013049). Oracle automatically allocates 10 % of the size of the shared pool in the buffer pool for a streams pool configured with size 0. This implicit reduction of the buffer pool must be taken into account if you want to use Data Pump.

Redo Buffer

A good value for the Redo Buffer is 1MB, which should never be exceeded.

PGA

The larger you set the *_AREA_SIZE parameter or PGA_AGGREGATE_TARGET, the more PGA operations can be performed in the memory. As a starting point, refer to the recommendations in Notes 124361 and 619876.

4. How do I determine Oracle's current memory requirements?
In accordance with the information above, the areas for Java pool, large pool, streams pool and Redo Buffer may be neglected in terms of size. Therefore, the following formula approximately defines the amount of memory currently allocated by Oracle:

Oracle Memory = Buffer Pool + Shared Pool + PGA + Process Memory

Due to their static size, it is easy to determine the size of the buffer and shared pools from the underlying parameters, but calculating PGA memory consumption is more complicated, since this can vary over time. You can determine the actual consumption of PGA memory using the following query:

SELECT VALUE FROM V$PGASTAT WHERE NAME = 'total PGA allocated';

The following applies for the conventional PGA administration: The longer the database processes have already been running, the greater the PGA memory consumption, since no further PGA memory is released.

The size of the process-local memory is mainly determined by the number of Oracle processes. If you assume that a process allocates 5 MB memory on average, you can determine the process-local memory as follows:

SELECT
   MAX_UTILIZATION MAX_PROCESSES,
   5 * MAX_UTILIZATION PROCESS_LOCAL_MEMORY_MB
FROM
   V$RESOURCE_LIMIT
WHERE
   RESOURCE_NAME = 'processes';

5. How do I determine the PGA consumption of individual Oracle processes?
The following statement allows you to determine the Oracle sessions and the client processes associated with them that currently have the highest PGA consumption:

SELECT * FROM
   (SELECT
   SS.SID SID,
   TO_CHAR(SS.VALUE / 1024 / 1024, 9990.99) "PGA (MB)",
   SUBSTR(S.MACHINE, 1, 17) HOST,
   SUBSTR(S.PROGRAM, 1, 29) "PROGRAM",
   S.PROCESS "CLIENT PID"
   FROM V$SESSTAT SS, V$STATNAME SN, V$SESSION S
   WHERE
       SS.STATISTIC# = SN.STATISTIC# AND
       SN.NAME = 'session pga memory' AND
       S.SID = SS.SID
   ORDER BY VALUE DESC )
WHERE ROWNUM <=20;

If the client processes are SAP work processes ("dw" in the PROGRAM name), the relevant work process can be identified by means of the client-PID in transaction SM50 or SM66.

6. Where can I find details on the memory consumption of the shared pool
There are several sources from which the size of the shared pools can be determined. The calculated size can vary depending on the origin of the data:
The memory consumption of the shared pool is determined by parameter SHARED_POOL_SIZE. Up to and including Oracle 9i, if you use a non-dynamic SGA, there are also certain subcomponents of the shared pool (such as "db_block_buffers") that are created outside of SHARED_POOL_SIZE. As a result, the memory consumption of the shared pool is sometimes several hundred MB larger than specified by SHARED_POOL_SIZE. You can use the following query to determine the actual memory requirement of the shared pool:

SELECT SUM(BYTES) FROM V$SGASTAT WHERE POOL = 'shared pool';

You can use the following statement to determine the areas sorted according to size within the shared pool:

SELECT BYTES, NAME FROM V$SGASTAT WHERE POOL = 'shared pool'
ORDER BY BYTES;

The size that is displayed for SHARED_POOL_SIZE in transaction ST04 was historically equated with the "Variable Size" of view V$SGA. However, this variable size also includes memory areas such as the Large Pool or the Java Pool. The size displayed in ST04 was therefore bigger than the SHARED_POOL_SIZE parameter in many cases.

When you use the dynamic SGA on Oracle 9i, the freespace that is not yet allocated under SGA_MAX_SIZE is also added to the "Variable Size", which is why the size displayed in ST04 for the Shared Pool could be significantly higher than the value of the SHARED_POOL_SIZE parameter.

As of Oracle 9i, the view V$SGA_DYNAMIC_COMPONENTS is available, which specifies the size of SHARED_POOL_SIZE correctly and which is accessed by Transaction ST04 after you implement the correction from Note 776021.

7. How I can determine which objects are represented with the most blocks in the buffer pool?

The following SQL statement returns the 20 objects that are represented with the most blocks in the buffer pool:
sqlplus "/ as sysdba"
SELECT SUBSTR(OWNER, 1, 20) OWNER,
       SUBSTR(NVL(OBJECT_NAME,
         TO_CHAR(OBJ) || ' (UNDO?)'), 1, 35) OBJECT,
       CNT BLOCKS
FROM (SELECT O.OWNER, O.OBJECT_NAME, O.DATA_OBJECT_ID, B.OBJ,
             B.CNT, O.OBJECT_TYPE, ROW_NUMBER() OVER
             (PARTITION BY O.DATA_OBJECT_ID
               ORDER BY O.OBJECT_TYPE) CLUSTRN
       FROM DBA_OBJECTS O,
           (SELECT OBJ, CNT, ROWNUM
             FROM (SELECT OBJ, COUNT(*) CNT
                   FROM X$BH
                   GROUP BY OBJ
                   ORDER BY CNT DESC)
             WHERE ROWNUM <= 20) B
       WHERE O.DATA_OBJECT_ID (+) = B.OBJ)
WHERE CLUSTRN = 1
ORDER BY CNT DESC;

8. What characteristic errors can occur during memory allocation?
ORA-04031: unable to allocate bytes of shared memory

ORA-04031 occurs if there is no longer sufficient space available in the shared pool. This may be because:

The shared pool is too small (less than 400MB)
There are SQL statements that consume a lot of memory, due to long IN lists, for example (see Note 133351).
As of Oracle 9i: The sub-pools are too small (see Note 690241).

For further information, see Note 869006.

ORA-04030: out of process memory when trying to allocate bytes

The system issues the error ORA-04030 if it cannot allocate any more PGA memory. This happens if the memory allocation reaches the limits set by the operating system (for example, the virtual address space of a process, or available swap). In general, this error occurs on systems with 32-bit architecture that have restricted virtual address space.

For example: In the case of an Oracle installation on a 32-bit Windows system, where the 3GB option is not activated, a total of 2GB of virtual address space is available. Oracle can use approximately 1.7GB of that. If you allocate 1GB for the buffer pool and 400MB for the shared pool, another 300MB of PGA memory remains available to all Oracle processes. Error ORA-04030 occurs if this limit is exceeded.

While all Oracle processes run as threads in a single operating system process on Windows - and therefore, in the example above, ALL processes must share the remaining 300MB - on UNIX there is a separate operating system process for every Oracle process. This means that on a UNIX system, EACH INDIVIDUAL process can allocate the rest of the available virtual memory as PGA memory.

If an ORA-04030 occurs, to solve the problem you can:

Switch to a 64-bit operating system.
Use the automatic PGA administration (Note 619876), so that PGA memory is released again after being used.
WINDOWS: Activate the 3GB option (see Note 142019).
WINDOWS: Activate AWE (see Note 441663).
LINUX: Configure an SGA greater than 3GB (see Note 804186)
Restart all work processes regularly (see Note 101717). This also restarts the Oracle shadow processes and releases the PGA memory that was allocated so far. This is less useful if you are using automatic PGA administration.
UNIX: Adjust the operating system parameters (such as ULIMIT settings or process-memory-limitation parameters; see also Note 502782).
Set event 10191 as described in Note 128221.
Reducing the buffer pool (DB_CACHE_SIZE or DB_BLOCK_BUFFERS for non-AWE, AWE_WINDOW_MEMORY for AWE) or shared pools so that more virtual memory is available for the PGA memory (Caution: this may negatively affect performance!).
Reduction of the PGA parameters (Caution: this may negatively affect performance!).
Use a Multi Threaded Server to reduce the number of Oracle processes (Caution: Note the restrictions from Note 70197)
Avoid using USE_CONCAT hints in connection with long IN lists, since this combination can consume a great deal of PGA memory while parsing.
Activate automatic PGA administration (WORKAREA_SIZE_POLICY = AUTO) in order to avoid a high PGA consumption in connection with INSERTs (refer to Note 71624 (18)).