monitoring and managing memory

15Copyright © 2004, Oracle. All rights reserved.

Monitoring and Managing Memory

15-2 Copyright © 2004, Oracle. All rights reserved.

Objectives

After completing this lesson, you should be able to:

• Describe the memory components in the SGA

• Implement Automatic Shared Memory Management

• Manually configure SGA parameters

• Use automatic PGA memory management


Oracle Memory Structures

Java poolDatabase

buffer cache

Shared pool

Redo log buffer

Large pool

SGA

Streams pool

Serverprocess

1PGA

Serverprocess

2PGA

Back-groundprocess

PGA

Keep buffer cache

Recycle buffer cache

nK Block Size buffer caches


Buffer Cache

Data files

SGA

Checkpoint queue

.

.

.

.

DBWn

Server

DB_BLOCK_SIZE

DB_CACHE_SIZE

DB_RECYCLE_CACHE_SIZE

DB_KEEP_CACHE_SIZE

LRU lists

.

.

.

.

DB buffer cache


Using Multiple Buffer Pools

SGADB buffer caches

Keep pool

Recycle pool

Default pool


Using Multiple Buffer Pools

CREATE INDEX cust_idx … STORAGE (BUFFER_POOL KEEP …);

ALTER TABLE oe.customers STORAGE (BUFFER_POOL RECYCLE);

ALTER INDEX oe.cust_lname_ix STORAGE (BUFFER_POOL KEEP);


Shared pool

Librarycache

Datadictionary

cache

UGA

Shared poolShared pool

• Defined by SHARED_POOL_SIZE• Library cache contains statement text, parsed

code, and execution plan.• Data dictionary cache contains definitions for

tables, columns, and privileges from the data dictionary tables.

• UGA contains session information for Oracle Shared Server users when a large pool is not configured.

Shared Pool


Shared poolDatabase

buffer cacheRedo log

buffer Large pool

Large Pool

Library cache

Dictionary cache

User global area

• Can be configured as a separate memory area in the SGA

• Is sized by the LARGE_POOL_SIZE parameter

• Is used to store data in memory for:– Backup and restore operations– Session data for the shared servers– Parallel query messaging


UGA and Oracle Shared Server

Shared pool

PGA

Shared poolor

large pool Stack space

PGA

Stack space

Cursor state

User session

data

UGA

Dedicated server configuration

Shared server configuration

User session

data

Cursor state

UGA


Java Pool

• Can be configured as a separate memory area in the SGA

• Is sized by the JAVA_POOL_SIZE parameter

• Is used to store data in memory for all session-specific Java code and data within the JVM

Shared poolDatabase

buffer cacheRedo log

buffer Large pool Library cache

Dictionary cache

User global area


The Redo Log Buffer

SQL> UPDATE employees

2 SET salary=salary*1.1

3 WHERE employee_id=736;

Serverprocess

LGWR

Control files

ARCn

Archivedlog files

Redo log files

Data files

Shared poolRedo logbuffer

Library cache

Dictionary cache

User global area

Database buffer cache


Automatic Shared Memory Management: Overview

• Automatically adapts to workload changes

• Maximizes memory utilization

• Helps eliminate out-of-memory errors

Buffer cache

Large pool

Shared pool

Java pool

Buffer cache

Large pool

Shared pool

Java pool

Online users Batch jobs


Benefits of Automatic Shared Memory Management

DB_CACHE_SIZESHARED_POOL_SIZELARGE_POOL_SIZEJAVA_POOL_SIZE

Total SGA size

SGA_TARGET


SGA Tuning Principles

• Based on workload information, MMAN captures statistics periodically in the background.

• MMAN uses the different memory advisories.

• Memory is moved to where it is most needed.

• Using an SPFILE is recommended:– Component sizes saved across shutdowns– Saved values used to bootstrap component sizes– Avoids having to relearn optimal values


Database Control andAutomatic Shared Memory Management


