태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

[오우경]Wiki와 Oracle Reference를 애용합시다!!

엑셈 사람들 2008. 8. 22. 15:53

사용자 삽입 이미지

외근 나가계신 용범씨에게 전화가 왔다. 모 고객사의 장애 원인을 파악해달라고 하셨다.

사무실에 있었던 나는 고객사와 연락을 해서 원격 접속을 하고 어디가 이상한지 찾아보았다. 살펴보니 8월 12일자에 성능이 현저하게 떨어져있는 것을 발견했다. 그래서 해당일자 log를 열어보았고, 특정시점에서 active session이 높게 나타나고 성능은 떨어져있는 것을 확인했다. 그래서 전에 배웠던 대로 Stat Ratio Event를 찾아 보았고, 그 시점에 gc buffer busy 와 SQL *Net message from dblink가 대부분을 차지하고 있음을 발견할 수 있었다.

신입사원인 나에게는 아직 이런 지표들이 익숙하지 않고 확신이 서지 않았기에 WIKI와 Oracle Reference를 찾아보았다. 

잠깐 이 지표들을 살펴보자.

[ gc buffer busy ]
 Gc buffer busy 이벤트는 로컬 프로세스가 읽고자 하는 블록이 현재 리모트 노드의 요청에 의해 사용중임을 의미하는 이벤트이다. Gc buffer busy 이벤트는 Placeholder/Fixed-up의 분류의 따르지 않는 독립 이벤트이다.

 Gc buffer busy 이벤트는 buffer busy waits 이벤트나 read by other session 이벤트의 글로벌 버전으로 이해하면 된다. 서버 프로세스가 특정 블록을 사용하고자 하는 시점에 버퍼 락 경합이 발생하면 대기하게 되는데, 그 발생 사유에 따라 이들 대기 이벤트들 중 하나를 사용하게 된다.

[ gc buffer busy의 해결 ]
 Gc buffer busy 이벤트의 근본적인 발생 원인은 buffer busy waits 이벤트와 동일하며, 해결책 또한 동일하다. 핫 블록이 gc buffer busy 이벤트 발생의 가장 일반적인 원인이다. 따라서 문제가 되는 핫 블록을 분산시킴으로써 문제를 해결할 수 있다. 세그먼트 레벨의 파티셔닝 적용, 우편향 인덱스 현상의 해소, 시퀀스 캐시 크기의 증가, PCTFREE의 증가 등이 보편적으로 사용되는 방법이다.

 FLM을 사용하는 경우에는 세그먼트 헤더 블록이 버퍼 경합의 원인이 될 수 있다. 다행히 오라클 10g R2부터는 ASSM이 기본적으로 사용되기 때문에 FLM 환경에서 발생하는 성능 문제가 크게 줄어든다. 만일 FLM을 사용하는 환경이라면, 반드시 FREELIST GROUPS 속성을 노드 수와 동일하게 설정해서 세그먼트 헤더 블록의 경합을 최소화해야 한다.

[ SQL *Net message from dblink ]
 SQL *Net message from client 대기 이벤트는 원격 DB로부터 데이터를 전송 받아 대기할 때 발생한다. 일반적으로 해당 이벤트는 세션이 IDLE 상태라는 것을 의미한다.

 사용자와 상호작용 하지 않는 배치 프로그램에서 SQL *Net message from dblink 대기 이벤트의 대기시간이 과다할 경우, 이것은 애플리케이션 소스 코드가 비효율적으로 작성되었거나, 네트워크 레이어 부분이 문제일 수 있다. 따라서 해당 대기이벤트의 높은 대기시간에 의해 데이터베이스 성능이 저하되는 것은 아니다. 대기시간이 과다하다는 것은 성능상에 문제가 있다는 것을 감지할 수 있으나, 실질적으로 데이터베이스의 문제는 아니라는 것을 명확하게 알 수 있다.

음…..이렇게 지표 설명을 찾아보고 하면서….뭐가 제일 큰 문제일까?? 고민하기 시작했다. 무엇이 문제이더냐!!

해당 시점의 SQL도 확인해보고 alert.log도 확인 해 보았으나 별 문제가 없는 것 같았다.
현 시점의 I/O양도 적었고, SQL도 부하가 큰 것이 아니었다. 음….그럼 뭐가 문제여서 이 시점에서 성능이 떨어졌던 것일까?? 뭔가 딱 보이면 좋을텐데…;; 별 문제가 없어 보여서 다시 고민이 시작됐다.

그러던 중 갑자기 저 위에 밑줄 친 문장에 눈이 확~ 가는 것이 아닌가?? 아! 혹시…..DB가 아니라 네트워크에 문제가 있는 것은 아닐까??

혹시나 하는 마음에 담당자께 말씀 드려서 DB에 문제가 있는 것이 아니라 네트워크상에 문제가 있는 것 같다고 말씀을 드렸더니 확인 해 보신다고 말씀하셨다. 그래서 혹시 또 다른 문제가 있었나?? 초조해 하며 로그를 보고 있었더니 담당자한테서 연락이 왔다. 확인해보니 그 당시에 파이버 채널(Fibre channel)에 문제가 있었다고 말씀해주셨다. 다행히 예상했던 대로 네트워크 문제였고, 짧고도 긴 시간이었지만, 로그와 지표를 보면서 네트워크 문제인지 알아 낸 내 자신에게도 스스로 대견스러웠다.^^;;

Wiki나 Oracle Reference가 많은 도움이 된 것 같다. 여러분도 자주 애용하세요~*^^*

