Science of Database

▶ 더미 테이블을 사용해서 장애를 만나는 경우

▶ 더미 테이블을 사용하는 이유

▶ 더미 테이블을 사용하지 않는 방법

포장마차에서 지인에게 재미있는 이야기를 들었다. 물론 공장 이야기 이다. 나는 이야기를 재미있게 들었지만, 지인의 입장에서는 머리가 쭈뼛쭈뼛 서는 심각한 일이었다. 사건은 2011년 겨울에 시작된다.

2011년 1월 1일 이른 아침, 갑자기 잘 돌아가던 시스템에 몇몇 프로그램들이 작동하지 않는 장애를 만났다. Y2K 버그도 아니고 2011년 1월 1일에 장애라니? 서버와 네트워크 그리고 Database는 정상이므로 관심의 화살은 개발팀으로 집중되었다. 개발팀에서 장애 프로그램을 조사해보니 지난 한 달간 프로그램 수정이 없다고 하였다. 결국 모든 것이 정상인데 프로그램만 돌아가지 않는 상황이다. 귀신이 곡할 노릇이 아닌가? 빨리 정상적인 서비스를 해야 하므로 1분, 1초가 아쉬운 시점이었다. 모두들 땀을 흘리며 원인을 찾고 있었다. 프로그램 담당자는 장애를 일으킨 사람을 찾으면 죽여버리겠다고 소리쳤다.

여러분은 이런 장애에서 안전한가?

다행히 오래 걸리지 않고 원인을 찾았다. 돌아가지 않는 프로그램들의 공통점은 더미테이블을 사용한다는 것이었다. 즉 Copy_ymd를 사용한 것이다. 그 테이블을 조사해보니 일자가 2010년 까지만 들어가 있었다. 그래서 2011년이 되자마자 장애가 발생한 것이었다. 다시 말해, Copy_ymd 테이블에 2011년 데이터가 없으므로, 이 테이블과 조인하면 한 건도 나오지 않는 것이다. 생각해보니, 모든 시스템에 이런 일이 발생할 수 있다. 이야기를 듣는 필자의 간담이 갑자기 서늘해진다.

시스템을 구축한 업체에게 항의하려고 문서를 찾아보니 2001년에 Open한 시스템으로 2001년 기준으로 미래의 일자를 10년치 넣어 놓았다. 소프트웨어의 라이프 사이클을 고려한다면, 10년이면 충분하다고 생각했을 것이다. 하지만 운이 없게도 차세대 프로젝트를 하지 않고 10년간 유지보수를 하면서 사용한 것이다. 그리고 인수인계서에 2011년이 되기 전에 몇 년치의 데이터를 더 넣어놓으라고 명시되어 있었다. 시스템을 구축한 업체에게 항의할 수 도 없는 일이었다. 인수인계서를 보는 사람이 한 명이라도 있었을까?

왜 더미 테이블을 사용할까?

데이터베이스에 관심이 있는 개발자라면 Copy_ymd, Copy_ym, Copy_y, Copy_t 등 네 개의 더미테이블을 알 것이다. 많은 시스템에 이런 더미 테이블들이 있다. 과거에는 이런 테이블들을 사용해야만 했다. 하지만 2011년의 시점에서 새로운 프로젝트를 할 때 이런 테이블들이 필요할까? 필요한지 아닌지를 알려면 먼저 더미테이블의 용도를 알아야 한다. 이 테이블들의 용도 중에서 대표적인 것은 아래와 같이 세 가지로 볼 수 있다.

1. Copy: 같은 집합을 여러 번 복제하여 원하는 결과집합을 구한다.

2. 데이터 체크: 일자의 경우 입력된 값이 올바른지 확인한다. 예를 들면, 2월 30일은 잘못된 일자이다.

3. 인덱스의 효율적 사용: 인덱스의 첫 번째 컬럼 혹은 중간 컬럼이 Where 조건에 사용되지 않을 때 더미 테이블을 이용하여 IN으로 공급해주면 인덱스를 효율적으로 사용할 수 있다.

물론, 다른 용도로 더미테이블을 사용할 수 도 있지만, 대부분은 위의 세가지 경우 때문에 더미테이블이 필요하다. 가끔 기준일자를 관리하는 테이블을 볼 수 있는데, 이것은 더미테이블이 아니라 business에 필요한 것이다. 더미테이블은 업무적인 것이 아니라, 성능적인 관점, 혹은 관리적인 목적으로 사용되는 것이다. 업무적인 데이터가 없으므로 차세대 시스템을 구축할 때 더미 테이블은 분석 대상에서 빠져도 된다. 이런 이유 때문에 모델러들도 더미테이블을 중요하게 생각하지 않는다.

더미 테이블의 단점

