느낀점 :

SQL 튜닝은 단순히 성능을 향상하는 것이 아니라, 근본적으로 성능을 저하시킬 수 있는 요소를 이해하는 것부터 시작해야 한다는 점을 배웠다. 특히 I/O가 왜 시간이 오래 걸리는지, 그리고 이를 어떻게 개선할 수 있는지에 대한 학습이 중요했다.

I/O 성능의 핵심적인 요소로 인덱스, 풀 스캔, 블록 단위 I/O, 싱글 블록 vs 멀티 블록 I/O 등의 개념을 배운 후, 이를 기반으로 최적화 방법을 차근차근 이해할 수 있었다. 단순히 실행 계획을 보고 특정 인덱스를 추가하는 식의 접근이 아니라, SQL이 실제로 데이터를 어떻게 읽고 처리하는지부터 이해하는 것이 필수적이라는 것을 깨달았다.

전공 시간에 배웠을 것 같지만 SGA(System Global Area)에 대한 개념이 새롭게 다가왔다. 세그먼트, 익스텐트, 블록 단위로 데이터가 저장되고, 테이블을 읽을 때는 세그먼트 내의 블록을 가져오며, 한 번에 읽을 수 있는 최대 크기가 1MB라는 점이 흥미로웠다.

또한, SQL 튜닝의 핵심이 결국 I/O를 줄이는 것이라는 점을 실감했다. I/O가 발생할 때 프로세스가 대기 상태로 들어가면서 CPU를 사용하지 못하는데, 이를 줄이는 것이 곧 성능 최적화로 이어진다. 논리적 I/O와 물리적 I/O를 구분하고, 캐시 히트율을 높이거나 멀티 블록 I/O를 활용하는 등의 방법을 통해 SQL을 튜닝하는 원리를 더욱 깊이 이해할 수 있었다.

특히, 인덱스가 항상 정답이 아니라는 점이 중요했다.
많은 데이터를 조회할 때는 오히려 Table Full Scan이 더 효율적일 수도 있다는 점을 배웠다. 인덱스가 랜덤 액세스를 유발하면서 Single Block I/O를 발생시키는 경우 성능이 떨어질 수 있기 때문에, 읽어야 할 데이터 양에 따라 Full Scan과 Index Scan을 적절히 선택해야 한다는 점을 깨달았다.

마지막으로, SQL이 실행될 때 실제로 어떤 과정이 일어나는지 (파싱, 옵티마이저의 역할, 실행 계획의 선택 방식 등)에 대해 상세히 학습하면서, SQL을 튜닝할 때 무조건적인 방법론이 아닌, 데이터베이스 내부 동작을 이해하고 최적의 방법을 찾아가는 것이 중요하다는 점을 다시금 느꼈다.

학습 :

1.1 SQL 파싱과 최적화

1.1.1 구조적, 집합적, 선언적 질의 언어

SQL은 "Structured Query Language"의 줄임말이다. 말 그대로 구조적 질의 언어다.

• SQL is designed for a specific purpose: to query data contained in a relational database
• SQL is a set-based, declarative query language, not an imperative language such as C or BASIC

원하는 결과 집합을 구조적, 집합적으로 선언하지만, 그 결과집합을 만드는 과정은 절차적일 수밖에 없다. 즉, 프로시저가 필요한데, 그런 프로시저를 만들어 내는 DBMS 내부 엔진이 바로 SQL 옵티마이저다. 옵티마이저가 프로그래밍을 대신 해주는 셈이다.

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 'SQL 최적화'라고 한다.

1.1.2 SQL 최적화

SQL을 실행하기 전 최적화 과정을 세분화하면 아래와 같다.

1. SQL 파싱

사용자로부터 SQL을 전달받으면 가장 먼저 SQL 파서Parser가 파싱을 진행한다. SQL 파싱을 요약하면 아래와 같다.

• 파싱 트리 생성 : SQL 문을 이루는 개별 구성요소를 분석해서 파싱 트리 생성
• Syntax 체크 : 문법적 오류가 없는지 확인, 예를 들어, 사용할 수 없는 키워드를 사용했거나 순서가 바르지 않거나 누락된 키워드가 있는지 확인
• Semantic 체크 : 의미상 오류가 없는지 확인. 예를 들어, 존재하지 않는 테이블 또는 칼럼을 사용했는지, 사용한 오브젝트에 대한 권한이 있는지 확인