아직 많이 부족하지만, 틈틈이 이렇게 로그도 분석해보고 지표공부도 꾸준히 하면서 엑셈을 대표할 수 있는 DataBase Artist로 성장해야겠다.!! 오늘도 아자아자!!

  • 이명진 2008.08.27 09:44 ADDR 수정/삭제 답글

    오~ 날이 가면 갈수록 실력이 쑥쑥 향상되는 당신, 당신이 자랑스럽습니다^^

[김종민]다시 한번 돌아보자, 뮤텍스

엑셈 사람들 2008. 8. 11. 15:38

사용자 삽입 이미지
얼마 전 모 고객사에 DB 장애로 인한 MAXGAUGE LOG 분석 의뢰가 들어왔다.

 

업무가 많아 달갑지는 않았지만 급한 상황이라 먼저 일을 처리 해야겠다는 생각으로 로그를 받아 로그에 남아있는 현상을 살펴 보기 시작하였다. 비교적 문제 시점은 빨리 확인을 할 수가 있었다.

 

장애 시점으로 보이는 구간에 CURSOR PIN S WAIT ON X 대기 이벤트가 대량 발생하고 있었고, 담당자의 말에 의하면 이 시점에 DB HANG이 걸린 것 같은 현상이 있었다는 것이었다.

 

단순히 파라미터 수정을 통해 문제는 해결이 된다는 것으로 답변을 하였지만, 담당자가 원하는 답변은 그런 것이 아니었고 CURSOR PIN S WAIT ON X 대기에 대한 구체적인 내용을 원하는 것이었다. 하지만 아직 이 문제에 대해 나름대로 정리를 해본 적이 없었고 설명을 대략적으로 하려 하였으나 결국 제대로 이해시키지 못하였다.

 

업무가 많아 일을 빨리 처리 하려다 제대로 된 지원을 하지 못하게 되었던 것이었다. 그래서 다른 업무를 미루고 관련 정보를 찾을 수 밖에 없었다.


내가 알게 된 정보는 아래와 같다.

 

해당 대기 이벤트는 ORACLE에서 LOCK 메커니즘 중 MUTEX라는 기능을 10R2부터 디폴트로 사용함으로써 발생되는 문제이며, 현재 내가 지원하는 고객사 중 ORACLE 10.2.0.3을 운영하는 곳에서 종종 발견되는 이벤트였다. (물론 장애를 포함하여서..)

 

근본적인 해결 책을 아닌 것 같지만 현재 해당 문제는 _kks_use_mutex_pin 라는 ORACLE 히든 파라미터의 수정을 통해 이벤트를 해소 할 수 있는 것으로 알려져 있다.


뮤텍스란 무엇인지 살펴보면,  

뮤텍스(MUTual EXclusion)(상호배제)
Critical Section을 가진 Thread들의 running time이 서로 겹치지 않게, 각각 단독으로 실행되게 하는 기술이다.
* Critical Section : 프로그램 상에서 동시에 실행될 경우 문제를 일으킬 수 있는 부분.
만약 어느 Thread에서 Critical Section을 실행하고 있으면 다른 Thread들은 그 Critical Section에 접근할 수 없고 앞의 Thread 가 Critical Section을 벗어나기를 기다려야 한다.
 

뮤텍스의 특징

 

-       다수의 프로세스가 동일한 리소스를 공유할 수 있게 해줌

-       시스템으로부터 프로그램이 요청한 리소스를 위한 mutex을 하나 생성하고

-       시스템이 고유id를 부여한다. ( no wait mode latch와 비슷함)

-       latch와 달리 mutex은 시스템이 관장

-       latch spin을 수행하지 않기 때문에 가벼울 수 있다.

-       문제발생시 oracle해결범위를 넘어선다.

-       mutex의 경우 복구가 안 된다.

 

아마도 뮤텍스라는 기능을 이용함으로 인해 mutex pin을 사용하는 과정에서 아래와 같은 현상이 발생하였고 mutex pin exclusive로 획득 중인 세션이 비정상적인 이유로 인해 계속해서 pin을 획득하게 되어 mutex pin을 획득하기 위해 다른 세션의 대기가 지속 되는 것으로 예측이 되었다.


사용자 삽입 이미지

 

오라클 버그에도 등록이 되어 있는 내용을 보면 특정 hp장비에 (CAS기능을 지원하지 않는 장비) 드물게 이러한 문제가 있으며, 아직 해당 문제는 해결이 되지 않았으며 근본적으로 11g에서 해결이 된다고 한다.

 

시간을 투자하여 나름대로 관련 정보를 검색한 후 정리를 하고 나니 비로소 담당자가 이해를 잘 하였다. 그리고 고객사 담당자의 DB HANG이라는 말은 확인을 해본 결과 개발자의 얘기였으며, 엄밀히 말하면 HANG의 상태는 아니었고 CURSOR PIN S WAIT ON X 대기 이벤트를 대기하는 세션의 작업이 수행이 되지 않아 그렇게 얘기 한 듯 하였다. 담당자에게 TOTAL SESSION의 증가 구간을 확인 할 것을 권고 하였고 실제로 맥스게이지 로그를 통해 확인해 보니 문제 발생 이 후 세션의 증가 구간이 있었다.

 

결국 이와 같은 문제는 _kks_use_mutex_pin = false 로 설정하면서 mutex 기능을 사용 해제 하면 해결이 되나 히든 파라미터인 관계로 oracle에 확인 후 설정을 하시는 것이 좋다는 권고만 할 수 있었다.

 

지원은 시간이 많이 걸리긴 했지만 답변을 줄 수 있었고 이러한 경험을 통해 명확히 문제에 대한 지식이 없을 경우, 지원의 시간이 많이 걸릴 뿐 아니라 담당자가 이해하기까지도 어려움이 많음을 느꼈다.

 

