SGA 자동관리 전환

library cache miss 비율을 보고 shared_pool_size를 수정하고 좀 고생을 했습니다.

구글에 찾다가가 SGA에 관한 글을 보고 많은 도움이 되었습니다.

사고경위(?)는 이렇습니다. ㅋㅋ

shared_pool은 자동관리가 되고 있다는 것을 잠시 잊고 있었습니다.

library cache miss 비율이 4%가 넘어서 shared_pool_size를 늘려주어서 0.2%로 떨어트렸습니다.

문뜩 shared_pool_size를 직접적으로 올려주는 것보다 

sga_max_size 이나 sga_target 값을 올려서 자동으로 shared_pool_size를 올려주는게 나을 것 같아서

수정하기로 했습니다.

SQL> select name, value, isdefault from v$parameter   --이 값은 최초 sga 영역 메모리에 대한 값들입니다.

  2  where name like '%size%';                                     즉, shared_pool_size를 변경해 주기 전

NAME                            VALUE          ISDEFAULT

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

sga_max_size                    205520896  TRUE

shared_pool_size                           0   TRUE

large_pool_size                              0   TRUE

java_pool_size                               0    TRUE

streams_pool_size                         0    TRUE 

shared_pool_reserved_size     3355443   TRUE

java_max_sessionspace_size         0    TRUE

db_block_size                            8192   TRUE

db_cache_size                              0     TRUE

...

29 rows selected.

[수정작업]

SQL> alter system set shared_pool_size = 0 scope=spfile; --다시 원복을 시켰습니다.

System altered.

SQL> show parameter sga_target  

NAME            TYPE           VALUE

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

sga_target       big integer       196M

SQL> show parameter sga_max_size;

NAME               TYPE           VALUE

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

sga_max_size    big integer       196M

sga_target 값이 max size가 되어 sga_max_size값을 올려야겠네요.

SQL> select component, current_size/1024/1024 size

  2* from v$sga_dynamic_components

COMPONENT                            size

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

shared pool                                   72

large pool                                       4

java pool                                         4

streams pool                                   0

DEFAULT buffer cache                   112

...

13 rows selected.

SQL> alter system set sga_max_size =228m scope=both;  

System altered.

SQL> select component, current_size/1024/1024 size

  2* from v$sga_dynamic_components

COMPONENT                            size

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

shared pool                                   72

large pool                                       4

java pool                                         4

streams pool                                   0

DEFAULT buffer cache                   144  --한번 증가시킨 sga_max_size는 다시 줄일수가 없습니다.

       이미 자동으로 메모리에 할당을 했기때문이죠.

                         줄이고자 한다면 기존에 있던 pfile로 startup을 해야겠지요!

...

13 rows selected.

SQL> select name, value, isdefault from v$parameter

  2  where name like '%size%';

NAME                                       VALUE   ISDEFAULT

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

sga_max_size                         239075328  FALSE

shared_pool_size                                0   FALSE  

...

29 rows selected.

SQL>

sga_max_size 와 shared_pool_size는 값을 한번씩 변경해주었습니다. 

그 이후로는 isdefault가 false로 되었습니다.

true로 설정을 어떻게 해야하는지 몰라서 한참을 헤멨습니다.

sga_target 값을 0으로 설정 후 다시 sga_max_size 값을 설정하였습니다.

즉, sga 관리를 수동으로 전환 후 다시 자동으로 전환을 시켰습니다.

SQL> select name, value, isdefault from v$parameter

  2  where name like '%size%';

NAME                            VALUE          ISDEFAULT

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

sga_max_size                    239075328   FALSE

shared_pool_size                 75497472   FALSE

large_pool_size                     4194304    FALSE

java_pool_size                      4194304    FALSE

streams_pool_size                         0    FALSE   

shared_pool_reserved_size     3774873   TRUE

java_max_sessionspace_size         0    TRUE

db_block_size                            8192   FALSE

db_cache_size                   150994944   FALSE

...

29 rows selected.

컥! 자동 관리 되었던 모든 메모리가 false로 변했네요! 

value도 알아서 지정이 되버렸군요.

같은 쿼리문을 실행시킨 처음 결과 값들과 비교를 해보면 많이 달라졌지요!

어떻게 수정을 해야하는지 몰라서 별짓을 다해봤습니다.ㅋㅋㅋ

그냥 pfile로 올려서 다시 원상복귀 시킬까도 생각해보았습니다.

나중에 비슷한 일이 생길 것을 대비해서 다시 열심히 찾아보았습니다.

[해결방안]

일단 쓸데없이 메모리를 차지하지 않도록 해당 자동관리 되는 메모리들을 0으로 수정하였습니다.  

SQL> alter system set shared_pool_size = 0 scope=spfile;

System altered.

SQL> alter system set large_pool_size = 0 scope=spfile;

System altered.

SQL> alter system set java_pool_size = 0 scope=spfile;

System altered.

SQL> alter system set streams_pool_size = 0 scope=spfile;

System altered.

SQL> alter system set db_cache_size = 0 scope=spfile;

System altered.

* 아래와 같은 reset 명령어가 있었습니다.

SQL> alter system reset sga_max_size scope=spfile sid='*';

System altered.

SQL> alter system reset shared_pool_size scope=spfile sid='*';

System altered.

SQL> alter system reset large_pool_size scope=spfile sid='*';

System altered.

SQL> alter system reset java_pool_size scope=spfile sid='*';

System altered.

SQL> alter system reset streams_pool_size scope=spfile sid='*';

System altered.

SQL> alter system reset db_cache_size scope=spfile sid='*';

System altered.

SQL> shutdown immediate;

Database closed.

Database dismounted.

ORACLE instance shut down.

SQL> startup

ORACLE instance started.

Total System Global Area  239075328 bytes

Fixed Size                  1218724 bytes

Variable Size              83887964 bytes

Database Buffers          150994944 bytes

Redo Buffers                2973696 bytes

Database mounted.

Database opened.

SQL> select name, value, isdefault from v$parameter

  2  where name like '%size%';

NAME                            VALUE          ISDEFAULT

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

sga_max_size                    239075328  TRUE

shared_pool_size                           0   TRUE

large_pool_size                              0   TRUE

java_pool_size                               0    TRUE

streams_pool_size                         0    TRUE 

shared_pool_reserved_size     3774873   TRUE

java_max_sessionspace_size         0    TRUE

db_block_size                            8192   TRUE

db_cache_size                              0     TRUE

...

29 rows selected.

정상으로 되었습니다.ㅋㅋ sga_max_size을 늘리려다가 무슨 난리를 친건지..ㅎㅎㅎㅎ

library cache miss 비율은 1%로 되었네요!

나의 의견 : 

참고로 위 설명에는 나와있지 않았지만 shutdown을 너무 많이 한 것 같습니다.ㅜㅜ