[이명진]아침공부편: "대용량 데이터베이스를 위한 오라클 SQL 튜닝" 저자특강 세미나

엑셈에서는 신입 DBA들의 역량 향상을 위한 여러가지 프로그램들이 운영 중에 있는데 그 중에 하나가 바로 지난 9월부터 진행된 "아침공부" 이다.

"아침공부"에서는 데이터베이스에 대한 분석력과 튜닝 기법들에 대한 이해를 기르기 위해 선배 DBA 분들이 각각 주제들을 맡아 강의를 진행하거나 신입 DBA들이 학습한 것을 발표하고 있다.

그렇게 "아침공부"를 진행되고 있던 중 얼마 전 아주 특별한 강의가 있었다.
"대용량 데이터베이스를 위한 오라클 SQL 튜닝"책을 쓰신 사장님의 저자특강 세미나

2000년에 출간된 책이지만 아직도 많은 DBA들 사이에서 추천되고 있으며, 이제 절판 된 이 책을 사려고 하는 "삽니다" 라는 글도 쉽게 찾아볼 수 있다.

저자 특강 세미나로 그런 책을 만나볼 수 있다니 SQL 튜닝교육에 목말라 있는 우리들에게는 절호의 기회였다.

우선 교재에 나오는 액세스 분석을 이해하기 위해, 실습 테이블(SALE)에 어떠한 인덱스가 있는지 확인했다. 그리고 unique 스캔과 range 스캔에 대해서 배웠는데, range 스캔으로 발생한 비효율을 해결하기 위해 서브 쿼리를 사용하거나, 조인 조건을 써서 unique 스캔으로 바꾸면, 일량이 대폭 줄어든다는 원리를 예제를 통해 배웠다. 이 예제에는 수천 건의 비효율만 발생했기에 수행시간에 문제는 없겠지만, 만약 이 건수가 1,000만건, 1억건 이상이었다면 이러한 기본적인 튜닝만 통해서도 수행시간을 현저하게 줄일 수 있을 것이다. 사장님께서는 "역발상"이라는 용어를 강조하시며, unique 스캔으로 바꾸기 위해 어떻게 해야할 지를 고민해 보라고 하셨다. 조금씩 이해가 되는 것 같았다.

이번엔 배운 예제를 토대로 해서, 응용하는 문제를 풀어보았다. 사장님의 설명을 먼저 듣고 나서 풀어서인지 그다지 어렵게 느껴지지는 않았다. 인덱스의 일량에는 기여하지 못했지만 테이블로의 랜덤 액세스는 줄여주는 역할을 한 조건, 그리고 그 조건이 얼마만큼의 랜덤을 줄여주었는지. 또 조건을 수정하였을 때 얼마의 수치를 보일 것인지, 그리고 이를 통해 튜닝의 포인트는 어떻게 잡을 수 있는지를 배울 수 있었다.

설명과 함께 예제를 통한 응용문제를 풀어보니 그다지 어렵지도 않고, 거기에다 보너스로 재미도 느낄 수 있었다. 어렵고 딱딱하게만 느껴졌던 튜닝이었는데, 이날은 마음이 편안했다. 하지만 한편으로는 이런 생각도 들었다. ‘지금 이 문제가 나 혼자서 해결할 수 있는 문제일까? 설명을 듣지 않고 풀었다면…?’

Where 조건절에서 어떠한 조건들이 일량을 발생시키고, 또 일량에는 기여하지 못해도 랜덤 액세스를 줄여주는데 기여했는지를 파악하는 것도 정말 중요함을 배웠다. number 타입인데 character로 surpressing 되어 있어서 일량에 기여하지 못하는 조건절도 확인했고, 당연한 이야기지만 pk unique의 조건이 like라면 이 조건에서만 일량이 생기고, 나머지 조건들은 랜덤 액세스를 줄여주는 역할만 하는 것도 알게 되었다. 연습문제를 풀면서 사장님은 액세스의 일량은 정확히 나와야 하고 정말 원리를 모르면 알 수 없기 때문에 이러한 일량들이 어떻게 변화하는지 읽을 수 있는 훈련이 정말 중요함을 계속 강조하셨다.

어떠한 SQL을 보고, 어떻게 이 SQL이 NL조인을 수행할 것인지 추측해보는 것은 쉽지 않다. SQL과 trace 을 보고 추측하는데, 이것만으로 추측한다면 잘못된 환상만을 가지게 되는 경우도 많이 있다. 사장님께서는 실제 예를 보여주시며, SQL과 trace 이외에도 데이터 값들의 물리적인 시컨스도 중요하게 생각해봐야 한다고 말씀하셨다. 물론 먼저 SQL부터 알아야 하는 것은 기본이고, I/O차이, 시간차이, 랜덤 액세스 차이 등을 확인해서 적절한 튜닝이 필요하다고 하시면서, 정말 많은 연습을 거치면 이러한 부분들은 자연스럽게 그려질 수 있으므로, 자주 연습해 보는 것이 중요하다고 강조하셨다. 즉, 테이블과 인덱스 내의 정확한 값을 알면 환상이 아닌 정확한 현실을 파악할 수 있고, 데이터 시컨스와 물리적 상황 등을 확인해서 최적의 인덱스 액세스 효율이 어떤 것인지 생각할 수 있다. 이것이 바로 비즈니스 모델을 몰라도 오라클 컨설턴트들이 기계적으로 SQL 튜닝이 가능한 이유라며, 사장님이 쓰신 이 책에는 이러한 방법에 대해 방법론화 시켜 놓은 책이라고 알려 주셨다.

조인연결에서 중요한 부분이 Lookup 인덱스의 일량을 파악하는 것인데, 인덱스에 대한 랜덤 횟수와 테이블 랜덤 횟수 그리고 LOOKUP INDEX FILTERING 수가 인덱스의 일량이라며, 이를 줄이기 위해 어디에서 비효율이 발생하는 가를 파악하는 것이 핵심이라고 말씀하셨다. 즉, DRIVED 테이블이 얼마나 filtering되고, LOOKUP 인덱스에서 얼마나 filtering이 되는 지를 파악해야 한다고 하시면서, 조인하면서 I/O를 어떻게 줄일 것인가라는 문제를 고민하지 말고, 미리 연결횟수를 줄여 주어야 한다고 알려 주셨다. 그러면서 다시 한번 강조하셨던 부분이 바로 Clustering Factor였다.

마지막으로 사장님께서 B카드의 배치작업을 튜닝하셨을 때의 에피소드를 들려주셨다. 그 당시에 담당자가 왜 트레이스도 똑같고, 일량도 똑같은데 수행시간이 틀리는지에 대한 질문을 했는데, 이것은 바로 clustering factor에 차이에 기인한 일이었다고 한다. Clustering factor를 높이기 위해 연결고리를 통해 넘어오는 값과 테이블 순서를 동일하게 하면 수행시간이 많이 단축된다. 그래서 특정한 조인연결과 Driving table의 index look-up 히팅률을 높이고, inline view로 만들어 sort하여 10시간 걸리던 작업을 30분으로 단축되었다고 한다. 이 에피소드를 마지막으로 1시간 30분의 수업이 끝났다.

이번 세미나를 통해 튜닝이라는 것이 어떤 것이고, 어떠한 방법으로 접근해야 하는지 배웠다. 물론 세미나 내용을 100% 이해하지는 못했다. 물론 100% 다 이해하신 분도 있겠지만, 나 같은 경우는 한 60% 정도 이해했다고 할까? 하지만 그동안 답답하기만 했던 튜닝에 있어서, 가이드 라인을 세울 수 있었던 좋은 기회가 되었다.