Enabling Automatic Shared Memory Management (98)

To enable automatic shared memory management:
1. If you are migrating from a manual management scheme, execute the following
query on the instance running in manual mode to get a value for SGA_TARGET:
SELECT (
(SELECT SUM(value) FROM V$SGA) -
(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
) "SGA_TARGET"
FROM DUAL;

2. Set the value of SGA_TARGET, either by editing the text initialization parameter
file and restarting the database, or by issuing the following statement:
ALTER SYSTEM SET SGA_TARGET=value [SCOPE={SPFILE|MEMORY|BOTH}]
where value is the value computed in step 1 or is some value between the sum of
all SGA component sizes and SGA_MAX_SIZE.

3. Do one of the following:
■ For more complete automatic tuning, set the values of the automatically sized
SGA components listed in Table2–3 to zero. Do this by editing the text
initialization parameter file, or by issuing ALTER SYSTEM statements similar
to the one in step 2.
■ To control the minimum size of one or more automatically sized SGA
components, set those component sizes to the desired value. (See the next
section for details.) Set the values of the other automatically sized SGA
components to zero. Do this by editing the text initialization parameter file, or
by issuing ALTER SYSTEM statements similar to the one in step 2.

For example, suppose you currently have the following configuration of parameters
on a manual mode instance with SGA_MAX_SIZE set to 1200M:
■ SHARED_POOL_SIZE = 200M
■ DB_CACHE_SIZE = 500M
■ LARGE_POOL_SIZE=200M
Also assume that the result of the queries is as follows:
SELECT SUM(value) FROM V$SGA = 1200M
SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY = 208M
You can take advantage of automatic shared memory management by setting Total
SGA Size to 992M in Oracle Enterprise Manager, or by issuing the following
statements:
ALTER SYSTEM SET SGA_TARGET = 992M;
ALTER SYSTEM SET SHARED_POOL_SIZE = 0;
ALTER SYSTEM SET LARGE_POOL_SIZE = 0;
ALTER SYSTEM SET JAVA_POOL_SIZE = 0;
ALTER SYSTEM SET DB_CACHE_SIZE = 0;
ALTER SYSTEM SET STREAMS_POOL_SIZE = 0;
where 992M = 1200M minus 208M.

启用ASSM
1. 计算SGA_TARGET的值
利用当前sga总值减去以后为sga组件留下来的值
SELECT (
(SELECT SUM(value) FROM V$SGA) -
(SELECT CURRENT_SIZE FROM V$SGA_DYNAMIC_FREE_MEMORY)
) "SGA_TARGET"
FROM DUAL;
2. 设置第一步计算出来的SGA_TARGET
3. 将可自动调整的SGA组件值设置为0(如SHARED_POOL_SIZE) , 为了控制可自动调整组件
的最小值 , 可为这些组件设置期望的值

[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10599713/viewspace-996219/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10599713/viewspace-996219/