2. SQL 최적화

그다음 단계가 SQL 최적화이고, 옵티마이저Optimizer가 그 역할을 맡는다. SQL 옵티마이저는 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성해서 비교한 후 가장 효율적인 하나를 선택한다. 데이터베이스 성능을 결정하는 가장 핵심적인 엔진이다.

3. 로우 소스 생성

SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계다. 로우 소스 생성기Row-Source Generator가 그 역할을 맡는다.

1.1.3 SQL 옵티마이저

1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아낸다.
2. 데이터 딕셔너리Data Dictionary에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
3. 최저 비용을 나타내는 실행계획을 선택한다.

1.1.4 실행계획과 비용

SQL 옵티마이저는 자동차 내비게이션과 여러모로 흡사하다. 일례로, 경로 요약이나 모의 주행 같은 기능이 그렇다. 경로를 검색하고 나서 이동 경로를 미리 확인하는 기능이며, 내비게이션이 선택한 경로가 마음에 들지 않으면 검색모드를 변경하거나 경유지를 추가해서 운전자가 원하는 경로로 바꿀 수 있다.

DBMS에도 'SQL 실행경로 미리보기' 기능이 있다. 실행계획Execution Plan이 바로 그것이다. SQL 옵티마이저가 생성한 처리절차를 사용자가 확인할 수 있게 아래와 같이 트리 구조로 표현한 것이 실행계획이다.

미리보기 기능을 통해 자신이 작성한 SQL이 테이블을 스캔하는지 인덱스를 스캔하는지, 인덱스를 스캔한다면 어떤 인덱스인지를 확인할 수 있고, 예상과 다른 방식으로 처리된다면 실행결과를 변경할 수 있다.

옵티마이저가 T_X01 인덱스를 선택한 근거가 비용임을 알 수 있다. 비용Cost은 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값이다.

SQL 실행계획에 표시되는 Cost도 어디까지나 예상치다. 실행경로를 위해 옵티마이저가 여러 통계정보를 활용해서 계산해낸 값이다. 실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이가 난다.

1.1.5 옵티마이저 힌트

자동차 네비게이션이 보편적으로 좋은 선택을 하지만, 그 선택이 항상 최선은 아니다. 내비게이션 두 개를 동시에 사용할 때, 서로 다른 길로 안내하는 것을 보면 알 수 있다. SQL 옵티마이저도 대부분 좋은 선택을 하지만 완벽하진 않다. SQL이 복잡할수록 실수할 가능성도 크다.

자율이냐 강제냐, 그것이 문제다

어떤 방식이 옳은지는 애플리케이션 환경에 따라 다르다. 통계정보나 실행 환경 변화로 인해 옵티마이저가 가끔 실수하더라도 별문제가 없는 시스템이 있는가 하면, 옵티마이저의 작은 실수가 기업에 큰 손실을 끼치는 시스템도 있다. 후자처럼 중대한 시스템이라면, 가끔 실수가 있더라도 옵티마이저의 자율적 판단에 맡기자는 말을 감히 할 수 없다. 기왕에 힌트를 쓸 거면, 빈틈없이 기술해야 한다.

1.2 SQL 공유 및 재사용

1.2.1 소프트 파싱 vs 하드 파싱

SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 '라이브러리 캐시Libraray Cache 라고 한다. 라이브러리 캐시는 SGA 구성요소다. SGASystem Global Area 는 서버 프로세스와 백그라운드 프로세스가 공통으로 엑세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간이다.

사용자가 SQL 문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인한다. 캐시에서 찾으면 곧바로 실행 단계로 넘어가지만, 찾지 못하면 최적화 단계를 거친다. SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 '소프트 파싱 Soft Parsing ' 이라 하고, 찾는 데 실패해 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 '하드 파싱Hard Parsing' 이라고 한다.

SQL 최적화 과정은 왜 하드Hard한가

