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

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

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

통계정보의 중요성  

정상도로로 주행하는데도 내비게이션에는 논두렁을 달리는 것으로 표시되면서 "경로를 이탈하여 새로운 길을 탐색합니다."라는 안내 멘트를 계속 들어야 했던 경험이 있을 것이다. 도로정보를 업데이트하지 않아 생기는 현상이다. 이 정도는 웃고 넘어갈 수 있지만, 자칫 내비게이션의 실수가 운전자를 위험에 빠뜨릴 수도 있다. 좁은 골목길에 이어 가파른 계단으로 안내하거나, 산악도로를 달리다 낭떠러지로 안내한다면? 귀찮더라도 주소정보, 도로정보를 주기적으로 업데이트해야 위와 같은 사태를 미연에 방지할 수 있다. 

주소정보, 도로정보가 부정확할 때 난처한 또는 위험한 일이 생기는 것처럼, SQL 옵티마이저가 사용하는 통계정보가 잘못되면 애플리케이션 성능이 갑자기 느려지고 심하면 장애상황으로 이어질 수 있다. 가장 비근한 예는, 1,000만 건짜리 테이블에서 특정 고객 데이터를 조회하는데 테이블 전체를 스캔하거나, 100만 건이 넘는 데이터를 조회하는 데 인덱스를 통해 테이블을 액세스하는 경우다. 비효율적인 인덱스를 선택하거나 잘못된 조인 메소드를 선택하는 경우도 흔하다. 

잘 돌던 프로그램이 어느 날 갑자기 느려졌다면 십중팔구는 통계정보가 원인이다. 예를 들어, 아래와 같이 다양한 원인에 의해 종종 시스템 장애가 발생한다.

• 특정 테이블의 통계정보를 갑자기 삭제한다.

• 대량 데이터를 지웠다가 다시 입력하기 직전에 통계정보를 수집한다.

• 3년간 갱신하지 않던 특정 테이블 통계정보를 어느 날 갑자기 재수집한다.

• 통계정보 없이 관리하던 테이블에 인덱스를 재생성한다. (오라클 10g부터는 인덱스를 생성하면 자동으로 인덱스 통계가 수집됨)

• 테이블이나 인덱스를 재생성하면서 파티션 단위로만 통계정보를 수집한다. (바인드 변수를 사용하면 파티션 통계가 아닌 Global 통계가 사용됨)

많은 이들이 통계정보의 불완전성, 그로 인한 옵티마이저의 불안정성을 얘기하지만, 사실 여기 나열한 원인들만 살펴봐도, 통계정보에 대한 관리원칙 부재가 원인이라는 사실을 알 수 있다. 대부분, 인재(人災)란 뜻이다. 

통계정보가 성능은 물론 시스템 안정성에 미치는 영향은 절대적이다. 그럼에도 불구하고, 차세대 시스템 구축 프로젝트에서 여러 차례 단위/통합 테스트를 거치면서도 통계정보 수집 정책과 스크립트에 대한 검증 및 안정성 테스트를 진행하는 경우를 본 적이 없다. 그보다 더 큰 문제는, 그런 검증과 테스트 없이 시스템을 운영하다 보니 통계정보로 인한 장애가 여러 차례 발생했고, 결국 어느 날부터 통계정보 수집을 멈추기로 결정한다는 사실이다. 

통계정보 수집을 멈추면 당장은 안정적이라고 느낄지 몰라도, 시간이 지날수록 오차가 커지면서 언제든 같은 유형의 장애가 발생할 수 있다. 언제 폭발할지 모르는 시한폭탄과 같은 상태가 되는 것이다. 내비게이션을 업데이트하지 않아도 당분간 이용하는 데 지장은 없겠지만, 어느 날 장거리 여행을 하다가 새로 생긴 논길 또는 고속도로 중간에서 안내 종료 메시지를 받게 되는 것처럼 말이다. 

그렇게 통계정보를 수집하지 않고 시스템을 운영하다가 1~2년쯤 지나 다시 통계정보를 수집할 수 있을까? 시간이 지날수록 변동성이 더 커지므로 어느 날 통계정보를 재수집했을 때 어떤 끔찍한 일이 발생할지 누구도 장담할 수 없다. 따라서 한번 통계정보 수집을 멈추면 다시 수집하기 어렵다고 보면 틀림없다. (처음부터 통계정보를 수집하지 않으면? 앞서도 얘기했듯이, 통계정보가 없으면 옵티마이저는 RBO 모드가 아닌, 통계정보 없이 내달리는 CBO 모드로 작동한다. 마치 70~80년대 도로정보를 기준으로 길을 안내하는 내비게이션처럼 말이다.)

