DB스토리

ASMM  좋으나  증상기반 메모리값 변경이어서 느려진후에야 설정 바꿈.

 Buff Cache

 S SYS> show parameter db_block

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_block_buffers                     integer     0
db_block_checking                    string      FALSE
db_block_checksum                    string      TRUE
db_block_size                        integer     8192

S SYS> show parameter cache_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_16k_cache_size                    big integer 0
db_2k_cache_size                     big integer 0
db_32k_cache_size                    big integer 0
db_4k_cache_size                     big integer 0
db_8k_cache_size                     big integer 0
db_cache_size                        big integer 0
db_keep_cache_size                   big integer 0
db_recycle_cache_size                big integer 0

 Recycle pool, keep pool, default pool 은 ASMM이 관리 하지 않음. 수동으로 잡아 줘야 함.

using Multiple Buffer Pools

-- 여유공간 만들기.

다른 메모리를 줄이거나,   => 문제점  : 사용중일경우 바로 안줄어듬.

전체 메모리 늘려주기.      => 문제점 : os 에서 지원하는 메모리의 크기를 넘어설 수 없음

[oracle@orcl ~]$ free
             total       used       free     shared    buffers     cached
Mem:        603728     574208      29520          0       3168     355548
-/+ buffers/cache:     215492     388236
Swap:      1534196     125348    1408848

S SYS> show parameter sga_max_size

NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
sga_max_size                         big integer 160M

S SYS> alter system set sga_max_size=180M scope=spfile;

System altered.

S SYS> shutdown immediate;

-- 용량 주기

 S SYS> alter system set db_keep_cache_size=4M scope=both;

System altered.

S SYS> alter system  set db_recycle_cache_size=4M scope=both;

System altered.

-- 적용

S SYS> alter table scott.tmaa storage(buffer_pool recycle);

Table altered.

S SYS> alter table scott.dept storage(buffer_pool keep);

Table altered.

EM->Admin-> table->edit-> storage탭에 보면 나옴.

S SYS> select owner,table_name, buffer_pool from dba_tables where buffer_pool in ('KEEP','RECYCLE');
OWNER                          TABLE_NAME                     BUFFER_
------------------------------ ------------------------------ -------
SCOTT                          TMAA                           RECYCLE
SCOTT                          DEPT                           KEEP

통계값

 S SYS> select pool,sum(bytes)/1024/1024 "MB" from v$sgastat group by pool;

POOL                 MB
------------ ----------
             79.9979515
java pool             4
shared pool  84.0044899
large pool            4

 현재값

S SYS> show sga                                             ==잘 안쓰임 밑에꺼 볼 것.

Total System Global Area  188743680 bytes
Fixed Size                  1218460 bytes
Variable Size             104859748 bytes
Database Buffers           79691776 bytes
Redo Buffers                2973696 bytes

S SYS> select component, current_size/1024/1024 size_mb from v$sga_dynamic_components;

COMPONENT                                                           SIZE_MB
---------------------------------------------------------------- ----------
shared pool                                                              84
large pool                                                                4
java pool                                                                 4
streams pool                                                              0
DEFAULT buffer cache                                                     68
KEEP buffer cache                                                         4
RECYCLE buffer cache                                                      4
DEFAULT 2K buffer cache                                                   0
DEFAULT 4K buffer cache                                                   0
DEFAULT 8K buffer cache                                                   0
DEFAULT 16K buffer cache                                                  0
DEFAULT 32K buffer cache                                                  0
ASM Buffer Cache                                                          0

13 rows selected.

위 부분은 ASMM 에서 자동으로 잡아주는 부분이기 때문에 ASMM 을 끄기전에 수동으로 잡아 주어야 함.

S SYS> alter system set shared_pool_size=84M scope=both;

System altered.

S SYS> alter system set large_pool_size=4M scope=both;

System altered.

S SYS> alter system set java_pool_size=4M scope=both;

System altered.

S SYS> alter system set streams_pool_size=0M scope=both;

System altered.

S SYS> alter system set db_cache_size=68M scope=both;

System altered.

ASMM 끄기

S SYS> alter system set sga_target=0M scope=both;

System altered.