ASMM/AMM开启时动态调整内存组件(shared_pool_size, db_cache_size等)大小

在ORACLE ASMM或AMM开启的情况下, 数据库来动态调整其中的各个组件,但是在绑定变量较差的系统中shared pool会慢慢增长但不能自动收缩,操作buffer cache 越来越来小影响性能,需要手动调大db_cache_size的值,来控制shared pool的增长,在调整db_cache_size的时候经常会出现

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00384: Insufficient memory to grow cache

需要先关闭ASMM和AMM来强制收缩shared pool的大小,调整后再开启ASMM或AMM

SQL> show parameter mem

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

hi_shared_memory_address             integer     0

memory_max_target                    big integer 1584M

memory_target                        big integer 1584M

shared_memory_address                integer     0

SQL> show parameter sga

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 1584M

sga_target                           big integer 0

SQL>

SQL> show parameter db_cache

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_advice                      string      ON

db_cache_size                        big integer 0

SQL> show parameter pga            

 

SQL> alter system set db_cache_size=500M sid='oracle2';

alter system set db_cache_size=500M sid='oracle2'

*

ERROR at line 1:

ORA-02097: parameter cannot be modified because specified value is invalid

ORA-00384: Insufficient memory to grow cache

 

SQL> show parameter sga

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

lock_sga                             boolean     FALSE

pre_page_sga                         boolean     FALSE

sga_max_size                         big integer 1584M

sga_target                           big integer 1040M

SQL> alter system set sga_target=0 sid='oracle2';

 

System altered.

 

SQL> alter system set db_cache_size=500M sid='oracle2';

 

System altered.

 

SQL> alter system set  memory_target=1584M sid='oracle2';

 

System altered.

 

SQL> show parameter db_cac

 

NAME                                 TYPE        VALUE

------------------------------------ ----------- ------------------------------

db_cache_advice                      string      ON

db_cache_size                        big integer 512M

 

SQL> 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 15938355

shared_pool_size                     big integer 608M

shared_server_sessions               integer

shared_servers                       integer     1

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

转载于:http://blog.itpub.net/15747463/viewspace-1389702/