.ora 文件,将这些参数的值设成0:
db_cache_size, shared_pool_size, java_pool_size,large_pool_size
2. 启动SQLPLUS,以新的pfile文件启动数据库
SQL> startup pfile='....'
让我们来看看调整的结果:
SQL> select name, block_size, current_size from v$buffer_pool;
name block_size current_size
-------------------------------------------------------------
KEEP 8192 204
SQL> Select pool, sum(bytes)/1024/1024 as "M bytes" from v$SGASTAT
group by pool;
pool M bytes
-------------------------------------------------
java pool 4
large pool 4
shared pool 84
205.002403
205.002403=buffer cache + log buffer + fixed sga + all others ...
改动java pool的值
SQL> alter system set java_pool_size=20M;
SQL> select name, block_size, current_size,prev_size from v$buffer_pool;
name block_size current_size prev_size
----------------------------------------------------------------------------------------------
KEEP 8192 188 204
SQL> Select pool, sum(bytes)/1024/1024 as "M bytes" from v$SGASTAT
group by pool;
pool M bytes
-------------------------------------------------
java pool 20
large pool 4
shared pool 84
189.002403
可以看出, db_cache_size的值已经被自动调小了.
再把java pool 的值改回去
SQL> alter system set java_pool_size=8M;
SQL> select name, block_size, current_size,prev_size from v$buffer_pool;
name block_size current_size prev_size
-----------------------------------------------------------------------------------
KEEP 8192 188 204
SQL> select name, block_size, current_size,prev_size from v$buffer_pool;
pool M bytes
-------------------------------------------------
java pool 20
large pool 4
shared pool 84
189.002403
这一次, db_cache_size的值没有变化 , JAVA_POOL_SIZE的值也没有变化
修改large pool的值为16M
SQL> alter system set large_pool_size=16M;
System altered.
SQL> select name,block_size,current_size,prev_size from v$buffer_pool;
NAME BLOCK_SIZE CURRENT_SIZE PREV_SIZE
-------------------- ---------- ------------ ---------
DEFAULT 8192 176 188
SQL> Select pool, sum(bytes)/1024/1024 as "M bytes" from v$sgastat group by pool;
POOL M bytes
------------ ----------
java pool 20
large pool 16
shared pool 84
177.002403
这次，db_cache_size和large_pool_size的值都变了
同样,调大shared_pool_size后, db_cache_size会自动减小.
虽然db_nk_cache_size的值不会随着workload 的改变而自动调整, 我们还是可以看看手工改动db_nk_block_size 的情况
SQL> alter system set db_2k_cache_size=4m;
System altered.
SQL> select name,block_size,current_size,prev_size from v$buffer_pool;
NAME BLOCK_SIZE CURRENT_SIZE PREV_SIZE
---------------- ----------------- ------------ ----------
DEFAULT 8192 172 176
DEFAULT 2048 4 0
SQL> alter system set db_2k_cache_size=0;
System altered.
SQL> select name,block_size,current_size,prev_size from v$buffer_pool;
NAME BLOCK_SIZE CURRENT_SIZE PREV_SIZE
----------------- ---------------- ------------------ --------------
DEFAULT 8192 176 172
SQL> alter system set db_2k_cache_size=8m;
System altered.
SQL> select name,block_size,current_size,prev_size from v$buffer_pool;
NAME BLOCK_SIZE CURRENT_SIZE PREV_SIZE
-------------------- ---------- ------------ ----------
DEFAULT 8192 168 176
DEFAULT 2048 8 0
结论: 手工调整db_nk_cache_size确实会影响原有的参数.
最后说一点: SGA_TARGET参数与SGA_MAX_SIZE参数有密切关联，基本的原则就是前者的值不能大于后者的值.
总结：设置了SGA_TARGET参数后，数据库会在这个范围内自行调整；但许多情况下, 怎样合理地设置这个参数仍是DBA需要考虑的问题, 他们需要结合AWR Report等辅助的工具来分析.( 当然，我们可以根据Advisor的历史信息而确定一个比较合理的值)。
(待续...)
