oracle数据库max使用,【学习笔记】Oracle内存管理 sga_max_size参数配置描述

天萃荷净

分享一篇Oracle数据库内存管理文章，结合案例来熟悉sga_max_size参数配置描述

1.不设置sga_max_size参数

SQL> show sga;

Total System Global Area 420549952 bytes

Fixed Size 451904 bytes

Variable Size 201326592 bytes

Database Buffers 218103808 bytes

Redo Buffers 667648 bytes

SQL> select sum(bytes)from v$sgastat;

SUM(BYTES)

----------

420538688

SQL> !ipcs -m

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x9ba476a4 65536 oracle 640 440401920 65

SQL> alter system set db_cache_size=300M;

alter system set db_cache_size=300M

*

ERROR at line 1:

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

ORA-00384: Insufficient memory to grow cache

1)当sga_max_size不设置时，数据库启动时，会使用数据库默认分配sga大小为初始化值

2)当sga_max_size不设置时，不能在线扩展组件内存大小(使得sga大于当前大小)

2.设置sga_max_size参数

SQL> alter system set sga_max_size=600M scope=spfile;

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area 638654020 bytes

Fixed Size 452164 bytes

Variable Size 419430400 bytes

Database Buffers 218103808 bytes

Redo Buffers 667648 bytes

Database mounted.

Database opened.

SQL> !ipcs -m

------ Shared Memory Segments --------

key shmid owner perms bytes nattch status

0x9ba476a4 98304 oracle 640 658505728 65

SQL> select sum(bytes)from v$sgastat;

SUM(BYTES)

----------

420538948

SQL> alter system set db_cache_size=405M;

System altered.

SQL> select 638654020/1024/1024 from dual;

638654020/1024/1024

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

609.067936

说明sga中剩余空闲内存0.01M

SQL> select 609.067936-sum(bytes)/1024/1024 from v$sgastat;

609.067936-SUM(BYTES)/1024/1024

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

.010742244

继续增加组件大小

SQL> alter system set db_cache_size=416M;

System altered.

SQL> select sum(bytes)/1024/1024 from v$sgastat;

SUM(BYTES)/1024/1024

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

609.057194

SQL> select 638654020/1024/1024 from dual;

638654020/1024/1024

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

609.067936

SQL> alter system set db_cache_size=417M;

alter system set db_cache_size=417M

*

ERROR at line 1:

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

ORA-00384: Insufficient memory to grow cache

这里显示，当db_cache_size增加到415M的时候，sga只有0.01M剩余

但是直到db_cache_size增加到417的时候才报错

1)当sga有剩余时，可以动态调整sga中的部分组件(java_pool_size不能在线设置)

2)当sga没有剩余时，如果继续增加某组件的内存，在一定的范围内，sga会自动调整其他组件大小，以实用该值增加

关于sga_max_size总结

1)如果你的系统内存比较紧张，对停机时间要求不是特别严格，那可以不设置sga_max_size参数，这样在重启数据库设置sga组件的时候，不会因忘记设置sga_max_size而导致不能正常启动

2)如果你的系统内存充足，对停机有严格限制，那建议设置一个较大的sga_max_size，后续可以根据需求动态在线调整sga部分组件

--------------------------------------ORACLE-DBA----------------------------------------

最权威、专业的Oracle案例资源汇总之【学习笔记】Oracle内存管理 sga_max_size参数配置描述