SQL 성능 최적화는 데이터베이스 운영에서 매우 중요한 요소입니다. 특히 대용량 데이터를 처리할 때, SQL 파싱 방식에 따라 성능 차이가 발생할 수 있습니다. 이번 글에서는 SQL 파싱의 두 가지 방식, 즉 ‘소프트 파싱(Soft Parsing)’과 ‘하드 파싱(Hard Parsing)’의 차이점과 그 영향을 중점적으로 살펴보겠습니다. SQL이 실행될 때 어떤 방식으로 파싱되는지, 그리고 각 방식이 데이터베이스 성능에 어떤 영향을 미치는지에 대해 자세히 설명하며, 바인드 변수 사용의 중요성과 I/O 메커니즘을 통해 SQL 튜닝의 필요성도 함께 알아보겠습니다.
소프트 파싱 vs 하드 파싱
SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 ‘라이브러리 캐시(Library Cache)’라고 라이브러리 캐시는 SGA(System Global Area)의 구성요소이며, SGA는 ‘DB Buffer Cache’, ‘Redo Log Buffer’, Library Cache, Data Dictionary Cache 등이 포함된 ‘Shared Pool’로 구성되어 있습니다.
SQL문을 실행하면, DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인합니다. 캐시 단계에서 찾는다면 곧 바로 실행 단계로 넘어가지만, 찾이 못한다면 최적화 단계로 넘어갑니다. SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 ‘소프트 파싱(Soft Parsing)’, 찾는 데 실패하여 최적화 및 로우 소스 생성 단계까지 모두 거치는 것을 ‘하드 파싱(Hard Parsing)’이라고 합니다.
하드 파싱(Hard Parsing)은 왜 ‘하드’하다고 불릴까?
옵티마이저는 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를 많이 사용하는 작업 중 하나입니다. 이렇게 ‘어려운(Hard)’이 있기에 단발성으로 한번 사용하고 버리기엔 매우 비요율 적이라 라이브러리 캐시가 저장하여 사용합니다. 이러한 이유가 라이브러리 캐시가 존재하는 이유입니다.
바인드 변수의 중요성
이름없는 SQL 문제
사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖으며, 컴파일된 상태로 딕셔너리에 저장된고 사용자가 삭제하지 않는 한 영구적으로 보관된다. 실행할 때 라이브러리 캐시에 적재함으로써 여러 사용자가 공유함녀서 재사용한다. 반면, SQL은 이름이 따로 없다. ‘전체 SQL 텍스트’가 이름 역할을 하며, 딕셔너리에 저장하지도 않는다. 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재함으로써 여러 사용자가 공유하면서 재사용한다. 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재된다. SQL은 텍스트 중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생하는 구조다. 그러나 파라미터 Driven 방식으로 SQL 작성을하여 바인드 변수를 사용하면 라이브러리 캐시에 하나의 쿼리만 저장되어 사용할 수 있다.
데이터 저장 구조 및 I/O 메커니즘
SQL이 느린 이유
I/O 튜닝이 곧 SQL 튜닝이라고 해도 과언이 아니다. 왜냐하면 SQL이 느린 이유는 십중팔구 I/O 때문이다. 구체적으로 말하자면, 디스크 I/O 때문이다. OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스 는 잠(Sleep)을 자기 때문에, ‘I/O = 잠(Sleep)이라고 설명하면 적합하다. 프로세스가 작동하지 않고 sleep에 들어가는 이유는 I/O가 가장 대표적이고 절대적으로 많은 비중을 차지한다. 왜냐하면 CPU는 여러 프로세스에 접근할 수 있지만, 특정 시점에는 CPU가 하나의 프로세스에만 접근하기 때문이다. 디스크 I/O 속도는 생각보다 느리기 떄문에 SQL이 느리다. 즉, 디스크 I/O가 SQL 성능을 좌우한다고 해도 과언이 아니다.
데이터베이스 저장구조
데이터를 저장하려면 테이블스페이스를 가장 먼저 생성해야 하며, 테이블스페이스는 세그먼트를 담는 콘테이너로서 여러 개의 데이터파일(디스크 상의 물리적인 OS파일)로 구성된다. 세그먼트틑 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트이며, 테이블 인덱스를 생성할 때 데이터를 어떤 테이블스페이스에 저장할지를 저장한다. 또한, 세그먼트는 여러 익스텐트로 구성되며, 파티션 구조가 아니람녀 테이블도 하나의 세그먼트, 인덱스도 하나의 세그먼트이다. 테이블 또는 인덱스가 파티션 구조라면, 각 파티션이 하나의 세그먼트가 된다. LOB 컬럼은 그 자체가 하나의 세그먼트는 구성하므로 자신이 속한 테이블과 다른 별도 공간에 값을 저장한다. 익스텐트는 공간을 확장하는 단위이다. 테이블이나 익덱슨에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로서 익스텐트를 추가로 할당받는다. 익스텐트는 연속된 블록들의 집합이기도 하며, 연속된 여러 개의 데이터 블록으로 구성된다.익스텐트 단위로 공간을 확장하지만, 사용자가 입력한 레코들 실제로 저장하는 공간은 데이터 블로읻. 참고로 DB2, SQL Server 같은 DB는 브록 대신 페이지(Page)라는 용어를 사용하며 한 블록은 하나의 테이블이 독점한다. 즈, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드이다. 한 익스텐트 또한 하나의 테이블이 독점하며 한 익스텐트에 담긴 블록은 모두 같은 테이블 블록이다. 참고로 MS-SQL Server는 한 익스텐트를 여러 오브젝트가 같이 사용할 수도 있다.
만약, 세그먼트 공간이 부족해지면 테이블 스페이스로부터 익스텐트를 추가로 할당받는다고 했는데, 세그먼트에 할당된 모든 익스텐트가 같은 데이터 파일에 위치하지 않을 수도 있다. 정확히는, 서로 다른 데이터파일에 위치할 가능성이 더 높다. 하나의 테이블스페이스를 여러 데이터파일로 구성하면, 파일 경합을 줄이기 위해 DBMS가 데이터를 가능한 한 여어 데이터 파이로 분산해서 저장하기 때문이다. 블록 익스텐트, 세그먼트, 테이블스페이스, 데이터파일을 간단히 정의하면 다음과 같다.
- 블록: 데이터를 읽고 쓰는 단위
- 익스텐트: 공간을 확장하는 단위, 연속된 블록 집합
- 세그먼트: 데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
- 테이블스페이스: 세그먼트를 담는 콘테이너
- 데이터파일: 디스크 상의 물리적인 OS 파일
블록 단위 I/O
클라우드에 위치한 문서는 파일 단위로 저장하고, 파일 단위로 읽는다. 테이블 세그먼트 단위(예를 들어, 100MB)도 마찬가지다. 익스텐트는 공간을 확장하는 단위라고 했는데, 블록이 바로 DBMS가 데이터를 읽고 쓰는 단위이다. 데이터 I/O 단위가 블록이므로 특정 레코드를 하나 읽고 싶어도 해당 블록을 통째로 읽으며, 심지어 1Byte짜리 컬럼 하나만 읽고 싶어도 블록을 통째로 읽는다. 테이블뿐만 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다.
시퀀셜 액세스 vs 랜덤 액세스
테이블 또는 인덱스 블록을 액세스하는(=읽는) 방식으로는 시퀀셜 액세스와 랜덤 액세스, 두 가지가 있다. 첫째, 시퀀셜(Sequential) 액세스는 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다. 인덱스 리프 블록끼리 앞뒤를 가리키는 주소 값을 통해 논리적으로 서로 연결되어 있으며, 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식이 시퀀셜 액세스이다. 마치 Linked Array와 비슷하다. 오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵(map)으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫 번쨰 블록 주소 값을 갖으며, 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번쨰 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면, 그것이 곧 Full Table Scan이다. 둘째, 랜덤(Random) 액세스는 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식이다.
결론
SQL 최적화 과정에서 소프트 파싱과 하드 파싱의 차이는 시스템 성능에 큰 영향을 미칩니다. 하드 파싱은 많은 리소스를 소모하기 때문에, 가능한 소프트 파싱을 활용하는 것이 성능 개선에 필수적입니다. 이를 위해 바인드 변수를 사용하는 것이 좋으며, 데이터베이스 내부 구조와 I/O 메커니즘에 대한 이해도 중요합니다. SQL 튜닝은 단순히 쿼리 성능을 높이는 것에 그치지 않고, 데이터베이스의 전반적인 효율성을 향상시켜 최적의 성과를 내도록 돕는 중요한 작업입니다. 따라서, SQL 최적화는 성능 최적화를 넘어 안정적인 데이터베이스 운영을 위한 필수적인 과정입니다.