Manual Configuration

SGA_TARGET = 8G

Total SGA size = 8 GB

Java pool

Databasebuffer cache

Redo log buffer

Shared pool

Large poolFixed SGA

STATISTICS_LEVEL = TYPICAL

Auto-tuned Auto-tuned

Auto-tunedAuto-tuned

Streams pool

Keepbuffer pool


Behavior of Auto-TunedSGA Parameters

• When SGA_TARGET is not set or is set to zero:– Auto-tuned parameters behave as previously.– SHARED_POOL_SIZE may need to be increased

from settings used on earlier database versions.

• When SGA_TARGET is set to a non-zero value:– Default value of auto-tuned parameters is zero.– If set to a non-zero value, the specified value is

used as a minimum size.

SELECT SUM(bytes)/1024/1024 FROM v$sgastatWHERE pool = 'shared pool';

SELECT component,current_size/1024/1024||'M'FROM v$sga_dynamic_components;


Behavior of Manually Tuned SGA Parameters

• Some components are not auto-tuned.– KEEP and RECYCLE buffer caches– Multiple block size caches– Log buffer– Streams pool

• These components must be manually configured using database parameters.

• The memory used by these components reduces the amount of memory available for auto-tuning the SGA.


Using the V$PARAMETER View

SGA_TARGET = 8G

SELECT name, value, isdefault FROM v$parameterWHERE name LIKE '%size';

DB_CACHE_SIZE = 0JAVA_POOL_SIZE = 0LARGE_POOL_SIZE = 0SHARED_POOL_SIZE = 0


Resizing SGA_TARGET

• The SGA_TARGET initialization parameter:– Is dynamic– Can be increased up to SGA_MAX_SIZE– Can be reduced until all components reach their

minimum size

• A change in the value of SGA_TARGET affects only automatically sized components.


SGA size = 8 GB

Disabling Automatic Shared Memory Management

• Setting SGA_TARGET to zero disables auto-tuning.

• Auto parameters are set to their current sizes.

• SGA size as a whole is unaffected.

Parameters:

sga_target = 8G

shared_pool_size=1G

Parameters:

sga_target = 0

db_cache_size = 4G

shared_pool_size = 2G

large_pool_size = 512M

java_pool_size = 512M

SGA size = 8 GB

Original values


Manually Resizing Dynamic SGA Parameters

• For auto-tuned parameters, manual resizing:– Results in immediate component resize if the new

value is greater than the current size– Changes the minimum size if the new value is

smaller than the current size

• Manually tuned parameter resizing affects only the tunable portion of the SGA.


Program Global Area (PGA)

PGA

Serverprocess

PGA

Shared poolor

large poolPGA

Dedicatedconnections

Sharedserver

PGA

Shared serverconnections

PrivateSQLareas

Cursor and SQL

area

Sessionmemory

Workarea


Automatic PGA Memory Management

• Dynamically adjusts the amount of PGA memory dedicated to work areas

• Memory allocated to work areas is derived from the PGA_AGGREGATE_TARGET parameter

• Helps to maximize the performance of all the memory-intensive SQL operations

• Enabled by default


PGA Management Resources

• Statistics to manage the PGA_AGGREGATE_TARGET initialization parameter, such as PGA cache hit percentage

• Views for monitoring the PGA work area include:– v$sql_workarea_histogram– v$pgastat– v$sql_workarea_active– v$sql_workarea– v$tempseg_usage

• Views to assist in sizing the PGA work area are:– v$pga_target_advice– v$pga_target_advice_histogram


Using the Memory Advisor


Summary

In this lesson, you should have learned how to:

• Describe the memory components in the SGA

• Implement Automatic Shared Memory Management

• Manually configure SGA parameters

• Use automatic PGA memory management


Practice 15 Overview:Automatic Shared Memory Management

This practice covers using Automatic Shared Memory Management to avoid long running query issues.

monitoring and managing memory

Documents