오늘의 교훈은 급한 길일수록 여유를 갖고 주어진 상황을 처리하는 것이 결과적으로 고객사에게 더욱 믿음을 줄 수 있는 방법임을 알게 되었다.

 

  • Favicon of https://ukja.tistory.com 욱짜 2008.08.11 16:50 신고 ADDR 수정/삭제 답글

    11g부터 Mutex의 사용은 더 이상 선택이 아니라 필수인 거 같습니다.
    아래 결과를 보면 library cache와 관련된 대부분의 latch가 없어졌고, 이는 곧 library cache와 관련해서는 더 이상 latch가 아닌 Mutex를 사용하겠다는 것과 동일한 의미인거 같습니다.
    버그는 어떻게 피하지? ㅠㅠ

    10g:
    UKJA@ukja102> select name from v$latch
    2 where name like '%library%';

    NAME
    ----------------------------------------
    library cache pin allocation
    library cache lock allocation
    library cache hash chains
    library cache lock
    library cache
    library cache pin
    library cache load lock

    11g:
    UKJA@ukja116> select name from v$latch
    2 where name like '%library%';

    NAME
    ------------------------------
    library cache load lock

[정영원]벌레 잡는 엑셈킬라

엑셈 사람들 2008. 7. 31. 17:10

사용자 삽입 이미지
 

 몇 년 전 PIXAR에서 제작한 ‘A Bug’s Life’라는 애니메이션을 보고 너무 재미있어서 영화관을 나와서도 한참을 깔깔거리며 웃었던 기억이 있다. 그리고 약 10년 후 나의 하루하루는 매일매일 Bug와 함께 씨름하고 있고 Bug는 더 이상 나에게 웃음을 주지 않는다. (월급을 준다 -_-;;)

 

QA업무를 하면서 제품 자체에 대한 지식은 물론이거니와 Domain영역에 대한 지식의 중요성을 많이 느낀다. Maxgauge For Oracle을 테스팅 하기 위해서는 당연히 오라클 관련 지식이 필요하고 설치 플랫폼도 다양하기 때문에 많이 알면 알수록 제품에 대한 이해도 빠르고 숨어있는 BUG들을 쉽게 찾을 수 있다.

 

얼마 전 릴리즈 된 Maxgauge V3.1에서 ‘SQL Planner’라는 새로운 기능이 추가 되었다. 약간 생소한 부분이어서 제발 다른 사람이 맡았으면 좋겠다고 생각하고 있었는데 다른 사람들도 같은 생각이었는지 결국 내게 SQL Planner의 테스팅업무가 맡겨 졌다. 처음에는 어떤 이유에서 이런 기능이 추가되었고 주요 기능은 무엇이며 실행 파일이 3개가 있는데 구성은 어떻게 되어 있는지 뭘 어떻게 시작해야 할지 전혀 감이 잡히지 않아서 갑갑했다. 프로그램을 켜놓고 무작정 눌러 보는 체력기반 테스트를 해야 하나.. 하는 생각과 함께 한숨이 흘러나왔다.

 

그때!!!!!!!!!!!!!!!!!!!!!!!!!

 

 지난 ASTA 국제 소프트웨어 테스팅 컨퍼런스에서 접한 “Noun and Verb’ 테스팅기법이 생각났다.  “Elisabeth Hendrickson의해 처음 제시하였으나 살을 덫 붙여 “Vipul Kocher”가 소개해준 이 기법은 제품 동작에 중요한 질문들을 많이 만들어 효율적으로 TestCase를 작성할 수 있고 요구사항에오류나 부족한 부분이 없는지 확인할 수 있는 방법이다.


기왕 나온 김에 간단하게 그 방법을 살펴보면 다음과 같다.

(어설픈 번역으로 소문이 잘못 날까봐 원문을 옮겼다.)

1)     Extract the nouns and verbs

2)     Use “5W1H” (who, what, why, where, when, how, how many, how far …) to extract adjectives, adverbs

3)     Use pre and post conditions to extract more nouns and verbs

4)     Enumerate properties of every noun

5)     Break down these properties to base elements

6)     Use boundary values, equivalence classes on the base elements

7)     Write tests for individual properties

8)     Combine operations (verbs) to write scenario based tests  


 잘 활용한다면 굉장히 유용한 방법으로 생각되었지만 전문가가 제시한 방법을 그대로 실무에 적용하기는 어려움이 있어 우리 제품에 맞게 조금 변형하여 Maxgauge V3.1 SQL Planner 테스팅에 이 기법을 적용해 보기로 했다. 우선 명사로 정의 가능한 요소들을 뽑고 조작할 수 있는 범위 등을 동사로 설정해서 TestCase의 기본 뼈대를 작성하고 조작방법을 다양하게 변형하여 TestCase를 확장해 나갔다.

 

 그 결과, 지정한 테스팅 기간이 끝났을 때 98건의 TestCase가 작성되었고 45건의 Bug를 등록할 수 있었다. 음하하하


 사실 큰 문제가 없을 것으로 생각했던 프로그램이었는데 예상 보다 많은 수의 Bug가 발견되어서 개발팀도 놀라고 우리팀도 놀랐다. 그 이후에도 2번의 릴리즈가 더 있었지만 그 때 작성했던 TestCase는 계속 보완/재사용되면서 유용하게 활용하고 있다.

 

 퇴근 시간, 어딘가 숨어서 나를 비웃고 있는 Bug들을 생각하며 GPS를 통해 Bug의 위치상세 정보를 확인(자동 수정은 아무리 생각해도 오바다.) 해주는 Google Bugs가 나온다면 얼마나 좋을까 하는 엉뚱한 상상을 해본다.

  • hyon 2008.08.04 13:25 ADDR 수정/삭제 답글

    ㅋㅋ 벅스라이프..구글벅스..센스짱이에요! 버그 완전박멸을 위한 엑셈킬라의 활약 기대기대!!!!

