Oracle Memory Management Methods

Automatic SGA Management
  • You specify the total amount of SGA availabe using SGA_TARGET
  • Oracle automatically distributes the memory among the various SGA components.
  • SGA Components: Pools of memory used to satisfy different classes of memory allocation requests.
    • Shared Pool (Library cache, Data Dictionary Cache, Server Result cache, reserved pool, Private sql areas, etc)
    • Large Pool
    • Database Buffer Cache
    • Java Pool
    • Streams Pool
    • Fixed SGA
  • If you are using an SPFILE, the database remember the sizes of the various components across instance shutdowns (no need to learn workload characteristics at each startup).
  • Oracle allocates/deallocates memory in chuncks of allocation units (granules)
    • If total sga size < 1G ==> granule = 4Mb.
    • If total_sga_size > 1G ==> granule = 16Mb

1. Automatic Instance tuning
  • New on Oracle 11g
  • Set parameters:
    • MEMORY_TARGET (dynamic) and
    • MEMORY_MAX_TARGET (static and optional)
  • Database redistributes memory as needed between SGA and PGA.
  • Oracle automatically calculate the values for SGA_TARGET and PGA_TARGET.
  • MEMORY_TARGET can be dynamically modified.
  • If SGA_TARGET or PGA_AGGREGATE_TARGET are specified, then

2. Automatic SGA and PGA tuning
  • Until Oracle 10g, automatic memory management was configured for the SGA and PGA separately.
  • The amount of memory allocated to both of them was fixed and unused space in one of them could not be used by the other.
  • Set parameters:
    • SGA_TARGET and
    • SGA_MAX_TARGET (optionally) and

  • You can query the V$SSGAINFO view to check the size of the various SGA components and see the allocation unit size (called granule size) that is being used by the instance.
  • granule size is platform-specific and is determined by the total SGA size
SQL> select name, bytes, resizeable from v$sgainfo;

NAME                            BYTES      RES
------------------------------- ---------- ---
Fixed SGA Size                  1336260    No
Redo Buffers                    12582912   No
Buffer Cache Size               318767104  Yes
Shared Pool Size                385875968  Yes
Large Pool Size                 16777216   Yes
Java Pool Size                  16777216   Yes
Streams Pool Size               33554432   Yes
Shared IO Pool Size             0          Yes
Granule Size                    16777216   No
Maximum SGA Size                1272213504 No
Startup overhead in Shared Pool 67108864   No
Free SGA Memory Available       486539264

12 rows selected.

3. Automatic SGA and Manual PGA tuning
  • Set parameters for SGA:
    • SGA_TARGET and
    • SGA_MAX_TARGET (optionally)
  • Set parameters for PGA:

4. Manual SGA and Automatic PGA tuning
  • Set parameters for SGA:
  • Set parameters for PGA:

5. Fully manual (SGA and PGA) tuning
  • Set parameters for SGA:
  • Set parameters for PGA:

Memory Management on Oracle 11g

About the Shared Global Area (SGA)

  • SGA_MAX_SIZE : specifies the maximum size of the SGA for the lifetime of the instance.
  • If not specified, defaults to the sum of all components specified or defaulted at initialization time.
SQL> show parameter sga_max_size

NAME           TYPE        VALUE
-------------- ----------- ------
sga_max_size   big integer 1216M

  • You enable automatic shared memory management by setting SGA_TARGET to a nonzero value.
  • STATISTICS_LEVEL must be set to TYPICAL or ALL

  • The entire SGA should fit in real memory. If virtual memory needs to be used performance decreases dramatically.
  • Use V$SGA_TARGET_ADVICE to help decide on a value for SGA_TARGET

To set the value for SGA_TARGET:
(1) find out how much space the SGA is using by:
SQL> select (
  2    ( select sum(value) from v$sga) -
  3    ( select current_size from v$sga_dynamic_free_memory)
  4    ) "sga_target"
  5  from dual;


(2) Edit the text initialization parameter file and restart the database, or issue the statements below:
(b) SQL> ALTER SYSTEM SET <sga_component_parameter> = 0;  -- for every component.