위의 세 가지를 더미 테이블을 사용하지 않고 처리할 수 있다면 굳이 사용할 필요는 없다. 왜냐하면 아래와 같은 단점이 있기 때문이다.

첫 번째, 더미 테이블이라고 해도 시스템 속성을 추가해야만 한다. 시스템 속성이란 입력자, 입력일시, 수정자, 수정일시 등을 의미한다. 모든 테이블에 이런 컬럼들이 4개 ~ 6개 정도 존재한다. 많은 기업들이 메타시스템을 사용하고 있다. 메타시스템에 테이블에 시스템 속성이 없으면 등록할 수가 없는 경우가 많다. 심지어 자동으로 시스템속성을 추가하는 메타시스템도 있다.

그런데 더미테이블은 튜닝의 목적이 있으므로 매우 가벼워야 한다. 생각해보라. Copy_t에 존재하는 숫자컬럼의 length는 3 byte에 불과한데 시스템 속성 네 개가 48 byte를 차지한다. 3 byte를 위해서 건건이 48 byte를 낭비해야 한다. 테이블이 무거워 질 수 밖에 없다. 더미 테이블은 메타시스템으로 관리하지 말고 엑셀로 관리하면 된다고? 왜 추가적인 관리를 해야만 하는가?

두 번째, 누가 더미 테이블을 중요하게 생각하는가? 더미 테이블을 인수인계 시 중요항목으로 관리되고 있는가? 2011년이 가까이 다가와도, Copy_ymd에 데이터를 넣어줄 생각을 하는 사람은 아무도 없었다. 왜냐하면 10년간 담당자가 세 번이나 바뀌었고, 더미테이블은 인수인계 시 중요관심사가 아니었기 때문이다. 결국 더미테이블을 신경 쓰는 사람은 아무도 없을 수 있다. 시스템은 이렇게 중요 테이블이 아니더라도 조그만 블랙홀이 생기면 장애를 맞는다. 이런 일이 발생할 수 밖에 없는 걸까?

세 번째, 관리해야 할 DB 서버가 많다면 위험이 증가한다. DB 팀이 관리하는 DB가 30개라고 가정하자. 지금 30개의 DB에 대해서 더미테이블을 관리하고 있는가? Copy_ymd에 추가적인 데이터를 insert 해야 하는 시기를 알고 있는가? 관리하고 있지 않다면 장애를 맞을 가능성이 높다. 그렇다면, 신경 쓰지 않아도 되도록, 시간이 되면 자동으로 insert되는 프로그램을 고려해 보아야 하는가? 아니면 시스템마다 더미테이블 들을 뒤져서 안전하게 100년치를 넣을 것인가? 왜 그래야 하는가? 아예 더미테이블을 사용하지 않으면 될 것을

지금은 운영 중이기 때문에 SQL을 바꾸는 것이 어렵다고 하더라도, 차세대 시스템을 구축할 때는 테이블을 관리할 필요도 없고, 장애도 일으키지 않는 방법이 무엇인지 고려하기 바란다. 방법은 얼마든지 있다. 이미 똑똑한 개발자들은 아래의 방법을 사용하고 하고 있다.

1. Copy

Copy_t 대신에 Rollup, Cube, Grouping Sets를 활용하면 원하는 집합을 만들 수 있다. 사용방법은 해당 글을 참고하라. 물론 내부적으로 쿼리변환이 발생되어 UNION ALL로 풀릴 수도 있으므로 성능이 저하되는지 실행계획의 확인은 필요하다. 이런 경우에도 COPY_T는 필요 없으며 DUAL + CONNECT BY LEVEL을 사용하면 된다. 또한 LEAD/LAG를 사용한다면 복제하지 않고도 전/후의 데이터를 비교할 수 있다.

2. 데이터 체크

데이터를 Insert 하기 전에 일자 컬럼을 체크하려고, DBMS Call을 해야만 하나? 다시 말해, 무슨 이유 때문에 DB에 불필요한 부하를 주어야 하는가? 비슷한 노력을 들이고도 DBMS Call을 하지 않을 수 있다. 화면 단에서 Java Script로 처리하던지, 아니면 Constraint를 걸면 Insert할 때에 자동으로 체크 되므로 별도의 DBMS Call은 필요 없다. Constraint에 대해서는 관련 글을 참조하라.

3. 인덱스의 효율적 사용

INDEX SKIP SCAN 기능이 추가되었기 때문에 IN 서브쿼리를 사용해야 되는 경우는 많이 한정 되었다. 또한 IN 서브쿼리를 사용한다고 하더라도 Copy_t, Copy_ymd 대신에 Dual + Connect By를 사용하면, Pseudo 컬럼인 Level을 사용할 수 있다. 물론 주의사항은 있다. 해당 글을 참조하라.

3번에 대해서 어느 개발자가 다음과 같이 질문한다.

