[조시형의 DB이야기] SQL 내비게이션 - 옵티마이저 4(최종회)

[조시형의 DB이야기] SQL 내비게이션 - 옵티마이저 1 - 보기

[조시형의 DB이야기] SQL 내비게이션 - 옵티마이저 2 - 보기

[조시형의 DB이야기] SQL 내비게이션 - 옵티마이저 3 - 보기

옵티마이저의 한계 

주소정보, 도로정보를 아무리 잘 업데이트해 준다 해도 정보의 불완전성, GPS 수신불량, 내비게이션 엔진의 기계적인 판단 등으로 인한 실수가 있게 마련이다. 

목적지까지 10여 분밖에 안 남은 것을 확인하고 여유 있게 운전하다가 1분쯤 지나 다시 내비게이션을 보는 순간 화들짝 놀란 적이 있다. 남은 거리 150km, 남은 시간은 2시간!! 주위를 살펴보니 경부고속도로 진입로 근처를 지나는 중이었는데, 아마 내비게이션은 고속도로를 타고 대전쯤 다녀온 거 같았다. 

올봄에도 가족과 지리산으로 여행을 갔다가 황당한 경험을 했는데, 산속 깊은 곳에 숙소를 잡은 게 실수였다. 천년송으로 유명한 지리산 뱀사골 와운마을에 숙소를 잡았는데, 여행 전날 급하게 숙소를 알아보느라 이것저것 따져보질 못했다. 와운마을이 그렇게 깊은 산 속에 위치한 줄 모르고 밤늦게 뱀사골 입구에 도착해 산길을 따라 3km쯤 올라갔는데, 갑자기 내비게이션이 목적지에 도착했다며 안내를 종료하는 것이다. 밤늦은 산길에 길 물어볼 사람은 없고, 좁은 외길이라 차를 돌려 내려갈 수도 없고, 어두운 산길이라 후진은 더더욱 생각하기 어려웠다. 다른 선택의 여지가 없는 상황에서 1km가량 더 올라가 다행히 숙소를 찾긴 했지만, 그때의 황당했던 경험을 잊을 수가 없다. 

지인에게서 들은 얘긴데, 실시간 교통정보를 반영하는 내비게이션만 믿고 교통 혼잡 지역을 피해 20km를 우회했더니, 그 지역을 그대로 통과한 다른 동료에 비해 고작 1분 먼저 도착했다고 한다. 1분 단축하자고 몇천 원의 기름만 낭비한 셈이다. 검색 포탈사이트에서 '황당 내비게이션'으로 조회해 보면 비슷한 경험 사례들이 수없이 쏟아진다. 

내비게이션도 어디까지나 사람이 만든 소프트웨어에 불과하므로 애매한 상황에 놓일 때 수많은 가정과 정해진 로직에 따라 기계적인 판단을 한다. 두 제품을 동시에 켜고 실험해 보면 순간순간 서로 다른 길로 안내하는 것을 보게 되는데, 이를 통해서도 내비게이션의 판단이 항상 최선이 아님을 쉽게 짐작할 수 있다. 아무리 기술이 발전해도 그런 한계점은 여전히 존재할 것이다. 

SQL 옵티마이저도 마찬가지다. DBA가 통계정보를 아무리 잘 수집하고 개발자가 SQL을 아무리 잘 작성해도 실수가 있게 마련이다. 우선, 필요한 통계정보를 "충분히" 확보한다는 것부터가 불가능한 일이다. 정보야 많으면 많을수록 좋지만, 그것을 수집하고 관리하는 데 어마어마한 시간과 비용이 들기 때문이다. 

아무리 통계정보를 완벽하게 수집해도 바인드 변수를 사용한 SQL을 최적화할 때는 컬럼 히스토그램을 활용할 수 없다는 아주 치명적인 제약을 가진다. OLTP 시스템에서 동작하는 대부분 SQL에 바인드 변수를 사용하므로 이 문제를 반드시 해결해야 하는데, DBMS 벤더들이 많은 노력을 기울이고 있음에도 아직 완벽한 해결방안을 찾지 못한 상태다. 

또한, 아무리 비용기반 옵티마이저라고 해도 내부적으로는 여러 비현실적인 가정과 정해진 규칙을 사용하며, 애매한 상황에서는 그런 규칙에 따라 기계적인 판단을 하므로 한계를 드러낼 수밖에 없다. 또한, 최적화에 허용되는 시간이 매우 짧다는 것도 중요한 제약 중 하나다. 

