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

최근 10년 새 우리 생활을 바꾼 가장 혁신적인 기기로 스마트폰을 꼽는 이가 가장 많겠지만, 필자는 자동차 내비게이션을 꼽는다. 게임에 전혀 관심이 없고 늘 컴퓨터 앞에서 일하는 필자 입장에서 스마트폰은, 이동 중에도 뉴스 검색이 가능한 전화기에 불과하다. 반면, 내비게이션은 일상에서 겪는 가장 큰 불편을 덜어주었기 때문이다. (물론 내비게이션이 스마트폰 기능 중 일부가 된 지 오래다.)

내비게이션이 지금처럼 대중화되기 전, 가끔 길을 잘못 들어 곤혹을 치른 경험이 누구나 있을 것이다. 특히 고속도로 분기점이나 나들목에서 길을 잘못 들면 여간 난처하지 않다. 먼 길을 떠나기 전 항상 지도책을 펼쳐 놓고 이동경로를 그려보지만, 시간이 얼마나 걸릴지 예측하기 어려우니 예정보다 훨씬 일찍 또는 늦게 도착하기 일쑤였고, 아무리 꼼꼼히 메모해도 낯선 길에서 헤매지 않고 목적지에 도착하기란 결코 쉬운 일이 아니다. 그 당시, 대한민국 이정표 체계에 불만을 갖지 않은 운전자는 아마 없었을 것이다. 

지금은 출발하기 전날 내비게이션으로 목표 지점을 검색하고 예상 도착시각을 확인한 후, 다음날 시간에 맞춰 출발하기만 하면 된다. 길을 잘못 들더라도 내비게이션만 믿고 따라가다 보면 결국 목적지에 도착할 수 있다는 믿음 때문에 크게 불안하지 않다. 

최근엔 실시간 교통정보를 반영해 정체가 심한 구간을 우회하는 제품들이 인기다. 지인의 적극적인 추천으로 얼마 전부터 새로운 제품을 사용하기 시작했는데, 늘 정체되던 구간(이수교차로 방면 사평지하차도 입구)에서 이전에 사용하던 내비게이션이 한 번도 안내한 적이 없던 새로운 길로 안내해 준 덕분에 예상보다 빠르게 집에 도착하는 경험을 했다. 

직업병일까? 그 순간 SQL 옵티마이저를 떠올렸고, 우리 생활에 친숙한 자동차 내비게이션과 비교해 설명하면 평소 어렵게 느끼던 분들에게 도움이 되겠다는 생각을 했다. 

 무거운 최적화 과정                                                                                  

서울에서 부산까지 이동경로 개수는 몇 개나 될까? 모든 도로를 총망라해서 이동경로를 찾고 시간을 계산하려면 어마어마한 시간이 걸릴 것이다. 모든 경우의 수를 다 고려하는 것은 불가능하므로, 가능성이 높은 주요 도로 중심으로 일부 후보군을 뽑고 그 중에서 가장 빠른 길을 선택하는 방식일 텐데, 그럼에도 보통 3~10초 가량이 소요되는 걸 보면, 최적의 경로 찾기가 꽤 어렵고 무거운 작업임을 짐작할 수 있다. 

DBMS 옵티마이저가 SQL을 최적화(사용자로부터 전달받은 SQL을 가장 빠르게 처리할 수 있는 루틴을 찾아 내부 프로시저를 생성하는 과정)할 때도 데이터베이스 사용자들이 일반적으로 생각하는 것보다 훨씬 큰 비용이 든다. SQL 최적화 과정을 "하드(Hard)" 파싱이라고 부르는 데는 다 이유가 있다.

 

예를 들어, 5개 테이블을 조인하는 쿼리문 하나를 최적화하는 데도 무수히 많은 경우의 수가 존재한다. 조인 순서(Join Order)만 고려해도 120(=5!)가지. 거기에 NL Join, Sort Merge Join, Hash Join 등 다양한 조인 방식(Join Method)이 있고, 테이블 전체를 스캔할지, 인덱스를 이용할지를 결정해야 하고, 인덱스 스캔에도 Index Range Scan, Index Unique Scan, Index Full Scan, Index Fast Full Scan, Index Skip Scan 등 다양한 방식이 제공된다. 게다가, 사용할 수 있는 인덱스가 테이블당 하나뿐이겠는가.