[염동환]쉘 프로그래밍, 얕봤다간 큰 코 다칩니다

엑셈 사람들 2008. 7. 25. 17:47

사용자 삽입 이미지
멕스게이지 지원을 가다보면 SMS 연동이나 기본 기능 외의 기능을 추가하기 위해 스크립트를 만들거나 서버상에서 추가적인 작업들을 많이 하게 된다.


이때
필요한 기술이 프로그래밍이다.


어떻게
보면 제일 간단하고 쉬운것인데 아주 작은 실수 때문에 오히려 고생할 때가 많다.

 

첫번째 중간중간 파일을 생성하고 읽어야 할경우가 많이 있다. 이때 절대경로를 쓰지 않으면 파일이 어디로 떨어질지 알기 어렵다.

실제로 절대 경로가 아닌 현제 디렉토리 기준에서 작성했다가 고생한적이 있다. 분명히 스크립트만 수행하면 정상적으로 동작하는데 데몬의 띄어 데몬에 의해 수행하게 하면 생성되어야 파일이 생성이 안되는 것이었다.

스크립트가 잘못되었나 계속 확인해 보아도 답이 안나와서 헤메는데 엉뚱한데 파일이 계속 생기고 있었음을 알고 ! 이것때문이구나 하면서 이런 실수를 하다니 하며 고생했던 적이 있다.

 

그리고 두번째가 실행권한이다 파일을 만들게 되면 기본적으로 읽기 쓰기만 되고 실행할수 있는 권한이 빠져있게 된다. 그래서 반드시 실행하기 전에 실행권한을 chmod 바꿨는지 확인해야 한다. 이것도 아주 기본적이지만 쉽게 놓칠 있는 경우이다.

 

서버작업을 제일 많이 했었던 H모증권사에서 있었던 일이다.


여기는
10개정도의 DB 멕스게이지가 설치되어있는데 서버에 SMS연동을 시켜 놓았다. 그날 낮에 열심히 각서버에 SMS 연동 스크립트를 만들어서 그들이 원하는 지표에 대해 그리고 이벤트에 대해 SMS연동을 시켜 놓았다. 그래서 정상적으로 동작하는지 테스트도 해가면서 문자가 잘가는 것을 확인하고 아주 뿌듯하게 나왔었었다. 그런데 다음날 아침 일찍 나를 깨우는 전화가 있었다.

 

바로 H모증권의 담당자였다. 담당자 얘기로 밤새 문자가 날라와서 잠을 잘수가 없었다고 했다. 계속 쉬지않고 와서 문자 수신함이 가득차고 그래서 결국 SMS 서비스가 안되게 데몬을 내려놓기까지 했다는 것이다.

 

이유는 밤마다 배치작업을 하는데 배치작업을 하면 CPU 100%까지 사용되고 각종 이벤트가 발생하는 것이었다. 그래서 결국 SMS문자를 warning 아닌 critical 레벨일때만 갈수 있게 고치고 항목도 줄이고 그리고 밤에는 아예 보내지 않게 다시 수정했다.

 

이번 사건을 통해서 너무 많이 설정하는 것도 좋지 않다는 것을 배우게 되었다.

[한민호]HashedStringList 제대로 알고 사용하기

엑셈 사람들 2008. 7. 18. 17:44

사용자 삽입 이미지
 

 엑셈에 입사하게 되면서 학창시절 어렵게 공부했던 List에 관심을 갖기 시작했다. 프로그래밍에 문외한이었던 나에게 자료구조 초반에 배웠던 List는 어렵고 과연 저것이 얼마나 많이 사용될까 싶었는데 현업에서는 매우 자주 사용되고 있었다. 또한 초보 프로그래머들에게는 라이브러리 클래스로 지원되는 List를 이용하면 공들여 List를 프로그래밍 하지 않고도 쉽게 사용할 수 있었다.


 
델파이를 접하게 되면서 가장 처음 접하게 된 리스트가 StringList였다. 정말 유용하지만 List에 데이터가 많을 경우 첫 번째 인덱스부터 순차적으로 검색하기 때문에 검색속도가 매우 떨어지는 단점에도 불구하고 다른 방법이나 내부적 구조를 몰랐기에 그냥 사용할 때가 많았다.


어느 날 검색속도에 관심을 갖고 StringList의 대안을 찾던 중 HashedStringList라는 꽤 괜찮은 List을 알게 되었다. HashedStringList StringList를 그대로 상속받아 만들어져 StringList가 가진 기능들을 전부와 단지 IndexOf 메소드의 기능을 충실히 할 수 있도록 Name ValueHash키만을 가지고 있었다.


 Hash Table
을 가지고 있는 것만으로도 검색속도는 매우 좋을 것이라 생각했고 이것이 어떠한 함정이 있을 것이라곤 깊게 생각하지 못했다.


 이 HashedStringList에 대해 자세히 알아보기 위해 예제를 만들어 확인해 보기로 했다. 예제는 1500만 건의 숫자 스트링을 10 Insert 해본 통계와 10회의 Search 성능의 통계이다.

 

 
 
예제를 보면 StringList HashedStringList Insert 속도는 거의 동일한 것을 확인 할 수 있고 검색 속도는 평균적으로 HashedStringList가 훨씬 앞서는 것을 확인할 수 있다. 예제상의 화면만으로 분석을 해보자면 Insert Hash Table을 생성하지 않는 다는 것을 추측해 볼 수 있다. Hash Key 를 생성하고 Hash List를 만든다면 1500만 건의 데이터를 삽입하는데 절대 동일한 성능이 나올 수가 없기 때문이다. 또한 Search 테스트를 보면 Hash List가 언제 생성되는지 추측해 볼 수 있다. Search 테스트를 보면 Hash List는 첫 번째 데이터를 조회할 때 생성된다고 볼 수 있다.

 1