Database Buffer Cache
  • Optimizes physical I/O:
    • After a COMMIT: writes redo to disk
    • DBWn performs lazy writes in the background (writes cold, dirty blocks to disk)
  • Buffer states can be:
    • Unused - buffer is available for use
    • Clean - Used earlier and now contains a read-consistent version of a block as of a point in time. Does not need to be checkpointed. Can be pinned to the cache so it does not age out of memory.
    • Dirty - Contains modified data not yet written to disk. Must be checkpointed befor reusing.
  • Buffer Modes
    • Current mode get - same as db block get: read block as is in buffer, including when contains uncommitted changes
    • Consistent mode - provides a read-consistent version of a block (read undo data if needed)
  • Buffer reads
    • Cache hit - block is on buffer cache. Logical read occurs. If flash cache list is used, an optimized physical read takes place.
    • Cache miss - block is not in memory. A physical read is required.
    • Buffer caceh hit ratio - The relative frequency of logical versus physical reads
    • .
  • Buffer pools
    • Default pool - where blocks are normally cached. (only pool by default)
    • Keep pool - To keep frequently accessed blocks that would have to be aged out for lack of space. Pinned blocks stay here.
    • Recycle pool - for infrequently used blocks. Full table scan blocks go here.
    • non-default block size pools - tablespaces with different block sizes have their blocks stored on size-specific pools.

Log Buffer
  • LWGR writes redo sequentially to disk.
  • DBWn performs scattered writes of data blocks to disk.
  • LOG_BUFFER parameter specifies the amount of memory used for the Redo log Buffer.
  • This value is taken out of the the area specified in SGA_TARGET

Shared Pool
  • Stores parsed SQL, PL/SQL code, system parameters and data dictionary information
  • Library Cache - Stores executable SQL, parsed PL/SQL code, locks and library cache handles and private sql areas (in shared server environment).
    • Library cache hit - also known as soft parse: Occurs when a parsed representation of a SQL statements exists in the library cache and is reused.
    • Libray cache miss (hard parse): Parsed representation is not found in the library cache and has to be recreated.
  • Use ALTER SYSTEM FLUSH SHARED_POOL to manually remove all information in the shared pool
  • Use RESULT_CACHE hint to indicate that the results of a SQL query should be stored in the result cache.
  • RESULT_CACHE_MODE parameter specify whether the SQL query result cache is used for all queries or only for annontated queries.

Large Pool
  • Optional area intended for large memory allocations (i.e. buffers for RMAN I/O slaves)

Java Pool
  • Stores all session-sepcific Java code and data within the Java Virtual Machine (JVM).
  • It includes java objects that are migrated to the Java session space at end-of-call.
  • Includes the share part of each java class (methods an dread-only memory such as code vectors). (dedicated server connections only).

Streams Pool
  • Stores buffered queue messages and provides memory for Oracle Streams capture and apply processes

(3) Language, Identifiers, Declarations

Character Sets
Lexical Units
Data Types
References to identifiers
Scope and visibility of identifiers
Assigning Values to variables
Error-reporting functions
SQL Functions in PL/SQL Expressions
Conditional Compilation

Character Sets
Single-byte vs Multi-byte representation

(a) Database CS
  • Stored source text of PL/SQL units
  • Character values of data types CHAR, VARCHAR2, CLOB, and LONG
(a) National CS
  • Character values of data types NCHAR, NVARCHAR2 and NCLOB

Lexical Units

Identifiers name PL/SQL Elements. Reserved words and Keywords should not be used as identifiers.
  • Predefined identifiers are declared in the predefined package STANDARD.
  • User-defined (ordinary or Quoted)
To list all predefined identifiers:
select type_name from all_types where predefined='YES';

Data Types

(a) Scalar data types
Can have subtypes. A data type and its subtypes comprise a data type family.

PL/SQL scalar data types are:
  • The SQL data types
  • User-defined subtypes

(b) Composite data types
  • These are structured data types. They have internal components (scalar or composite) that can be individually accessed. Composite variables can be passed as parameter.
  • Two types: Collections and Records.

  • Internal components (elements) are always of the same data type.
  • To create a collection variable: either (a) define a collection type and then create a variable of that type OR use %TYPE attribute.
  • Lists and arrays are classic examples of collections.
  • Here the internal components (fields) can be of different data types.
  • You access each field by its name.
  • A record variable can hold a table row, or some columns from a table row.
  • To create a record variable: either (a) define a RECORD type and then create a variable of that type, OR use %ROWTYPE or %TYPE.
  • Struct (in C); RECORD (in Pascal)

Click here for more information on Oracle and SQL Data types...

Initial Values
NOT NULL Constraint
%TYPE attribute

//declaring scalar Variable and Constant
  part_number NUMBER(6); -- SQL data type
  part_name VARCHAR2(20); -- SQL data type
  in_stock BOOLEAN := FALSE; -- PL/SQL-only data type

  max_days CONSTANT INTEGER := 366; -- SQL data type
  ispaid CONSTANT BOOLEAN := FALSE; -- PL/SQL-only

If the declaration is in a block or subprogram, the initial value is assigned to the variable or constant every time control passes to the block or subprogram.
If the declaration is in a package specification, the initial value is assigned to the variable or constant for each session (whether the variable or constant is public or private).