SQL 최적화 기법이 과거와 비교하면 아주 많이 좋아졌고 계속 발전해 나가겠지만, 위와 같은 한계 및 제약으로 인해 앞으로도 여전히 옵티마이저는 불완전할 수밖에 없다. 같은 SQL이더라도 DBMS마다 다른 실행계획을 생성하고, 심지어 같은 DBMS에서도 버전에 따라 다르게 선택하는 것을 보면 SQL 옵티마이저의 판단이 항상 최선이 아님을 쉽게 짐작할 수 있다.

개발팀의 역할 

"안내사항이 실제 도로상황과 다를 수 있으니 이 시스템은 참고용으로 사용하십시오."

자동차 내비게이션을 켤 때마다 나타나는 경고 메시지다. 메시지 내용처럼, 운전할 때 내비게이션에만 의존하면 안 된다. 내비게이션을 켰더라도 전방을 주시하면서 순간순간 본인의 판단과 선택에 따라 운전해야 한다. 경험 많고 노련한 운전자는 실제로도 내비게이션 정보를 참고만 한다. 

단체 행사로 가끔 관광버스를 탈 일이 생기는데, 한번은 출근길 러시아워임에도 불구하고 요리조리 안 막히는 도로로 빠르게 서울을 빠져나가는 운전기사를 보며 속으로 감탄한 적이 있다. 수십 년 경험에서 우러나온 전문 운전자의 경험과 판단력에는 최신형 내비게이션도 감히 따라가지 못한다. 

자주 다니는 도로에선 교통체계와 차량 흐름을 눈여겨봐 둘 필요가 있는데, 출퇴근 시간에 왕복 8차선 도로가 똑같이 막히는 거 같지만, 연결된 주변 도로 상황에 따라 좀 더 흐름이 좋은 차선이 있기 때문이다. 노련한 운전자들은 그런 차선을 잘 선택해 정체 구간을 비교적 쉽게 빠져나간다. 

필자도 익숙한 도로에선 내비게이션 안내 멘트를 무시하고 익숙한 도로를 선택하는데, 그것이 더 효과적일 때가 많다. 그리고 앞서 소개한 황당 경험을 두세 번쯤 한 이후로는 아주 중요한 약속장소를 찾아갈 때, 특히 초행길일 때는 자동차에 내장된 내비게이션과 스마트폰 내비게이션을 동시에 켜곤 하는데, 두 기기가 서로 다른 길로 안내할 때면 둘 중 하나를 스스로 선택하면서 운전한다. 

데이터베이스 세계에서도 SQL 옵티마이저의 분명한 한계와 불완전성을 이해했다면, 개발팀의 역할이 무엇인지 정확히 인식할 필요가 있다. 가장 강조하고 싶은 것은 역시 인덱스 설계와 통계정보 수집이다. 

오라클을 기준으로 볼 때, 8 버전에서 처음 도입된 CBO는 9i까지도 어이없는 실수를 참 많이 했었다. 단편적인 예로, 그 당시 SQL 튜닝 전문가들은 NO_MERGE 힌트로 재미를 많이 봤었다. 이 힌트만 잘 써도 마법처럼 빨라지는 SQL이 많았기 때문이다. 그러나 10g를 거쳐 11g에 이르러서는 놀라울 정도로 발전된 모습에 위기감마저 느낀다. 복잡한 분석용 SQL을 처리할 때는 아직도 실수가 잦지만, 전형적인 OLTP성 SQL에는 95점 이상의 후한 점수를 줘도 아깝지 않을 정도로 최적의 실행경로를 찾아낸다. 단, 인덱스 설계와 통계정보 수집이 잘 돼 있다면 말이다. 

어렸을 때 모친을 따라 1년에 한 번쯤 강원도 원통에 있는 외가를 방문하곤 했다. 지금은 서울 외곽에서 2시간이 채 걸리지 않지만, 그 당시엔 5시간 이상 걸렸었다. 만약 그때 내비게이션이 있었다면? 당시처럼 도로정비가 잘 안 된 상태에선 내비게이션이 있어도 별 소용이 없었을 것이다. 

내비게이션은 이미 구축된 도로망을 분석해 가장 빠른 이동경로를 찾아줄 뿐, 없던 도로를 새로 만들어 주지 못하는 것처럼, SQL 옵티마이저도 주어진 환경 내에서 가장 빠른 처리경로를 찾아주는 역할만 한다. 인덱스를 빼놓고 성능을 얘기할 수 없는데, 그렇게 중요한 인덱스를 최적으로 구성하는 건 어디까지나 인간의 몫이란 뜻이다. 