을 조회하는데 StringList 0ms이 걸렸지만 HashedStringList 7250ms( 7)나 걸렸기 때문이다. 오히려 Insert를 한 시간보다 더 오래 걸린 것을 확인할 수 있다. 이것은 Hash List Insert하기 전 Hash Key를 생성하는데 드는 시간이라 볼 수 있다
.


 StringList
1부터 1500만까지 0을 하나씩 늘려가며 조회해 볼 때마다 시간이 점점 늘어나는 것을 확인할 수 있는데 이것은 단순 for루프를 이용해서 조회가 되기 때문이다. 그러나 HashedStringList Hash List가 생성된 이후 Hash Key를 이용해서 단번에 찾아가기 때문에 검색 시 검색어의 Hash Key를 생성하는 시간을 제외하고는 더 이상 검색하는 속도에는 시간이 들지 않기 때문에 매우 효율적인 검색이 가능하다.


 이러한 결과에 대해 소스를 확인해보면서 좀더 설명해 보도록 하자.

 

-          THashedStringList 소스

THashedStringList = class(TStringList)

  private

    FValueHash: TStringHash;

    FNameHash: TStringHash;

    FValueHashValid: Boolean;

    FNameHashValid: Boolean;

    procedure UpdateValueHash;

    procedure UpdateNameHash;

  protected

    procedure Changed; override;

  public

    destructor Destroy; override;

    function IndexOf(const S: string): Integer; override;

    function IndexOfName(const Name: string): Integer; override;

  end;

 

 소스에서 IndexOf IndexOfName만 오버라이드 했다는 것만 봐도 이 함수들에 대한 내용이   변경이 되었다는 사실을 알 수 있다. 또한 중점적으로 보아야 할 부분이 Value Name에 대해 Hash List가 있다는 것이다. 이것이 바로 검색 시 참조될 리스트 이다.


 
이 두 개의 리스트로 인해 생기는 단점은 메모리 사용량이 데이터 개수가 늘어날 때마다 함께 증가 할 수 있다는 것이다(검색 기능을 이용하지 않는다면 증가하지 않을 수도 있다.). 때문에 데이터가 매우 많다면 윈도우에서 한 프로세스가 소유할 수 있는 메모리가 2GB로 제한되어 있기 때문에 Hash List가 생성될 시점에 Out of Memory와 같은 메시지가 출력될 수도 있다는 문제점이 있다.


 
그렇다면 이 Hash List가 어느 시점에 생성이 되느냐가 중요할 수 있는데 보통 Hash Table은 데이터가 Bucket에 들어가면서 함께 업데이트가 된다. 하지만 앞서 테스트 예제와 같이 HashedStringList는 좀 달랐다. IndexOf IndexOfName을 통해 검색이 될 시점에 Hash List가 생성이 되도록 되어 있었다.

 그래서 Insert 통계를 보면 Hash Table을 생성하지 않기 때문에 일반 StringList HashedStringList가 거의 동일한 데이터 Insert 성능을 보여주었던 것이다.


 
소스를 확인해보시면 더 이해가 빠르리라 생각된다.

 

-          IndexOf 함수

function THashedStringList.IndexOf(const S: string): Integer;

begin

  UpdateValueHash;

  if not CaseSensitive then

    Result :=  FValueHash.ValueOf(AnsiUpperCase(S))

  else

    Result :=  FValueHash.ValueOf(S);

end;

 

-          UpdateValueHash 프로시저

procedure THashedStringList.UpdateValueHash;

var

  I: Integer;

begin

  if FValueHashValid then Exit;

 

  if FValueHash = nil then

    FValueHash := TStringHash.Create

  else

    FValueHash.Clear;

  for I := 0 to Count - 1 do

    if not CaseSensitive then

      FValueHash.Add(AnsiUpperCase(Self[I]), I)

    else

      FValueHash.Add(Self[I], I);

  FValueHashValid := True;

end;

 

 

IndexOf 소스를 보면 UpdateValueHash 프로시저를 호출하게 되는데 이 UpdateValueHash를 확인해보면 ValueHash List를 생성하여 Hash Table을 만들고 있다. 이 과정 때문에 초기 검색 시 매우 많은 시간이 소요되며 많은 데이터가 존재할 경우 아예 검색이 안될 수도 있는 상황이 발생할 수 있다. 실제 1억 건 정도의 숫자 스트링 데이터를 Insert Out of Memory 메시지가 출력이 되어 검색이 제대로 되지 않았다.


 
그리고 최대 단점은 Hash List Bucket이 데이터 수만큼의 배열로 관리가 되고 있기 때문에 한 건의 데이터가 추가되거나 삭제되어 바뀌더라도 배열을 새로 생성해야 한다는 것이다. 때문에 HashedStringList의 데이터가 변경(Add, Delete, Update)되면 다시 검색속도가 느려지는 치명적인 단점을 가지고 있다.


 
결론을 짓자면 HashedStringList를 사용은 변동이 없는 정적인 데이터들을 보관하고 그것들을 자주 조회하는 프로그래밍에 적합하다고 생각된다. 무조건 조회가 빠르다고 해서 사용했다간 오히려 StringList보다 더 많은 메모리를 사용하면서 성능의 이득조차 볼 수 없는 그런 상황이 되어버릴 수 있다. 하지만 적합한 환경에 사용한다면 엄청난 이득을 볼 수 있는 좋은 List란 생각이 든다.