이제부터 두 가지 질문에 대해 대답해보자. 먼저 Sales 테이블에 인덱스를 하나 만들고 Copy_ymd를 만들자.

▶적절한 인덱스가 없을 때 Sort의 부하를 줄이는 방법

▶Pagination에서 Sort의 부하 줄이기

▶주의사항

Order by 절에 의한 Sort의 부하는 성능에 치명적이다. Block I/O의 최소화는 분명 튜닝의 핵심이다. 하지만, Block I/O를 최소화 해도 Sort의 부하가 심하다면 결코 만족스런 성능을 내지 못한다. 특히 “페이징 처리용 SQL에는 Sort를 대신할 수 있는 인덱스가 있어야 성능을 확보할 수 있다”고 많은 튜너들이 주장한다. 맞는 말이다. 그렇게만 된다면 Sort가 전혀 발생하지 않을 테니까. 하지만, 다음과 같은 어려움도 있다.

인덱스 최적화가 힘든 이유
첫 번째, 인덱스를 모든 조회화면의 기준에 맞게 만들려면 테이블마다 많은 수의 인덱스가 필요할 것이다. 두 번째, 운영중인 환경에서 인덱스를 생성 혹은 변경하기는 매우 어렵다. 따라서, 인덱스를 만들기 어렵다면, Sort의 부하를 최소화하는 다른 방법은 없는지를 고려해야 한다. 분명히 방법은 있다. 이 방법을 알지 못한다면 오직 인덱스에만 목숨을 거는 사람이 될 가능성이 높다. 오늘은 인덱스를 전혀 만들지 않은 상태에서 Sort의 부하를 최소화 하는 방법에 대해 알아볼 것이다.

단 한 가지 개념만 안다면, Order By에 의한 Sort의 부하를 이해한 것이다. Sort의 부하량은 면적에 비례한다는 것. 이 개념은 아래와 같이 표현할 수 있다. 참고로 ∝는 비례한다는 의미이다.

공식의 이해가 부족하다
주위의 지인들에게 위의 식을 질문한 결과 거의 모두가 세로에 대해서는 정확히 이해하고 있었다. 즉, Sort할 건수가 많아지면 Sort의 부하가 증가 한다는 것이다. 이에 반해서 가로에 대해서는 정확한 이해를 하는 사람이 드물었다. 대부분, Order By절에 의해 Sort의 부하가 발생하므로 Order By절에 존재하는 컬럼 Size의 합계가 가로라고 생각하는 것이다. 다시 말해, Order By절의 컬럼이 세 개라면, 세 컬럼의 Size를 합친 것이 가로라는 것이다. 과연 그럴까?

위의 주장을 검증하기 위해 테이블을 하나 만들고, 추가적으로 컬럼을 3개 만들자.

SQL 튜닝책을 세 권정도 읽은 신입사원이 SQL 튜닝방법론을 요청하였다. 이유는 튜닝책에 방법론이 없다는 것이다. 튜닝 방법론이란 “SQL을 튜닝 해달라고 요청 받았을 때 내가 무엇 무엇을 해야 하나?” 이다. 즉 SQL 튜닝시의 To-Do 리스트(체크리스트)를 요구한 것이다.

SQL 튜닝을 자주 하면서도, 그 안에 몇 가지 작업이 있는지 생각하지 못했다. 누가 그랬던가? 일상을 낯설게 느껴보라고… SQL 튜닝요청을 받았을 때 내가 어떤 일을 하는지 가르쳐 주면 되겠구나 하는 생각이 들었다. 그 결과 7가지 방법이 결론으로 도출되었다. 만약 7가지 방법을 모두 적용할 수 있는 경우임에도 불구하고 하나라도 빠진다면 최적화된 SQL을 만들 수 없다.

아래는 필자와 신입사원의 대화이다.

신입사원 : SQL 튜닝의 원칙 몇 가지를 저에게 일러 주실 수 있나요? 튜닝책도 몇 가지 보았고, 강의도 많이 들었지만 “이 원칙만 지키면 100점 만점에 90점은 맞는다.” 는 원칙 같은 것은 없더군요. 저는 이제 입문하는 단계이므로 모든 경우에 100점을 맞을 필요는 없습니다.

필자 : 온라인 SQL이냐 대용량 배치 SQL이냐에 따라 튜닝방법이 달라지므로 설명하기가 힘들군요.

신입사원 : 걱정 하실 것 없습니다. 대용량 배치는 프로그램이 많지 않으므로 제외하고, 온라인 SQL 튜닝 원칙을 몇 가지 일러주세요.

필자 : 온라인 SQL이라 하더라도 관점에 따라 튜닝방법이 다릅니다. 예를 들어 Peak Time에 Insert 문이나 Update 문, Select문이 집중적으로 몰릴 때의 튜닝방법이 있고, 단순히 SQL 하나에 에 집중해서 응답시간을 최소화 하는 튜닝방법이 있습니다.

