[DB] 하드파싱(Hard parsing)과 소프트파싱(Soft parsing) 그리고 라이브러리 캐시(Library cache)

실제 엔터프라이즈 레벨의 어플리케이션에서는 UNION ALL로 여러번 결합된 복잡한 쿼리를 반복해서 수행해야 하는 일이 생기곤 합니다. 이 때 쿼리 자체의 길이가 길어지면서 해시 알고리즘을 통해 ID 생성에 걸리는 시간이 길어지고, 그럼으로써 파싱에 소요되는 시간까지도 덩달아 길어지는 문제가 생깁니다. 이번 포스팅을 통해 파싱이 무엇이며 어떤 종류가 있는 지와 플랜이 저장되는 라이브러리 캐시에 대해 한번 정리해보고자 합니다.

---

1. SQL의 특성과 Optimizer

일반적인 프로그래밍 언어와 달리 SQL은 처리 방법(절차)을 기술하지 않습니다. 다시 말해, SQL을 사용하는 개발자는 원하는 데이터만 정의할 뿐, 해당 데이터를 어떻게 찾아와야 할지는 따로 작성하지 않는데요, 아래 SQL 문을 한번 보시죠.

SELECT e.name
FROM employees e
WHERE 1=1 
AND e.id = '0001';

위 예를 보면, employee 테이블에서 id가 '0001' 인 직원의 name을 가져오라는 데이터의 조건이나 관계만 기술할 뿐 처리 방법은 따로 기술하지 않음을 확인할 수 있습니다. 그렇다면 우리가 SQL을 사용할 때 처리방법은 누가 만들고, 요청한 작업은 어떤 절차에 따라 처리 되는걸까요?

 

1-1 . OPTIMIZER (= PARSER)

정답은 바로 Optimizer 입니다. Optimizer 는 흔히 Parser 라고도 불리는데요, 일반적으로 개발자가 작성한 SQL 쿼리는 DBMS의 옵티마이저가 만든 절차에 따라 수행된다고 할 수 있습니다.

 

다시 말해, RDBMS의 옵티마이저(=parser)가 어플리케이션으로부터 요청받은 SQL문을 분석하고(Parsing) 실행 계획(Plan)이라고 하는 처리 방법을 생성해준다는 것입니다. 덕분에 SQL을 작성하는 프로그래머 입장에서는 별도의 처리방법을 기술하지 않고도 원하는 데이터를 얻어올 수 있는 것이죠.

 

1-2. 하드 파싱(hard parsing)과 소프트 파싱(soft parsing)

사실 옵티마이저가 수행하는 분석에는 두가지 종류가 있는데요, 바로 하드 파싱(hard parsing)과 소프트 파싱(soft parsing) 입니다. 이 두 가지 파싱이 언제 수행되는지는 아래의 표를 보면 알 수 있습니다.

 

하드 파싱	소프트 파싱
공유 풀에 실행 계획이 존재하지 않아 실행 계획을 생성하는 경우	공유 풀에 존재하는 실행 계획을 찾아 이를 재사용 하는 경우

 

2. Shared Pool (공유 풀), Library Cache (라이브러리 캐시)

하드 파싱과 소프트 파싱이 수행되는 상황을 보면 DBMS가 파싱 과정에서 실행 계획을 찾기 위해 '공유 풀(라이브러리 캐시)'  영역을 참조한다는 것을 알 수 있습니다. 그렇다면 공유 풀은 어떤 메모리 공간일까요? 마지막으로 오라클 DBMS 메모리 구조를 살펴보며 실행계획이 어느 영역에 저장되는지 살펴보도록 하겠습니다. 이제부턴 실행 계획을 SQL 플랜이라고 쓰도록 하겠습니다.

 

Oracle Database Memory Structure

 

오라클 데이터베이스의 메모리 구조를 보면, SQL 플랜은 공유 풀(Shared Pool)의 라이브러리 캐시(Library Cache) 영역에 저장됨을 알 수 있습니다. 공유 풀은 SQL 플랜을 재사용해 분석 작업에 쓰이는 자원을 줄이기 위해 존재하며, 프로세스 간에 공유되어야 하므로 버퍼 캐시와 마찬가지로 공유 메모리에 위치합니다(오라클에서는 프로세스들이 공유하는 메모리를 SGA 라고 부름). 실제로는 공유 메모리의 많은 부분이 버퍼 캐시로 사용되고 남은 일부가 공유 풀로 사용된다고 하는데요, 이러한 공유 풀은 다시 라이브러리 캐시(플랜이 저장되는 곳)나 딕셔너리 캐시 같은 영역으로 나누어집니다.

 

3. 값이 다른 SQL 문을 반복해서 실행할 때는 바인드 변수를!

오라클 데이터베이스는 해시 알고리즘을 사용해 SQL문마다 고유한 ID를 생성합니다. 다시 말해, 오라클은 SQL문을 문자열로 해시 함수에 입력하고, 출력된 해시 값을 SQL 문의 ID로 사용한다는 건데요, 이로 인해 쿼리에 쓰인 대소문자나 같은 조건 절에 쓰이더라고 값이 다른 경우 오라클은 이를 다른 SQL 쿼리로 인식하게 됩니다.

 

이는 개발자가 보기에는 같은 SQL 쿼리라도 다시 하드 파싱 과정을 거쳐야 한다는 의미인데요, 이 때 바인드 변수를 사용하면 불필요한 자원의 낭비를 막을 수 있습니다. 그러니 되도록 쿼리를 작성할 땐 되도록 바인드 변수를 활용해주세요! 😀 

-- 바인드 변수 없이 쿼리를 작성하는 경우
SELECT id FROM emp WHERE id = 1;
SELECT id FROM emp WHERE id = 2;

-- 바인드 변수를 활용하는 경우
SELECT id FROM emp WHERE id = :A;

 

 

Reference

1. 그림으로 공부하는 오라클 구조(제이펍)

2. https://docs.oracle.com/database/121/CNCPT/memory.htm#CNCPT1238

Memory Architecture