在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

ASMM/AMM開啟時動態調整記憶體元件(shared_pool_size, db_cache_size等)大小

相關文章