그럼, 인덱스 설계는 누가 맡아야 할까? DBA? 아니다. 개발팀이 맡아야 한다. 인덱스는 어디까지나 SQL 조건절을 기준으로 설계해야 하기 때문이다. (물론, 전문가의 도움이 가장 필요한 어려운 작업이지만, 개발팀이 주도해야 한다는 뜻에서 개발팀의 역할로 정의하였다.) 개발팀이 인덱스 생성이나 변경을 요청할 때, 관련 SQL을 토대로 타당성을 검토하고 적절한 시간대를 골라 시스템에 반영하는 건 DBA의 역할이지만, 전체적인 시각에서 테이블별로 최적의 인덱스를 유지하고 관리하는 건 개발팀 역할이어야 한다. (인덱스 설계 원리에 대해 더 자세한 얘기를 하자면 너무 많은 지면이 필요하므로 생략하고, 나중에 다른 글을 통해 설명하기로 하겠다.)

최적의 인덱스 설계 못지 않게 중요한 게 통계정보를 효과적으로 수집하는 일이다. 물론 통계정보 수집에 관한 원칙과 기본 운영정책은 DBA가 수립하지만, 테이블별 세부 방안을 수립할 때는 개발팀의 적극적인 도움이 절대적으로 필요하다. 이때는 테이블별 데이터 입력 시점과 보관주기, 컬럼 히스토그램 필요 여부, 高변동성 여부 등을 고려해야 하기 때문이다. 기본 운영정책을 따르지 않고 별도로 관리할 테이블에 대한 통계정보는 오히려 개발팀이 주도적으로 수집정책을 세워야 하고, 적절한 방법을 통해 실행까지 맡는 것이 좋다고 생각한다.

애플리케이션 특성에 맞는 최적화 모드를 선택하고, 통계정보에 담기 힘든 업무적 특성을 고려해 SQL을 효과적으로 작성하는 것은 누가 뭐래도 개발팀의 역할이다. 

개발팀이 이런 역할들을 잘 수행하려면, 조직 내에 더 많은 DB 전문가를 양성해야 하며, 전문가 수준은 아니더라도 일반 DB 개발자들이 인덱스와 조인을 포함한 SQL 최적화 원리를 더 잘 이해하고 익숙해지려고 노력해야 한다. 

그러나 대부분 개발자는, 프로그램 결과가 올바르게 나오는 데만 신경을 쓰지, SQL 수행과정과 성능에는 큰 관심을 두지 않는 게 현실이다. 관심이 있어도 깊이 알려고 노력하지는 않는다. 마치, 지도상 현재 위치를 궁금해하지 않고 내비게이션 기능에만 의존해 달리는 운전자의 모습과 비슷하다. 자동차 내비게이션 기능이 많이 좋아졌고, 앞으로 더 좋아질 것이므로 길눈이 어두운 길치라면 그렇게 운전하는 것도 나쁘지 않다. 지금과 같은 첨단과학 시대에 내비게이션 하나 없이 여전히 지도책을 펼쳐가며 운전하는 것보다 낫다고 생각한다. 

하지만 고가의 최신형 내비게이션을 사용하면서도 정체가 심한 구간을 지날 때면 골목길로 유유히 복잡한 도로를 빠져나가는 멋진 드라이버가 되고 싶지 않은가?

평소에 SQL 옵티마이저를 막연하고 어렵게 느끼는 분들을 위해 우리 생활과 밀접한 자동차 내비게이션과 비교하면서 그 숨은 (하지만 이미 널리 알려진) 기능과 원리, 통계정보의 중요성, 그리고 개발팀의 역할 등에 대해 살펴봤다. 

요약하면, 대용량 데이터베이스 환경에서는 반드시 CBO를 선택해야 한다. 그리고 CBO를 선택했다면 반드시 통계정보를 수집해 줘야 한다. 어떻게? 주기적으로 잘!! 어렵다고 회피하려 하지 말자. CBO에 대한 깊이 있는 이해를 바탕으로 우리 회사가 관리하는 데이터와 애플리케이션 특성에 맞는 통계정보 수집 정책을 세우고 잘 관리하는 데 역량을 집중하자. OLTP 환경에서 라이브러리 캐시 효율성을 높이고 하드파싱 부하를 최소화하려면 바인드 변수를 잘 사용해야 한다. 

더 자세한 내용을 학습하길 원한다면, 필자가 저술한 '오라클 성능고도화 원리와 해법'을 참고하기 바란다. 특히, 1권 4장과 2권 3장이 도움이 될 것이다.

글을 마치기 전에 한마디만 더하면, SQL 최적화 원리를 잘 모른다면 어설프게 SQL 힌트를 구사하는 것보다 옵티마이저에게 의지하는 편이 대개 더 나은 결과를 가져온다. 단, 통계정보를 주기적으로 잘 수집해 준다면 말이다.