데이터이야기

DB 노하우, 데이터직무, 다양한 인터뷰를 만나보세요.

조시형의 ‘친절한 SQL 튜닝’ : 왜 이러는 걸까요

데이터 이야기
작성자
dataonair
작성일
2019-06-11 00:00
조회
10098


조시형의 ‘친절한 SQL 튜닝’

왜 이러는 걸까요

기고자 조시형 증명사진
기고자 조시형

- 現) 디비안 대표이사 / 前) 엔코아, 비투엔

- 제1회 우수DB人상 수상 / 국가공인 SQLP, DAP 자격검정 전문위원

- 저서 : 친절한 SQL 튜닝, 오라클 성능 고도화 원리와 해법, 비용기반의 오라클

원리(공역)



‘친절한 SQL 튜닝’, ‘오라클 성능 고도화’ 저자로 유명한 조시형은 엔코아, 비투엔

을 거쳐 지금은 디비안 대표이사를 맡고 있다. 제1회 우수DB人상을 수상하기도 한 그는 20년 가까이 컨설팅을 수행하면서 터득한 성능관리 방법론을 솔루션화하는 일에 관심이 많고, DB 전문서적을 꾸준히 집필하면서 교육과 후배 양성에도 더 매진하려고 노력 중이다. 요즘은 인터넷 카페를 통한 지식공유 활동에도 열심인데, 그가 운영하는 ‘디비안 포럼(www.dbian.net)’을 방문하면 DB 관련 고급 기술자료, SQLP/DAP 자격증에 관한 정보를 습득할 수 있고, 직접 저술한 책에 대한 Q&A 서비스도 받을 수 있다.



어느 날 튜닝하다가 아래 프로그램을 발견했다.



[프로그램 1]


이 프로그램을 발견하는 순간, 드는 생각! 왜 이러는 걸까


왜 이러는 걸까요? 라는 의문을 표현하는 사진
[그림1]


아래와 같은 단일 UPDATE 문으로 처리할 수 있는데, 개발자는 왜 루프(Loop)를 선택했을까



[프로그램 2]


아래 표는 이제 더는 사용되지 않는 규칙기반 옵티마이저(Rule-Based Optimizer, RBO)가 내부적으로 사용하던 우선순위 규칙이다.


RBO 우선순위 규칙, 1순위 액세스 경로 Single Row By ROWID, 15순위 액세스 경로 Full Table Scan
[표 1] RBO 우선순위 규칙


앞서 본 프로그램 개발자는 테이블을 액세스하는 가장 빠른 경로가 ‘(1순위) Single Row by ROWID’라는 사실에 착안하지 않았을까 싶다. 내 추측이 맞는다면, 해당 개발자에게 아래 두 가지 개념을 알려주고 싶다.

첫째, ‘테이블 랜덤 액세스’에 대한 개념이다. 테이블 랜덤 액세스란, ROWID에서 얻은 주소 값을 이용해 테이블 블록을 찾아가는 과정을 말한다. 한 로우를 읽기 위해 해당 로우가 속한 블록(8KB)를 통째로 읽어야 하니 기본적으로 비효율을 내포한다. 블록을 먼저 버퍼 캐시에서 찾고, 찾지 못했을 때 디스크에서 읽게 되는데, 일반적으로 테이블 랜덤 액세스 과정에 발생하는 디스크 I/O Call이 SQL 성능에 가장 큰 영향을 미친다.


ROWID를 이용한 테이블 액세스가 비효율적이긴 하나, 한 로우를 읽을 때는 그래도 가장 빠르다(1위). 하지만, 많은 데이터를 읽을 때는 RBO 우선순위 규칙에서 최하위(15위)를 점하고 있는 Full Table Scan보다 느릴 수도 있다. 많은 데이터를 읽을 때는 버퍼캐시 히트율이 낮을 수밖에 없으므로 I/O Call이 많이 발생하고 그만큼 랜덤 액세스 성능이 느려지게 마련인데, 일정량을 넘어서는 순간 Full Table Scan보다 느려지는 것이다.


프로그램 1처럼 조건절에 ROWID를 직접 기술해서 테이블을 액세스하는 방식이 일반적이지는 않다. 프로그램 2를 처리하는 과정에 인덱스를 사용하게 되면 그림 2처럼 SQL 실행 엔진이 인덱스에서 읽은 ROWID를 이용해 테이블을 액세스하게 되는데, 이것이 ROWID로 테이블을 액세스하는 가장 일반적인 방식이다.


인덱스 ROWID를 이용한 테이블 액세스
[그림2] 인덱스 ROWID를 이용한 테이블 액세스


「최종접속일시 + 접속오류횟수」 순으로 구성된 인덱스를 이용한다면 두 프로그램의 I/O 성능 차이는 거의 없다고 말할 수 있다. ROWID로 테이블 레코드를 랜덤 액세스한다는 관점에서는 일량이 똑같기 때문이다. 하지만, Full Scan이 유리할 정도로 많은 데이터를 처리하는 상황이라면, 프로그램 1은 그렇게 처리할 수 없어 프로그램 2에 비해 성능상 매우 불리하다.


I/O 관점에서 정리하면, 프로그램 1이 2보다 I/O 성능이 나쁘지 않은 경우는 있을 수 있지만, 더 좋은 경우는 없다. 결론적으로, 굳이 프로그램 1처럼 구현할 이유는 없다. (※ 이 대목에서 프로그램 로직이 훨씬 더 복잡한 경우를 가정하고 이의를 제기하지 않길 바란다. 위 프로그램은 테이블명, 컬럼명만 변경했을 뿐, 필자가 어떤 개발 프로젝트에서 발견한 형태 그대로다.)

둘째, ‘데이터베이스 Call’에 대한 개념이다. 많은 데이터를 조회할 때는 ROWID를 이용한 테이블 랜덤 액세스가 성능을 떨어뜨리는 주요인이지만, 때로는 그보다 더 무서운 게 데이터베이스 Call이다. 나중에 데이터베이스 Call에 대해 더 자세히 얘기할 기회가 있겠지만, 프로그램 2는 단 한 번의 UPDATE Call이 발생하는 반면, 프로그램 1은 루프를 도는 횟수만큼 UPDATE Call이 발생한다. 그나마 위 프로그램은 DBMS 내부에서 수행되는 PL/SQL이므로 데이터베이스 Call마다 네트워크를 경유하지는 않는다. 매번 CPU와 메모리를 할당받고 해제하기를 반복하지도 않는다. 만약 JAVA 프로그램이었다면 그런 부하까지 더해지므로 성능은 급격히 나빠진다.

오늘 살펴본 것처럼 데이터베이스 성능은 ‘Call’과 관련이 깊다. 디스크 I/O Call과 데이터베이스 Call을 줄여야 애플리케이션 성능을 높일 수 있다는 사실을 기억하자.


위 글은 무단 복사 및 링크는 금지합니다.
글과 관련한 문의사항이 있으면, 아래로 연락 주시기 바랍니다.

biz@dbian.co.kr

070-4806-8246

www.dbian.co.kr

www.dbian.net




출처 : 한국데이터산업진흥원

제공 : 데이터 전문가 지식포털 DBguide.net