많은 곳에서 성능 최적화를 수행하면서 주어진 SQL만을 그대로 최적화하려고 하는 경우가 많다. 물론 주어진 SQL의 튜닝을 통해 성능을 최적화할 수 있는 것은 분명하다.

하지만 우리가 생각을 전환해 기존 방식과 다른 방식으로 수행해 엄청난 성능 향상을 기대 할 수 있는 경우도 많다. 대용량 데이터베이스로 변하고 있는 지금시점에서 성능 최적화를 위해 이와 같은 생각의 전환은 반드시 필 요한 요소가 되고 있다.

많은 프로젝트에서 성능을 향상시키기 위해 SQL 최적화에 전념하는 사이트들이 많이 있을 것이다. SQL 최적화를 통해 매우 많은 성능 향상을 기대할 수 있는 것은 사실이다.

예전에는 성능 저하가 발생하는 경우 SQL 튜닝보다도 해당 시스템의 CPU 또는 디스크 등의 자원을 증설하는 부분에 초점을 맞췄었다. 이와 같은 방법보다 SQL을 튜닝해 성능을 최적화하고자 하는 것은 매우 고무적인 현상임에는 틀림없다.

그 만큼 관리자들의 생각이 IT 선진화로 가는 것은 아닐까? 하지만 아직도 SQL 튜닝을 고정 관념에 맞춰 그리고 그 자체로 튜닝하고자 하는 경우가 많은 것 같다.

우리가 조금만 다르게 생각한다면 SQL 튜닝의 효과를 배 가시킬 수 있다는 것을 아는가? 주어진 SQL을 그대로 보지 않고 다르게 보는 순간 우리에게는 새로운 세상이 펼쳐질 것이다.

데이터 삭제를 DELETE로 수행하지 않고 데이터의 갱신을 UPDATE로 수행하지 않는다면 우리에게는 새로운 세상이 펼쳐 질 것이다. 이제부터 이와 같은 현상에 대해 하나하나 자세히 확 인해 보자.

DML은 왜 성능을 저하시키는가?

우리가 데이터를 저장하기 위해서는 INSERT를 수행하게 되 고 데이터를 제거하기 위해서는 DELETE를 수행하게 된다. 또 한 기존의 데이터를 변경하기 위해서는 UPDATE를 수행하게 된다.

이와 같은 사실은 개발을 한 번이라도 한 사람이라면 아 니 SQL에 관련된 책을 한 번이라도 본 사람이라면 누구나 알 수 있다.

DML 작업을 수행한다면 그리고 DML 작업을 수행해야 하는 데이터가 매우 많다면 많은 시간이 소요될 것이라고 누구나 생각 할 것이다. 그렇다면 이와 같이 대용량의 데이터에 대해 DML 작업을 수행하는 경우 어떤 이유에서 많은 시간이 소요되는 것일 까?

이에 대해서는 많은 사람들이 정확히 이야기하지 못하는 것 같다. 나를 알고 적을 안다면 백전백승이 되듯이, DML 작업의 성능을 최적화하기 위해서는 DML 작업이 왜 성능을 저하시키 는지를 알아야 할 것이다.

DML 작업이 왜 성능을 저하시키는지 정확히 이해하지 못한다면 우리는 어떤 방법을 사용해도 성능을 향상시킬 수 없을 것이다.

첫 번째로 INSERT의 성능 저하를 확인해 보자. INSERT는 데이터를 저장하는 SQL 중 하나로 다음과 같은 이유에서 많은 데이터의 저장 시 성능을 저하시키게 된다.

• - 로그 기록
• - HWM BUMP UP
• - 인덱스의 개수
• - 롤백을 위한 로그 기록
• - 디스크 I/O

INSERT 작업은 위와 같이 네 가지 현상에 의해 성능이 저하 된다. 이는 어떤 데이터베이스를 이용해도 동일하게 발생하는 현 상이다. 데이터베이스는 작업의 수행도 중요하지만 작업이 실패 하거나 또는 다른 장애에 의해 시스템이 재기동되는 등의 데이터 베이스 장애에 대해 데이터를 보호해야 하는 중요한 책임을 가지 고 있다.

이와 같은 이유에서 실제 데이터베이스에서 INSERT 작업을 수행하기 전에 어떤 작업을 수행하는지에 대한 로그를 기 록해야 한다. 이와 같은 기법을 선 로그(LOG AHEAD) 기법이 라고 한다.

실제 INSERT를 수행하기 전에 로그를 기록하기 때 문에 우리는 언제든지 INSERT 작업 중 데이터베이스에 문제가 발생해도 복구가 가능하게 되는 것이다. 실제 INSERT 작업과 관계없는 로그를 기록해야 하기 때문에 INSERT의 성능은 저하 된다.

그렇다면 HWM BUMP UP에 의한 성능 저하는 무엇을 의미 할까? HWM BUMP UP은 오라클 데이터베이스의 내부적인 요 소이다.