신입사원 : 그런 것을 지금 모두 알아야 할 필요는 없습니다. 제가 튜닝 프로젝트에 투입되었다고 가정하고, 성능이 느린 Select문 하나를 받았을 때 튜닝을 어떻게 해야 하는지에 대해서만 설명해주시면 됩니다.

고단수 신입사원

이렇게 해서 신입사원에게 말려들게 되었다. 즉, 초보라도 몇 가지 원칙만 지키면 온라인 Select문에 대한 튜닝을 100점 만점에 90점을 맞을 수 있는 방법을 요구하는 것이다. 사실 이런 질문에 가장 적합한 답변은 “SQL 튜닝책을 읽어보라”는 것이다. 그런데 신입사원이 필자와 대화과정(튜닝책도 몇 가지 보았고 ~)에서 이런 답변을 못하도록 교묘히 막고 있다. 고단수이다. 몇 가지 방법만 알게 된다면 90점을 받는다고? 처음부터 그런 방법은 없다고 할 걸 그랬나? 후회가 된다. 어찌되었든 약속처럼 되어버렸으므로 이 글을 쓰게 되었다. 음…..머리가 아파온다.

온라인 Select문 튜닝 방법론

온라인 SQL의 튜닝방법은 여러 가지가 있을 수 있다. 하지만 그 중에서 가장 기초적이고, 기본적인 방법을 공개한다. 아래의 7가지 항목을 점검하고 약한 곳을 보강하면 된다. 이 글은 SQL 튜닝책을 두 권 정도 본 사람들을 위한 것이다. 튜닝에 자신있는 사람들은 볼 필요가 없다.

1. 적절한 인덱스를 사용하여 Block I/O를 최소화 하라

조인이 없는 경우는 적절한 인덱스를 사용하는 것 만으로도 상당한 효과를 볼 수 있다. 조인이 있는 경우는 특히 Driving(선행) 집합에 신경을 써야 한다. 왜냐하면 Nested Loop 조인을 사용했고, 선행집합의 건수가 많다면, 후행집합의 조인의 시도횟수가 증가하므로 성능이 느려진다. 따라서 적절한 인덱스를 이용하여 선행집합의 건수를 줄인다면, 혹은 가장 적은 집합을 선행으로 놓는다면, 후행집합으로의 조인건수는 줄어든다. 물론 이때에도 후행집합의 적절한 인덱스는 필수 조건이다. Driving 집합의 Block I/O를 줄이기 위하여 최적화된 인덱스가 없다면 생성하고, 있다면 그것을 사용하라. 다시 말해 최적의 Access Path를 만들어라.

운영중인 시스템이라면 최적의 Access Path를 위해 인덱스를 변경하거나 생성할 때는 주의해야 한다. 현재 튜닝하고 있는 SQL에 최적화된 인덱스를 생성하더라도 다른 SQL에 악영향을 줄 수 있기 때문이다. 인덱스를 생성하거나 변경할 때는 그 테이블을 사용하는 다른 SQL의 실행계획이 변경되지 않는지 각별히 신경을 써야 한다. 이런 이유 때문에 개발과정에서 효율적인 인덱스 설계가 중요시 된다.

2. 조인방법과 조인순서를 최적화 하라

온라인에서 사용하는 Select문은 좁은 범위를 검색하는 경우가 많다. 이럴 때는 대부분 Nested Loop Join이 유리하다. 그러므로 조인건수가 소량인 SQL에 Hash Join이나 Sort Merge Join이 발견되면 Nested Loop Join으로 변경하는 것이 더 유리한지 검토해야 한다. 물론 여기서도 Nested Loop 조인에 관해서만 다룬다.

Nested Loop 조인에서 가장 중요한 것은 조인순서이다. From절에 테이블(집합)이 두 개라면 후행집합의 관점에서는 적절한 인덱스만 존재한다면 그것으로 족하다. 만약 From절에 테이블(집합)이 세 개 이상이라면 조인순서를 변경할 수 있는지에 대한 두 가지 원리를 사용하라. 두 가지 원리는 아래의 단락에서 소개된다. 아무리 조인할 집합이 많다고 하더라도 이 두 가지의 원리는 동일하게 적용될 수 있다. 두 가지 원리를 이용할 때 필요하다면 Leading 힌트를 사용해야 한다.

