|
|
Oracle Automatic Shared Memory Management
by Donald Burleson |
Notes:
-
Oracle ASMM and AMM are not the same and are associated
with different releases of Oracle. See my notes on
the
difference between ASMM and AMM.
-
See these important notes scripts for
detecting AMM resize
operations.
-
There is an advisory on using bitmap
free lists (ASMM). ASMM (AMM) and dynamic memory management
has measurable overhead for resize operations, and some shop may
want to disable AMM. See my important notes on
Oracle dynamic memory management
and how
Oracle AMM resize operations can hurt performance.
-
Automatic
Memory Management has issues since its inception and by 11g
release 2 it remains problematic, and in some cases ASMM should be disabled in 11g
release 2. See MOSC note 793845.1 titled: "High
direct path read waits in 11g".
Oracle has always tried to provide advisory tools
for the Oracle DBA who wants to monitor and re-size their data
buffers (db_block_buffers, db_cache_size):
-
Oracle7 - x$kcbcbh
-
Oracle9i - v$db_cache_advice
-
Oracle 10g - Oracle Automatic Shared Memory
Management (ASMM)
-
Oracle 11g - Oracle Automatic Memory
Management (AMM)
Enabling Automatic Shared
Memory Management (in 10g ONLY)
The Oracle Automatic Shared Memory Management
is a feature that automatically readjusts the sizes of the main
pools (db_cache_size, shared_pool_size, large_pool_size,
java_pool_size) based on existing workloads. Oracle Automatic
Shared Memory Management is enabled by setting:
-
You must use an spfile for the
init.ora values
-
sga_target parameter is set to a
non-zero value
-
statistics_level parameter set to to
TYPICAL (the default) or ALL
-
shared_pool_size must be set to a
non-zero value
Oracle10g has introduced special double
underscore hidden parameter to control ASMM. Once you see
these, AMM is being used.:
-
__db_cache_size
-
__shared_pool_size
-
__large_pool_size
Once enabled, Oracle Automatic Shared Memory
Management will morph the pool areas within the confines of
sga_max_size.
When using AMM you have to consider the
interaction of these parameters:
-
sga_target: (pre 11g):
If the sga_target is set to some value then the automatic shared
memory management (ASMM) is enabled, the sga_target value can be
adjusted up to the sga_max_size parameter, not more than
sga_max_size parameter value.
-
memory_target (starting in
11g): If memory_target is set, then AMM is enabled:
If memory_target is set to non zero value and :
-
sga_target, sga_max_size and
pga_aggregate_target are set to 0, then 60% of memory
mentioned in memory_target is allocated to SGA and rest 40% is
kept for PGA.
-
sga_target and pga_aggregate_target are
set to non-zero values, then these values will be considered
minimum values.
-
sga_target is set to non zero value and
pga_aggregate_target is not set. still these values will be
auto-tuned and pga_aggregate_target will be initialized with
value of (memory_target-sga_target).
-
pga_aggregate_target is set and
sga_target is not set. still both parameters will be
auto-tuned. The sga_target will be initialized to a value of
(memory_target-pga_aggregate_target).
memory_target |
sga_target |
sga_max_size |
pga_aggregate_target |
Behavior |
non-zero |
0 |
0 |
0 |
60% of memory_target to SGA, 40% to PGA |
non-zero |
non-zero |
|
non-zero |
Minimum values |
non-zero |
non-zero |
|
un-set |
pga_aggregate_target = memory_target - sga_target |
non-zero |
un-set |
|
un-set |
sga_target is set to memory_target - pga_aggregate_target |
AMM and the memory_target
parameter
In Oracle 11g and beyond, automatic memory
management is enabled by using the memory_target and
memory_max_target initialization parameters. The
memory_target parameter specifies the amount of shared memory
available for Oracle to use when dynamically controlling the SGA and
PGA. The memory_max_target AMM parameter specifies the max
size that memory_target may take. The Oracle
documentation
notes that the memory_target parameter specifies a target
value for system-wide RAM memory, both PGA and SGA RAM:
"MEMORY_TARGET specifies the Oracle
system-wide usable memory.
The database tunes memory to the MEMORY_TARGET
value, reducing or enlarging the SGA and PGA as needed."
Disabling Automatic Shared
Memory Management
Simply re-set sga_target
to zero. Be aware that the data buffer cache (db_cache_size)
will take-on the current value from the spfile.
Using Automatic Shared Memory
Management
You need to note that
Automatic Shared Memory Management does not change the multiple
block sizes and the KEEP pool. In an article titled
Automatic Shared Memory Management we see:
Some pools in SGA are
not subject to dynamic resizing, and must be specified explicitly.
Notable among them are the buffer pools for nonstandard block sizes
and the non-default ones for KEEP or RECYCLE. If
your database has a block size of 8K, and you want to configure 2K,
4K, 16K, and 32K block-size pools, you must set them manually.
Their
sizes will remain constant; they will not shrink or expand based on
load. You should consider this factor when using multiple-size
buffer, KEEP, and RECYCLE pools.
In addition, log
buffer is not subject to the memory adjustment—the value set in the
parameter log_buffer is constant, regardless of the
workload. ( In 10g, a new type of pool can also be defined
in the SGA: Streams pool, set with parameter streams_pool_size.
This pool is also not subject to automatic memory tuning.)
From the bestselling book "Oracle
10g New Features", we see how Oracle monitors the pool
workloads::
A new background process named Memory
Manager (MMAN) manages the automatic shared memory. MMAN serves
as the SGA Memory Broker and coordinates the sizing of the
memory components. The SGA Memory Broker keeps track of the
sizes of the components and pending resize operations.
Monitoring Automatic Shared
Memory Management
MOSC Note:295626.1, How To Use Automatic
Shared Memory Management (ASMM) In Oracle10g, has some script for
monitoring Oracle Automatic Shared Memory Management:
select
component,
oper_type,
oper_mode,
initial_size/1024/1024 "Initial",
TARGET_SIZE/1024/1024 "Target",
FINAL_SIZE/1024/1024 "Final",
status
from
v$sga_resize_ops;
COMPONENT
OPER_TYPE OPER_MODE INITIAL
TARGET FINAL
STATUS
------------------------------ ------------- --------- ----------
---------- ---------- ---------
DEFAULT buffer cache
SHRINK MANUAL
160 148
148 COMPLETE
streams pool
GROW MANUAL
0 12
12 COMPLETE
select
component,
current_size/1024/1024 "CURRENT_SIZE",
min_size/1024/1024 "MIN_SIZE",
user_specified_size/1024/1024 "USER_SPECIFIED_SIZE",
last_oper_type "TYPE"
from
v$sga_dynamic_components;
COMPONENT
CURRENT_SIZE MIN_SIZE USER_SPECIFIED_SIZE TYPE
------------------------------ ------------ ----------
------------------- -------------
shared pool
80 80
80
STATIC
large pool
8
8 8
STATIC
java pool 4
8 48
48
STATIC
streams pool
12
0 12
GROW
DEFAULT buffer cache
48 24
24
SHRINK
KEEP buffer cache
0
0 0
STATIC
RECYCLE buffer cache
0
0 0
STATIC
DEFAULT 2K buffer cache
0
0 0
STATIC
DEFAULT 4K buffer cache
0
0 0
STATIC
DEFAULT 8K buffer cache
0
0 0
STATIC
DEFAULT 16K buffer cache 0
0 0
STATIC
DEFAULT 32K buffer cache 0
0 0
STATIC
OSM Buffer Cache
0
0 24
STATIC
Click here
to see scripts for monitoring the shared pool for free RAM chunks.
You can also issue these queries to see free RAM in Oracle10g:
-
select
sum(value) from v$sga;
-
select
sum(bytes) from v$sgastat;
-
select
sum(current_size) from v$sga_dynamic_components;
-
select *
from v$sga_dynamic_free_memory;
Automatic Shared Memory
Management Internals
With the advent of the advisory utilities in
Oracle9i (v$db_cache_advice, v$shared_pool_advice, and
v$pga_target_advice, we see how
Oracle plots marginal benefits from different pool sizes:
Robert Freeman notes the default behavior with
a blank init.ora file:
I did shutdown my local 10g database and
brought it up with a blank init.ora (only had the db_name
parameter in it). The actual default in XP 10g is
db_cache_size=48m, shared_pool_size=32m.
MOSC Note:257643.1, Oracle Database 10g
Automated SGA Memory Tuning gives hints about how MMAN
determines current workloads. The documentation shows that
Oracle uses the memory advisories from Oracle9i and applies
heuristics (rules of thumb) to determine the best shift in RAM pool
sizes. These heuristics consist of hypothesis testing with
"what if" scenarios, computing the ratio of the marginal
reduction in physical disk reads, and choosing the size with the greatest overall
marginal benefit:
The SGA Memory Broker observes the system
and workload in order to determine the ideal distribution of memory.
It is never complacent and performs this check every few minutes so
that memory can always be present where needed.
Based on workload information, automatic
shared memory tuning:
-
Captures statistics periodically in the
background
-
Uses the different memory advisories
-
Performs ?what-if? analyses to determine
best distribution of memory
-
Moves memory to where it is most needed
-
Has no need to configure parameters for
the worst-case scenario
-
Resurrects component sizes from last
shutdown if SPFILE is used
Automatic Shared Memory
Management Errors
ASMM can precipitate these errors:
ORA-00093: _shared_pool_reserved_min_alloc
must be between 4000 and 0
alter system set sga_target = 330M
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value
is invalid
ORA-00827: could not shrink sga_target to specified value
alter system set sga_target=160M;
alter system set sga_target=160M
* ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value
is invalid
ORA-00823: Specified value of sga_target greater than
sga_max_size
SQL> startup
ORA-00824: cannot set sga_target due to existing internal
settings
Cause: If you enable automatic SGA Management by setting
SGA_Target >0 and
if you are using db_block_buffers(Obsolete parameter) in your
init.ora.
Startup of Database fails with ORA-00824 Error
Potential issues with ASMM
The Oracle 10g Automatic Storage Memory
Management (ASMM) feature (the default on Oracle10g) should
anticipate high updates and allocate additional data buffers during
high update periods.
For example, here is an actual output from
an Oracle10g database where it appears that ASMM is not
allocating enough free blocks to accommodate concurrent updates:
STATUS NUMBER_BUFFERS
------- --------------
cr 616
free 1
xcur 14790
Here we see the
double-underscore ASMM hidden parms:
316 6 __db_cache_size
130,023,424
96 6 __java_pool_size
12,582,912
94 6 __large_pool_size
4,194,304
92 6 __shared_pool_size
142,606,336
How to disable AMM: See these
important notes on disabling AMM (Automatic Space Memory
Management)
|