옵티마이저가 SQL을 최적화할 때도 데이터베이스 사용자들이 보통 생각하는 것보다 훨씬 많은 일을 수행한다. 예를 들어, 다섯 개 테이블을 조인하는 쿼리문 하나를 최적화하는 데도 무수히 많은 경우의 수가 존재한다. 조인 순서만 고려해도 120(=5!)가지다. 여기에 NL 조인, 소트 머지 조인, 해시 조인 등 다양한 조인 방식이 있다. 테이블 전체를 스캔할지, 인덱스를 이용할지를 결정해야 하고, 인덱스 스캔에도 Index Range Scan, Index Unique Scan, Index Full Scan , Index Fast Full Scan, Index Skip Scan등 다양한 방식이 제공된다. 게다가, 사용할 수 있는 인덱스가 테이블당 하나뿐이겠는가.

SQL 옵티마이저는 순식간에 엄청나게 많은 연산을 한다. 그 과정에 옵티마이저가 사용하는 정보는 다음과 같다.

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 오브젝트 통계 : 테이블 통계, 인덱스 통계, (히스토그램을 포함한) 컬럼 통계
• 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
• 옵티마이저 관련 파라미터

하나의 쿼리를 수행하는 데 있어 후보군이 될만한 무수히 많은 실행경로를 도출하고, 짧은 순간에 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단하는 과정은 결코 가벼울 수 없다 데이터베이스에서 이루어지는 처리 과정은 대부분 I/O 작업에 집중되는 반면, 하드 파싱은 CPU를 많이 소비하는 몇 안 되는 작업 중 하나다.
이렇게 어려운 작업을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버린다면 이만저만한 비효율이 아니다. 라이브러리 캐시가 필요한 이유가 바로 여기에 있다.

1.2.2 바인드 변수의 중요성

이름없는 SQL 문제

사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖는다. 컴파일한 상태로 딕셔너리에 저장되며, 사용자가 삭제하지 않는 한 영구적으로 보관된다. 실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다.

반면, SQL은 이름이 따로 없다. 전체 SQL 텍스트가 이름 역할을 한다. 딕셔너리에 저장하지도 않는다. 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재된다.

DBMS에서 수행되는 SQL이 모두 완성된 SQL은 아니며, 특히 개발 과정에는 수시로 변경이 일어난다. 일회성ad hoc SQL도 많다. 일회성 또는 무효화된 SQL까지 모두 저장하려면 많은 공간이 필요하고, 그만큼 SQL을 찾는 속도도 느려진다. 오라클, SQL Server 같은 DBMS가 SQL을 영구 저장하지 않는 쪽을 선택한 이유다.

공유 가능 SQL

라이브러리 캐시에서 SQL을 찾기 위해 사용하는 키 값이 'SQL 문 그 자체'이므로 아래는 모두 다른 SQL이다. 의미적으로는 모두 같지만, 실행할 때 각각 최적화를 진행하고 라이브러리 캐시에서 별도 공간을 사용한다.

내부 처리 루틴이 같은 경우라면 프로시저를 여러 개 생성할 것이 아니라 파라미터로 받는 프로시저 하나를 공유하면서 재사용하는 것이 마땅하다. 이처럼 파라미터 Driven 방식으로 SQL을 작성하는 방법이 제공되는데, 바인드 변수가 바로 그것이다.

1.3 데이터 저장 구조 및 I/O 매커니즘

I/O 튜닝이 곧 SQL 튜닝이라고 해도 과언이 아니다. SQL 튜닝 원리를 제대로 이해하려면 I/O에 대한 이해가 중요할 수밖에 없다. SQL 튜닝을 본격적으로 시작하기에 앞서 데이터 저장 구조, 디스크 및 메모리에서 데이터를 읽는 매커니즘을 차례로 살펴보자.

1.3.1 SQL이 느린 이유

SQL이 느린 이유는 십중팔구 I/O 때문이다. 구체적으로 말해, 디스크 I/O 때문이다.

그렇다면, I/O란 무엇일까? I/O 무엇이냐고 후배 개발자가 묻는다면, 어떻게 설명하겠는가? 필자는 'I/O = 잠Sleep이라고 설명한다.' OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 잠을 자기 때문이다. 프로세스가 일하지 않고 잠을 자는 이유는 여러 가지가 있지만, I/O가 가장 대표적이고 절대 비중을 차지한다.