첫 번째, 후행집합에 적절한 인덱스가 없는 경우에 조인순서를 바꾸면, 최적의 인덱스를 사용할 수 있는 경우가 많다. 예컨대, 튜닝전의 조인순서가 Aà B à C 라고 하면, 중간집합인 B에 적절한 인덱스가 없고 오히려 C에 적절한 인덱스가 존재하는 경우가 있다. 이럴 때는 B에 인덱스를 무작정 생성하지 말고, 조인순서를 A à C à B로 바꿀 수 있는지, 바꾸는 것이 더 효율적인지 검증하라. 조인순서만 바꾸어 주어도 일량이 획기적으로 줄어드는 경우가 많다. 만약 조인순서를 바꿀 수 없거나, C를 중간집합으로 하는 것이 비효율적이라면, B를 중간집합으로 유지하고 적절한 인덱스를 사용해야 한다.

두 번째, 조인되는 집합 중 특정 인덱스에서 Block I/O가 증가하는 경우에 조인순서의 변경을 검토하라. 이때 10046 Trace나 DBMS_XPLAN.Display_Corsor를 이용하면 조인집합들의 Block I/O량을 관찰할 수 있다. 예를 들어, 튜닝전의 조인순서가 Aà B à C 라고 하고, 집합 B에서 Block I/O량이 증가하면 A à C à B로 바꾸면 일량이 줄어드는 경우가 많다. C를 먼저 조인(Filter)하여 선행집합(B의 입장에서는 C가 선행이다)의 건수를 줄이고 B에 조인하면 성능이 향상된다.

3. Table Access(Random Access)를 최소화 하라

Random Access란 rowid로 테이블을 엑세스하는 것을 말한다. 1번과 2번을 최적화 했다면 Random Access도 자동으로 많이 줄어들었을 것이다. 하지만 그것이 끝은 아니다. 여전히 성능이 만족스럽지 못하다면 Random Access 횟수를 줄이는 것을 간과해서는 안 된다.

인덱스를 사용하면 rowid가 자동으로 획득된다. 만약 인덱스에 없는 컬럼을 Select 해야 한다면 rowid로 테이블을 엑세스 해야 한다. 이때 테이블로 엑세스 해야 할 건수가 많고, 인덱스의 컬럼순으로 테이블이 sort되어있지 않다면 성능이 매우 저하된다. 왜냐하면 테이블이 인덱스 기준으로 sort되어 있지 않기 때문에 테이블을 방문할 때마다 서로 다른 블럭을 읽어야 하기 때문이다.

비유적으로 설명해보자. 우리가 심부름을 할 때 세 군대의 상점(A,B,C)을 들러야 한다고 치자. 그 상점들이 모두 한 건물 내부에 존재한다면 얼마나 좋겠는가? 그 심부름은 매우 빠른 시간에 끝날 것이다. 하지만 반대로 상점 A는 부산에 있고 상점 B는 대구에 있고, 상점 C는 서울에 있다면? 만약 당신의 성격이 매우 좋아서 그 심부름을 한다고 해도 시간이 많이 걸릴 것이다. Random Access도 마찬가지이다. 인덱스의 rowid로 테이블을 방문할 때, 테이블이 인덱스기준으로 sort되어 상점처럼 다닥다닥 붙어있다면 성능은 매우 빠르고, 흩어져 있을수록 성능이 느려진다. (오라클에서는 테이블이 인덱스 기준으로 sort 되어 있는 정도를 Clustering Factor라고 한다.) 바로 이런 이유 때문에 index scan보다는 Table Scan이 느린 것이다. 따라서 우리는 Random Access의 부하를 최소화 해야 한다.

Random Access의 부하를 줄이는 방법은 네 가지이다. 첫 번째, 테이블의 종류를 변경하는 방법이다. IOT나 클러스터를 이용하면 Clustering Factor가 극단적으로 좋아진다. 또한 파티션을 이용하면 같은 범위의 데이터를 밀집시킬 수 있다. 두 번째, 효율적인 인덱스를 사용하거나 조인방법과 순서를 조정하여 Table Access를 최소화 하는 방법이다. 이 방법은 1번과 2번에서 이미 설명 되었다. 세 번째, 인덱스에 컬럼을 추가하여 Table Access를 방지하는 방법이다. 예를 들어 Select절의 특정 컬럼 때문에 테이블이 엑세스 된다면, 인덱스의 마지막에 그 컬럼을 추가하면 된다. 네 번째, 인덱스만 엑세스 하고 테이블로의 엑세스는 모든 조인을 끝내고 마지막에 시도하여 Random Access의 횟수를 줄이는 방법이다. 해당 글을 참조하라. 

4. Sort나 Hash 작업을 최소화 하라

1,2,3번을 통하여 최적의 Access Path와 Join을 사용했다면, Block I/O의 관점에서는 튜닝이 끝난 것이다. 하지만 1,2,3번이 모두 해결되었다 해도 Order by나 Group By 때문에 성능이 저하 될 수 있다. 특히 결과가 많은 경우, sort는 치명적이다.

