Oracle Database 10G - Automatic Sga Memory Management.pdf

(344 KB) Pobierz
Oracle7 Server
The Self-Managing Database:
Automatic SGA Memory
Management
An Oracle White Paper
Nov. 2003
22902471.001.png
The Self-Managing Database: Automatic SGA
Memory Management
The Self-Managing Database: Automatic SGA Memory Management Page 2
The Self-Managing Database: Automatic SGA
Memory Management
INTRODUCTION
One of the key self-management enhancements in the Oracle Database 10g is
Automatic Shared (SGA) Memory Management. This functionality automates
the management of shared memory used by an Oracle Database 10g instance
and liberates administrators from having to manually configure the sizes of
shared memory components. Besides making more effective use of available
memory and thereby reducing the cost incurred of acquiring additional hardware
memory resources, the Automatic Shared Memory Management feature will
significantly simply Oracle database administration by introducing a more
dynamic, flexible and adaptive memory management scheme.
This paper introduces this functionality and illustrates its advantages.
CURRENT CHALLENGES
The Shared Global Area (SGA) in Oracle comprises multiple memory
components -- a component being a pool of memory used to satisfy a particular
class of memory allocation requests. Examples of memory components include
the shared pool (used for allocating memory for SQL and PL/SQL execution),
java pool (used for java objects and other java execution memory), buffer cache
(used for caching disk blocks), etc.
In past releases, the Oracle administrator was required to manually set a number
of parameters for specifying different SGA component sizes, such as
SHARED_POOL_SIZE, DB_CACHE_SIZE, LARGE_POOL_SIZE, and
JAVA_POOL_SIZE.
The task of manually adjusting the sizes of individual SGA components could
pose a few challenges. It may not be easy to determine the optimal sizes of these
components suitable for a given workload. Oracle9i alleviated this problem to a
great extent by introducing advisory mechanisms that allow DBAs to determine
the optimal sizes of the buffer cache and shared pool. However, these
recommendations still had to be implemented by the administrator. This
challenge is further compounded in situations in which the workload tends to
vary with the time of the day e.g online users during the day and batch jobs at
night. Sizing for peak load could mean memory wastage while under-sizing may
The Self-Managing Database: Automatic SGA Memory Management Page 3
cause out-of-memory errors (ORA-4031). For example if a system is configured
with a big large pool to accommodate a nightly RMAN backup job, most of this
memory – which could have been better utilized in the buffer cache or shared
pool for OLTP activity – remains unused for the most part of the day. At the
same time, the cost of failures could be prohibitive from a business point of
view leaving administrators with few other options.
INTRODUCING AUTOMATIC SHARED MEMORY MANAGEMENT
To resolve these challenges, Oracle Database 10g introduces Automatic Shared
Memory Management. In Oracle Database 10g, DBAs can just specify the total
amount of SGA memory available to an instance using the new parameter
SGA_TARGET. The database server then automatically distributes the available
memory among various components as required. The Automatic Shared
Memory Management feature is based on a sophisticated algorithm internal to
the database that continuously monitors the distribution of memory and changes
it periodically as needed, according to the demands of the workload.
The SGA_TARGET Parameter
The SGA_TARGET parameter reflects the total size of the SGA and includes
memory for:
• Fixed SGA and other internal allocations needed by the Oracle instance
• Log buffer
• Shared Pool
• Java Pool
• Buffer Cache
• Keep/Recycle buffer caches (if specified)
• Non standard block size buffer caches (if specified)
• Streams Pool (New in Oracle Database 10g)
An important point to note is that SGA_TARGET now includes the entire
memory for the SGA. This is a change from past releases in which memory for
the internal allocations and fixed SGA was added to the sum of the configured
SGA memory parameters. Thus, SGA_TARGET allows the user to precisely
control the size of the shared memory area allocated by Oracle.
Automatically Managed SGA Components
When SGA_TARGET is set, the most commonly configured components are
sized automatically. These include:
1. Shared pool (for SQL and PL/SQL execution)
2. Java pool for (java execution state)
The Self-Managing Database: Automatic SGA Memory Management Page 4
3. Large pool (for large allocations such as RMAN backup buffers)
4. Buffer cache
There is no need to set the of size any of the above components explicitly and by
default the parameters for these components will appear to have values of zero.
Whenever a component needs memory, it can request that it be transferred from
another component via the internal auto-tuning mechanism. This will happen
transparently without user-intervention.
The performance of each of these components is also monitored by the Oracle
instance. Now the instance uses internal views and statistics to determine how to
optimally distribute memory among the automatically sized components. Thus,
as the workload changes, memory would be redistributed to ensure optimal
performance with the new workload. This algorithm is never complacent and
always tries to find the optimal distribution by taking into consideration long
term as well as short terms trends.
In the following example, the shared pool advisory shows that the shared pool is
sized to a value below the knee of the curve and hence, growing the shared pool
will considerably improve parse times. In this scenario, memory may be
transferred from the buffer cache to the shared pool by the auto-tuning
algorithm, in order to ensure a more optimal distribution of the memory.
The Self-Managing Database: Automatic SGA Memory Management Page 5

Plik z chomika:

Inne pliki z tego folderu:

Inne foldery tego chomika:

Zgłoś jeśli naruszono regulamin