[박성호]물리적 파일 처리가 당신을 괴롭힌다면

엑셈 사람들 2008. 7. 11. 10:10
최근 몇 년간 외부 성능개선 튜닝컨설팅을 나가 담당했던 파트가 온라인 프로그램(OLTP)이였었다.

그러던 와중 모 텔레콤사에서 수납관리 배치 프로그램 튜닝을 진행하게 되었다. 통신회사에서 수납관리라는 업무도 처음 접하는 것이였고, 배치 프로그램 또한 예전 포스코 튜닝그룹에서 일할 때 ERP 파트 튜닝을 진행하면서 겪었던 DW성 튜닝이 전부라 상당히 고전할 것으로 여겨져 은근히 부담이 많이 들었었다.

 

첨에 튜닝을 진행하기 위해서 고객사에 가서 수납관리에 관한 업무를 이해하고, 그 업무들에서 수행되는

프로그램들에 대해 친숙해 지는 과정을 거치며 업무를 수행하는 배치 프로그램들에 대한 튜닝을 실시하였다.

 

일반적으로, OLTP 프로그램(온라인)에 대한 튜닝을 진행하는데 중요한 성능개선방법은 국내에 나와있는 많은 책들에서 이미 소개된 것과 같이

 

-      인덱스에 대한 재조정

-      SQL 재 작성

-      힌트 조정

-      페이징 기법

-      Memory Keep

-      Parameter 조정

-      기타

 

등을 활용하여 프로그램의 일량을 최소화 할 수 있도록 효율적인 Access Path로 유도하는 것이 중요한 성능개선 방법이 될 것이다. 온라인 프로그램과 마찬가지로 배치 프로그램에서도 물론 위의 개선방법들을 많이 사용을 하게 된다.

하지만, 배치 프로그램을 계속 진행하면서 익히 알고 있는 다양한 성능개선방법으로만 튜닝을 진행 하는 것은 뭔가 알 수 없는 벽을 만난듯한 그런 느낌을 많이 받았다. 그런 벽의 존재가 기존에 일반적으로 널리 알려진 성능개선방법과 오라클에서 제안하는 다양한 신기술들이 접목이 되었을 때 배치 프로그램에 대한 성능개선이 좀더 잘 이뤄진다라는 그런 사실이 날 가로막고 있었던 거다.

 

오라클 제품이 버전 업그레이드 되면서 새롭게 등장하는 많은 신기술, 신기능들에 대한 지식이 필요하다라는 생각이~ 들게 했던 계기가 있었는데

물리 파일을 읽어 조회작업과 트랜잭션 처리(UPDATE)를 수행하는 프로그램 중 성능문제가 심각한 프로그램이 있어 이를 해결하기 위해 조치를 취한 방법으로 배치 프로그램(or DW) 튜닝을 지속적으로 해 오셨던 분들은 모두들 알고 있는 기능이겠지만 실제 적용을 해보지 않았던 분들에게는 생소한 기능들 중(나 역시도 한번도 접하지 않고 기능이 나온 지만 알고 있었던 ) 오라클이 9i 에서 첨 소개한 External Table이 있다.  External Table에 대한 정보를 찾고 적용하게 된 것은 아래의 그림과 같은 로직으로 수행이 되는 배치 프로그램이 있었고, 해당 프로그램은 수납관리 프로그램 중 아주 중요한 역할을 수행하고 수행시간이 아주 중요한 배치 프로그램으로 성능개선이 꼭 되어야 하는 핵심 프로그램 중 하나의 내용이다.

사용자 삽입 이미지


위의 배치 프로그램의 로직을 보면, 물리 파일에서 최대 300만 건을 한 건씩 읽어, 300만 건에 대한 한 건씩

UPDATE를 수행하는 프로그램으로 최대 300만 건 수행하는데 3~4시간이 소요되어 수행시간이 길었으며, 고객의 요구는 최대 30분 이내에 처리를 해달라는 것 이여서 상당히 고민에 빠지게 하는 배치 프로그램 이었다.

 

오라클 9i 이전에는 물리 파일에 있는 내용을 읽어 처리하는 조회작업이나 DML 작업(트랜잭션 처리)

경우는 Temp(임시 처리용) Table을 생성하고 SQL*Loader를 이용하여 DB Insert 한 이후 배치 처리하는

방법이 있을 것이고, 물리 파일(업무적인 병렬처리)을 읽어 배치 프로그램 로직을 수행(조회,DML작업)하는 방법이 일반적인 방법일 것이라 생각이 들었다.

그렇지만, 이 방법들은 식빵에 쨈을 발라서 먹지 않을 때 혹은 저녁을
먹을 때 반찬 중에 김치가 빠져 있을 때 느끼는뭔가 허전하고 뭔가 중요한 것이 빠져있는 느낌이 상당히 많이 들게 되었고 또한, 30분 이내에 배치 프로그램의 수행이 완벽히 끝낼 수 있을지 의문이 들었고 기존에 사용되던 방식들을 이용하여 30분 이내에 처리가 되더라도 업무적인 병렬 처리 등등의 처리를 활용하게 되면 프로세스간의 경합과 비효율적인 서버 자원을 사용하게 될 가능성이 많게 되고, 프로그램 수행을 하기 위한 관리적인 요소가 별도로 들어가야 할 것으로 판단되어, 오라클 DW 성능개선 방법들을 오라클 매뉴얼등등을 활용하여 문제의 배치 프로그램의 성능개선을 위한 좋은 방법이 있는지도 꼼꼼히 훑어보기 시작했다.

 

