暂无图片
暂无图片
暂无图片
暂无图片
暂无图片

Dynamically Changing shared_pool_size fail!(动态修改Shared pool size失败)

原创 Anbob 2013-09-10
1338
因安装数据库时启用了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.
「喜欢这篇文章,您的关注和赞赏是给作者最好的鼓励」
关注作者
【版权声明】本文为墨天轮用户原创内容,转载时必须标注文章的来源(墨天轮),文章链接,文章作者等基本信息,否则作者和墨天轮有权追究责任。如果您发现墨天轮中有涉嫌抄袭或者侵权的内容,欢迎发送邮件至:contact@modb.pro进行举报,并提供相关证据,一经查实,墨天轮将立刻删除相关内容。

评论