Transcript Lesson 8

Monitoring and Managing Memory
Copyright © 2006, 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
• Configure automatic PGA memory management
8-2
Copyright © 2006, Oracle. All rights reserved.
Memory Management: Overview
DBAs must consider memory management to be a
crucial part of their job because:
• There is a finite amount of memory available
• Allocating more memory to serve certain types of
functions can improve overall performance
• Automatically tuned memory allocation is often
the appropriate configuration, but specific
environments or even short-term conditions may
require further attention
8-3
Copyright © 2006, Oracle. All rights reserved.
Oracle Memory Structures
Server
process
1
PGA
Server
process
2
PGA
Background
process
PGA
SGA
Shared pool
Streams pool
Large pool
Keep buffer
cache
Recycle
buffer cache
Java pool
8-4
Database
buffer cache
Redo log
buffer
Copyright © 2006, Oracle. All rights reserved.
nK block size
buffer caches
Buffer Cache
SGA
Server
LRU Checkpoint
queue
lists
.
.
.
.
DB buffer cache
.
.
.
.
DBWn
DB_BLOCK_SIZE
DB_CACHE_SIZE
DB_RECYCLE_CACHE_SIZE
DB_KEEP_CACHE_SIZE
Data files
8-6
Copyright © 2006, Oracle. All rights reserved.
Using Multiple Buffer Pools
SGA
DB buffer caches
Recycle pool
Keep pool
Default pool
8-8
Copyright © 2006, Oracle. All rights reserved.
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);
8-9
Copyright © 2006, Oracle. All rights reserved.
Shared Pool
•
•
•
•
Size is specified by using 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.
The User Global Area (UGA) contains session
information if using Oracle shared server.
Shared pool
Shared pool
Library
cache
Shared
Datapool
dictionary
cache
UGA
8-10
Copyright © 2006, Oracle. All rights reserved.
Large Pool
•
•
•
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:
–
–
–
–
UGA
Backup and restore operations
Session data for the shared servers
Parallel query messaging
Redo log Database
buffer buffer cache
Shared pool
Library cache
Large pool
UGA Backup
Dictionary cache
Session
Parallel
8-11
Copyright © 2006, Oracle. All rights reserved.
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 sessionspecific Java code and data within the JVM
Redo log Database
buffer buffer cache
Shared pool
Library cache
Dictionary cache
8-12
Copyright © 2006, Oracle. All rights reserved.
Large pool
Java pool
Redo Log Buffer
Redo log Database
buffer buffer cache
Shared pool
Library cache
Dictionary cache
Server
process
LGWR
ARCn
Control files
SQL> UPDATE employees
2
SET salary=salary*1.1
3
WHERE employee_id=736;
Data files
Redo log files
8-13
Copyright © 2006, Oracle. All rights reserved.
Archived
log files
Automatic Shared Memory Management:
Overview
•
•
•
Automatically adapts to workload changes
Maximizes memory utilization
Helps eliminate out-of-memory errors
Buffer cache
Example:
Buffer cache
Large pool
Large pool
8-14
Shared pool
Shared pool
Java pool
Streams pool
Java pool
Streams pool
Online users
Batch jobs
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
8-15
Copyright © 2006, Oracle. All rights reserved.
How ASMM Works
•
•
ASSM is based on workload information that MMAN
captures in the background.
MMAN uses memory advisors.
•
•
Memory is moved to where it is needed the most.
If an SPFILE is used (which is recommended):
– Component sizes are saved across shutdowns.
– Saved values are used to bootstrap component
sizes.
– There is no need to relearn optimal values.
8-16
Copyright © 2006, Oracle. All rights reserved.
Configuring ASMM by Using
Database Control
8-17
Copyright © 2006, Oracle. All rights reserved.
Manually Configuring ASMM
To have minimum sizes for some of the memory
components, you should manually configure those
component sizes:
• If the automatically computed sizes do not serve
your purpose for some reason
• If there are short peaks or valleys in required
memory that are not detected by ASMM
• If you simply want a guaranteed minimum amount
of memory for a specific component
8-18
Copyright © 2006, Oracle. All rights reserved.
Manually Configuring ASMM
Autotuned
Autotuned
Autotuned
Shared pool
Database
buffer cache
Redo log
buffer
Streams
pool
Fixed SGA
Large pool
Keep
buffer pool
Java pool
Autotuned
Autotuned
Total SGA size = 8 GB
SGA_TARGET = 8G
STATISTICS_LEVEL = TYPICAL
8-19
Copyright © 2006, Oracle. All rights reserved.
Behavior of Autotuned
SGA Parameters
•
When SGA_TARGET is not set or is set to zero:
– Autotuned parameters behave as normal
– SHARED_POOL_SIZE may need to be increased from
settings used in earlier database versions
SELECT SUM(bytes)/1024/1024 size_mb
FROM
v$sgastat WHERE pool = 'shared pool';
•
When SGA_TARGET is set to a nonzero value:
– Default value of autotuned parameters is zero
– The specified value is used as a minimum size
SELECT component, current_size/1024/1024 size_mb
FROM
v$sga_dynamic_components;
8-21
Copyright © 2006, Oracle. All rights reserved.
Behavior of Manually Tuned
SGA Parameters
•
Some components are not autotuned.
– KEEP and RECYCLE buffer caches
– Multiple block size caches
– Log buffer
•
•
8-22
These components must be manually configured
using database parameters.
The memory used by these components reduces
the amount of memory available for autotuning the
SGA.
Copyright © 2006, Oracle. All rights reserved.
Using the V$PARAMETER View
SGA_TARGET = 8G
DB_CACHE_SIZE = 0
JAVA_POOL_SIZE = 0
LARGE_POOL_SIZE = 0
SHARED_POOL_SIZE = 0
STREAMS_POOL_SIZE = 0
SELECT name, value, isdefault
FROM
v$parameter
WHERE name LIKE '%size';
8-23
Copyright © 2006, Oracle. All rights reserved.
Modifying the SGA_TARGET Parameter
•
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
•
8-24
A change in the value of SGA_TARGET affects only
automatically sized components.
Copyright © 2006, Oracle. All rights reserved.
Disabling ASMM
•
Setting SGA_TARGET to zero disables autotuning.
•
Autotuned parameters are set to their current
sizes.
The SGA size as a whole is unaffected.
•
SGA size = 8 GB
Parameters:
sga_target = 8G
shared_pool_size = 1G
Original values
8-25
SGA size = 8 GB
Parameters:
sga_target = 0
db_cache_size = 5G
shared_pool_size = 2G
large_pool_size = 512M
java_pool_size = 256M
streams_pool_size = 256M
Copyright © 2006, Oracle. All rights reserved.
Manually Resizing
Dynamic SGA Parameters
•
For autotuned 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
•
8-26
Resizing manually tuned parameters affects only
the tunable portion of the SGA.
Copyright © 2006, Oracle. All rights reserved.
Program Global Area (PGA)
PGA
Server
process
PGA
Private Cursor
Session
SQL and SQL
memory
areas
area
Dedicated
connections
Shared
server
PGA
Shared server
connections
8-27
Shared pool
or
large pool
Copyright © 2006, Oracle. All rights reserved.
PGA
Work
area
Automatic PGA Memory Management
•
Dynamically adjusts the amount of PGA memory
dedicated to work areas, on the basis of the
PGA_AGGREGATE_TARGET parameter
•
Helps maximize the performance of all the
memory-intensive SQL operations
Is enabled by default
•
8-29
Copyright © 2006, Oracle. All rights reserved.
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
8-30
Copyright © 2006, Oracle. All rights reserved.
Using the Memory Advisor to
Size the SGA
8-31
Copyright © 2006, Oracle. All rights reserved.
Using the Memory Advisor
to Size the PGA
8-32
Copyright © 2006, Oracle. All rights reserved.
Efficient Memory Usage: Guidelines
•
•
Try to fit the SGA into physical memory.
Tune for a high buffer cache hit ratio, with the
following caveats:
– Even valid and necessary full table scans lower it.
– It is possible that unnecessary repeated reads of
the same blocks are artificially raising it.
•
8-33
Use the Memory Advisor.
Copyright © 2006, Oracle. All rights reserved.
Memory Tuning Guidelines
for the Library Cache
•
8-35
•
•
•
Establish formatting conventions for developers
so that SQL statements match in the cache.
Use bind variables.
Eliminate unnecessary duplicate SQL.
Consider using CURSOR_SHARING.
•
•
•
Use PL/SQL when possible.
Cache sequence numbers.
Pin objects in the library cache.
Copyright © 2006, Oracle. All rights reserved.
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
8-37
Copyright © 2006, Oracle. All rights reserved.
Practice Overview: Using ASMM to Correct
a Memory Allocation Problem
This practice covers the following topics:
• Diagnosing a memory allocation problem
• Enabling Automatic Shared Memory Management
8-38
Copyright © 2006, Oracle. All rights reserved.