SQL 튜닝 - SQL 실행 과정

개요

이번 글에서는 SQL 문장을 실행할 때 Oracle 서박 따르는 단계들에 대해 다룬다. 쿼리를 실행하면 Oracle 서버는 문장을 실행하기 전에 몇가지 중요한 단계를 거친다. 각 단계를 짚으며 이해를 해보도록 하겠다.

 

1. 구문 검사(Syntax Check)

쿼리를 실행할 떄 서버가 가장 먼저 하는 일은 쿼리의 구문을 검사하는 일이다. 이 단계에서는 SQL 문장이 구문적으로 올바른지 확인한다. 구문 오류가 있으면 서버는 즉시 오류를 반환하고 실행을 종료한다.

2. 의미 검사(Semantic Check)

다음 단계는 시멘틱 체크이다. 여기서는 데이터베이스에 테이블이 존재하는지, SELECT * 문을 사용할 때 어떤 컬럼이 있는지 등을 확인한다. 이러한 정보는 데이터 딕셔너리 캐시에 저장되어 있으며 여기에서 정보를 가져온다.

3. 권한 검사(Privilege Check)

서버는 쿼리를 실행하는 사용자가 적절한 권한을 가지고 있는지 확인한다. 이 정보 역시 데이터 사전 캐시에 저장되어 있다. 사용자가 권한이 없으면 오류를 반환하고 실행을 종료한다.

4. 개인 SQL 영역 할당(Private SQL Area Allocation)

쿼리를 처리하고 커서를 사용하여 메모리에 저장하기 위해 사용자의 PGA(Program Global Area)에 개인 SQL 영역을 생성한다. 이 영역에 쿼리의 결과가 일시적으로 저장된다.

5. 실행 계획 찾기 (Finding an Execution Plan)

서버는 실행 계획을 찾는다. 이전에 유사한 쿼리가 실행된 적이 있다면 실행 계획이 공유 SQL 영역에 저장되 있다. 서버는 먼저 기존 실행 계획을 찾는다.

소프트 파싱과 하드 파싱

• 소프트 파싱: 실행 계획이 공유 풀에 이미 있는 경우, 이를 소프트 파싱 또는 라이브러리 캐시 히트라고 한다. 이는 성능을 크게 향상시킨다.
• 하드 파싱: 실행 계획이 없는 경우 서버는 하드 파싱 또는 라이브러리 캐시 미스를 수행한다. 하드 파싱을 새로운 실행 계획을 생성하는 과적으로 더 많은 리소스를 소비한다.

6. 최적화(Optimization)

최적화는 성능 튜닝에서 가장 중요한 단계이다. 옵티마이저는 쿼리를 입력받아 가장 효율적인 실행 계획을 생성한다. 다양한 실행 계획 중에서 가장 빠른 방법을 선택한다. 옵티마이저는 보통 1초 이 내에 최적의 실행 계획을 선택한다.

7. 행 소스 생성 (Row Source Generation)

실행 계획이 생성되면 행 소스 생성 단계가 시작된다. 여기서는 실행 계획을 기반으로 실제로 데이터를 어떻게 가져올지 데이터를 어떻게 조작할지 결정한다.

8. 실행 및 결과 반환(Execution And Result Retrun)

행 소스 생성 단계를 완료하면 해당 단계를 따라 쿼리를 실행하고 결과를 사용자에게 반환한다. 소프트 파싱의 경우 바로 이 단계로 넘어온다.

9. 결과 캐시(Result Cache)

결과 캐시는 동일한 쿼리의 반복 실행에 따른 성능 향상을 위해 결과를 메모리에 저장하는 기능이다. 다음번에 동일한 쿼리가 실행될 때 서버는 디스크 I/O를 피하고 저장된 결과를 즉시 반환할 수 있다.

결과 캐시 동작 원리

1. 쿼리 실행: 쿼리가 실행되면 Oracle은 먼저 결과 캐시에 해당 쿼리 겨로가가 있는지 확인한다.
2. 캐시 확인: 결과가 있으면 저장된 결과를 반환한다.
3. 캐시 미스: 결과가 없으면 쿼리를 실행하고 결과를 결과 캐시에 저장한다.
4. 결과 저장: 결과는 일정기간 동안 캐시에 유지된다.
5. 쿼리 재실행: 동일한 쿼리가 다시 실행되면 서버는 결과 캐시에서 결과를 반환한다.

10. 캐시 최적화 및 유지 관리

결과 캐시는 메모리 리소스를 효율적으로 사용하여 성능을 최적화 한다. 이를 위해 다음과 같은 설정을 관리할 수 있다.

• RESULT_CACHE_MODE: 결과 캐시 동작 모드를 설정한다.
• RESULT_CACHE_MAX_SIZE: 결과 캐시에 할당된 최대 메모리 크기를 설정한다.
• RESULT_CACHE_MAX_RESULT: 단일 쿼리 결과가 차지할 수 있는 최대 메모리 비율을 설정한다.

결론

이번 글에서는 SQL 문장 실행 과정의 각 단계를 살펴보았다. 구문 검사, 의미 검사, 권한 검사, 실행 계획 찾기, 최적화, 행 소스 생성, 실행 및 결과 반환, 결과 캐시 등 중요한 단계들을 다루었다. 이러한 과정들을 이해하면 SQL 성능 튜닝에 필요한 기본 지식을 갖출 수 있다. 이후 글에서 각 단계에 대해서 보다 자세히 다루도록 하겠다.

 

감상

SQL 튜닝 강의 답게 SQL의 실행 순서에 대해서 다루는 강의였다. 강의를 따라가면서 메모를 하다보면 영어 강의를 한글 자막 없이 보다보니 용어를 어떻게 써야 좋을지에 대해서 혼동이 오는 감이 있다. 한글로 번역에서 적으면 이해는 쉬운데 실제 쓰이는 번역인지 어떤지 혼란을 가져올 수 있지 않을까하는 생각이 든다. 가능한한 영문과 번역어를 섞어서 쓰는 게 전반적으로는 좋지 않을까 싶다.