프로세스Process는 '실행 중인 프로그램'이며, 생명주기를 갖는다. 즉, 생성new 이후 종료terminated 전까지 준비ready 와 실행running과 대기waiting 상태를 반복한다. 실행 중인 프로세스는 interrupt에 의해 수시로 실행 준비 상태Runnable Queue로 전환했다가 다시 실행 상태로 전환한다. 여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 이런 매커니즘이 필요하다.

interrupt 없이 열심히 일하던 프로세스도 디스크에서 데이터를 읽어야 할 땐 CPU를 OS에 반환하고 잠시 수면waiting 상태에서 I/O가 완료되기를 기다린다. 정해진 OS 함수를 호출I/O Call하고 CPU를 반환한 채 알람을 설정하고 대기 큐Wait Queue에서 잠을 자는 것이다. 열심히 일해야 할 프로세스가 한가하게 잠을 자고 있으니 I/O가 많으면 성능이 느릴 수밖에 없다.

I/O Call 속도는 Single Block I/O 기준으로 평균 10ms쯤된다. 초당 100블록쯤 읽는 셈이다. 큰 캐시를 가진 SAN 스토리지는 4~8ms쯤 된다. 초당 125~250 블록쯤 익는 셈이다. SSD까지 활용하는 최근 스토리지는 1~2ms, 즉 초당 500 ~ 1,000 블록즘 읽는다.

Block I/O 방식으로 10,000 블록을 읽는다면, 가장 최신 스토리지에서도 10초 이상 기다려야 한다. 전반적으로 I/O 튜닝이 안 된 시스템이면, 수 많은 프로세스에 의해 동시다발적으로 발생하는 I/O Call 때문에 디스크 경합이 심해지고 그만큼 대기 시간도 늘어난다. 10초가 아니라 20초를 기다려야 할 수도 있다는 뜻이다. SQL이 느린 이유가 바로 여기에 있다. 디스크 I/O 때문이다. 디스크 I/O가 SQL 성능을 좌우한다고 해도 과언이 아니다.

I/O 매커니즘을 자세히 설명하기에 앞서 데이터베이스 저장 구조부터 살펴보자.

1.3.2 데이터베이스 저장 구조

데이터를 저장하려면 먼저 테이블스페이스를 생성해야 한다. 테이블스페이스는 세그먼트를 담는 콘테이너로서, 여러 개의 데이터파일(디스크 상의 물리적인 OS파일)로 구성된다.

테이블스페이스를 생성했으면 세그머트를 생성한다. 세그먼트는 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트다. 테이블, 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 지정한다.

세그먼트는 여러 익스텐트로 구성된다. 파티션 구조가 아니라면 테이블도 하나의 세그먼트요, 인덱스도 하나의 세그먼트다. 테이블 또는 인덱스가 파티션 구조라면, 각 파티션이 하나의 세그먼트가 된다. LOB 컬럼은 그 자체가 하나의 세그먼트를 구성하므로 자신이 속한 테이블과 다른 별도 공간에 값을 저장한다.

익스텐트는 공간을 확장하는 단위다. 테이블이나 인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로부터 익스텐트를 추가로 할당받는다. 익스텐트는 연속된 블록들의 집합이기도 하다. 익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코드를 실제로 저장하는 공간은 데이터 블록이다. 참고로, DB2, SQL Server 같은 DBMS는 블록 대신 페이지page라는 용어를 사용한다. 한 블록은 하나의 테이블이 독점한다. 즉, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드다.

한 익스텐트도 하나의 테이블이 독점한다. 즉, 한 익스텐트에 담긴 블록은 모두 같은 테이블블록이다. 참고로, MS-SQL Server는 한 익스텐트를 여러 오브젝트가 같이 사용할 수도 있다.

테이블스페이스, 세그먼트, 익스텐트, 블록 간 관계뿐만 아니라, 이들과 데이터파일 간의 관계도 알아둘 필요가 있다. 세그먼트 공간이 부족해지면 테이블스페이스로부터 익스텐트를 추가로 할당받는다고 했는데, 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지않을 수 있다. 아니, 서로 다른 데이터파일에 위치할 가능성이 더 높다. 하나의 테이블스페이스를 여러 데이터파일로 구성하면, 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 한 여러 데이터파일로 분산해서 저장하기 때문이다.

익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아니라는 사실을 알 수 있다.

DBA (Data Block Address)