실제 INSERT를 수행하게 되면 해당 테이블에 할당돼 있는 공간에 데이터를 저장하게 되며 해당 공간을 익스텐트라고 부르게 된다. 익스텐트에는 HWM가 설정되어 있어 데이터는 HWM 앞의 블록에만 저장된다. 이것은 무엇을 의미할까?

HWM 앞까지 데이터를 저장한 후에는 HWM이 뒤로 후진해야 만 데이터를 INSERT할 수 있다는 의미다. 이를 HWM BUMP UP이라 하며 많은 데이터를 INSERT하게 되면 HWM BUMP UP은 많은 횟수가 발생하게 될 것이다.

하지만 HWM BUMP UP은 고비용의 내부적인 작업이다. 따라서 대용량 데이터를 저 장한다면 HWM BUMP UP의 횟수 증가로 INSERT의 성능은 저하된다.

INSERT의 속도와 인덱스의 개수는 INSERT의 성능 향상을 위해 매우 중요한 요소다. 데이터를 테이블에 저장하는 것은 여 유 공간을 가지고 있는 데이터 블록에 해당 데이터를 저장하면 된다.

하지만 인덱스에는 정해진 위치가 존재하게 되므로 정해진 위치를 찾는 프로세스가 수행된다. 따라서 해당 테이블에 인덱스 가 10개라면 이와 같이 저장되는 데이터에 대해 인덱스에서의 위치를 찾기 위해 정해진 위치를 찾는 프로세스가 10번 수행되 어야 할 것이다.

이와 같기 때문에 인덱스의 개수가 많다면 INSERT의 성능이 저하되는 것은 당연한 사실일 것이다.

어떤 사이트에서 어떤 테이블에 10개의 인덱스가 존재했으며 이를 최적화해 5개의 인덱스로 변경한 적이 있다. 단지 10개의 인덱스를 5개로 감소시키는 순간 SQL의 변경 없이 INSERT 작 업은 4배 정도의 성능이 향상됐다.

이는 4배의 성능 향상이 중요 한 것이 아니라 인덱스가 INSERT 작업에 많은 부하를 발생시킨 다는 중요한 사실을 우리에게 전해주는 셈이다.

롤백을 위한 로그 기록은 해당 작업을 수행하고 나서 작업을 취소하는 경우 이전 데이터로 복구하기 위해 이전 데이터의 값을 저장하는 것을 의미한다.

이와 같은 작업 또한 실제 데이터를 저 장하는 작업과는 별개로 수행되므로 INSERT의 성능 저하를 발 생시키게 된다.

HWM BUMP UP을 제외한 로그 기록, 인덱스 및 롤백을 위한 로그 기록은 모두 디스크 I/O를 발생시킨다. 또 한 실제 데이터를 저장하는 작업에서도 디스크 I/O가 발생하게 된다.

이와 같이 모든 단계에서 디스크 I/O가 발생하기 때문에 INSERT의 성능은 저하될 것이다.

두 번째로 UPDATE의 성능 저하를 확인해 보자. UPDATE 는 이미 저장되어 있는 데이터에 대해 변경 작업을 수행하는 것 이다. 이와 같은 UPDATE는 다음과 같은 요소에 의해 성능 저 하가 발생하게 된다.

• - 로그 기록
• - UPDATE 컬럼이 사용된 인덱스의 개수
• - 롤백을 위한 로그 기록
• - 디스크 I/O

UPDATE의 경우에는 HWM BUMP UP은 발생하지 않게 된 다. 또한 인덱스의 개수도 해당 테이블에 존재하는 모든 인덱스 는 아니며 UPDATE가 수행되는 컬럼이 사용된 인덱스의 개수 를 의미하게 된다.

UPDATE가 수행되면 해당 컬럼을 인덱스의 컬럼으로 구성하고 있는 인덱스만 갱신하게 된다. 그렇기 때문에 UPDATE 컬럼이 사용된 인덱스의 개수에 의해 UPDATE 성능 은 저하된다.

INSERT에 비해 UPDATE는 성능을 저하시키는 항목이 더 적다. 하지만 동일한 양에 대해 INSERT와 UPDATE를 수행한 다면 UPDATE가 성능 저하를 더 많이 발생시키게 된다. 이와 같은 이유는 무엇일까?

분명히 성능 저하의 요소는 INSERT가 더 많기 때문에 INSERT가 더 많은 부하를 발생시킨다고 생각하 기 쉽다. 이는 로그 기록과 롤백을 위한 로그 기록의 방식 차이 때문이다.

INSERT 작업은 로그에 이전 데이터라는 것이 존재하 지 않는다. INSERT 작업이 수행된 데이터의 위치 정보만을 가 지게 된다면 우리는 언제든지 롤백을 수행할 수 있으며 장애 시 복구도 어렵지 않게 된다.

하지만, UPDATE의 경우에는 이전 데이터의 값이 존재하기 때문에 이전 데이터를 로그에 기록하게 된다. 따라서 로그 기록 및 롤백을 위한 로그 기록에서 UPDATE가 INSERT에 비해 더 많은 데이터를 기록해야 하므 로 디스크 I/O의 증가로 UPDATE의 성능은 INSERT의 성능보 다 더욱 저하되게 된다.