Oracle Automatic Shared Memory Management

最新推荐文章于 2019-05-08 10:02:43 发布

cuipeng3714

最新推荐文章于 2019-05-08 10:02:43 发布

阅读量131

点赞数

文章标签：数据库 java

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)

[@more@]

Oracle Automatic Shared Memory Management

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)

Enabling Automatic Shared Memory Management

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:

__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.

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

MetaLink 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.

MetaLink 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