[신용범]경험은 곧 힘이다

사람은 살아가면서 누구나 경험을 하게 되고 그 경험에서 오는 지식 또한 상당하다고 생각합니다.
그 경험만으로 지식이 되는 경우도 있지만 그렇지 않고 계속 생각해보고 자기 것으로 만들기 위한 노력해야 하는 경우도 있습니다.

저는 oracle 교육 과정을 거쳐 EXEM에 입사하면서 많은 것을 경험하게 되었고 배우게 되었지만 가끔 뒤돌아 생각해보면 ‘내가 정말 아는 것일까?’ 하는 생각이 들곤 합니다. 그것은 제가 그 지식을 내 것으로 만들기 위해 얼마나 노력을 하였는지 생각해 보는 계기가 되곤 하는데 실제로 고객 사에서 어떤 질문을 받게 되었을 때 책에서 지식을 얻었을 뿐 직접 경험하거나 여러 가지의 경우를 생각하여 테스트해 본 적이 드물기 때문에 확신이 가지 않을 경우가 많습니다.

물론 사람이 모든 것을 기억하지는 못하지만 내가 그 문제에 대하여 얼마나 생각하였는가는 곧 누구보다 이 문제에 대해서는 자신감이 생기게 되고 문제를 해결하는데 큰 힘이 될 것입니다.

 얼마 전 제가 기술 지원을 하는 사이트에 대리님과 지원을 하던 중 빈번하게 자주 사용되는 Table의 physical read의 발생량이 많아서 cache buffer에 keep 하는 것에 대하여 가이드를 한 적이 있습니다.

Oracle 9i부터 적용된 파라미터인 DB_KEEP_CACHE_SIZE 를 적용하면 되는 것인데 알고는 있지만 직접 테스트 해 본 적은 없었고 상세하게 어떻게 적용해야 하는지에 대해서는 생각을 하지 못하고 있었습니다. 담당자에게 적용법을 설명 드리기 위하여 직접 테스트를 하면서 느끼는 바가 켰는데 테스트의 중요성과 아는 지식을 나름대로 체계적으로 정리해야겠다는 생각이 우선 들게 되었던 것 같습니다.

아래는 DB_KEEP_CACHE_SIZE에 대하여 간략하게 테스트를 한 내용입니다.

SQL> show sga

Total System Global Area  285212672 bytes
Fixed Size                  1218992 bytes
Variable Size              67110480 bytes
Database Buffers    213909504 bytes
Redo Buffers                2973696 bytes

SQL> show parameter db_keep_cache_size
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
db_keep_cache_size                   big integer 0

1. Keep 할 table 생성

SQL> create table TEST_KEEP as select * from emp;
14 rows created.

SQL> insert into test_keep select * from test_keep;
28 rows created.

…

SQL> insert into test_keep select * from test_keep ;
114688 rows created.

SQL> select owner, segment_name, bytes/1024/1024 Mbyte
  2  from dba_segments
  3  where segment_name='TEST_KEEP';

OWNER                   SEGMENT_NAME                                 MBYTE
------------------------------ ----------------------------------------------------------
SYS                           TEST_KEEP                                          10

2. DB_KEEP_CACHE_SIZE 파라미터 적용

SQL> ALTER SYSTEM SET DB_KEEP_CACHE_SIZE=11M;
System altered.
SQL> alter system flush buffer_cache;
System altered.

3. Keep 시킬 table 설정

SQL> alter table TEST_KEEP storage(buffer_pool keep);
SQL>
SQL> select count(*) from TEST_KEEP;
  COUNT(*)
----------
    229376
    여기까지 하면 적용이 완료된 것이며 별다른 메시지는 보이지 않는다.
buffer에 keep시키기 위하여 Table을 Full Scan을 유도
   
4. Disk I/O 발생 여부 Test
우선 buffer cache에서 밀려나도록 사이즈가 큰 테이블의 Full Scan

OWNER                   SEGMENT_NAME                   MBYTE
------------------------------ ----------------------------------------- ----------
SYS                       IDL_UB1$                          167
SCOTT                    TEST_SYS                          158
SYS                       TEST                              72
MAXUSER                 XM_SYS_SESSION                  200
MAXUSER                 XM_USER_SESSION                 600
MAXUSER                 XM_USER_SESS_UX                 184
MAXUSER                 XM_SQL_TEXT                      504
MAXUSER                 XM_SQL_STAT                      104
MAXUSER                 XM_SQL_WAIT                     104
ORACLE                   INVENTORY                        62
10 rows selected.

그 후에 다시 TEST_KEEP table을 Full Scan하여서 Disk I/O가 발생하였는지 확인
   Trace를 확인하여 Disk I/O 관련한 OWI 이벤트가 발생하였는지 검토
   (db file sequential read , db file scattered read)

5. 참고 사항
적절한 양의 CR 블록까지 담을 공간을 고려하면 테이블의 크기의 최소 1.5 ~ 2 배 정도가 적당하며 만일 시간이 지나면서 테이블이 점점 커진다고 하면 충분하게 3 배 정도를 잡는 것이 적당할 것입니다.

과거에 테스트를 하였을 때는 Table의 크기만큼만 keep size를 정하게 되면 Disk I/O 가 다소 발생하는 것을 볼 수 있었는데 이번에 테스트 할 때는 너무 간단하게 해서 인지 Disk I/O 가 발생하지 않았습니다.

위의 참고사항은 조동욱 차장님의 욱짜 블로그에서 참조 하였습니다.
http://ukja.tistory.com/133