그러던 와중 ~ 이것이다.” 라는 기능이 있는 것이었다.
그것도 Oracle 9i에서 나온 기능 중에.

그것이 바로, External Table 이었다.

External Table
은 물리 파일의 처리에 대한 문제점을 해결하기 위해서 Oracle 9i에서 새로 나온 기능으로 문제의 배치 프로그램을 External Table + Merge Into ~ 구문을 활용하면 아주 괜찮은 성능개선이 될 것이라는 확신이 생겨났다. 또한, 이곳 ORACLE 버전이 10.2.0.3으로 10g부터는 Merge Into ~ 구문도 Upsert (UPDATE+INSERT)으로 작성하지 않고 UPDATE문만 처리하는 것도 가능하기에 문제의 배치 프로그램의 성능 개선방법으로 걸림돌이 되지는 않았다. External Table Merge Into~구문을 활용하여 배치 프로그램을 개선방법을 적용하여 아래와 같이 개선이 되었다.

사용자 삽입 이미지

물리 파일의 300만 건의 읽는 작업은 External Table을 활용하여 한번 읽는 작업으로 처리를 하고, 300만 건의 UPDATE 처리는 Merge Into ~ 구문에서 PARALLEL DML 처리로 하여 적용하였고, 개선 전에 300만 건 처리 시 3~4시간 소요되는 것이 적용 후 최대 15분 이내에는 처리가 완료되는 것을 확인할 수 있었다.

 

적용했던 사례는 아래와 같다.

 

먼저, 물리 파일을 읽기 위하여 UTL DIRECTORY에 물리 파일이 있는 DIRECTORY UTL_FILE_DIR 파라미터에 설정을 한다.

STEP1. utl directory에 위치시키기


SQL> show parameter utl

     

NAME                                    TYPE        VALUE

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

create_stored_outlines           string

utl_file_dir                             string      /usr/tmp, /usr/tmp, /app/oracl

                                                           e/product/10.2.0/db/appsutil/o

                                                           utbound/NDB2_cdb4, /usr/tmp

 

: /home1/var/lll/projs/if/work 폴더(물리적 파일위치) UTL_FILE_DIR 파라미터에 추가하여야 함.




그리고, 물리 파일을 읽어 들일 External Table을 생성한다.






STEP2. External Table 생성하기

 

- Directory 지정

    CREATE OR REPLACE DIRECTORY dat_dir AS '/home1/var/lll/projs/if/work ';

  CREATE OR REPLACE DIRECTORY log_dir AS '/home1/var/lll/projs/if/work ';

    CREATE OR REPLACE DIRECTORY bad_dir AS '/home1/var/lll/projs/if/work ';

    CREATE OR REPLACE DIRECTORY dat_dir AS '/usr/tmp';

    CREATE OR REPLACE DIRECTORY log_dir AS '/usr/tmp';

    CREATE OR REPLACE DIRECTORY bad_dir AS '/usr/tmp';

 

    GRANT READ ON DIRECTORY dat_dir TO 배치유저; à 프로그램 수행 유저

    GRANT WRITE ON DIRECTORY log_dir TO 배치유저;

    GRANT WRITE ON DIRECTORY bad_dir TO 배치유저;

 

- External Table 생성하기 

    CREATE TABLE TB_PAYMENT_UPDATE (

                            payment_data varchar2(180)

    ) ORGANIZATION EXTERNAL

    (

       TYPE ORACLE_LOADER

       DEFAULT DIRECTORY dat_dir

       ACCESS PARAMETERS

       (

         records delimited by newline

         badfile bad_dir:'ext_test%ar_%p.bad

         logfile log_dir:'ext_test%ar_%p.log'

         fields terminated by ','

         missing field values are null

         (

              payment_data

         )

       )

       LOCATION ('payment_update.dat')

    )

    PARALLEL 4

    REJECT LIMIT UNLIMITED

    ;

    ALTER TABLE 배치유저. TB_PAYMENT_UPDATE NOPARALLEL

/home1/var/lll/projs/if/work/payment_update.dat(물리 파일)을 읽을 준비는 다 되었다.









위에서 물리파일의 하나의 Row가 컬럼 구분자 없이 구성이 되어 있어, 물리 파일을 TB_PAYMENT_UPDATE라는






External Table로 불러들이게 되는데, 물리 파일의 한 Row(한줄)을 Payment_data 라는 하나의 Column으로 매칭을
 






시켜서 데이터를 가져와서 원하는 데이터를 자리수로 계산하여 잘라 필요한 데이터를 사용하게 된다. 물리 파일을 읽고,






물리 파일의 데이터를 업데이트하는 로직을 Merge Into ~ 구문을 활용하여 처리를 변경하여 최종 반영하게 된다.





STEP3. SQL 변경하기

 

개선 전 :

- FILE READ : MAX 300만건 à /home1/var/lll/projs/if/work/payment_update.dat (물리 파일)

     - UPDATE STATEMENT 수행 : MAX 300만번 수행됨.

      UPDATE TB_PAYMENT SET

             APPLICATION_ID         = :sql_application_id,

             WDRW_IMPSB_RSN_CD      = :sql_wdrw_impsb_rsn_cd,

             ATST_RETN_AMT          = to_number(:atst_retn_amt2),

             SYS_UPDATE_DATE        = SYSDATE

       WHERE WORK_YYMM  BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-1),'YYYYMM')

         AND TO_CHAR(SYSDATE,'YYYYMM')

         AND BILL_ACNT_GRP_NO       = MOD(:sql_bill_acnt_no,20) + 1

         AND BILL_ACNT_NO           = :sql_bill_acnt_no

         AND ATST_BILL_SEQNO        = to_number(:atst_bill_seqno2)

         AND FILE_PRSS_NO           = :sql_file_prss_no

 

