Transcript SGA Sizing Parameters

Automatic Shared Memory Management
Copyright © 2006, Oracle. All rights reserved.
Objectives
After completing this lesson, you should be able to do
the following:
• Enable Enterprise Manager (EM) memory
parameters
• Set auto-tuned memory parameters
• Override minimum size with the manually tuned
SGA parameters
• Use the SGA advisor to set SGA_TARGET
10-2
Copyright © 2006, Oracle. All rights reserved.
Oracle Database Architecture
Instance
SGA
Fixed
size
nK
Default Keep Recycle
Redo
Sort Global Flash
Large Java Streams buffer buffer buffer buffer Shared ASH
log
pool buffer extent context back
pool
pool pool
cache
caches
cache
cache
buffer
pool
pool
buffer
PMON
SMON
RECO
MMON
MMAN
MMNL
QMNC
LGWR
CTWR
ARCn
S000
RVWR
D000
FMON
Qnnn
Password
file
Control
files
Redo log
files
Temp
SYSTEM
SYSAUX
Data file
Change
tracking
file
Undo
10-3
CKPT
CJQ0
PSP0
Spfile
DBWn
Copyright © 2006, Oracle. All rights reserved.
Flashback
logs
Archive
log files
Dynamic SGA Feature
•
•
Implements an infrastructure to allow the server to
change its SGA configuration without shutting
down the instance
SGA size is limited by SGA_MAX_SIZE:
– Used for reserving virtual memory address space at
instance startup
– Cannot be changed dynamically
•
Allows for certain SGA components to be
dynamically resized
SELECT bytes
FROM
V$SGAINFO
WHERE name = 'Free SGA Memory Available';
10-4
Copyright © 2006, Oracle. All rights reserved.
Granule
•
•
SGA memory is allocated in units of contiguous
memory chunks called granules.
The size of a granule depends on the estimated
total SGA. If the estimated SGA size is:
– Less than or equal to 1 GB, the granule size is 4 MB
– Greater than 1 GB, the granule size is 16 MB
SELECT bytes
FROM
V$SGAINFO
WHERE name = 'Granule Size';
10-5
Copyright © 2006, Oracle. All rights reserved.
Memory Advisories
•
Buffer Cache Advice (introduced in 9i R1):
– V$DB_CACHE_ADVICE
– Predicts physical reads for different cache sizes
•
Shared Pool Advice (in 9i R2):
– V$SHARED_POOL_ADVICE
– Predicts parse time savings from having different
sizes of the shared pool
•
Java Pool Advice (in 9i R2):
– V$JAVA_POOL_ADVICE
– Predicts Java class load time savings from having
different sizes of Java pool
•
Streams Pool Advice (10g R2)
– V$STREAMS_POOL_ADVICE
– Predicts spill and unspill activity for various sizes
10-6
Copyright © 2006, Oracle. All rights reserved.
Manually Adding Granules to Components
•
•
•
•
10-7
Use the ALTER SYSTEM command to dynamically
increase memory allocation to a component.
Increasing the memory use of a component
succeeds only if there are enough free granules to
satisfy the request.
Memory granules are not freed automatically from
another component to satisfy the increase.
Decreasing the size of a component is possible,
but only if the granules being released are unused
by the component.
Copyright © 2006, Oracle. All rights reserved.
Increasing the Size of an SGA Component
SQL> show parameter
NAME
------------sga_max_size
shared_pool_size
db_cache_size
TYPE
----------big integer
big integer
big integer
VALUE
----------------------200M
84M
92M
SQL> alter system set shared_pool_size=120M;
alter system set shared_pool_size=120M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified …
ORA-04033: Insufficient memory to grow pool
SQL> alter system set db_cache_size=50M;
System altered.
SQL> alter system set shared_pool_size=120M;
System altered.
10-8
Copyright © 2006, Oracle. All rights reserved.
Automatic Shared Memory Management:
Overview
•
•
•
•
10-9
Uses dynamic SGA and memory advisors to
automatically adapt to workload changes
Maximizes memory utilization
Helps eliminate
Buffer cache
out-of-memory
Buffer cache
errors
Large pool
Avoids relearning
Large pool
when using SPFILE
Shared pool
Shared pool
Java pool
Streams pool
Java pool
Streams pool
Online users
Batch jobs
Copyright © 2006, Oracle. All rights reserved.
SGA Sizing Parameters: Overview
•
•
•
With ASMM, five important SGA components can
be automatically sized.
Nondefault buffer pools are not auto-tuned.
Log buffer is not a dynamic component but has a
good default.
Auto-tuned
parameters
Manual
dynamic parameters
Manual
static parameters
SHARED_POOL_SIZE
DB_CACHE_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
STREAMS_POOL_SIZE
DB_KEEP_CACHE_SIZE
LOG_BUFFER_SIZE
DB_RECYCLE_CACHE_SIZE
SGA_MAX_SIZE
DB_nK_CACHE_SIZE
SGA_TARGET
10-10
Copyright © 2006, Oracle. All rights reserved.
Benefits of Automatic Shared
Memory Management
DB_CACHE_SIZE
SHARED_POOL_SIZE
LARGE_POOL_SIZE
JAVA_POOL_SIZE
STREAMS_POOL_SIZE
Total SGA size
SGA_TARGET
10-11
Copyright © 2006, Oracle. All rights reserved.
Dynamic SGA Transfer Modes
•
ASMM IMMEDIATE transfer mode:
– Out-of-memory (ORA-04031) errors
– Partial granules can be used.
•
ASMM DEFERRED transfer mode:
– Transparently executed in the background
– Partial granules can be used.
•
MANUAL transfer mode:
– Used with ALTER SYSTEM commands
– Resize must use full granules.
10-12
Copyright © 2006, Oracle. All rights reserved.
Memory Broker Architecture
MMON
Circular SGA
buffer of stats
captured by
MMON
Statistic
deltas across
different time
periods
Trade-off
different
components
benefit/lost
Output: resize
requests
Add
two granules
to shared
pool.
Memory
Broker
Policy
Module
resize
queue
MMAN
MMAN transfers
the memory.
10-13
Copyright © 2006, Oracle. All rights reserved.
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
•
10-14
Manually tuned parameter resizing affects the
tunable portion of the SGA.
Copyright © 2006, Oracle. All rights reserved.
Behavior of Auto-Tuned SGA Parameters
•
When SGA_TARGET is not set or is set to zero:
– Auto-tuned parameters are explicitly set
– Note: SHARED_POOL_SIZE
Internal startup overhead is included
Value may need to be increased from previous
releases
•
When SGA_TARGET is set:
– Default value of auto-tuned parameters is zero
– A nonzero value is a lower bound
– Current values in megabytes are shown by:
SELECT component, current_size/1024/1024
FROM
V$SGA_DYNAMIC_COMPONENTS;
10-15
Copyright © 2006, Oracle. All rights reserved.
Behavior of Manually Tuned
SGA Parameters
•
Manually tuned components are:
– KEEP and RECYCLE buffer caches
– Nondefault block size caches
– LOG_BUFFER
•
•
10-16
Manually tuned components are user specified.
Manually tuned components are included in
SGA_TARGET to precisely control the SGA size.
Copyright © 2006, Oracle. All rights reserved.
Using the V$PARAMETER View
SGA_TARGET = 8G
SELECT name, value, isdefault
FROM
V$PARAMETER
WHERE name LIKE '%size';
DB_CACHE_SIZE = 0
JAVA_POOL_SIZE = 0
LARGE_POOL_SIZE = 0
SHARED_POOL_SIZE = 0
STREAMS_POOL_SIZE = 0
10-17
Copyright © 2006, Oracle. All rights reserved.
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
minimum size
– Changes affect only automatically sized
components
•
Includes everything in the SGA:
– Fixed SGA and other internal allocations
– Automatically sized SGA components
– Manual SGA components
•
10-18
Allows precise sizing of the total shared memory
allocation by the Oracle server
Copyright © 2006, Oracle. All rights reserved.
Disabling Automatic Shared
Memory Management
•
Setting SGA_TARGET to zero disables auto-tuning.
•
•
Auto-tuned parameters are set to their current sizes.
SGA size as a whole is unaffected.
SGA size = 8 GB
SGA size = 8 GB
Parameters:
SGA_TARGET = 8G
SHARED_POOL_SIZE=1G
Original values
10-19
SGA_TARGET=0
Parameters:
SGA_TARGET = 0
DB_CACHE_SIZE = 4G
SHARED_POOL_SIZE = 1.5G
LARGE_POOL_SIZE = 512M
JAVA_POOL_SIZE = 512M
STREAMS_POOL_SIZE = 512M
Copyright © 2006, Oracle. All rights reserved.
Configuring ASMM
10-20
Copyright © 2006, Oracle. All rights reserved.
SGA Advisor
10-21
Copyright © 2006, Oracle. All rights reserved.
Monitoring ASMM
Monitor Automatic Shared Memory Management and
examine the resize decisions it made with the
following views:
• V$SGA_CURRENT_RESIZE_OPS: Information about
resize SGA operation in progress
• V$SGA_RESIZE_OPS: Circular history buffer of the
last 800 SGA resize requests
• V$SGA_DYNAMIC_COMPONENTS: Current status of
all memory components
• V$SGA_DYNAMIC_FREE_MEMORY: Information about
SGA memory available for future resize operations
10-22
Copyright © 2006, Oracle. All rights reserved.
Practice: Overview
This practice covers the following topics:
• Enable Automatic Shared Memory Management
• Adjust memory as workloads change
10-23
Copyright © 2006, Oracle. All rights reserved.
Summary
In this lesson, you should have learned how to:
• Enable EM memory parameters
• Set auto-tuned memory parameters
• Set the manually tuned SGA parameters
• Use the SGA advisor to set SGA_TARGET
10-24
Copyright © 2006, Oracle. All rights reserved.