통계정보를 자동수집 기능에 온전히 맡기는 것도 문제다. 다시 내비게이션 얘기를 하자면, 주소 및 도로정보 업데이트의 중요성을 알면서도 귀찮은 마음에 장거리 여행을 많이 하는 여름 휴가철에나 한 번씩 업데이트를 하곤 했었다. 그런데 몇 달 전부터 사용하기 시작한 내비게이션은 지정된 브랜드 주유소를 지날 때마다 자동으로 업데이트를 진행하기 때문에 참 편하다. 편리하긴 한데, 부분 업데이트여서 그런지, 원거리 이동 시 새로 생긴 도로를 못 찾아 헤매는 경우가 종종 있다. 

시동을 걸면 자동으로 전체 업그레이드를 진행하는 제품도 있는데, 한시가 급한 상황에 모든 작업이 완료되기를 기다려야 한다면 난감하기 그지없다. 

약속시각에 늦어 가뜩이나 신경이 곤두서 있는데, 꽉 막힌 도로로 진입할 때의 심정은 말로 표현하기 힘들다. 그래서 요즘은 실시간 교통정보를 반영하는 제품들이 인기다. 아니, 필수다. 이 기능의 효과성을 높이려면, 실시간 정보수집 및 반영 주기를 어떻게 설정하느냐가 중요한데, 예를 들어 출퇴근 시간에 서울 올림픽대로는 5분만 늦게 진입해도 교통량이 급변한다는 점을 고려하면 정보를 자주 수집해야 효과가 있다. 

새로 수집된 정보를 이동경로에 반영하는 시점도 중요하다. 초행길에 교차로를 지나거나, 고속도로 분기점이나 나들목을 지날 때 갑자기 경로 재탐색이 진행되면 순간 어디로 가야 할지 망설이며 당황하게 된다.

DBMS 세계로 돌아와, 통계정보 자동수집 기능을 사용하면 편리한 점은 있지만, 그것이 시스템의 불안정성을 높이기도 한다. 사실 오라클의 자동통계 수집 기능은, 데이터 변동량에 따라 실시간으로 통계정보를 재수집하는 기능이 아니라 패키징된 스크립트를 Job(또는 Task)에 기본적으로 등록해 줄 뿐이다. 따로 변경해 주지 않으면 매일 밤 10시에 통계정보 수집을 시작하도록 설정되며, Job에 등록된 스크립트가 DML 발생량과 오브젝트 크기에 따라 통계정보 재수집 대상을 결정하고 샘플링 비율을 결정해 준다. 공교롭게 일 배치 프로그램도 야간에 주로 수행하는데, 이 두 작업이 맞물려 돌아가면서 종종 문제를 일으킨다. 

통계정보 수집주기와는 별개로, 수집된 정보를 캐싱된 SQL 실행계획에 언제 반영할지도 중요한 고려사항인데, 기본적으로 통계정보 수집 이후 5시간 이내에 점진 반영되도록 설정된다. 통계정보 수집으로 인해 라이브러리 캐시 오브젝트에 경합이 급증하는 현상을 해결하기 위해 10g 이후 생긴 변화다. 

이런 복잡한 기능들이 맞물려 크고 작은 문제들을 일으키는데, 심지어 오라클 12c에서는 실시간 통계정보 수집 및 반영 기능이 소개되었다. 필자도 아직 사용해 보지 않아 속단하긴 이르지만, 오랜 경험에 비추어 볼 때 실시간으로 실행계획이 바뀌면서 생길 여러 가지 현상과 문제점이 예상된다. 

여기에 다 소개할 수 없는 훨씬 더 복잡한 기능들이 암암리에 작동하기 때문에 SQL 옵티마이저 작동 원리에 대한 지식이 부족한 DBA들은 (비용 기반) 옵티마이저를 어렵게 느낄 수밖에 없다. 그렇다고 제발, 통계정보 수집을 어느 날 갑자기 멈추는 일은 없길 바란다. 하지만 애석하게도, 너무나 많은 DBA가 잦은 장애 상황을 회피하기 위해 그런 선택을 한다. 알 수 없는 이유로 시스템 장애가 자주 발생한다면, 전문가 상담과 진단을 통해 원인치료를 해야 한다. 통계정보가 원인으로 밝혀진다면, 통계정보 관리원칙을 세우고, 그 원칙을 뒷받침할 정교한 스크립트를 작성하고, 검증과 테스트를 거친 후에 운영 시스템에 반영해야 한다. 그런 과정을 거친다면, 주기적으로 통계정보를 수집하면서도 안정적인 고성능 시스템을 구축할 수 있다.

이번 글에는 통계정보의 중요성에 관해서만 소개하고, 구체적으로 어떻게 수집하고 관리하는 게 효과적인지는 다음 기회에 심층적으로 다룰 예정이다.  

이번 글에서는 통계정보의 중요성을 집중적으로 살펴봤다. 마지막으로 다음 글에서는 옵티마이저의 한계는 무엇이고, 이를 극복하기 위한 개발팀 역할은 무엇인지 설명하려고 한다.