모든 데이터 블록은 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값을 갖는다. 이 주소값을 DBA(Data Block Address)라고 부른다. 데이터를 읽고 쓰는 단위가 블록이므로 데이터를 읽으러면 먼저 DBA부터 확인해야 한다.

인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID를 이용한다. ROWID는 DBA + 로우 번호(블록 내 순번)로 구성되므로 이를 분해하면 읽어야 할 테이블 레코드가 저장된 DBA를 알 수 있다.

테이블을 스캔할 때는 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용한다. 익스텐트 맵을 통해 각 익스텐트의 첫 번째 블록 DBA를 알 수 있다. 익스텐트는 연속된 블록 집합이므로 테이블을 스캔할 때는 첫 번째 블록 뒤에 연속해서 저장된 블록을 읽으면 된다.

블록, 인스텐트, 세그먼트, 테이블스페이스, 데이터파일을 간단히 정의하면, 다음과 같다.

• 블록 : 데이터를 읽고 쓰는 단위
• 익스텐트 : 공간을 확장하는 단위, 연속된 블록 집합
• 세그먼트 : 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
• 테이블스페이스 : 세그먼트를 담는 콘테이너
• 데이터파일 : 디스크 상의 물리적인 OS 파일

1.3.3 블록 단위 I/O

클라우드에 위치한 문서는 파일 단위로 저장하고, 파일 단위로 읽는다.
파일 단위(예를 들어, 2GB)로 매번 데이터를 읽고 쓰는 건 상상하기 어렵다. 테이블 세그먼트 단위(예를 들어, 100MB)도 마찬가지다. 익스텐트는 공간을 확장하는 단위라고 했다. 블록이 바로 DBMS가 데이터를 읽고 쓰는 단위다.

데이터 I/O 단위가 블록이므로 특정 레코드 하나를 읽고 싶어도 해당 블록을 통재로 읽는다. 심지어 1Byte 짜리 칼럼 하나만 읽고 싶어도 블록을 통째로 읽는다. 테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.

1.3.4 시퀀셜 엑세스 vs 램덤 액세스

테이블 도는 인덱스 블록을 엑세스하는(=읽는) 방식으로는 시퀀셜 엑세스와 랜덤 액세스, 두 가지가 있다.
첫째, 시퀀셜Sequencial 엑세스는 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다. 인덱스 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결돼 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 엑세스다.

테이블 블록 간에는 서로 논리적인 연결고리를 갖고 있지 않다. 테이블은 어떻게 시퀀셜 방식으로 엑세스할까?
오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵map으로 관리한다.

둘째, 랜덤random 엑세스는 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근(=touch)하는 방식이다.

1.3.5 논리적 I/O vs 물리적 I/O

DB 버퍼캐시

다시 강조하지만, 디스크 I/O가 SQL 성능을 결정한다. SQL을 수행하는 과정에 계속해서 데이터 블록을 읽는데, 자주 읽는 블록을 매번 디스크에서 읽는 것은 매우 비효율적이다. 모든 DBMS에 데이터 캐싱 메커니즘이 필수인 이유다.

데이터를 캐싱하는 'DB 버퍼캐시'도 SGA의 가장 중요한 구성요소 중 하나다. 라이브러리 캐시가 SQL과 실행계획, DB 저장형 함수/프로시저 등을 캐싱하는 '코드 캐시'라고 한다면, DB 버퍼캐시는 '데이터 캐시'라고 할 수 있다. 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다.

논리적 I/O vs 물리적 I/O

논리적 블록 I/O는 SQL을 처리하는 과정에 발생한 총 블록 I/O를 말한다. 일반적으로 메모리상의 버퍼 캐시를 경유하므로 메모리 I/O가 곧 논리적 I/O라고 생각해도 무방하다. (메모리를 경유하지 않는 Direct Path I/O를 고려하면, 논리적 I/O는 메모리 I/O와 Direct I/O를 더한 개념이다.)

물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O를 말한다. SQL 처리 도중 일겅야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 엑세스하므로 논리적 블록 I/O 중 일부를 물리적으로 I/O 한다.
메모리 I/O는 전기적 신호인 데 반해, 디스크 I/O는 엑세스 암(Arm)을 통해 물리적 작용이 일어나므로 메모리 I/O에 비해 상당히 느리다. 보통 10,000배쯤 느리다. 디스크 경합이 심할 때는 더 느리다.