대충 계산해도 수십만 개의 경우의 수가 존재하는데, 그 모든 경우의 수를 고려해 SQL을 최적화하려면 실제 실행하는 시간보다 더 오래 걸릴지도 모른다. 그래서 가능성이 낮은 경로는 가지치기하고 가능성이 높은 경로부터 처리하다 일정 시간이 지나면 멈추는 등 시간을 단축하기 위한 여러 알고리즘을 사용한다. 

내비게이션도 그런 알고리즘을 사용할 것이다. 목적지가 멀수록 경로탐색시간도 많이 늘어날 거 같지만, 실제 매우 먼 거리(예를 들어, 미국 샌프란시스코에서 뉴욕까지)의 이동경로 및 소요시간을 검색해 보면 예상보다 아주 빠르게 결과가 나온다. 모든 경우의 수를 다 고려하는 것은 아니란 뜻이다. 이러한 알고리즘과 하드웨어의 발달로 워낙 빠르게 처리하다 보니 잘 느끼지 못하겠지만, 실제 자동차 내비게이션과 SQL 옵티마이저는 순식간에 엄청나게 많은 연산을 한다. (참고로, 샌프란시스코에서 뉴욕까지 2,906마일에 43시간 걸리는 것으로 조회되었다.)

 라이브러리 캐시와 바인드 변수의 중요성                                                       

무거운 최적화 과정을 거쳐 얻은 결과물, 즉 SQL 실행계획을 한 번만 쓰고 버린다면 이만저만한 낭비가 아니다. 저장해 두었다가 반복 재사용하기 위한 메모리 공간이 필요한데, 라이브러리 캐시가 그것이다. 

내비게이션도 사용자가 검색한 최근 목적지를 자동으로 저장해 준다. 만약 이 기능이 없으면, 같은 목적지를 갈 때 매번 주소를 새로 입력해야 하니 얼마나 불편하겠는가. (라이브러리 캐시에 저장된 SQL은 그 실행계획까지 프로시저 형태로 컴파일해서 저장하는 반면, 내비게이션은 최근 검색한 경로명이나 주소만 가지고 있지 그 이동경로까지 저장하지 않는 점은 다르다. 최근 목적지를 선택하면 그때 경로를 다시 탐색한다.)

이 편리한 기능을 효과적으로 사용하려면 명칭이나 주소를 통일성 있게 입력하는 것이 중요한데, 등록할 수 있는 목적지 개수가 제한돼 있기 때문이다.

 

경기도 가평에 '아난티'라는 명문 골프장이 있다. 공식 명칭은 '아난티 클럽 서울'인데, 이 골프장을 찾기 위해 매번 아래와 같이 다양한 키워드를 사용한다면 어떻게 될까?

● ANANTI CC

● ANANTI CC 입구

● ANANTI CC 주차장

● ANANTI CC 클럽하우스

● 아난티CC

● 아난티CC 입구

● 아난티CC 주차장

● 아난티CC 클럽하우스

● 아난티클럽서울

● 아난티클럽서울 입구

● 아난티클럽서울 주차장

● 아난티클럽서울 클럽하우스

같은 장소임에도 불구하고 '최근 목적지'에 모두 등록되면서 여러 가지 비효율을 초래한다. 첫째, 같은 대상을 여러 번 등록하므로 공간을 낭비하고, 둘째, 기존에 등록된 다른 목적지를 밀어내고, 셋째, 최근 목적지 목록에서 특정 방문지를 찾을 때 방해가 된다. 

JAVA를 예로 들어, SQL도 아래와 같은 방식으로 개발하면 똑같은 비효율을 초래한다. 

  String SQLStmt = "select 회원ID, 회원명 from 회원 where 로그인ID = '" + login_id + "'"; 

  PreparedStatement stmt = con.prepareStatement(SQLStmt)

  ResultSet rs = stmt.executeQuery();

