자동화 툴로 채울 수 없는 DB 성능관리 2% 3. 데이터베이스 기반 프로그래밍을 통한 성능개선
지금까지 우리는 데이터베이스 성능관리에 대하여 기존과는 다른 ‘활용’적인 면에 초점을 맞춰 개략적으로 알아보았다.
이번 시간은 단순한 편협한 지식만으로 해결되는 문제가 아닌 데이터베이스 성능에 관해서 필자가 현장에서 컨설팅했던 실전 사례들을 중심으로 데이터베이스 기반의 프로그래밍 사례들을 소개한다.
실제 현장에서 사용되는 프로그램은 크게 온라인 유형과 배치성의 유형으로 나눠지게 된다. 이것은 모든 프로그램의 유형을 대표한다고 하기 보다는 실제 end user(사용자)들이 사용하게 되는 유형으로 개발자들이 프로그래밍을 하는 유형이라고 볼 수 있다.
이렇게 유형을 나누는 이유는 이 유형에 따라 프로그래밍을 하는 방법과 전략이 달라야 하기 때문이다. 실제로 현장에서 많은 성능 문제를 일으키는 이유는 이 두 가지의 단순한(?) 유형의 구분 없이 프로그래밍을 하는 이유가 굉장히 많다.
<그림 1> 온라인 유형과 프로그래밍 방안
<그림 1>에서 보듯이 온라인의 프로그래밍은 업무 분석이 선행되어진 상태에서 프로그래밍 기술적인 부분에 들어갈 때 여러 가지 팩터(factor)들을 고려해서 전략을 짜야 한다. 고려해야 할 팩터들은 다음과 같다.
◆ Real Time Data를 조회해야만 하는가?
◆ 검색조건의 조합에 따른 다양성과 데이터량은 얼마나 되는가?
◆ 배치를 이용할 수는 없는가?
◆ 결과 건수가 적당한가?
◆ 정렬의 기준은 무엇인가?
이와 같은 팩터들에 대해 답을 얻었다면 다음과 같은 순서도에 의해 화면 구성과 프로그래밍 전략을 짜야 한다. 이것은 데이터베이스를 기반으로 하는 프로그래밍을 하기 위함이며, 이런 절차를 제대로 밟은 프로그램은 성능 상 전혀 문제를 일으키지 않고 향후 데이터가 많이 쌓이더라도 시스템의 재개발이 필요없게 된다. 따라서 사용자들의 만족도 또한 충족시킬 수 있게 된다.
<그림 2> 최적의 프로그래밍 방안
화면이 복잡해질수록 프로그래밍하기가 어려워지고 SQL이 어렵게 되므로 당연히 성능이 저하되기 마련이지만 <그림 2>와 같은 순서에 입각하여 정리하다 보면 복잡한 프로그램이라도 프로그래밍을 단순하게 짤 수 있는 능력이 길러지게 된다. 온라인 프로그램은 대게 데이터베이스의 조인방식 중 Nested Loops 조인으로 풀리게 짜게 되는데 이것이 전략을 구성하는데 핵심요소가 된다. Nested Loops 조인이란 조인방식 중 가장 전통적이고 기본적인 방식으로 온라인 프로그래밍을 할 때 반드시 확인해야 할 방식이다.
이번 연재에서는 지면 관계상 데이터베이스의 조인 방식에 대하여 자세히 다루지는 않고 관련 자료를 찾아보거나 기회가 되면 자세히 기술하는 시간을 가져보도록 하겠다.
Nested Loops 조인 방식의 가장 핵심은 드라이빙 팩터를 전략적으로 부여해야 하는 것이 핵심이다. 즉, 인덱스와 Random Access로 풀리게 되는 방식인 만큼 드라이빙 팩터의 건수가 적을수록 성능 상에 문제를 일으키지 않는다. 따라서 화면을 구성할 때 단일 조건 또는 조건의 조합이 드라이빙 팩터의 건수를 적게 하기 위한 전략을 구사하면 되는 것이다. 만약 그렇게 불가능하다면 부분범위처리나 배치에 의한 방식으로 전략을 변경해야 성능 상에 문제가 되지 않는다. 실제 여러 가지 사례들을 통해서 데이터베이스 기반의 프로그래밍 방법에 대해 구체적으로 알아본다.
예 1. 효율적인 인덱스를 이용하지 못하고 있는 프로그램
<화면 1> 회원조회 프로그램
<화면 1>은 어느 ‘봉사자’라는 회원을 조회하는 화면으로 조회 조건에 따라 어떻게 SQL을 구사하는 지를 알아보고자 한다. 간단하게 요약해 보면 다음과 같다.
* ‘주민번호’로 조회하거나 ‘이름’으로 조회가 가능하다.
* ‘주민번호’나 ‘이름’이 ‘=’ 조건으로 드라이빙 팩터가 될 수 있도록 한다. 예) union all로 실행계획 분리
* ‘드라이빙 팩터’의 역할을 할 수 있도록 인덱스 전략을 세운다.
* ‘이름’의 경우 유사한 이름의 데이터가 많은지 확인하여 최소자리를 설정하여 팝업(pop-up)을 이용하여 ‘=’로 드라이빙 될 수 있도록 유도한다.
‘봉사자’ 기본 정보를 조회하는 화면인데 그 조건으로 주민등록번호나 봉사자의 이름이 들어올 수 있다. 해당 테이블의 인덱스 상황에 따라 한 명의 봉사자를 검색하기 위하여 Full Scan을 할 수 있는 부분이다. 따라서 이 부분을 ‘이름’이나 ‘주민번호’가 조건이 들어올 경우로 나누어 SQL을 구성하여 인덱스를 이용할 수 있도록 Access Path를 고정함으로써 효과적인 인덱스 Access를 할 수 있도록 유도한다.
2) 봉사자의 주민번호가 들어올 경우
기존에는 이 검색조건만을 위해 bm_name, bm_jumin의 단일 인덱스가 만들어져 있었으나 이 쿼리는 내부적으로 cent_sigun이라는 상당히 변별력 있는 조건이 항상 같이 들어오게 되므로 단일 인덱스를 drop하고 현재는 cent_code+bm_jumin, cent_code+bm_name 등의 결합 인덱스를 만들었고 이 인덱스를 쓰기 위해서 이와 같이 SQL을 분리하고 힌트를 이용하여 인덱스를 쓸 수 있도록 유도했다.
물론 주민번호 13자리가 전부 조건에 들어오고 bm_jumin이라는 인덱스를 이용하게 되면 성능에는 문제가 없겠지만 기존에는 주민번호와 이름의 자리 수 제약이 전혀 없었고 이름으로만 조회하는 경우 같은 성을 가진 회원(김XX )이 수없이 많기 때문에 화면에 뿌리는 데 어려움이 많았지만 cent_sigun이라는 변별력이 좋은 조건을 함께 결합인덱스를 이용하여 필수 조건화했기 때문에 전혀 문제가 되지 않았다.
예 2. 다양한 검색조건과 많은 양의 결과 수를 뿌릴 경우
<화면 2> 고객정보 조회 프로그램
이 프로그램은 어느 전자회사의 상품판매 고객을 관리하는 프로그램으로 소위 콜센터에서 주로 이용하는 프로그램이다. 즉 고객의 전화가 올 경우 고객을 응대하기 위해 고객 정보를 조회하는 프로그램이다. 고객 정보를 여러 가지로 입력할 수 있게 하여 다양하게 조회를 할 수 있도록 프로그래밍된 온라인 화면이다. 이 화면을 분석한 결과는 다음과 같다.
* ‘다양한 검색조건’ 중 의미가 겹치지 않는지 확인 예) ‘주민번호’와 ‘고객명+주민번호’는 의미상 겹침
* ‘ID’성의 주민번호나 카드번호는 조건으로 적당(드라이빙 팩터, 결과건수)
* ‘고객명+휴대폰’, ‘고객명+자택번호’는 데이터량을 보고 판단하여 최소 자리 수를 정한다.
* ‘결과건수’가 많을 경우 부분범위처리로 유도한다.
* 업무상으로 많은 결과 Row를 뿌릴 것인지 판단한다.
문제점 및 원인
이 프로그램은 고객을 조회하는 화면으로 콜센터 업무의 Intro 업무이다. 조회조건이 다양한데 ‘주민등록번호’와 ‘카드번호’가 입력될 경우는 결과 Set이 많지 않을 것이지만 대부분 업무에서 조회할 것으로 예상하는 ‘고객명+휴대폰’이나 ‘고객명+자택전화번호’ 같은 경우는 조건 값에 따라서 많은 양의 결과 Set이 나올 것으로 예상이 된다.
3-티어 구조에서는 조건 값에 맞는 쿼리가 끝나지 않으면 결과로 나타나지 않고 또한 결과 Set이 많으면 그 결과 Set을 네트워크로 끌어오거나 화면에 뿌릴 때 상당한 시간이 소요되므로 업무에 대단한 지장을 초래할 가능성이 있다.
결과 Set이 많을 경우에 첫 페이지의 경우는 빨리 뜰 수 있으나 ‘NEXT’를 누르면 누를수록 속도 저하가 일어나게 되는 것이다. 기존에 되어 있던 프로그램 방식은 흔히 웹 프로그램에서 많이 볼 수 있는 것인데 조건에 따라 SQL을 조합해서 만드는 (if… else if에 의해 SQL을 조합) 조합형 SQL을 구사하고 있다. 이는 데이터베이스가 유일하게 이해할 수 있는 SQL과 일반 로직 부분을 혼용해서 이용하고 있는 것이다.
이렇게 되면 데이터베이스는 조건에 따라 다양한 SQL을 받아들이게 되고 전체적으로 Access Path가 최적화되지 못하여 조건에 따라 성능 차이가 심해지게 된다. 또한 결과 건수에 따른 부분범위 처리 방식의 프로그래밍 또한 불가능하게 되어 데이터베이스 중심의 프로그래밍이 아닌 절차형의 프로그램이 되는 것이다.
개선방안
‘주민등록번호’나 ‘카드번호’가 입력될 경우는 현재 프로그램 방식으로 이용해도 별 무리가 없어 보이고 ‘고객명+주민등록번호’의 경우는 ‘주민등록번호’ 조건으로 대체 이용 가능하므로 크게 문제가 되지 않을 것으로 보이나 ‘고객명+휴대폰’ 또는 ‘고객명+자택전화번호’ 같은 경우는 다음과 같이 부분범위처리 방식으로 프로그램을 변경해야 한다. ‘고객명’이 들어올 때 동명이인의 경우 휴대폰 번호와 Rowid 값을 이용해서 구분하여 페이징을 한다.
‘부분범위처리’ 방식의 프로그램이란 개념은 간단하나 구현에 있어 고도의 기술을 요하는 것으로 웹 페이징 처리 시 필요한 부분만 데이터베이스에서 읽어 올 수 있게 구현하는 방식을 말한다(지면 관계상 세부 내용은 메일이나 다른 지면을 통해 소개하도록 하겠다). 기존 프로그램을 ‘부분범위처리’ 방식으로 필자가 구현한 내용을 소개한다.
이 방식은 다양한 조건을 인덱스를 이용하여 Access Path를 고정시키고 ‘NEXT’를 누를 경우 해당하는 키 값을 넘겨주어 화면의 개수만큼만 DB를 Access하도록 하는 방식이다. 본 내용을 현재 다 이해하지 못하여도 상관이 없다. 이런 방식이 있다고 하는 것만 알게 된다면 독자 여러분은 그 목적을 다했다고 생각한다.
예 3. 굉장히 다양한 검색조건의 화면의 경우
<화면 3> 조건이 다양한 검색프로그램
이 프로그램은 처음에 소개한 예제의 상세 검색 화면에 해당된다. 즉, ‘회원’의 가입당시의 여러 조건들을 다양하게 검색할 수 있도록 구성한 화면인 것이다.
이런 화면을 구성할 때 개발자들은 데이터베이스 위주의 프로그램이 불가능하다고 생각하고 있고 로직 위주의 프로그램을 구사하면서 개발자 편의의 프로그램을 짜고 있는 것이다. 하지만 이런 복잡한 프로그램일수록 데이터베이스 위주의 즉, SQL 위주의 프로그램을 구사해야 프로그램이 단순해지고 성능상의 위력을 발휘할 수 있으며 데이터의 정확도와 차후 변경사항의 유지보수 등이 좋아지게 된다.
일단 이렇게 복잡하게 구성해야 하는 프로그램은 다음과 같은 단순한 절차를 생각하면 된다.
* ‘Mandatory’조건을 파악하여 ‘Mandatory’조건 중 드라이빙 팩터를 선정한다.
* ‘결과 건수’에 따라 Mandatory 조건 추가 또는 부분범위처리 결정
* 드라이빙 팩터와 필터링 조건을 분리하여 드라이빙 팩터는 Nested Loops의 조건으로 이용하고 나머지 조건들은 필터링하는 조건으로 구분한다.
* 인덱스 전략을 수립한다.
* 드라이빙 팩터와 필터링 팩터를 잘 탈 수 있도록 가능한 One-SQL로 구성한다.
이 부분도 ‘부분범위처리’ 방식으로 구현한 것으로 로직으로 밑줄로 되어 있는 부분이 드라이빙 팩터이고 나머지 조건들은 전부 필터링 팩터에 해당된다. 구사했던 부분을 SQL로 구현하는 것은 쉽지 않지만 ‘생각의 전환’을 하게 되면 그리 어려운 부분도 아니다. 필자는 같이 프로젝트를 했던 개발자들이 프로젝트 종료시점에 놀랄만한 SQL 구사 능력 향상을 가져온 것을 여러 번 경험했다.
예 4. 인덱스 전략의 부재로 성능저하가 있는 경우
<화면 4> 상품조회 프로그램
이 프로그램은 어느 유통회사에서 ‘상품’을 조회하는 간단한 화면으로 회사의 특성상 관리하는 상품의 종류와 수가 많고 대부분의 업무 자체가 이 상품을 관리하는 것이다 보니 이 간단한 프로그램을 제일 많이 사용하면서도 성능 저하가 가장 많이 되고 있는 프로그램이었다. 예를 들어 앞의 화면에서 ‘메이커’와 ‘품목’조건에 둘 중 하나만 들어와도 실행이 가능하다고 해보면, ‘메이커’의 조건에 ‘삼성’이라는 값을 주고 조회를 할 수 있다. 만약 이런 현상이 발생한다면 이 시스템의 문제는 심각해진다. 그런데 이것이 실제 사례이다.
문제점 및 원인
상기 프로그램은 상품조회 화면에서 쓰이는 프로그램으로 상품의 ‘메이커’ 또는 ‘품목’을 조회조건으로 하고 있다. 조건으로 들어온 ‘삼성’이라는 메이커에 해당되는 데이터량은 엄청나다. 즉, ‘삼성의 모든 상품을 조회’하는 결과가 되는 것이다. 따라서 데이터베이스를 엑세스하여 화면에 뿌리는 데는 상당한 시간이 걸리고 있는 것이다.
개선방안
‘메이커’나 ‘품목’ 두 조건을 모두 Madatory 조건으로 변경하고 ‘메이커’ 부분은 데이터베이스에서 ‘=’로 엑세스할 수 있도록 하며 ‘메이커’+ ‘품목’으로 결합 인덱스를 구성하여 결과 Row 수가 최소화되도록 유도하여 빠른 응답시간을 유도한다.
지금까지 필자가 실제로 경험했던 간단한 몇 개의 예제들을 통해 RDBMS에 기반을 두어 성능개선을 한 사례를 살펴 보았다. 데이터베이스 기반의 프로그래밍은 결코 어렵지 않다. 다만 대부분의 개발자들이 접근을 잘못하고 있을 뿐이다. 그리고 앞의 사례들을 현재 100% 설명하기란 지면관계상 불가능한 점을 안타깝게 생각한다. 하지만 이번 연재를 통해 데이터베이스 컨설팅 영역에 대해 공감을 했다면 필자의 목적을 달성한 것이라 생각한다. 자세한 기술적인 문제들은 다음 기회가 되면 자세히 설명하겠다.
이로써 본 연재가 여러 자동화된 툴을 단순히 이용하는 것이 아닌 종합적이고 활용적인 데이터베이스 성능관리 영역의 소개가 되었으면 하는 바람이다. 또한 진정한 데이터베이스 사용 전문가들이 많이 나타나길 기대하는 마음으로 연재를 마친다.
출처 : 마이크로소프트웨어 2005년 4월호
이번 시간은 단순한 편협한 지식만으로 해결되는 문제가 아닌 데이터베이스 성능에 관해서 필자가 현장에서 컨설팅했던 실전 사례들을 중심으로 데이터베이스 기반의 프로그래밍 사례들을 소개한다.
실제 현장에서 사용되는 프로그램은 크게 온라인 유형과 배치성의 유형으로 나눠지게 된다. 이것은 모든 프로그램의 유형을 대표한다고 하기 보다는 실제 end user(사용자)들이 사용하게 되는 유형으로 개발자들이 프로그래밍을 하는 유형이라고 볼 수 있다.
이렇게 유형을 나누는 이유는 이 유형에 따라 프로그래밍을 하는 방법과 전략이 달라야 하기 때문이다. 실제로 현장에서 많은 성능 문제를 일으키는 이유는 이 두 가지의 단순한(?) 유형의 구분 없이 프로그래밍을 하는 이유가 굉장히 많다.
<그림 1> 온라인 유형과 프로그래밍 방안
<그림 1>에서 보듯이 온라인의 프로그래밍은 업무 분석이 선행되어진 상태에서 프로그래밍 기술적인 부분에 들어갈 때 여러 가지 팩터(factor)들을 고려해서 전략을 짜야 한다. 고려해야 할 팩터들은 다음과 같다.
◆ Real Time Data를 조회해야만 하는가?
◆ 검색조건의 조합에 따른 다양성과 데이터량은 얼마나 되는가?
◆ 배치를 이용할 수는 없는가?
◆ 결과 건수가 적당한가?
◆ 정렬의 기준은 무엇인가?
이와 같은 팩터들에 대해 답을 얻었다면 다음과 같은 순서도에 의해 화면 구성과 프로그래밍 전략을 짜야 한다. 이것은 데이터베이스를 기반으로 하는 프로그래밍을 하기 위함이며, 이런 절차를 제대로 밟은 프로그램은 성능 상 전혀 문제를 일으키지 않고 향후 데이터가 많이 쌓이더라도 시스템의 재개발이 필요없게 된다. 따라서 사용자들의 만족도 또한 충족시킬 수 있게 된다.
<그림 2> 최적의 프로그래밍 방안
화면이 복잡해질수록 프로그래밍하기가 어려워지고 SQL이 어렵게 되므로 당연히 성능이 저하되기 마련이지만 <그림 2>와 같은 순서에 입각하여 정리하다 보면 복잡한 프로그램이라도 프로그래밍을 단순하게 짤 수 있는 능력이 길러지게 된다. 온라인 프로그램은 대게 데이터베이스의 조인방식 중 Nested Loops 조인으로 풀리게 짜게 되는데 이것이 전략을 구성하는데 핵심요소가 된다. Nested Loops 조인이란 조인방식 중 가장 전통적이고 기본적인 방식으로 온라인 프로그래밍을 할 때 반드시 확인해야 할 방식이다.
이번 연재에서는 지면 관계상 데이터베이스의 조인 방식에 대하여 자세히 다루지는 않고 관련 자료를 찾아보거나 기회가 되면 자세히 기술하는 시간을 가져보도록 하겠다.
Nested Loops 조인 방식의 가장 핵심은 드라이빙 팩터를 전략적으로 부여해야 하는 것이 핵심이다. 즉, 인덱스와 Random Access로 풀리게 되는 방식인 만큼 드라이빙 팩터의 건수가 적을수록 성능 상에 문제를 일으키지 않는다. 따라서 화면을 구성할 때 단일 조건 또는 조건의 조합이 드라이빙 팩터의 건수를 적게 하기 위한 전략을 구사하면 되는 것이다. 만약 그렇게 불가능하다면 부분범위처리나 배치에 의한 방식으로 전략을 변경해야 성능 상에 문제가 되지 않는다. 실제 여러 가지 사례들을 통해서 데이터베이스 기반의 프로그래밍 방법에 대해 구체적으로 알아본다.
예 1. 효율적인 인덱스를 이용하지 못하고 있는 프로그램
<화면 1> 회원조회 프로그램
<화면 1>은 어느 ‘봉사자’라는 회원을 조회하는 화면으로 조회 조건에 따라 어떻게 SQL을 구사하는 지를 알아보고자 한다. 간단하게 요약해 보면 다음과 같다.
* ‘주민번호’로 조회하거나 ‘이름’으로 조회가 가능하다.
* ‘주민번호’나 ‘이름’이 ‘=’ 조건으로 드라이빙 팩터가 될 수 있도록 한다. 예) union all로 실행계획 분리
* ‘드라이빙 팩터’의 역할을 할 수 있도록 인덱스 전략을 세운다.
* ‘이름’의 경우 유사한 이름의 데이터가 많은지 확인하여 최소자리를 설정하여 팝업(pop-up)을 이용하여 ‘=’로 드라이빙 될 수 있도록 유도한다.
‘봉사자’ 기본 정보를 조회하는 화면인데 그 조건으로 주민등록번호나 봉사자의 이름이 들어올 수 있다. 해당 테이블의 인덱스 상황에 따라 한 명의 봉사자를 검색하기 위하여 Full Scan을 할 수 있는 부분이다. 따라서 이 부분을 ‘이름’이나 ‘주민번호’가 조건이 들어올 경우로 나누어 SQL을 구성하여 인덱스를 이용할 수 있도록 Access Path를 고정함으로써 효과적인 인덱스 Access를 할 수 있도록 유도한다.
기존 프로그램
SELECT /*+ ordered use_nl(b c bg) index(B BONGSAJA_IDX3) */
b.bm_no,bm_regdate,bm_name,bm_jumin,bm_sex,cent_name,
bm_addr1,bm_addr2,bm_tel,bm_mtel,bg_name
FROM bongsaja b, center c,bong_group bg
WHERE b.cent_code = c.cent_code
AND b.bg_no = bg.bg_no (+)
AND B.cent_sigun in (select cent_sigu
from center
where cent_code like decode(:sw, 1, '%', 2, substr(:cent_code,1,5)||'%'))
AND (B.BM_NAME like :name||'%' or B.BM_JUMIN like :jumin||'%')
SELECT /*+ ordered use_nl(b c bg) index(B BONGSAJA_IDX3) */
b.bm_no,bm_regdate,bm_name,bm_jumin,bm_sex,cent_name,
bm_addr1,bm_addr2,bm_tel,bm_mtel,bg_name
FROM bongsaja b, center c,bong_group bg
WHERE b.cent_code = c.cent_code
AND b.bg_no = bg.bg_no (+)
AND B.cent_sigun in (select cent_sigu
from center
where cent_code like decode(:sw, 1, '%', 2, substr(:cent_code,1,5)||'%'))
AND (B.BM_NAME like :name||'%' or B.BM_JUMIN like :jumin||'%')
Tuned-SQL
1) 봉사자 이름이 들어올 경우
SELECT /*+ ordered use_nl(b c bg) index(B BONGSAJA_IDX3) */
b.bm_no,bm_regdate,bm_name,bm_jumin,bm_sex,cent_name,
bm_addr1,bm_addr2,bm_tel,bm_mtel,bg_name
FROM bongsaja b, center c,bong_group bg
WHERE b.cent_code = c.cent_code
AND b.bg_no = bg.bg_no (+)
AND B.cent_sigun in (select cent_sigu
from center
where cent_code like decode(:sw, 1, '%', 2, substr(:cent_code,1,5)||'%'))
AND B.BM_NAME like :name||'%'
1) 봉사자 이름이 들어올 경우
SELECT /*+ ordered use_nl(b c bg) index(B BONGSAJA_IDX3) */
b.bm_no,bm_regdate,bm_name,bm_jumin,bm_sex,cent_name,
bm_addr1,bm_addr2,bm_tel,bm_mtel,bg_name
FROM bongsaja b, center c,bong_group bg
WHERE b.cent_code = c.cent_code
AND b.bg_no = bg.bg_no (+)
AND B.cent_sigun in (select cent_sigu
from center
where cent_code like decode(:sw, 1, '%', 2, substr(:cent_code,1,5)||'%'))
AND B.BM_NAME like :name||'%'
2) 봉사자의 주민번호가 들어올 경우
SELECT /*+ ordered use_nl(b c bg) index(B BONGSAJA_IDX4) */
b.bm_no,bm_regdate,bm_name,bm_jumin,bm_sex,cent_name,
bm_addr1,bm_addr2,bm_tel,bm_mtel,bg_name
FROM bongsaja b, center c,bong_group bg
WHERE b.cent_code = c.cent_code
AND b.bg_no = bg.bg_no (+)
AND B.cent_sigun in (select cent_sigun
from center
where cent_code like decode(:sw, 1, '%', 2, substr(:cent_code,1,5)||'%'))
AND B.BM_JUMIN = :jumin
b.bm_no,bm_regdate,bm_name,bm_jumin,bm_sex,cent_name,
bm_addr1,bm_addr2,bm_tel,bm_mtel,bg_name
FROM bongsaja b, center c,bong_group bg
WHERE b.cent_code = c.cent_code
AND b.bg_no = bg.bg_no (+)
AND B.cent_sigun in (select cent_sigun
from center
where cent_code like decode(:sw, 1, '%', 2, substr(:cent_code,1,5)||'%'))
AND B.BM_JUMIN = :jumin
기존에는 이 검색조건만을 위해 bm_name, bm_jumin의 단일 인덱스가 만들어져 있었으나 이 쿼리는 내부적으로 cent_sigun이라는 상당히 변별력 있는 조건이 항상 같이 들어오게 되므로 단일 인덱스를 drop하고 현재는 cent_code+bm_jumin, cent_code+bm_name 등의 결합 인덱스를 만들었고 이 인덱스를 쓰기 위해서 이와 같이 SQL을 분리하고 힌트를 이용하여 인덱스를 쓸 수 있도록 유도했다.
물론 주민번호 13자리가 전부 조건에 들어오고 bm_jumin이라는 인덱스를 이용하게 되면 성능에는 문제가 없겠지만 기존에는 주민번호와 이름의 자리 수 제약이 전혀 없었고 이름으로만 조회하는 경우 같은 성을 가진 회원(김XX )이 수없이 많기 때문에 화면에 뿌리는 데 어려움이 많았지만 cent_sigun이라는 변별력이 좋은 조건을 함께 결합인덱스를 이용하여 필수 조건화했기 때문에 전혀 문제가 되지 않았다.
예 2. 다양한 검색조건과 많은 양의 결과 수를 뿌릴 경우
<화면 2> 고객정보 조회 프로그램
이 프로그램은 어느 전자회사의 상품판매 고객을 관리하는 프로그램으로 소위 콜센터에서 주로 이용하는 프로그램이다. 즉 고객의 전화가 올 경우 고객을 응대하기 위해 고객 정보를 조회하는 프로그램이다. 고객 정보를 여러 가지로 입력할 수 있게 하여 다양하게 조회를 할 수 있도록 프로그래밍된 온라인 화면이다. 이 화면을 분석한 결과는 다음과 같다.
* ‘다양한 검색조건’ 중 의미가 겹치지 않는지 확인 예) ‘주민번호’와 ‘고객명+주민번호’는 의미상 겹침
* ‘ID’성의 주민번호나 카드번호는 조건으로 적당(드라이빙 팩터, 결과건수)
* ‘고객명+휴대폰’, ‘고객명+자택번호’는 데이터량을 보고 판단하여 최소 자리 수를 정한다.
* ‘결과건수’가 많을 경우 부분범위처리로 유도한다.
* 업무상으로 많은 결과 Row를 뿌릴 것인지 판단한다.
기존 프로그램
If (sa_Help[0][0].equals(“1”))
{
tempIndex = “ /*+ INDEX( RM501M RM501M_IDX02) */ ”;
}
String v_Sql = “ select ” + tempIndex + “ rownum numb,
fn_Hicardno(cust_id) cardno, cust_id, cust_nm,”
v_Sql +=”decode(rsno, ‘XXXXXXXXXXXXX’, ‘’,rsno) rsno, tel_no, cell_tel_no, ”;
v_Sql +=”loc_id1, fnHILocID_FULL(loc_id1) loc_nm, addr1”;
v_Sql +=”from rm501m ”;
if (sa_Help[0][0].equals(“0”)))
{
v_Temp +=” where cust_nm like ‘” +
sa_Help[1][0] + “’||’%’ “;
v_Temp +=” and replace (replace (cell_tel_no,’-‘,’’),’ ‘, ‘’)
like ‘” + sa_Help[1][1]
}
else if (sa_Help[0][0].equals(“1”))
{
v_Temp +=” where cust_nm like ‘” +
sa_Help[2][0] + “’||’%’ “;
v_Temp +=” and replace (replace (cell_tel_no,’-‘,’’),’ ‘, ‘’)
like ‘” + sa_Help[2][1]
}
else if (sa_Help[0][0].equals(“2”)))
{
v_Sql =“ select /*+ ordered INDEX(RM501M_IDX01) */
rownum numb, fn_Hicardno(cust_id) cardno, “
v_Sql +=”decode(rsno, ‘XXXXXXXXXXXXX’, ‘’,rsno) rsno,
tel_no, cell_tel_no, ”;
v_Sql +=”loc_id1, fnHILocID_FULL(loc_id1) loc_nm, addr1”;
v_Sql +=”from rm501m ”;
v_Temp +=”where rsno like ‘” + sa_Help[3][0] + “’||’%’”;
}
else if (sa_Help[0][0].equals(“3”)))
{
v_Temp +=”where cust_nm like ‘” + sa_Help[4][0] + “’||’%’ “;
v_Temp +=”and rsno like ‘“ + sa_Help[4][1] + “’||’%’ “;
}
If (sa_Help[0][0].equals(“1”))
{
tempIndex = “ /*+ INDEX( RM501M RM501M_IDX02) */ ”;
}
String v_Sql = “ select ” + tempIndex + “ rownum numb,
fn_Hicardno(cust_id) cardno, cust_id, cust_nm,”
v_Sql +=”decode(rsno, ‘XXXXXXXXXXXXX’, ‘’,rsno) rsno, tel_no, cell_tel_no, ”;
v_Sql +=”loc_id1, fnHILocID_FULL(loc_id1) loc_nm, addr1”;
v_Sql +=”from rm501m ”;
if (sa_Help[0][0].equals(“0”)))
{
v_Temp +=” where cust_nm like ‘” +
sa_Help[1][0] + “’||’%’ “;
v_Temp +=” and replace (replace (cell_tel_no,’-‘,’’),’ ‘, ‘’)
like ‘” + sa_Help[1][1]
}
else if (sa_Help[0][0].equals(“1”))
{
v_Temp +=” where cust_nm like ‘” +
sa_Help[2][0] + “’||’%’ “;
v_Temp +=” and replace (replace (cell_tel_no,’-‘,’’),’ ‘, ‘’)
like ‘” + sa_Help[2][1]
}
else if (sa_Help[0][0].equals(“2”)))
{
v_Sql =“ select /*+ ordered INDEX(RM501M_IDX01) */
rownum numb, fn_Hicardno(cust_id) cardno, “
v_Sql +=”decode(rsno, ‘XXXXXXXXXXXXX’, ‘’,rsno) rsno,
tel_no, cell_tel_no, ”;
v_Sql +=”loc_id1, fnHILocID_FULL(loc_id1) loc_nm, addr1”;
v_Sql +=”from rm501m ”;
v_Temp +=”where rsno like ‘” + sa_Help[3][0] + “’||’%’”;
}
else if (sa_Help[0][0].equals(“3”)))
{
v_Temp +=”where cust_nm like ‘” + sa_Help[4][0] + “’||’%’ “;
v_Temp +=”and rsno like ‘“ + sa_Help[4][1] + “’||’%’ “;
}
문제점 및 원인
이 프로그램은 고객을 조회하는 화면으로 콜센터 업무의 Intro 업무이다. 조회조건이 다양한데 ‘주민등록번호’와 ‘카드번호’가 입력될 경우는 결과 Set이 많지 않을 것이지만 대부분 업무에서 조회할 것으로 예상하는 ‘고객명+휴대폰’이나 ‘고객명+자택전화번호’ 같은 경우는 조건 값에 따라서 많은 양의 결과 Set이 나올 것으로 예상이 된다.
3-티어 구조에서는 조건 값에 맞는 쿼리가 끝나지 않으면 결과로 나타나지 않고 또한 결과 Set이 많으면 그 결과 Set을 네트워크로 끌어오거나 화면에 뿌릴 때 상당한 시간이 소요되므로 업무에 대단한 지장을 초래할 가능성이 있다.
결과 Set이 많을 경우에 첫 페이지의 경우는 빨리 뜰 수 있으나 ‘NEXT’를 누르면 누를수록 속도 저하가 일어나게 되는 것이다. 기존에 되어 있던 프로그램 방식은 흔히 웹 프로그램에서 많이 볼 수 있는 것인데 조건에 따라 SQL을 조합해서 만드는 (if… else if에 의해 SQL을 조합) 조합형 SQL을 구사하고 있다. 이는 데이터베이스가 유일하게 이해할 수 있는 SQL과 일반 로직 부분을 혼용해서 이용하고 있는 것이다.
이렇게 되면 데이터베이스는 조건에 따라 다양한 SQL을 받아들이게 되고 전체적으로 Access Path가 최적화되지 못하여 조건에 따라 성능 차이가 심해지게 된다. 또한 결과 건수에 따른 부분범위 처리 방식의 프로그래밍 또한 불가능하게 되어 데이터베이스 중심의 프로그래밍이 아닌 절차형의 프로그램이 되는 것이다.
개선방안
‘주민등록번호’나 ‘카드번호’가 입력될 경우는 현재 프로그램 방식으로 이용해도 별 무리가 없어 보이고 ‘고객명+주민등록번호’의 경우는 ‘주민등록번호’ 조건으로 대체 이용 가능하므로 크게 문제가 되지 않을 것으로 보이나 ‘고객명+휴대폰’ 또는 ‘고객명+자택전화번호’ 같은 경우는 다음과 같이 부분범위처리 방식으로 프로그램을 변경해야 한다. ‘고객명’이 들어올 때 동명이인의 경우 휴대폰 번호와 Rowid 값을 이용해서 구분하여 페이징을 한다.
‘부분범위처리’ 방식의 프로그램이란 개념은 간단하나 구현에 있어 고도의 기술을 요하는 것으로 웹 페이징 처리 시 필요한 부분만 데이터베이스에서 읽어 올 수 있게 구현하는 방식을 말한다(지면 관계상 세부 내용은 메일이나 다른 지면을 통해 소개하도록 하겠다). 기존 프로그램을 ‘부분범위처리’ 방식으로 필자가 구현한 내용을 소개한다.
1) 고객명+휴대폰
select rnum, row_id, cardno, cust_id, cust_nm, rsno,
tel_no, cell_tel_no, loc_id1, loc_nm, addr1
from ( select /*+ driving_site(a) index(a rm501m_idx04) */
rownum rnum, rowidtochar(rowid) row_id,
(select mbrcard_no
from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm, decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'FIRST'
and a.cust_nm = :cust_nm and a.cell_tel_no like :cell_tell_no1||'%'
and rownum <= 51
)
select rnum, row_id, cardno, cust_id, cust_nm, rsno,
tel_no, cell_tel_no, loc_id1, loc_nm, addr1
from (select /*+ driving_site(a) index(a rm501m_idx04) */
rownum rnum, rowidtochar(rowid) row_id,
(select mbrcard_no from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm, decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'NEXT'
and a.cust_nm = :cust_nm
and (a.cell_tel_no > :cell_tell_no or (a.cell_tel_no =
:cell_tell_no and a.rowid >= chartorowid(:row_id)))
and a.cell_tel_no like :cell_tell_no1||'%'
and rownum <= 51
union all
select /*+ driving_site(a) index_desc (a rm501m_idx04) */
(52 - rownum) rnum, rowidtochar(rowid) row_id,
(select mbrcard_no
from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm, decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'PREV'
and a.cust_nm = :cust_nm
and (a.cell_tel_no < :cell_tell_no or (a.cell_tel_no =
:cell_tell_no and a.rowid <= chartorowid(:row_id)))
and a.cell_tel_no like :cell_tell_no1||'%'
and rownum <= 51
)
order by rnum
select rnum, row_id, cardno, cust_id, cust_nm, rsno,
tel_no, cell_tel_no, loc_id1, loc_nm, addr1
from ( select /*+ driving_site(a) index(a rm501m_idx04) */
rownum rnum, rowidtochar(rowid) row_id,
(select mbrcard_no
from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm, decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'FIRST'
and a.cust_nm = :cust_nm and a.cell_tel_no like :cell_tell_no1||'%'
and rownum <= 51
)
select rnum, row_id, cardno, cust_id, cust_nm, rsno,
tel_no, cell_tel_no, loc_id1, loc_nm, addr1
from (select /*+ driving_site(a) index(a rm501m_idx04) */
rownum rnum, rowidtochar(rowid) row_id,
(select mbrcard_no from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm, decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'NEXT'
and a.cust_nm = :cust_nm
and (a.cell_tel_no > :cell_tell_no or (a.cell_tel_no =
:cell_tell_no and a.rowid >= chartorowid(:row_id)))
and a.cell_tel_no like :cell_tell_no1||'%'
and rownum <= 51
union all
select /*+ driving_site(a) index_desc (a rm501m_idx04) */
(52 - rownum) rnum, rowidtochar(rowid) row_id,
(select mbrcard_no
from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm, decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'PREV'
and a.cust_nm = :cust_nm
and (a.cell_tel_no < :cell_tell_no or (a.cell_tel_no =
:cell_tell_no and a.rowid <= chartorowid(:row_id)))
and a.cell_tel_no like :cell_tell_no1||'%'
and rownum <= 51
)
order by rnum
2) 고객명+자택전화번호
select rnum, row_id, cardno, cust_id, cust_nm, rsno,
tel_no, cell_tel_no, loc_id1, loc_nm, addr1
from ( select /*+ driving_site(a) index(a rm501m_idx04) */
rownum rnum, rowidtochar(rowid) row_id,
(select mbrcard_no
from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm, decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'FIRST'
and a.cust_nm = :cust_nm and a.tel_no like :cell_tell_no1||'%'
and rownum <= 51
)
select rnum, row_id, cardno, cust_id, cust_nm, rsno,
tel_no, cell_tel_no, loc_id1, loc_nm, addr1
from (select /*+ driving_site(a) index(a rm501m_idx04) */
rownum rnum, rowidtochar(rowid) row_id,
(select mbrcard_no from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno, cust_id, cust_nm,
decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno, tel_no, cell_tel_no, loc_id1,
(select full_nm from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'NEXT'
and a.cust_nm = :cust_nm
and (a.tel_no > :tell_no or (a.tel_no = :tell_no and a.rowid >=
chartorowid(:row_id)))
and a.tel_no like :tell_no1||'%'
and rownum <= 51
union all
select /*+ driving_site(a) index_desc (a rm501m_idx04) */
(52 - rownum) rnum, rowidtochar(rowid) row_id,
(select mbrcard_no
from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm,
decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'PREV'
and a.cust_nm = :cust_nm
and (a.tel_no < :tell_no or (a.tel_no = :tell_no and a.rowid <=
chartorowid(:row_id)))
and a.tel_no like :tell_no1||'%'
and rownum <= 51
)
order by rnum
select rnum, row_id, cardno, cust_id, cust_nm, rsno,
tel_no, cell_tel_no, loc_id1, loc_nm, addr1
from ( select /*+ driving_site(a) index(a rm501m_idx04) */
rownum rnum, rowidtochar(rowid) row_id,
(select mbrcard_no
from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm, decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'FIRST'
and a.cust_nm = :cust_nm and a.tel_no like :cell_tell_no1||'%'
and rownum <= 51
)
select rnum, row_id, cardno, cust_id, cust_nm, rsno,
tel_no, cell_tel_no, loc_id1, loc_nm, addr1
from (select /*+ driving_site(a) index(a rm501m_idx04) */
rownum rnum, rowidtochar(rowid) row_id,
(select mbrcard_no from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno, cust_id, cust_nm,
decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno, tel_no, cell_tel_no, loc_id1,
(select full_nm from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'NEXT'
and a.cust_nm = :cust_nm
and (a.tel_no > :tell_no or (a.tel_no = :tell_no and a.rowid >=
chartorowid(:row_id)))
and a.tel_no like :tell_no1||'%'
and rownum <= 51
union all
select /*+ driving_site(a) index_desc (a rm501m_idx04) */
(52 - rownum) rnum, rowidtochar(rowid) row_id,
(select mbrcard_no
from rm507T b
where b.cls_dt = '99991231'
and b.cust_id = a.cust_id) cardno,
cust_id, cust_nm,
decode(rsno,'XXXXXXXXXXXXX','',rsno) rsno,
tel_no, cell_tel_no, loc_id1,
(select full_nm
from cm501c c
where c.loc_id = a.loc_id1) loc_nm, addr1
from rm501m a
where upper(:sw) = 'PREV'
and a.cust_nm = :cust_nm
and (a.tel_no < :tell_no or (a.tel_no = :tell_no and a.rowid <=
chartorowid(:row_id)))
and a.tel_no like :tell_no1||'%'
and rownum <= 51
)
order by rnum
이 방식은 다양한 조건을 인덱스를 이용하여 Access Path를 고정시키고 ‘NEXT’를 누를 경우 해당하는 키 값을 넘겨주어 화면의 개수만큼만 DB를 Access하도록 하는 방식이다. 본 내용을 현재 다 이해하지 못하여도 상관이 없다. 이런 방식이 있다고 하는 것만 알게 된다면 독자 여러분은 그 목적을 다했다고 생각한다.
예 3. 굉장히 다양한 검색조건의 화면의 경우
<화면 3> 조건이 다양한 검색프로그램
이 프로그램은 처음에 소개한 예제의 상세 검색 화면에 해당된다. 즉, ‘회원’의 가입당시의 여러 조건들을 다양하게 검색할 수 있도록 구성한 화면인 것이다.
이런 화면을 구성할 때 개발자들은 데이터베이스 위주의 프로그램이 불가능하다고 생각하고 있고 로직 위주의 프로그램을 구사하면서 개발자 편의의 프로그램을 짜고 있는 것이다. 하지만 이런 복잡한 프로그램일수록 데이터베이스 위주의 즉, SQL 위주의 프로그램을 구사해야 프로그램이 단순해지고 성능상의 위력을 발휘할 수 있으며 데이터의 정확도와 차후 변경사항의 유지보수 등이 좋아지게 된다.
일단 이렇게 복잡하게 구성해야 하는 프로그램은 다음과 같은 단순한 절차를 생각하면 된다.
* ‘Mandatory’조건을 파악하여 ‘Mandatory’조건 중 드라이빙 팩터를 선정한다.
* ‘결과 건수’에 따라 Mandatory 조건 추가 또는 부분범위처리 결정
* 드라이빙 팩터와 필터링 조건을 분리하여 드라이빙 팩터는 Nested Loops의 조건으로 이용하고 나머지 조건들은 필터링하는 조건으로 구분한다.
* 인덱스 전략을 수립한다.
* 드라이빙 팩터와 필터링 팩터를 잘 탈 수 있도록 가능한 One-SQL로 구성한다.
Tuned-SQL
select /*+ ordered use_nl(b a c) index(a BONGSAJA_IDX3)
index(c ACTION_BUN_PK) index(f center_uk)*/
rownum rnum, bm_regdate, bm_name, to_number(to_char(sysdate, 'yyyy')) -
to_number((case when substr(a.bm_jumin,7,1) in ('1','2') then '19'
when substr(a.bm_jumin,7,1) in ('3','4') then
'20' end)||substr(a.bm_jumin,1,2)) age,
bm_addr1, bm_addr2, bm_tel,
decode(a.actb_code, null, null,substr(c.actb_desc,1,2)
||'-'||d.actp_desc||'-'||decode(instr(e.actw_desc,
'('), 0, e.actw_desc, substr(e.actw_desc, 1,
instr(e.actw_desc, '(')-1))) action,
f.cent_name, bm_jumin, bm_school, job_code, bm_mtel, bm_email,
g.bg_name, a.bm_actday, substr(a.bm_facttime,1,2)||':'||
substr(a.bm_facttime,3,2)||'~'
||substr(a.bm_tacttime,1,2)||':'||substr(a.bm_tacttime,3,2) act_time,
h.KYUNG_DESC, a.BM_JONGKYO, a.bm_birthday, a.BM_WEDINGDAY,
a.birth_chk, a.bm_marry, a.bm_jumin jumin
from bongsaja a,
action_bun c,
action_prog d,
action_work e,
center f,
bong_group g,
kyungro h
where a.cent_sigun = :cent_sigun
and (a.bm_name > :start_name or (a.bm_name = :start_name
and a.bm_jumin >= :start_jumin))
and a.bm_state in (decode(:state,'1','1','5','1'),
decode(:state,'2','2','5','2'),
decode(:state,'3','3','5','3'),
decode(:state,'4','4','5','4'),
decode(:state,'5','5') )
and a.bm_regdate between :from_date and :to_date
and a.bm_sex like decode(:sex, '남', '1', '여', '2', '%')
and a.bm_school like :school||'%'
and a.job_code like :job||'%'
and a.group_check like :grp||'%'
and ((:insu_chk = '1' and a.insu_chk = '1'
and nvl(:insu_fdate, '00000000') <= insu_tdate
and insu_fdate <= nvl(:insu_tdate, '99999999'))
or (:insu_chk = '2' and a.insu_chk is null)
or (:insu_chk = '3' and a.insu_chk like '%')
)
……………………………
and rownum <= 5
select /*+ ordered use_nl(b a c) index(a BONGSAJA_IDX3)
index(c ACTION_BUN_PK) index(f center_uk)*/
rownum rnum, bm_regdate, bm_name, to_number(to_char(sysdate, 'yyyy')) -
to_number((case when substr(a.bm_jumin,7,1) in ('1','2') then '19'
when substr(a.bm_jumin,7,1) in ('3','4') then
'20' end)||substr(a.bm_jumin,1,2)) age,
bm_addr1, bm_addr2, bm_tel,
decode(a.actb_code, null, null,substr(c.actb_desc,1,2)
||'-'||d.actp_desc||'-'||decode(instr(e.actw_desc,
'('), 0, e.actw_desc, substr(e.actw_desc, 1,
instr(e.actw_desc, '(')-1))) action,
f.cent_name, bm_jumin, bm_school, job_code, bm_mtel, bm_email,
g.bg_name, a.bm_actday, substr(a.bm_facttime,1,2)||':'||
substr(a.bm_facttime,3,2)||'~'
||substr(a.bm_tacttime,1,2)||':'||substr(a.bm_tacttime,3,2) act_time,
h.KYUNG_DESC, a.BM_JONGKYO, a.bm_birthday, a.BM_WEDINGDAY,
a.birth_chk, a.bm_marry, a.bm_jumin jumin
from bongsaja a,
action_bun c,
action_prog d,
action_work e,
center f,
bong_group g,
kyungro h
where a.cent_sigun = :cent_sigun
and (a.bm_name > :start_name or (a.bm_name = :start_name
and a.bm_jumin >= :start_jumin))
and a.bm_state in (decode(:state,'1','1','5','1'),
decode(:state,'2','2','5','2'),
decode(:state,'3','3','5','3'),
decode(:state,'4','4','5','4'),
decode(:state,'5','5') )
and a.bm_regdate between :from_date and :to_date
and a.bm_sex like decode(:sex, '남', '1', '여', '2', '%')
and a.bm_school like :school||'%'
and a.job_code like :job||'%'
and a.group_check like :grp||'%'
and ((:insu_chk = '1' and a.insu_chk = '1'
and nvl(:insu_fdate, '00000000') <= insu_tdate
and insu_fdate <= nvl(:insu_tdate, '99999999'))
or (:insu_chk = '2' and a.insu_chk is null)
or (:insu_chk = '3' and a.insu_chk like '%')
)
……………………………
and rownum <= 5
이 부분도 ‘부분범위처리’ 방식으로 구현한 것으로 로직으로 밑줄로 되어 있는 부분이 드라이빙 팩터이고 나머지 조건들은 전부 필터링 팩터에 해당된다. 구사했던 부분을 SQL로 구현하는 것은 쉽지 않지만 ‘생각의 전환’을 하게 되면 그리 어려운 부분도 아니다. 필자는 같이 프로젝트를 했던 개발자들이 프로젝트 종료시점에 놀랄만한 SQL 구사 능력 향상을 가져온 것을 여러 번 경험했다.
예 4. 인덱스 전략의 부재로 성능저하가 있는 경우
<화면 4> 상품조회 프로그램
이 프로그램은 어느 유통회사에서 ‘상품’을 조회하는 간단한 화면으로 회사의 특성상 관리하는 상품의 종류와 수가 많고 대부분의 업무 자체가 이 상품을 관리하는 것이다 보니 이 간단한 프로그램을 제일 많이 사용하면서도 성능 저하가 가장 많이 되고 있는 프로그램이었다. 예를 들어 앞의 화면에서 ‘메이커’와 ‘품목’조건에 둘 중 하나만 들어와도 실행이 가능하다고 해보면, ‘메이커’의 조건에 ‘삼성’이라는 값을 주고 조회를 할 수 있다. 만약 이런 현상이 발생한다면 이 시스템의 문제는 심각해진다. 그런데 이것이 실제 사례이다.
기존 프로그램
SELECT COUNT(*) total_cnt
FROM (SELECT C.MODELCD MODEL_CODE, 'Y' IMG_FLAG
FROM HI_SECT A,
HI_SECT B,
HI_PRDITM C,
HI_PRC E
WHERE A.SECTID = B.PRSTID
AND A.LBL = 2
AND B.SECTID = C.SECTPRSTID
AND B.LBL = 3
AND C.ACCEPTFLAG = 'Y'
AND C.SALEFLAG = 'Y'
AND C.PRDITMID = E.PRDITMID
AND E.NORMAL_PRC > 0
AND A.DBSTS = 'A'
AND B.DBSTS = 'A'
AND C.DBSTS = 'A'
AND E.DBSTS = 'A'
AND C.MFR LIKE '삼성%'
UNION
SELECT a.MDLCD MODEL_CODE, 'N' IMG_FLAG
FROM SC011M a, SC013M b, SC010C c, CM101C d
WHERE b.MDLCD = a.MDLCD
AND c.PRDCLS_CD = substr(a.PRDCLS, 1, 2)
AND d.VEND_NO = a.MAKR_CD
AND a.DATA_REGDT >= to_char(sysdate - 30, 'YYYYMMDDHH24MISS')
AND b.AVCLDT = '99991231'
AND d.VEND_NM like '삼성%'
)
SELECT COUNT(*) total_cnt
FROM (SELECT C.MODELCD MODEL_CODE, 'Y' IMG_FLAG
FROM HI_SECT A,
HI_SECT B,
HI_PRDITM C,
HI_PRC E
WHERE A.SECTID = B.PRSTID
AND A.LBL = 2
AND B.SECTID = C.SECTPRSTID
AND B.LBL = 3
AND C.ACCEPTFLAG = 'Y'
AND C.SALEFLAG = 'Y'
AND C.PRDITMID = E.PRDITMID
AND E.NORMAL_PRC > 0
AND A.DBSTS = 'A'
AND B.DBSTS = 'A'
AND C.DBSTS = 'A'
AND E.DBSTS = 'A'
AND C.MFR LIKE '삼성%'
UNION
SELECT a.MDLCD MODEL_CODE, 'N' IMG_FLAG
FROM SC011M a, SC013M b, SC010C c, CM101C d
WHERE b.MDLCD = a.MDLCD
AND c.PRDCLS_CD = substr(a.PRDCLS, 1, 2)
AND d.VEND_NO = a.MAKR_CD
AND a.DATA_REGDT >= to_char(sysdate - 30, 'YYYYMMDDHH24MISS')
AND b.AVCLDT = '99991231'
AND d.VEND_NM like '삼성%'
)
문제점 및 원인
상기 프로그램은 상품조회 화면에서 쓰이는 프로그램으로 상품의 ‘메이커’ 또는 ‘품목’을 조회조건으로 하고 있다. 조건으로 들어온 ‘삼성’이라는 메이커에 해당되는 데이터량은 엄청나다. 즉, ‘삼성의 모든 상품을 조회’하는 결과가 되는 것이다. 따라서 데이터베이스를 엑세스하여 화면에 뿌리는 데는 상당한 시간이 걸리고 있는 것이다.
개선방안
‘메이커’나 ‘품목’ 두 조건을 모두 Madatory 조건으로 변경하고 ‘메이커’ 부분은 데이터베이스에서 ‘=’로 엑세스할 수 있도록 하며 ‘메이커’+ ‘품목’으로 결합 인덱스를 구성하여 결과 Row 수가 최소화되도록 유도하여 빠른 응답시간을 유도한다.
SELECT COUNT(*) total_cnt
FROM ( SELECT /*+ ordered use_nl(c a b e) */
C.MODELCD ODEL_CODE, 'Y' IMG_FLAG
FROM HI_PRDITM C,
HI_SECT A,
HI_SECT B,
HI_PRC E
WHERE A.SECTID = B.PRSTID
AND A.LBL = 2
AND B.SECTID = C.SECTPRSTID
AND B.LBL = 3
AND C.ACCEPTFLAG = 'Y'
AND C.SALEFLAG = 'Y'
AND C.PRDITMID = E.PRDITMID
AND E.NORMAL_PRC > 0
AND A.DBSTS = 'A'
AND B.DBSTS = 'A'
AND C.DBSTS = 'A'
AND E.DBSTS = 'A'
AND C.CREDATE >= TO_CHAR(SYSDATE-30, 'YYYYMMDDHH24MISS')
AND C.MFR = '삼성'
AND A.NAME LIKE '세탁기%'
UNION
SELECT /*+ ordered use_nl(a b c d) driving_site(a) */
a.MDLCD MODEL_CODE, 'N' IMG_FLAG
FROM SC011M a, SC013M b, SC010C c, CM101C d
WHERE b.MDLCD = a.MDLCD
AND c.PRDCLS_CD = substr(a.PRDCLS, 1, 2)
AND d.VEND_NO = a.MAKR_CD
AND a.DATA_REGDT >= to_char(sysdate - 30, 'YYYYMMDDHH24MISS')
AND b.AVCLDT = '99991231'
AND d.VEND_NM like '삼성%'
AND c.PRDCLS_KORNM like '세탁기%'
)
FROM ( SELECT /*+ ordered use_nl(c a b e) */
C.MODELCD ODEL_CODE, 'Y' IMG_FLAG
FROM HI_PRDITM C,
HI_SECT A,
HI_SECT B,
HI_PRC E
WHERE A.SECTID = B.PRSTID
AND A.LBL = 2
AND B.SECTID = C.SECTPRSTID
AND B.LBL = 3
AND C.ACCEPTFLAG = 'Y'
AND C.SALEFLAG = 'Y'
AND C.PRDITMID = E.PRDITMID
AND E.NORMAL_PRC > 0
AND A.DBSTS = 'A'
AND B.DBSTS = 'A'
AND C.DBSTS = 'A'
AND E.DBSTS = 'A'
AND C.CREDATE >= TO_CHAR(SYSDATE-30, 'YYYYMMDDHH24MISS')
AND C.MFR = '삼성'
AND A.NAME LIKE '세탁기%'
UNION
SELECT /*+ ordered use_nl(a b c d) driving_site(a) */
a.MDLCD MODEL_CODE, 'N' IMG_FLAG
FROM SC011M a, SC013M b, SC010C c, CM101C d
WHERE b.MDLCD = a.MDLCD
AND c.PRDCLS_CD = substr(a.PRDCLS, 1, 2)
AND d.VEND_NO = a.MAKR_CD
AND a.DATA_REGDT >= to_char(sysdate - 30, 'YYYYMMDDHH24MISS')
AND b.AVCLDT = '99991231'
AND d.VEND_NM like '삼성%'
AND c.PRDCLS_KORNM like '세탁기%'
)
지금까지 필자가 실제로 경험했던 간단한 몇 개의 예제들을 통해 RDBMS에 기반을 두어 성능개선을 한 사례를 살펴 보았다. 데이터베이스 기반의 프로그래밍은 결코 어렵지 않다. 다만 대부분의 개발자들이 접근을 잘못하고 있을 뿐이다. 그리고 앞의 사례들을 현재 100% 설명하기란 지면관계상 불가능한 점을 안타깝게 생각한다. 하지만 이번 연재를 통해 데이터베이스 컨설팅 영역에 대해 공감을 했다면 필자의 목적을 달성한 것이라 생각한다. 자세한 기술적인 문제들은 다음 기회가 되면 자세히 설명하겠다.
이로써 본 연재가 여러 자동화된 툴을 단순히 이용하는 것이 아닌 종합적이고 활용적인 데이터베이스 성능관리 영역의 소개가 되었으면 하는 바람이다. 또한 진정한 데이터베이스 사용 전문가들이 많이 나타나길 기대하는 마음으로 연재를 마친다.
출처 : 마이크로소프트웨어 2005년 4월호