왜 논리적 I/O인가?

데이터베이스 세계에서 논리적 일량과 물리적 일량을 정의해보자. SQL을 수행하려면 데이터가 담긴 블록을 읽어야 한다. SQL이 참조하는 테이블에 데이터를 입력하거나 삭제하지 않는 상황에서 조건절에 같은 변수 값을 입력하면, 아무리 여러 번 실행해도 매번 읽는 블록 수는 같다. SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O다.

Direct Path Read 방식으로 읽는 경우를 제외하면 모든 블록은 DB 버퍼캐시를 경유해서 읽는다. 따라서 논리적 I/O 횟수는 일반적으로 DB 버퍼캐시에서 블록을 읽은 횟수와 일치한다. 논리적 I/O가 메모리 I/O와 같은 개념은 아니지만, 결과적으로 수치는 같다.

DB 버퍼캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O가 물리적 I/O다. 데이터 입력이나 삭제가 없어도 물리적 I/O는 SQL을 실행할 대마다 다르다. 첫 번째 실행할 대보다 두 번째 실행할 때 줄어들고, 세 번째 실행할 땐 더 줄어든다. 연속해서 실행하면 DB 버퍼캐시에서 해당 테이블 블록의 점유율이 점점 높아지기 때문이다. 한참 후에 다시 실행하면 반대로 물리적 I/O가 늘어난다. DB 버퍼캐시가 다른 테이블 블록으로 채워지기 때문이다.

블록 I/O 적정량

실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 한다는 사실이다.

• 물리적 I/O = 논리적 I/O X (100% - BCHR)
• BCHRBuffer Cache Hit Ratio

논리적 I/O는 어떻게 줄일 수 있을까? SQL을 튜닝해서 읽는 총 블록 개수를 줄이면 된다. 논리적 I/O는 항상 일정하게 발생하지만, SQL 튜닝을 통해 줄일 수 있는 통제 가능한 내생변수다. 논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.

블록을 읽을 때는 해당 블록을 먼저 버퍼캐시에서 찾아보고 없을 때만 디스크에서 읽는다. 이때도 디스크에서 곧바로 읽는 게 아니라 먼저 버퍼캐시에 적재하고서 읽는다. 따라서 DB 버퍼캐시에서 읽은 블록에는 디스크에서 읽은 블록이 이미 포함돼 있다.

1.3.6 Single Block I/O vs Multiblock I/O

메모리 캐시가 클수록 좋지만, 데이터를 모두 캐시에 적재할 수는 없다. 비용적인 한계, 기술적인 한계 때문에 전체 데이터 중 일부만 캐시에 적재해서 읽을 수 있다.
캐시에서 찾지 못한 데이터 블록은 I/O Call을 통해 디스크에서 DB 버퍼캐시로 적재하고서 읽는다. I/O Call 할 때, 한 번에 한 블록씩 요청하기도 하고, 여러 블록씩 요청하기도 한다. 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식을 'Single Block I/O'라고 한다. 많은 벽돌을 실어 나를 때 손수레를 이용하는 것처럼 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식을 'Multiblock I/O'라고 한다.

인덱스를 이용할 때는 기본적으로 인덱스와 데이터 블록 모드 Single Block I/O 방식을 사용한다. 구체적으로 아래 목록이 Single Block I/O 대상 오퍼레이션이다. 인덱스는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적이다.

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

반대로, 많은 데이터 블록을 읽을 때는 Multiblock I/O 방식이 효율적이다. 그래서 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식을 사용한다. 테이블이 클수록 Multiblock I/O 단위도 크면 좋다. 이유는 다른 데 있지 않다. 프로세스가 잠자는 횟수를 줄여주는 데 있다.

읽고자 하는 블록을 DB 버퍼캐시에서 찾지 못하면 해당 블록을 디스크에서 읽기 위해 I/O Call을 한다. 그동안 프로세스는 대기 큐Wait Queue에서 잠을 잔다. 대용량 테이블이면 수많은 블록을 디스크에서 읽는 동안 여러 차례 잠을 잘 텐데, 기왕에 잠을 자려면 한꺼번에 많은 양을 요청해야 잠자는 횟수를 줄이고 성능을 높일 수 있다. 대용량 테이블을 Full Scan 할 때 Multiblcok I/O 단위를 크게 설정하면 성능이 좋아지는 이유다.