개선 후 :

- FILE READ : 테이블 두번 FTS로 해석 à Merge Into ~

- UPDATE   : Merge Into

~


MERGE /*+ USE_HASH(T B) */ INTO  TB_PAYMENT T

            USING ( SELECT /*+ LEADING(H P U) USE_HASH(H P U) FULL(H) FULL(P) FULL(U) */ à File 읽기

                                P.WORK_YYMM       ,  

                                P.BILL_ACNT_GRP_NO,   

                                P.BILL_ACNT_NO    ,                        

                                H.FILE_PRSS_NO    ,

                                P.ATST_BILL_SEQNO ,

                                P.WORK_DV_CD      ,

                                U.WDRW_IMPSB_RSN_CD,

                                TO_NUMBER(U.ATST_RETN_AMT) ATST_RETN_AMT

                          FROM  TB_PAYMENT P,

                              (

                                SELECT SUBSTR(PAYMENT_DATA,36,9) BILL_ACNT_NO, à 파일에서 해당 데이터 위치

                                       SUBSTR(PAYMENT _DATA,45,10) ATST_BILL_SEQNO,

                                       SUBSTR(PAYMENT _DATA,9,11) ATST_RETN_AMT,

                                       SUBSTR(PAYMENT _DATA,3,6) WDRW_IMPSB_RSN_CD

                                FROM  TB_PAYMENT_UPDATE

                                WHERE PYM_DATA LIKE 'RD%'  ) U,

                             (  SELECT SUBSTR(PYM_DATA,24,5) FILE_PRSS_NO

                                FROM  TB_PAYMENT_UPDATE

                                WHERE PYM_DATA LIKE 'RH%'

                              ) H

                         WHERE P.WORK_YYMM  BETWEEN TO_CHAR(ADD_MONTHS(SYSDATE,-1),‘

                                    YYYYMM') AND TO_CHAR(SYSDATE,'YYYYMM')

                           AND P.BILL_ACNT_GRP_NO   = MOD(U.BILL_ACNT_NO,20) + 1

                           AND P.BILL_ACNT_NO       = U.BILL_ACNT_NO

                           AND P.ATST_BILL_SEQNO    = U.ATST_BILL_SEQNO

                           AND P.FILE_PRSS_NO       = H.FILE_PRSS_NO

                       ) B

                ON ( -- PK로 해당건만 UPDATE -> ROWID 비교도 가능함.

                          T.WORK_YYMM        = B.WORK_YYMM

                      AND T.BILL_ACNT_GRP_NO = B.BILL_ACNT_GRP_NO

                      AND T.BILL_ACNT_NO     = B.BILL_ACNT_NO

                      AND T.FILE_PRSS_NO     = B.FILE_PRSS_NO

                      AND T.ATST_BILL_SEQNO  = B.ATST_BILL_SEQNO

                      AND T.WORK_DV_CD       = B.WORK_DV_CD

                )

                WHEN     MATCHED THEN

                         UPDATE SET  T.APPLICATION_ID         = :sql_application_id,

                                        T.WDRW_IMPSB_RSN_CD  = B.WDRW_IMPSB_RSN_CD ,

                                        T.ATST_RETN_AMT        = B.ATST_RETN_AMT,

                                        T.SYS_UPDATE_DATE      = SYSDATE










개선내용확인하기 - SELECT 처리 TEST 결과 (FILE READ -> UPDATE 대상 선별)

 

call     count       cpu    elapsed       disk      query    current        rows

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

      Parse        1      0.06       0.05          0        663          0           0

      Execute      1      0.00       0.00          0          0          0           0

      Fetch    68321     38.02      44.25          0      95293          0     1024792

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

      total    68323     38.08      44.31          0      95956          0     1024792

     

      Misses in library cache during parse: 1

      Optimizer mode: FIRST_ROWS

      Parsing user id: 427

     

      Rows     Row Source Operation

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

      1024792  FILTER  (cr=96701 pr=0 pw=0 time=40230686 us)

      1024792   HASH JOIN  (cr=96701 pr=0 pw=0 time=40230278 us)

      1024792    EXTERNAL TABLE ACCESS FULL TB_PAYMENT_UPDATE (cr=704 pr=0 pw=0 time=7209313 us)

      1194024    HASH JOIN  (cr=95997 pr=0 pw=0 time=18037752 us)

            1     EXTERNAL TABLE ACCESS FULL TB_PAYMENT_UPDATE (cr=704 pr=0 pw=0 time=7288229 us)

      1194024     PARTITION RANGE ITERATOR PARTITION: KEY KEY (cr=95293 pr=0 pw=0 time=8359986 us)

      1194024      PARTITION LIST ALL PARTITION: 1 20 (cr=95293 pr=0 pw=0 time=7165527 us)

      1194024       TABLE ACCESS FULL TB_PAYMENT PARTITION: KEY KEY (cr=95293 pr=0 pw=0 time=7168107 us)




외부 성능개선 컨설팅 중 발생했던 배치 프로그램의 문제를 접근하는 방향이 익히 알고 있는 성능개선 방법뿐만 아니고
 




잘 사용되지 않는 오라클 기술들을 잘 활용하면 문제 접근이 의외로 쉬울 수 있고, 성능개선 효과 또한 아주 큰 경우가 많






이 있을 것으로 생각된다. 오라클 버전 업그레이드 되고, 오라클이 신기술(New Feature)발표하면 이러한 기술들을 최






소한 그 기능이 어떻게 활용이 되면 좋을지 그리고, 향후 적용할 수 있는 영역이 어딘지 등등에 대해 고민하는 것도 아주
 





좋을 것 같다.