Oracle调大db_cache_size
Oralce如何调大db_cache_size
db_cache_size设置的即为buffer cache的大小，注意当SGA的大小小于1G时，则基数（granule size）为4M，当SGA大于1G时，则基数（granule size）为16M。本文档以SGA小于1G为前提，所以granule size为4M，即buffer cache的大小必须是4M的倍数,否则会发生四舍五入的近似4M倍数值。
思路
调大sga_max_size的大小–>调大sga_target的大小–>调大db_cache_size大小
实战
# 目前的sga_max_size、sga_target、db_cache_size大小
SQL> show parameter sga
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
748M
sga_target big integer
632M
SQL> show parameter db_cache_size
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
300M
# db_cache_size参数未达到v$sga_dynamic_components里面的max_size时，直接调整db_cache_size即可
SQL> select component,current_size,max_size from v$sga_dynamic_components where component like '%buffer cache%';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT buffer cache 373293056
377487360
KEEP buffer cache 0
0
RECYCLE buffer cache 0
0
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT 2K buffer cache 0
0
DEFAULT 4K buffer cache 0
0
DEFAULT 8K buffer cache 0
0
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT 16K buffer cache 0
0
DEFAULT 32K buffer cache 0
0
8 rows selected.
SQL> alter system set db_cache_size=312M scope=both;
System altered.
SQL> show parameter db_cache_size;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
312M
SQL> alter system set db_cache_size=356M scope=both;
System altered.
SQL> show parameter db_cache_size;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
356M
# db_cache_size参数达到v$sga_dynamic_components里面的max_size时，sga_target未达到sga_max_size时，需要调整sga_target和db_cache_size
SQL> alter system set db_cache_size=364M scope=both;
alter system set db_cache_size=364M scope=both
*
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 db_cache_size
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
360M
SQL> show parameter sga
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
748M
sga_target big integer
632M
SQL> alter system set sga_target= 720M scope=both;
System altered.
SQL> select component,current_size,max_size from v$sga_dynamic_components where component like '%buffer cache%';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT buffer cache 394264576
394264576
KEEP buffer cache 0
0
RECYCLE buffer cache 0
0
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT 2K buffer cache 0
0
DEFAULT 4K buffer cache 0
0
DEFAULT 8K buffer cache 0
0
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT 16K buffer cache 0
0
DEFAULT 32K buffer cache 0
0
8 rows selected.
SQL> show parameter db_cache_size
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
360M
SQL> alter system set db_cache_size=370M scope=both;
System altered.
SQL> show parameter db_cache_size
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
372M
# db_cache_size参数达到v$sga_dynamic_components里面的max_size时，sga_target达到sga_max_size时，需要调整sga_max_size,sga_target和db_cache_size(此处需要注意oracle11g中新增memory_target参数，sga_max_size不能大于memory_target的值)
SQL> alter system set sga_target=740M scope=both;
System altered.
SQL> show parameter sga
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
748M
sga_target big integer
740M
SQL> alter system set memory_target=800M scope=spfile;
System altered.
SQL> alter system set sga_max_size=776M scope=spfile;
System altered.
SQL> show parameter sga
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
lock_sga boolean
FALSE
pre_page_sga boolean
FALSE
sga_max_size big integer
748M
sga_target big integer
740M
SQL> shutdown immediate
Database closed.
Database dismounted.
ORACLE instance shut down.
SQL> startup
ORACLE instance started.
Total System Global Area 810090496 bytes
Fixed Size 2257520 bytes
Variable Size 415239568 bytes
Database Buffers 390070272 bytes
Redo Buffers 2523136 bytes
Database mounted.
Database opened.
SQL> show parameter memory
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
hi_shared_memory_address integer
0
memory_max_target big integer
800M
memory_target big integer
800M
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
776M
sga_target big integer
740M
SQL> show parameter db_cache_size;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
372M
SQL> alter system set sga_target=744M scope=both;
System altered.
SQL> select component,current_size,max_size from v$sga_dynamic_components where component like '%buffer%';
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT buffer cache 444596224
448790528
KEEP buffer cache 0
0
RECYCLE buffer cache 0
0
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT 2K buffer cache 0
0
DEFAULT 4K buffer cache 0
0
DEFAULT 8K buffer cache 0
0
COMPONENT CURRENT_SIZE
---------------------------------------------------------------- ------------
MAX_SIZE
----------
DEFAULT 16K buffer cache 0
0
DEFAULT 32K buffer cache 0
0
8 rows selected.
SQL> show parameter db_cache_size;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
372M
SQL> alter system set db_cache_size = 376M scope=both;
System altered.
SQL> show parameter db_cache_size;
NAME TYPE
------------------------------------ --------------------------------
VALUE
------------------------------
db_cache_size big integer
376M
正文到此结束
热门推荐
相关文章
Loading...
![[HBLOG]公众号](http://www.liuhaihua.cn/img/qrcode_gzh.jpg)
