이번에 차세대 프로젝트를 진행함에 따라서 진행했던 업무와 느꼈던 점에 대해서 정리하고자 한다.
📘프레임워크 아키텍쳐
먼저 DevOn 프레임워크라는 Spring을 감싼 프레임워크를 사용하였습니다.
Service
Service는 BaseService를 상속 받는데 이는 비지니스 로직을 수행함에 있어서 Logger, CommonDao를 사용할 수 있게 하여 DB 접속 및 로깅을 편하게 사용 할 수 있습니다. DB 설정이 XML형식으로 제공하여 누구나 쉽게 Service 로직을 구현 할 수 있었습니다.
또 한 ThrowException 이라는 함수를 제공하는데 사용자가 지정한 Message를 기준으로 BusinessException을 발생하는데 이는 화면에서 오류를 일괄적으로 보여주어 사용하기 용이하였습니다.
사용자로 하여금 ThrowException을 사용하여 Transaction 전파 옵션을 NESTED를 사용하게 되었을 때 자식 Transaction에서 오류가 발생했을 때 Logging만 하고 정상 프로세스로 진행하고자 한다면 leavTrace 라는 함수를 통해서 처리할 수 있습니다.
Controller
Ctr은 BaseCtr 이라는 클래스를 상속받아 Service와 마찬가지로 Logger 와 예외처리를 제공하여 개발을 용이하게 해주며 추가적으로 Session, Vaildation , Paging 기능을 제공합니다.
Session 은 로그인을 성공한 사용자들에 대해서 정보를 가져오고자 하는 경우 ex) 주무관의 관리행정동 혹은 ID 유용하며 화면에서 넘겨주거나 세션에서 정보를 가져오거나 유용하게 사용할 수 있습니다.
Vaildation은 보편적으로 많이 사용하는 검증을 제공하면서 주민번호의 유효성 혹은 전화번호, 카드번호를 검증 할 수 있다.
Ctr에서 반환하는 값에 따라서 화면에서 별도로 처리해야 하는 경우 BaseMap에 추가할 수도 있지만 Message 객체를 보내어 추가적인 프로세스를 진행 할 수 있다. message, errorMessage, warnMessage, postMessage를 사용하여 전달 할 수 있으며 postMessage를 제외한 다른 메시지들은 alert처리 됩니다.
DataAccess Layer
DataAccess Layer 에서는 BaseMap이라는 HashMap을 감싼 구조체를 사용하는데 해시맵과 다른 점은 키값에 ""값을 넣을 수 없습니다.
DB에 시스템 컬럼이 존재하는데 ex) 처음 등록 사용자, 등록 일자, 등록 화면 , 마지막 등록 사용자, 마지막 업데이트 일자, 마지막 화면 이는 사용자가 별도로 넣어주지 않아도 시스템에서 자동으로 넣어주어 편리함을 제공합니다.
해당 프레임워크를 사용하면서 스프링을 처음 써보는 사원들도 쉽게 로직구현을 할 수 있었습니다.
ETL 툴을 활용한 데이터 전환
사회보장시스템에서 자생활동 파트를 맡아 업무를 진행하던 중 전환팀의 도움 요청으로 데이터 전환 파트를 3개월 동안 맡았고
Tera Stream 이라는 ETL Tool을 사용하여 ASIS DB 에서 TOBE 전환 DB로 옮기는 작업을 맡았으며 아래와 같은 기술적인 문제를 해결할 수 있었습니다.
KeyMap 보안으로 Asis 누수 데이터를 줄이다.
단순 SELCT 문으로 ASIS 테이블과 TOBE 테이블의 컬럼이 대부분이 일치하여 완수할 수 있는 업무가 있는 반면에 차세대를 진행하면서 개인이 주체가 되는 테이블이 가구가 주체로 변하거나, 테이블 구조가 바뀌어 다른 테이블을 참조하여 값을 가져오는 업무가 대부분이었습니다.
그 중에서 의료급여 파트를 맡게 되었는데 개인정보법으로 인해 개인주체 테이블을 제외한 다른 테이블에서 주민등록번호나 성명을 가지면 안 되는 상황이었습니다.
따라서 주민등록번호를 통해서 개인key 로 대체하는 부분을 맡았는데 KeyMap 업무팀에서 제공한 키맵을 사용하면 의료급여 테이블에서 아래와 같은 이유로 누락 건들이 발생하였습니다.
1. 해당 주체가 주민등록번호가 변경 되었으나 다른 업무테이블에서 주민등록번호가 변경되지 않은 경우
2. 개인key는 주민등록번호와 주체자격으로 이루어져 있는데 여러개의 자격을 가지고 있어 key가 여러개 발생한 경우
3. 의료급여를 신청 하였지만 자격이 미달되어 신청이력만 남아있어 key가 없는 경우
해당 건으로 인해 의료급여 고객사는 모수가 너무 많아 이를 해결해 달라고 요청하였습니다.
처음에는 로직에 맞게 돌아가는 쿼리를 작성하고 이를 Function으로 작성하고 전환작업을 실행하였지만 1시간 동안 파일로 내리는 작업 건수가 0인 사태가 발생하여 전환을 진행하지 못하였습니다.
다음으로는 기존 KeyMap에 추가적인 데이터를 넣어 새로운 KeyMap을 만드는 작업을 하였습니다. 여러 개의 자격을 가지고 있는 경우 의료급여에서의 우선순위 자격 및 자격 상태코드를 통하여 하나의 자격만을 가져오며, 주체 이력 테이블에서 가장 최신의 이력 변경을 가져와 해당 주민등록번호에 맞는 개인key를 가져와 기존 KeyMap에 추가하여 저만의 KeyMap을 만들었습니다.
이를 통해서 기존에 150만 건의 누락건 중에서 10만 건으로 줄일 수 있었으며 나머지 key가 없는 경우에 새로 key를 할당하는 방식을 통하여 의료급여 전환을 완수 할 수 있었습니다.
프로젝트 오픈 전에 데이터 전환을 완수 하라!
기존에 양곡지원 전환을 담당하시는 분이 퇴사 하시고 양곡 지원 쪽 진행이 되지 않아 긴급하게 오픈 일주일 전에 양곡지원 전환 업무를 맡게 되었습니다. 양곡지원에서의 문제점은 다음과 같았습니다.
1. 개인주체ID 를 보장가구ID로 바꾸는 과정에서 KeyMap에 맞지 않는 누락건 수가 많다는 것
2. 각 자격마다 나누어진 테이블을 하나의 테이블로 합치는데 동시에 자격을 갖지 말아야 하는 하는데 동시 자격을 가지고 있는 경우가 있다는 것
3. 양곡지원 대상자 테이블에는 없는데 양곡지원 월별 지원이력 테이블에 있는 개인주체ID가 있다는 것
위의 문제는 고객사와의 협의를 통해서 먼저 KeyMap에 맞는 데이터 건들을 넣고, 동시 자격을 가지는 인원 중에서 우선순위가 높은 자격 대상자를 가져오는 것, 마지막으로 Master 테이블에는 없는 데이터들은 전환하지 않는 것으로 하였습니다. 총 12개 테이블을 7개의 테이블로 줄이는 과정을 3일 만에 완수하였습니다.
이 후 고객사 측에서는 KeyMap에 맞지 않는 데이터들도 넣어달라는 부탁을 받았지만 오픈 8시간 채 안남은 상황이었고 상세이력 테이블은 기존에 돌았던 job이 7시간 걸렸기에 어떻게든 전환 작업 시간을 단축시켰어야 했고 해결 방안은 다음과 같습니다.
1. UnLoad 에서 SELECT 절을 힌트절을 사용하여 시간을 단축 및 UpLoad 에서 direct , parallel 옵션을 주어 빠르게 전환하기
2. 기존에 중복을 TEMP 테이블에서 DELETE 하는 넣는 방법에서 중복이 발생한 레코드에 대한 한 건만 가져오는 방안으로 SELECT문 구현하기
3. 운영 테이블의 인덱스를 삭제하고 페러럴 다이렉트 Insert 를 통해 빠르게 적재 후 인덱스 재생성
위와 같은 해결방안을 통해서 7시간 걸린 JOB을 3시간 만에 해내었고 나머지 테이블의 모수건에 대해서도 무사히 전환 할 수 있었습니다.
📗양곡지원 업무
1년 중에서 양곡지원 업무를 약 8개월 동안 맡게 되었습니다. 이번 프로젝트를 진행하면서 가장 아쉬웠던 부분은 SR(클라이언트 리퀘스트)가 올라왔을 때 해당 SR을 믿고 비지니스 로직을 변경을 자주 한 것입니다.
아래와 같이 에러가 나는 코드에 주석을 하거나 로직을 변경하는 기간이 많아질 수록 방향을 잡기가 어려웠습니다. 양곡지원 부분만 봐야하는 것이 아닌 다른 업무 테이블도 봐야 했기 때문 입니다.
(자격 담당인원에게) 해당 대상자는 자격이 있는 대상자 인가요? → 해당 부분은 가구 업무를 담당하는 사람에게 물어보세요 → (가구 담당 인원에게) 해당 대상자는 자격이 있는 대상자 인가요? → 해당 부분은 자격 담당에게 물어보세요
와 같이 질문 뺑뻉이를 돌다 해당 업무를 잘 알고 있는 주무관님을 만나 기준을 잡고 문제점을 파악 할 수 있었습니다.
추가적으로 진행했던 업무는 양곡 지원을 받을 수 있는 자격을 가지고 있으면서 해당 읍면동에 거주중인 대상자들을 출력하는 메인 화면입니다.
워낙 읍면동에 거주중인 대상자들이 많을 뿐더러 양곡지원을 받을 수 있는 자격또한 13가지라 종종 TimeOut이 발생하였습니다.
먼저 타임아웃 시간 늘리기
기본적으로 프레임워크에서 설정한 쿼리 타임아웃, 서비스단 타임아웃은 30초 , 90초 인데 이를 각 140초, 280초로 늘리고 불필요한 컬럼을 가져오는 부분을 수정하였습니다.
하지만 월초에 많이 신청하는 양곡지원같은 경우 DB가 바쁜 상태이면 종종 TimeOut이 발생하였습니다.
부서코드를 통해 관리행정동 가져오는 부분 추출하기
부서코드를 통해서 해당 읍면동 코드를 가져오는 부분을 자바에서 하거나 혹은 오라클 함수를 통해서 가져올 수 있다. 기존에 후자의 방식을 사용하다가 보니 조인 방식이 Nested Loop Join 가 아닌 Hash Join 으로 풀리고 있었고
이를 자바에서 가져와 동적 파라미터로 넣어주니 TimeOut 빈도수가 절반으로 줄어들게 되었습니다.
최종 쿼리 튜닝 및 별도의 인덱스 생성
기존에 양곡자격이 있는지 유무를 Exists 를 통해서 확인하였으나 이를 밖으로 꺼내어 쿼리 튜닝을 하였고 자격 테이블에 양곡자격에 대한 별도의 인덱스 생성하여 240k 에서 180k 25% 개선할 수 있었습니다.
하지만 여전히 하루에 1~2건 타임아웃이 발생하여 아래와 같은 방법을 제시하였습니다.
1시간 배치를 사용하거나 조회 변경하기
1시간 배치를 사용하여 메인화면에 필요한 값들에 대해서 보여주는 방식을 사용하거나 현재 paging 방식이 (메인 쿼리) where 1<= rownum <= 20 와 같은 방식으로 이루어져 있는데 중복부분이 발생하지 않고 (오래걸리는 서브쿼리) where 1<= rownum<=20 와 같은 방식으로 모든 데이터를 조회 하는 것이 아닌 오래걸리는 부분을 간소화 하여 화면에 보여주는 방식입니다.
전자의 문제점은 배치를 사용하다보니 1시간 이내에 자격이 생성되고 바로 양곡을 신청하더라도 화면에 보이지 않는 다는 단점 과 후자의 경우는 rownum은 절대값이 아닌 해당 쿼리의 임시 번호를 나타내기 때문에 페이징을 하면서 전 화면에 나왔던 데이터가 이후에 나올 수 있다는 문제점이 있었습니다. 이는 700~2000 가구를 조회 하는 사람이 페이징을 하면서 알아차리기 어려워며 엑셀은 파일 서버가 별도로 있어 사용자가 알아차리기 어렵다고 의견을 전달하였지만 현재 타임아웃 빈도수가 많이 줄었으니 이후에 문제가 발생하는 경우 추후 검토하기로 하기로 하였습니다.
업무를 진행함에 있어서 복지부 높으신 분과 정보원 대리님과 양곡에 대해서 회의하는 시간을 가진 적이 있었다.
현재 양곡 시스템에 있는 문제점과 개선점을 들고 이번 업무에 대한 종지부를 찍을 생각에 회의에 대한 기대감이 컸었는데 해당 문제점을 해결할 방안에 대해서 들으신 이후에 반응이 무덤덤하였으며 이는 추후 검토 후 다시 이야기 하자는 방향으로 마무리가 되었습니다.
물론 해당 대리님과 복지부 인원이 바로 결단을 내려 줄 수 있는 상황이 아닐 뿐더러 회의를 길게 하더라도 결론이 나오는 상황이 아니라는 것을 알고 있으나 동일한 주제로 회의를 반복하다 보니 회의의 존재에 대한 궁금증이 생기는 프로젝트였습니다.
프로젝트를 진행하면서 학습하게 된 오라클 지식
연계번호 중복값이 DB에 들어가 있다.
프로젝트를 진행하면서 외부와 통신할 때 연계번호를 주키로 사용하여 데이터를 주고 받는 경우가 있다. 결함이 발생하여 해당 테이블 조회하다가 연계번호가 중복되서 들어가 있는 경우를 확인하였습니다.
오라클에서 제공하는 sequence는 내부 로직을 사용하여 매우 빠르고 중복에 대한 LOCK에러가 발생하지 않는다. 다만 Dedicated Instance 형식으로 돌아는 오라클에서는 속도 향상을 위해서 각 인스턴스마다 채번을 미리하기 때문에 비워져 있는 연계번호가 있을 수 있습니다.
이를 개선하기 위해서는 NO OREDER & NO CASHE 를 사용하면 된다. 이 방식 이외에도 채번 테이블을 사용하거나 MAX() +1 을 사용하여 번호 연속성을 제공하나 동시 트랜잭션 시에 오류가 발생할 수 있어 중복 에러 발생시 예외 처리가 필수적 입니다.
해당 건에 대해서 알아보니 기존 연계 데이터에 개인정보가 포함되어 있어 temp 테이블에 적재하고 난 이후에 암호화 후 업무 테이블에 넣고 temp 테이블의 데이터를 지우는데 해당 테이블을 생성하는 과정에서 연계번호 주키가 풀려서 중복된 값이 들어가 있는 것으로 확인하였습니다.
select count() from TABLE 와 select from TABLE 의 속도 차이
대학 동기 친구가 위 조건에 대해서 왜 속도차이가 나는지에 대해서 물었다. 처음에는 count(*) 는 모든 데이터에 대해서 조회를 하기에 오래 걸리고 단순 조회는 프레임워크 혹은 시스템에서 지정한 Buffer 크기만큼 가져오기 때문에 단순 조회가 빠르다고 생각하였습니다.
하지만 동기는 테이블의 데이터 건수는 3천 건이며 buffer 크기를 5천으로 설정해 두었기 때문에 버퍼 크기의 문제는 아니라고 하엿다.
두번째로는 오라클 버퍼 캐시를 의심하였다. 단순 조회 같은 경우에는 오라클 버퍼 캐시에 저장되는 경우가 많지만 count(*) 와 같은 집계함수는 일반 조회보다 많이 사용하지 않아 데이터 조회가 많이 느리다고 답을 해주었는데 버퍼 캐시 또한 초기화를 하였다는 친구의 답변을 들었습니다.
세번째로는 버퍼캐시에 저장되는 내용물에 대해서 생각해 보았다. 오라클 버퍼캐시에는 데이터 블럭으로 저장되며 이는 테이블의 컬럼 로우 를 조합한 데이터의 집합이 버퍼 캐시에 들어가는 것이며 집계함수의 결과값이 저장이 안된다고 생각하였습니다.
세번째가 맞는 답변이라고 생각하긴 하는데 DB에서는 쿼리 실행시에 DB 인스턴스가 바쁜 경우 혹은 커넥션을 얻는 과정에서 TimeWait 와 같은 수 많은 환경 변수가 존재합니다. 그러면 보편적으로 쿼리 튜닝 진행을 어떻게 할까?
실행계획을 통한 가져오는 BLOCK 개수 파악
양곡지원 계획 검색 쿼리를 튜닝함에 있어서 힌트절을 변경하더라도 PL/SQL 툴에서 변경된 실행계획을 보여주지 않는 다는 것을 확인하였습니다.
아마 오라클 서버에 내장되어 있는 Dictionary에서 가져오는 것으로 파악된다. 따라서 아래와 같은 Session 값을 변경하고 나서 실행계획을 가저오는 방향으로 튜닝 방안을 생각해 보았습니다.
ALTER SESSION SET STATISTICS_LEVEL = ALL;
-- 수행할 쿼리
SELECT * FROM TABLE(dbms_xplan.display_cursor(null,null, 'ALLSTATS LAST'))위와 같은 방식을 통해서 기존의 실행계획을 가져오는 것이 아닌 튜닝한 실행계획과 기존의 실행계획을 비교 할 수 있었다. 다만 해당 쿼리를 비교할 때 실행시간으로 비교하게 된다면 후에 실행된 쿼리가 버퍼캐시에 있는 데이터를 가져오기 때문에 가져오는 Block 수를 비교해야 합니다.
외래키를 주로 사용하지 않는다.
모순건으로 인해 테이블 조회를 했을 때 자식 테이블에 있는 데이터가 부모 테이블에 없는 데이터가 있는 경우가 있었다. 기존에 데이터 전환을 하면서 테이블 제약조건을 설정하기 전에 전환을 해서 생긴 문제라고 생각하였는데 현업을 하면서 각종 테이블을 조회 해본 결과 ERD에서는 종속관계인 테이블 대부분이 외래키 제약조건을 걸지 않는 것을 확인하였다. 데이터 문제가 있어서 설정을 하지 않는 것으로 생각하였는데 Lock 문제와 샤딩 문제로 인해 설정하지 않는 다는 것도 배웠습니다.
프로젝트를 마무리 하면서
처음 프로젝트로 투입된 인원들과 친해질 수 있는 계기가 되면서도 서로 의지할 수 있는 프로젝트였다. 또 한 모르는 파트가 생겼을 때 그 때 마다 잘 설명해 주신 신청팀 PL님에게 너무 감사하다. 프로젝트가 대내외적으로 이슈가 많았지만
신청팀 PL님과 같이 불철주야 노력하시는 분들이 계셔서 프로젝트가 무사히 끝날 수 있었던 것 같다. 😀