정리하면, Multiblock I/O는 캐시에서 찾지 못한 특정 블록을 읽으려고 I/O Call 할 때 디스크 상에 그 블록과 '인접한' 블록들을 한거번에 읽어 캐시에 미리 적재하는 기능이다. DBMS 블록 사이즈가 얼마건 간에 OS 단에서는 보통 1MB 단위로 I/O를 수행한다(OS마다 다름). 한 번 I/O 할 때 1MB 크기의 '손수레'을 사용하는 셈이다. 테이블 전체 블록을 읽을 때는 손수레에 한 번에 담을 수 있는 만큼 최대한 많이 담아야 유리하다.

조금 더 부연해 설명하면, '인접한 블록'이란 같은 익스텐트에 속한 블록을 의미한다. Multiblock I/O 방식으로 읽더라도 익스텐트 경계를 넘지 못한다는 뜻이다.

Multiblock I/O 중간에 왜 Single Block I/O가 나타나는가?

Multiblock I/O 중간에 Single Block I/O가 나타나는 이유는 인덱스 스캔과 테이블 접근 방식의 차이 때문이다.

4. 인덱스 범위 스캔(Index Range Scan) 사용 시

   • 인덱스를 통해 필요한 블록을 찾고, 해당 블록을 Single Block I/O로 개별적으로 읽음
   • 즉, 랜덤 액세스가 발생하여 Multiblock I/O 대신 Single Block I/O가 수행됨

5. 테이블 Full Table Scan(FTS) 시 Multiblock I/O 수행

   • 연속된 블록을 한 번에 읽기 때문에 Multiblock I/O 사용

따라서, SQL 실행 계획에서 인덱스 기반 조회와 테이블 스캔이 함께 수행될 경우 중간에 Single Block I/O가 섞일 수 있다.

1.3.7 Table Full Scan vs Index Range Scan

테이블에 저장된 데이터를 읽는 방식은 두 가지다. 테이블 전체를 스캔해서 읽는 방식과 인덱스를 이용해서 읽는 방식이다. 전자를 'Table Full Scan'이라고 부른다는 것은 주지의 사실이다. 후자는 , 제목에 'Index Range Scan'이라고 간략히 표현했지만, 보통 '인덱스를 이용한 테이블 엑세스'라고 표현한다.

Table Full Scan은 말 그대로 테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식이다. 인덱스를 이용한 테이블 엑세스는 '일정량'을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식이다. ROWID는 테이블 레코드가 디스크상에 어디에 저장됐는지를 가리키는 위치 정보다.

인덱스를 이용하는데 왜 성능이 더 느릴까? 앞서 우리는 시퀀셜 엑세스와 랜덤 엑세스, Single Block I/O와 Multiblock I/O 개념을 살펴봤다. 이런 I/O 매커니즘 관점에서 Full Scan과 Index Range Scan의 본질을 해석해 보자. 그러면 인덱스를 이용해 많은 데이터를 읽을 때 왜 성능이 느린지 쉽게 이해할 수 있다.

Table Full Scan은 시퀀셜 엑세스와 Multiblock I/O 방식으로 디스크 블록을 읽는다. 한 블록에 속한 모든 레코드를 한 번에 읽어 들이고, 캐시에서 못 찾으면 '한 번의 수면(I/O Call)을 통해 인접한 수십 ~ 수백 개 블록을 한꺼번에 I/O하는 매커니즘'이다. 이 방식을 사용하는 SQL은 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다.

시퀀셜 엑세스와 Multiblock I/O가 아무리 좋아도 수십 ~ 수백 건의 소량 데이터 찾을 대 수백만 ~ 수천만 건 데이터를 스캔하는 건 비효율적이다. 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 한다.

Index Range Scan을 통한 테이블 엑세스는 램덤 엑세스와 Single Block I/O 방식으로 디스크 블록을 읽는다. 캐시에서 블록을 못 찾으면, '레코드 하나를 읽기 위해 매번 잠을 자는 I/O 매커니즘'이다. 따라서 많은 데이터르 읽을 때는 Table Full Scan보다 불리하다.

