천당과 지옥의 차이

얼마전에 ORACLE DBMS ShutDown 과 관련한 회의가 있었는데 문제는 아래와 같다.

지옥이 시작되다.
  악성 SQL 이 하나 있었다. 그 SQL 은 한번수행시 평균 Elapsed Time 이 0.95 초 정도 걸리고 자주 사용되는 중요한 SQL 인데 DBA 가 조인방법을 바꾸어  Elapsed Time 을 0.8 초로 줄였다. 조인방법만 바꾸었을 뿐인데 성능이 10% 이상 향상 되었다.  그리고 그 DBA 는 튜닝된 SQL을 개발자에게 운영 시스템에 반영하라고 지시하였다. 그 SQL을 테스트 해본 개발자는 성능이 빨라진것을 확인하고 해당 힌트를 적용하여 운영시스템에 반영하였다.

 여기서 부터 지옥이 시작되었다. 잘운영되던 DBMS 가 버벅이기 시작한것이다.
결국 운영할수 없는 지경에 까지 이르러서 운영조직은 ORACLE DBMS ShutDown을 결정하였고 튜닝된 SQL 을 원복시켰다.
다시 천당이 되었다. 결국 원래의 SQL 은 문제가 없었고 DBA 가 튜닝한 SQL 이 악성 SQL 이었던 셈이다.

메모리 사용량을 계산해보자.
  원인은 튜닝된 SQL 이었다. 그 SQL 은 Peak Time 에 무려 초당 700 번이나 실행되었고 한번 수행시 사용되는 HASH AREA SIZE 를 계산해보니 5MB 정도를 소비하였다. 하나의 SQL 이 초당 3.5 Giga Byte(700 * 5MB) 를 소모한 것이다.  전체 PGA 의 1/3 이 넘는 메모리를 하나의 SQL 이 소모해버린것이다. 당연히 그 SQL 을 제외한 다른 SQL은 PGA 영역의 메모리를 사용하려고 줄을 서게 될것이고 시스템 전반적인 성능이 저하될 것이다. 그뿐인가? 원래 0.95 초 걸리던 SQL 도 수행속도가 1초가 넘어버렸다.
주로 개발자 출신의 DBA 가 이와같은 실수를 많이 저지른다. 시스템 엔지니어 출신의 DBA 는 절대 이런실수가 없다. 물론 개발자 출신 DBA 의 장점은 헤아릴수 없이 많다.

메모리 증설이 해결책인가?
  결국 Hash 조인을 을 Nested Loop 조인으로 바꾸고 Access Path를 파악하여 적절한 인덱스를 생성하는것으로 사태는 진정 되었다. 운영조직은 현재 메모리를 증설할 계획이라고 한다. 하지만 메모리 증설로 해결되는것은 미시적 관점이며 거시적 관점에서 해결책은 되지 못한다. 자주 사용하는 SQL 을 튜닝 할때마다 메모리를 증설 할것인가?

위의 문제해결과정이 우리에게 주는 교훈은 무엇인가?
  수행속도의 최적화 혹은 Logical Reads의 수를 줄이는것이 항상 튜닝의 목표가 아니라는 것이다. 자원(CPU, MEMORY) 등은 한정적이다. 따라서 이자원들을 전체 시스템관점에서 적절하게 분배하는것 또한 튜닝의 목표가 되어야 한다.
자주 사용되는 SQL을 튜닝할때 Hash Join 을 남발하지 말아야 한다. Hash 조인은 조인횟수를 획기적으로 줄여주지만 반대급부로 메모리 소모가 심하다. 물론 0.95 초 걸리던 SQL 이 0.001 초만에 끝난다면 그방법이 고려될수도 있다. 자원을 독점하는 시간과 SQL 의 수행속도가 현저하게 줄어들었으므로...

수행속도의 최적화가 항상 튜닝의 목표인가?
  튜닝의 목표는 물론 Response Time 을 줄이는것 혹은 Logical Read 등을 줄이는 것에 있다.
하지만 항상 추가적으로 고려해야 할것이 전체 시스템 관점에서 자원의 효율적인 배분이다. 이것이 시스템 튜닝의 기본이다.
생각해보라. 같은시간대에 수행되는 야간배치 SQL 이 여러개 있고 그중에 하나가 Parallel 힌트를 다음과 같이 사용하였다면 얼마나 끔찍한 일이 일어날 것인가?

INSERT /*+ Parallel(B 256) */ INTO ~
SELECT /*+ Parallel(A 256) */
  FROM ~   ;

 천당과 지옥의 차이는 힌트와 같은 아주 조그만 코드에서도 좌우될수 있다.