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

No comments:

Post a Comment