아래와 같이 조건절 값만 다른, 같은(?) SQL이 라이브러리 캐시에 수없이 저장되기 때문이다. 공간활용 측면에서 비효율적일 뿐만 아니라, 만약 수만 명의 회원이 동시에 로그인할 경우 시스템에 치명적인 문제를 일으킬 수 있다. 

  select 회원ID, 회원명 from 회원 where 로그인ID = 'oraking'; 

  select 회원ID, 회원명 from 회원 where 로그인ID = 'javaking'; 

  select 회원ID, 회원명 from 회원 where 로그인ID = 'tommy';

  select 회원ID, 회원명 from 회원 where 로그인ID = 'karajan';

제한된 캐시 공간을 효율적으로 사용하고 SQL 하드파싱 부하를 최소화하려면, 아래와 같이 바인드 변수를 사용해야 한다. 어떤 회원이 로그인하더라도 같은 실행계획을 사용해 데이터를 액세스한다면, parameter-driven 방식으로 구현하는 것이 마땅하지 않겠는가.

  String SQLStmt = "select 회원ID, 회원명 from 회원 where 로그인ID = ?"; 

  PreparedStatement stmt = con.prepareStatement(SQLStmt)

  stmt.setString(1, login_id);

  ResultSet rs = stmt.executeQuery();

독자가 관여하는 운영 시스템에 하드파싱이 초당 몇 회나 발생하는지 지금 바로 확인해 보기 바란다. 만약 온라인 업무 시간대에 하드파싱 발생빈도가 높게 나타난다면, 반드시 라이브러리 캐시 효율성에 대한 세부 진단을 통해 적절한 조치를 취해야 한다. 

그럼, 초당 몇 회가 적당할까? 전문가들조차도 하드파싱이 초당 5~10회쯤 발생하면 미미하다고 판단하는 경향이 있는데, 이는 결코 작은 수치가 아니다. 이것은 시간당 18,000~36,000개의 새로운 SQL을 생산해 낸다는 뜻으로, 현재 운영 중인 시스템에 캐싱돼 있는 전체 SQL에서 이 수치가 차지하는 비중을 따져보면 아마 깜짝 놀랄 것이다. 초당 5~10회 파싱하는 데 걸리는 시간이 문제가 아니다. 앞서도 얘기했듯이, 하드파싱을 많이 일으키는 프로그램들이 동시에 실행됐을 때 시스템에 미칠 악영향을 생각해야 한다. 

하드파싱 횟수를 초당 1회 미만으로 줄여야 한다. 방법은, 더 많은 SQL을 저장할 수 있도록 라이브러리 캐시 크기를 늘리는 것이 아니라, (조건절만 다른) 같은 SQL을 수없이 생산해 내는 프로그램을 찾아 조건절을 바인드 변수로 바꿔주는 것이다.

 

참고로, 가장 이상적인 라이브러리 캐시 크기는 얼마일까? 어쩌다 한번 방문한 목적지까지 내비게이션 최근 목적지에 모두 남아있으면, 이전 방문지를 다시 찾고자 할 때 오히려 방해된다고 앞서 설명했다. 자주 방문하는 목적지만 남기는 게 효율적이다. 마찬가지로 라이브러리 캐시에도, 우리 시스템에서 늘 사용되는, 수행횟수 측면에서 99%를 차지하는 SQL(대개 10,000여 개 이내)을 모두 수용할 수 있는 크기에 10% 정도 여유 공간을 더해주면 충분하다. 그 이상의 공간은 캐싱 효과도 별로 없으면서 탐색 효율만 떨어뜨리므로 차라리 데이터 버퍼 캐시에 할당해 주는 편이 낫다. 

그렇다고 라이브러리 캐시 크기를 지금 바로 줄이지는 마시라. 방금 제시한 내용은 바인드 변수를 잘 사용한 시스템을 기준으로 설명한 것이다. 바인드 변수를 사용하지 않아 비효율이 큰 상태에서 그나마 라이브러리 캐시 공간이 넉넉해서 버텨왔는데, 그 크기를 대폭 줄인다면 어떤 일이 생길지 아무도 책임질 수 없다. 