데이터베이스를 효과적으로 이용하는 데 있어 인덱스의 중요성은 아무리 강조해도 지나치지 않다. 하지만 , 인덱스에 대한 맹신은 금물이다. 인덱스가 항상 옳은 것은 아니며, 바꿔 말해 Table Full Scan이 항상 나쁜 것도 아니다. 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐이므로 모든 성능 문제를 인덱스로 해결하려 해선 안 된다. 읽을 데이터가 일정량을 넘으면 인덱스보다 Table Full Scan이 유리하다.

1.3.8 캐시 탐색 매커니즘

Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다. 구체적으로, 아래 오퍼레이션은 모두 버퍼캐시 탐색 과정을 거친다.

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브렌치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
• 테이블 블록을 Full Scan 할 때

버퍼캐시 탐색 매커니즘을 설명하기에 앞서 버퍼캐시 구조부터 살펴보자. DBMS는 버퍼캐시를 해시 구조로 관리한다. 해시함수로 모듈러(mod) 함수를 사용하는 경우를 표현하고 있다. 실제로는 훨씬 더 정교한 알고리즘을 사용하지만, 여기서는 5로 나누었을 때의 나머지 값을 반환하는 모듈러 함수로 캐시 매커니즘을 설명하겠다.

버커캐시에서 블록을 찾을 때 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터Pointer로 버퍼 블록을 엑세스하는 방식을 사용한다. 해시 구조의 특징을 요약하면 다음과 같다.

• 같은 입력 값은 항상 동일한 해시 체인(=버킷)에 연결될 수 있음
• 다른 입력 값이 동일한 해시 체인에 연결될 수 있음
• 해시 체인 내에서는 정렬이 보장되지 않음

메모리 공유자원에 대한 엑세스 직렬화

버퍼캐시는 SGA 구성요소이므로 버퍼캐시에 캐싱된 버퍼블록은 모두 공유자원이다. 공유자원은 말 그대로 모두에게 권한이 있기 때문에 누구나 접근할 수 있다. 문제는 하나의 버퍼블록을 두 개 이상 프로세스가 '동시에' 접근하려고 할 때 발생한다. 동시에 접근하면 블록 정합성에 문제가 생길 수 있기 때문이다.

따라서 자원을 공유하는 것처럼 보여도 내부에선 한 프로세스씩 순차적으로 접근하도록 구현해야 하며, 이를 위해 직렬화serialization 매커니즘이 필요하다 이해하기 쉽게 표현하면, '줄 세우기'다.

공유캐시의 특정 자원을 두 개 이상 프로세스가 같이 사용할 수 있나? 그럴 수 없다. 같이 사용하는 것처럼 보이지만, 특정 순간에는 한 프로세스만 사용할 수 있다. 그 순간 다른 프로세스는 줄 서서 기다려야 한다. 이런 줄서기가 가능하도록 지원하는 매커니즘이 래치Latch다

캐시버퍼 체인 래치

대량의 데이터를 읽을 때 모든 블록에 대해 해시 체인을 탐색한다. DBAData Block Address를 해시 함수에 입력하고 거기서 반환된 값으로 스캔해야 할 해시 체인을 찾는다. 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 변경하는 일이 생기면 곤란하다. 이를 막기 위해 해시 체인 래치가 존재한다. 0부터 4까지 다섯 개 체인 앞쪽에 자물쇠가 있다고 생각하면 된다. 자물쇠를 열 수 있는 키key를 획득한 프로세스만이 체인으로 진입할 수 있다.

SGA를 구성하는 서브 캐시마다 별도의 래치가 존재하는데, 버퍼캐시에는 캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등이 작동한다. 빠른 데이터베이스를 구현하려면 버퍼캐시 히트율을 높여야 하지만 ,캐시 I/O도 생각만큼 빠르지 않을 수 있다. 이들 래치에 의한 경합이 생길 수 있기 때문이다.

캐시버퍼 체인뿐만 아니라 버퍼블록 자체에도 직렬화 매커니즘이 존재한다. 바로 '버퍼 Lock'이다. 이런 직렬화 매커니즘에 의한 캐시 경합을 줄이려면, SQL 튜닝을 통해 쿼리 일량(논리적 I/O)자체를 줄여야 한다.