ANBOB™

因安装数据库时启用了ASMM,过了几天发现分配不太理想想禁用ASMM，手动调整shared_pool_size, 这是一个动态参数，修改后立即生效无需重启数据库实例，但修改时遇到了比较奇怪的现象。

SQL> select * from v$version where rownum=1;
BANNER
——————————————————————————–
Oracle Database 11g Enterprise Edition Release 11.2.0.3.0 – 64bit Production

SQL> select name,ISSYS_MODIFIABLE from  v$parameter where name=’shared_pool_size’;
NAME                 ISSYS_MOD
——————– ———
shared_pool_size     IMMEDIATE

sys@ICME>show parameter shared_pool
NAME                                 TYPE        VALUE
———————————— ———– ——————————
shared_pool_reserved_size            big integer 144M
shared_pool_size                     big integer 2560M

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 30272M
SQL> show parameter sga_max
NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_max_size                         big integer 30272M

sys@ICME>alter system set sga_target=0;
System altered.

sys@ICME>alter system set shared_pool_size=1200m;
alter system set shared_pool_size=1200m
*
ERROR at line 1:
ORA-02097: parameter cannot be modified because specified value is invalid
ORA-04034: unable to shrink pool to specified size

sys@ICME>alter system set sga_target=20g;
System altered.

sys@ICME>alter system set shared_pool_size=1200m;
System altered.

sys@ICME>alter system set sga_target=0;
System altered.

sys@ICME>show parameter shared_pool_
NAME                                 TYPE        VALUE
———————————— ———– ——————————
shared_pool_reserved_size            big integer 144M
shared_pool_size                     big integer 2560M

sys@ICME>alter system flush shared_pool;
System altered.

sys@ICME>alter system flush shared_pool;
System altered.

sys@ICME>alter system set shared_pool_size=1200m scope=both;
System altered.

sys@ICME>show parameter shared
NAME                                 TYPE        VALUE
———————————— ———– ——————————
_shared_io_pool_size                 big integer 0
hi_shared_memory_address             integer     0
max_shared_servers                   integer
shared_memory_address                integer     0
shared_pool_reserved_size            big integer 144M
shared_pool_size                     big integer 1216M
shared_server_sessions               integer
shared_servers                       integer     1

sys@ICME>alter system set sga_target=0;
System altered.

sys@ICME>show parameter shared_pool
NAME                                 TYPE        VALUE
———————————— ———– ——————————
shared_pool_reserved_size            big integer 144M
shared_pool_size                     big integer 2560M

SQL> alter system set sga_target=20g;
System altered.

SQL> show parameter sga_target
NAME                                 TYPE        VALUE
———————————— ———– ——————————
sga_target                           big integer 30272M

SQL> alter system set sga_max_size=30000m scope=spfile;
System altered.

SQL>shutdown immediate

SQL>startup

SQL> show parameter shared_pool
NAME                                 TYPE        VALUE
———————————— ———– ——————————
shared_pool_reserved_size            big integer 93952409
shared_pool_size                     big integer 1792M

SQL> alter system set shared_pool_size=1200m;
System altered.

SQL> show parameter shared_pool
NAME                                 TYPE        VALUE
———————————— ———– ——————————
shared_pool_reserved_size            big integer 93952409
shared_pool_size                     big integer 1216M

done!

Summary:

可以看到如果sga_target=0 改shared pool size 是ora-2097,ora-4037, 如果给sga_target 一个值，修改了shared_pool_size后再sga_target=0 ,shared_pool_size 又恢复了先前的配置，而且修改sga_target 的值是为非0时显示修改无变化，最终手动修改了sga_max_size 静态参数 重启实例后，修改生效 ，MOS’ NOTE 433864.1也有记录 原因是说sga_max_size未明确赋值。

这里还有一篇memery_target 的问题

http://www.dba-oracle.com/t_oracle_11g_memory_target_bug.htm

Tip:
SGA_MAX_SIZE is the summation of DB_CACHE_SIZE(DB_BLOCK_SIZE)+LOG_BUFFER+SHARED_POOL_SIZE+LARGE_POOL_SIZE +JAVA_POOL_SIZE+ STREAMS_POOL_SIZE+ DB_nk_CACHE_SIZE+DB_KEEP_CACHE_SIZE+DB_RECYCLE_CACHE_SIZE.

打赏

微信扫一扫，打赏作者吧～