최근에 진단한 어떤 시스템은 데이터 버퍼 캐시와 라이브러리 캐시가 1:1 비율로 조정돼 있었다. 짐작대로, SGA가 동적으로 구성되도록 설정돼 있었고(sga_target > 0), 라이브러리 캐시에는 (조건절만 다른) 같은 SQL이 수없이 발견되었다. 이처럼 잘못 개발된 애플리케이션 환경에 DB 메모리 구성까지 동적으로 변경되도록 설정한다면, 가뜩이나 부족한 메모리 공간을 낭비하고 캐시 탐색 성능만 떨어뜨린다는 사실을 기억하기 바란다. 

 실행계획과 예상 비용(Cost)                                                                       

내비게이션에서 경로를 검색하고 나면 경로 요약이나 모의 주행 같은 기능을 통해 이동경로를 미리 확인할 수 있다. 내비게이션이 선택한 경로가 마음에 들지 않으면 검색모드를 변경하거나 경유지를 추가해 운전자가 원하는 경로로 바꿔줄 수도 있다.

SQL 개발자도 자신이 작성한 SQL의 실행계획을 반드시 확인하는 습관을 들여야 한다. 테이블 전체를 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스를 사용하는지 확인하고, 예상과 다른 방식으로 처리된다면 적절한 조치를 한 후에 운영 시스템에 적용해야 한다.

내비게이션의 가장 편리한 기능 중 하나는, 목적지에 도착하는 데 걸리는 시간을 표시해 주는 것이다. 그런데 이것은 어디까지나 예상시각이다. 정체가 가장 심한 토요일 오후엔 예상보다 30분 이상 늦게 도착할 때가 많다. 반대로, 새벽엔 30분 이상 일찍 도착하기도 한다. 제한속도보다 빨리 주행하기 때문이다. 

SQL 실행계획에 표시되는 Cost도 마찬가지다. 어디까지나 예상 Cost이기 때문에 실제 수행할 때 발생하는 I/O와 많은 차이가 난다. (내비게이션은 가끔 터무니없이 틀리지만, 옵티마이저는 자주 터무니없이 틀린다는 점이 다르다.)

참고로, I/O Costing 모델을 사용할 때 오라클 실행계획에 나타나는 Cost는 '예상 I/O Call 횟수'를 표현한 것이다. 반면, 최신 CPU Costing 모델에서의 Cost는 Single Block I/O를 기준으로 상대적인 시간을 표현한 것이다. 예를 들어, Cost가 100으로 표시된다면, '우리 시스템에서 100번의 Single Block I/O하는 정도의 시간'으로 해석하면 된다. Cost를 상대적인 시간개념으로 표현하는 CPU Costing 모델을 개발한 이유는, 똑같이 100번의 I/O Call이 발생하더라도 그것이 Single Block I/O냐 Multiblock I/O냐에 따라 속도가 다르고, 시스템마다 Single Block I/O, Multiblock I/O 속도가 모두 다르기 때문이다. 

아이들은 장거리 여행을 싫어한다. 그래서 자주 "아빠, 얼마나 남았어?"라고 묻는데, 내비게이션에 표시된 대로 "응, 40km 남았어"라고 답변하면 잘 알아듣지 못한다. 알아듣더라도 고속도로를 달리느냐, 시내 도로를 달리느냐에 따라 도착시각은 크게 달라지므로 시간 개념으로 설명하는 게 좋다. 그런데 시간 개념이 부족한 유치원 꼬마들에게는 "응, 25분 정도" 이렇게 답변해도 잘 알아듣지 못한다. CPU Costing 모델의 표현방식으로 "OO 패밀리 레스토랑 알지? 집에서 거기 5번 갔다 오는 시간" 이렇게 답변해야 쉽게 이해한다. 

지금까지 무거운 SQL 최적화 부담을 줄이기 위한 라이브러리 캐시와 바인드 변수의 중요성, 실행계획 및 예상 비용(Cost)에 대한 개념을 살펴봤다. 다음 글에서는 옵티마이저가 사용하는 통계정보에 어떤 것들이 있는지 살펴본 후, 옵티마이저 종류 및 모드(Mode)에 대해 설명한다.