神刀安全网

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

转载本站任何文章请注明:转载至神刀安全网,谢谢神刀安全网 » Oracle调大db_cache_size

分享到:更多 ()

评论 抢沙发

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址