인덱스가 sort 되어있다는 특성을 이용하면 order by 작업을 대신할 수 있다.  Group By도 sort 가 발생하는데 group by 단위와 인덱스의 컬럼이 동일 하다면 sort는 발생하지 않는다. 최적의 인덱스를 사용하면 Access Path를 개선하는 효과뿐만 아니라 Sort의 부하도 없어진다.

Union All을 제외한 집합연산(Union, Minus, Intersect)를 사용하면 Sort Unique 혹은 Hash Unique가 발생한다. Union은 Union All로 바꿀 수 없는지 검토해야 하고, Minus는 Not Exists 서브쿼리를 이용하여 Anti Join으로 바꿀 수 없는지 고려해야 한다. Intersect는 교집합이므로 조인으로 바꿀 수 있는지 검토해야 한다. 아주 가끔 Distinct를 사용한 SQL이 눈에 뛰는데 이 또한 Sort Unique 혹은 Hash Unique를 발생시킨다. 모델러나 설계자에게 문의하여 Distinct를 제거할 방법이 없는지 문의해야 한다.

Oracle 10g부터는 Hash Group By가 발생할 수 있는데, 이미 적절한 인덱스를 사용하는 경우라면 Hash Group By를 사용할 필요는 없다. 이런 경우 NO_USE_HASH_AGGREGATION 힌트를 사용하면 Sort Group By로 바꿀 수 있다. 이렇게 해주면 실행계획에 “SORT GROUP BY NOSORT” Operation이 발생하며, Sort나 Hashing 작업이 전혀 발생하지 않는다. Group By의 부하를 해결하는 또 하나의 방법은 스칼라 서브쿼리를 사용하는 것이다. 조인을 사용하면 Sum 값을 구하기 위해 Group By가 필수적이다. 하지만 스칼라 서브쿼리를 사용하면 Group By를 사용하지 않고도 sum 이나 Min/Max 값을 구할 수 있다. 또한 분석함수의 Ranking Family(rank, dens_rank, row_number)를 최적화된 인덱스와 같이 사용하면 Group By나 Sort를 하지 않고도 Min/Max 값을 구할 수 있다. 이때는 실행계획에 “WINDOW NOSORT” Operation이 발생한다. 관련 글을 참조하기 바란다.

5. 한 블록은 한번만 Scan하고 끝내라

같은 데이터를 반복적으로 Scan하는 SQL이 의외로 많다. 대표적인 경우가 Union All로 분리되었지만 실제로는 그럴 필요가 없는 경우이다. 예를 들어 Where 절에 구분코드가 1일 때 , 2일 때, 3일 때 별로 SQL이 나누어져 있는 경우이다. Where 절을 구분코드 in (1,2,3) 으로 처리하고, Select절에서 Decode나 Case 문을 사용하여 구분코드별로 처리해준다면 Union All은 필요 없다. Union All을 사용하는 또 한가지의 경우는 Sub Total(소계)과 Grand Total(총계)를 구해야 하는 경우이다. 이 경우도 Rollup/Cube나 Grouping Sets를 Group By절에 사용한다면 소계나 총계를 위한 별도의 Select문을 실행 시킬 필요는 없다. 1~4번의 과정은 SQL문의 변경이 없거나 최소화 된다. 하지만 5번의 경우는 SQL을 통합시켜야 하기 때문에 시간이 많이 소모되며, 많은 사고가 요구되는 창조적인 과정이다. 여기까지 했다면 진행되었다면 원본 SQL 자체의 튜닝은 완료 된 셈이다.

6. 온라인의 조회화면이라면 페이징처리는 필수이다

부분범위 처리를 해야 한다. 물론 전체 건을 처리해야 하는 경우는 있을 것이다. 하지만 조회화면이라면 몇 십만 건 혹은 몇 만 건이나 되는 결과를 모두 볼 수 없다. 따라서 볼 수 있는 단위로 끊어서 출력해야 한다. 예를 들어 결과 건수가 10만 건이라고 해도 최초의 50건을 화면에 먼저 뿌린다면 1,2,3,4 번에서 설명했던 모든 부하(Block I/O의 부하, 조인의 부하, Random Access의 부하, Sort의 부하)를 한꺼번에 감소시킬 수 있다. 따라서 가능하면 개발자를 설득하거나 책임자를 설득하여 페이징 처리를 하는 것이 바람직하다.

페이징 처리를 해도 효과를 볼 수 없는 몇 가지 예외가 있다. 분석함수를 사용하거나, Connect By + Start With를 사용한다면 페이징 처리의 효과는 없다. 분석함수의 경우 인라인뷰의 외부로 뺄 수 있다면 부분범위 처리가 가능하다. 이에 관해서는 해당 글을 참조하기 바란다. Connect By + Start With를 사용한 경우는 부분범위처리가 불가능하다. 하지만 11g R2의 신기능인 Recursive With절을 사용한다면 페이징 처리의 효과를 볼 수 있다. 이때, Recursive With절에 Search절(Order By절과 같은 기능)을 사용한다면 Connect By와 마찬가지로 페이징 처리의 효과가 없으니 주의해야 한다. 즉 인덱스의 구성을 적절히 하여 Sort를 대신해야 한다. Recursive With가 무엇인지 궁금한 사람은 관련 글을 참조하기 바란다.

7. 답이 틀리면 안 된다. SQL을 검증하라

7번은 SQL 자체를 튜닝하는 것은 아니다. 하지만 7번을 튜닝 방법에 추가한 이유는 있다. 튜닝을 하였음에도 답이 틀린다면, 튜닝을 하지 않은 것 보다 못하다. 그러므로 튜닝 후에 답이 옳은지 항상 검증해야 한다. 1번~ 7번 중에 가장 중요한 것이 7번이다.

방법론 정리

1.     적절한 인덱스를 사용하여 Block I/O를 최소화 하라.

2.     조인방법과 조인순서를 최적화 하라.

3.     Table Access(Random Access)를 최소화 하라

4.     Sort나 Hash 작업을 최소화 하라

5.     한 블록은 한번만 Scan하고 끝내라

6.     온라인의 조회화면이라면 페이징처리는 필수이다

7.     답이 틀리면 안 된다. SQL을 검증하라

방법론의 의미

만약 1~7번을 모두 적용할 수 있는 경우임에도 불구하고 하나라도 빠진다면 그것은 최적화된 SQL이 아니다. 물론 튜닝을 할 때 위의 1~6번을 항상 적용할 수 있는 것은 아니다. 경우에 따라서는 하나만 적용될 수도 있고, 두 개만 적용할 수 있는 SQL도 있다. 하지만 1~6번을 모두 적용할 수 있는지 꼼꼼히 살펴야 한다.

이 글은 튜닝 입문하여 관련 책들을 몇 권 본 사람들을 위한 기본적인 튜닝방법에 관한 것이다. 1번부터 7번까지의 방법은 기본 중에 기본이다. 이것들만 알아도 온라인 조회화면에서 사용하는 SQL을 튜닝하는데 어려움이 없을 것이다. 다시 말해 90%는 해결 할 수 있다. 그렇다면 나머지 10%는? 그것들은 그때 그때 마다 다르게(On the fly 모드) 처리된다. 또한 그것들은 책이나 매뉴얼에 나와있지 않기 때문에 경험치 이거나 실험과 연구의 결과로 알아내는 것들이다.

일상을 낯설게 느껴보니 좋은 점이 많다. 언제 필자의 다른 일상(모델링, 시스템분석/진단)에 대한 방법론도 만들어 보려고 한다.

-대표적인 페이징 처리방법

-누적집계가 필요할 때 페이징(부분범위) 처리방법

-Pagination의 단점을 이용하는 방법

주의사항

이 글에서 사용되는 분석함수는 현재 row 까지의 누적집계(Cumulative total) 이다. 이와 반대로 전체집계(Grand Total)나 그룹집계(Sub total)는 부분범위처리를 할 수 없다. 왜냐하면 데이터를 모두 읽어야만 결과를 낼 수 있기 때문이다. 하지만 누적집계는 데이터가 sort 되어 있고, 이미 출력된 컬럼들의 값을 알 수 있다면 부분범위처리가 가능하다. 우리는 이점을 이용할 것이다.

상황

Time Out이 발생하여 개발자가 종이 한 장을 들고 급하게 뛰어왔다.

개발자: 페이징 처리를 했고, 최적의 인덱스도 존재하고, 그 인덱스를 잘 타는데도 Time Out이 발생합니다.

필자  : 그럴 리가요?

개발자: 이 SQL입니다. 한번 봐주세요.

필자  : 음….분석함수 때문에 전체 건을 읽고, 전체 건을 sort하는 군요. 페이징 처리방법을 약간 변경하면 됩니다.

개발자: 이 방법은 SQL 작성 가이드에 나온 방법입니다. 이 방법을 쓰지 않으면 사수에게 혼납니다.

필자  : 이 방법을 사용하지 말라는 이야기가 아니라, 분석함수의 위치만 옮기라는 이야기 입니다.

개발자: 그렇군요. 감사합니다.

이렇게 해서 장애상황은 없어졌다. 이후에 SQL 작성가이드에 페이징 처리시 누적집계가 있는 경우의 처리방법을 추가하였다고 한다.

Pagination SQL

개발자가 사용한 페이징 처리용 SQL은 아래와 같았다.

bypass_ujvc 힌트와 관련하여 필자와 의견을 달리하는 전문가도 있음을 밝혀둔다. 특정 버젼에서 특정 상황에서 힌트를 확실히 이해하고 성능문제가 큰 경우일 때만 사용한다면 된다는 것이다. 제약사항을 4가지나 붙였으므로 공감이 가는 부분이 있다. 아래의 댓글을 반드시 읽어보기 바란다. 2010-04-19 (추가)

ANSI SQL의 UPDATE문은 오라클과 달리 FROM 절이 존재하며 여러 테이블 혹은 뷰와 자유로이 조인할 수 있다. 아래의 SQL을 보자.

위의 SQL은 MS-SQL 서버에서 사용할 수 있는 UPDATE문이지만 오라클에서 사용할 수 없다. 위의 SQL을 오라클로 바꾼다면 조인이 불가능하므로 아래처럼 스칼라 서브쿼리와 서브쿼리를 사용해야 한다.

중복 조인을 피할 수 있나? 

언뜻 보기에도 비효율이 극심하게 드러난다. EMP와 조인이 두 번 발생한 것이다. 중복된 조인을 피하기 위해서 아래처럼 인라인뷰와 스칼라 서브쿼리를 혼합하여 사용할 수 있지만 이 또한 중복 조인을 피할 수 없다. 보기에는 중복조인이 없는 것처럼 보이지만 Query Transformation을 공부하였다면 중복 조인이 보일 것이다.

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

| Id  | Operation                      | Name           | Rows  | Bytes | Cost (%CPU)|

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

|   0 | UPDATE STATEMENT               |                |     4 |    64 |     5   (0)|

|   1 |  UPDATE                        | DEPT           |       |       |            |

|*  2 |   FILTER                       |                |       |       |            |

|   3 |    TABLE ACCESS FULL           | DEPT           |     4 |    64 |     3   (0)|

|   4 |    SORT AGGREGATE              |                |     1 |    11 |            |

|*  5 |     TABLE ACCESS BY INDEX ROWID| EMP            |     1 |    11 |     2   (0)|

|*  6 |      INDEX RANGE SCAN          | EMP_DEPTNO_IDX |     5 |       |     1   (0)|

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

Predicate Information (identified by operation id):

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

   2 - filter( (SELECT COUNT(*) FROM "SCOTT"."EMP" "E" WHERE "E"."DEPTNO"=:B1

              AND "E"."JOB"='CLERK')>0)

   5 - filter("E"."JOB"='CLERK')

   6 - access("E"."DEPTNO"=:B1)

Predicate Information을 보면 ID 기준으로 2번에서 서브쿼리가 FILTER로 사용되었고 6번에서 다시 스칼라 서브쿼리가 사용되었으므로 두 번 조인이 발생한 것이다. 이해가 되지 않는 독자는 스칼라 서브쿼리를 서브쿼리로 변환하라 글을 읽기 바란다.

또 다른 제약

조인이 안 된다는 제약을 피하기 위해 VIEW 혹은 인라인뷰를 UPDATE 하곤 한다. 하지만 아래의 새로운 예제를 본다면 또 다른 제약이 있음을 알 수 있다.

Dept와 emp는 1:N의 관계인데 1쪽을 Update 하지 못하는 제약을 만나게 된다. 이 제약을 피하기 위해서 bypass_ujvc 힌트를 사용하는 사람이 있는데 절대 사용하면 안 되는 힌트이다. 힌트를 해석하자면 Updatable Join View의 Check를 By-Pass(무시) 하겠다는 뜻이다. 실제로 Wrong Result(답이 잘못됨)가 나오므로 사용해서는 안 된다.

끝없는 제약

ODS 시스템이나 데이터를 이행하기 위한 Temp성 테이블에는 Key가 없는 경우가 많다. 아래는 PK를 제거한 상태에서 인라인뷰를 UPDATE 해보았다. 단순히 사번이 들어오면 부서번호가 부서 테이블에 존재하는지 체크하여 급여를 UPDATE 하는 SQL이다. 
 

Key를 사용할 수 없으므로 뷰 혹은 인라인뷰를 update할 때 키 보존 제약이 걸리게 된다. 이때 마찬가지로 bypass_ujvc를 사용하면 에러는 피할 수 있지만 결과를 보장 하지 않는다. 이 힌트는 “건널목 신호등에 빨강 불이 들어왔지만 알아서 건너가시오. 자동차에 부딪혀도 책임지지 않습니다.” 로 비유할 수 있다.

Bypass_ujvc 힌트를 사용하지 않고 해결해야 해

위에서 언급한 세가지 제약조건(조인이 안됨, 뷰에서 1쪽 update 안됨, 뷰에서 키가 없으면 update 불가)과 한가지 문제(중복 조인)를 피할 수 있는 방법이 있다.  

1) 조인이 안 되는 문제와 중복 조인문제 해결