[oracle] 소프트 파싱 과 하드파싱

오라클에서 SQL 의 처리과정 

https://docs.oracle.com/en/database/oracle/oracle-database/19/tgsql/sql-processing.html#GUID-873A7B2C-CD17-428A-8AE2-5B08906E45FB

사용자가 SQL 을 실행하면 아래 1~ 3 까지의 파싱 단계를 거치게 된다. 

 1. syntax check : 문법적 오류 체크 (ex. select * form tab_a )

 2. semantic check : 존재하지 않는 테이블 조회 등

 3. shared pool check : 동일한 sql 이 shared pool 에 캐싱되어 있는지 확인 

이 과정을 거쳐 SQL 이 캐싱되어 있지 않는 것으로 확인되면 (library cache miss) 

4. 실행계획 생성 단계를 거쳐 

5. 최종적으로 실행을 하게 된다. 

이때 실행계획 생성과정에서 library cache, data dictionary cache 에 반복적으로 접근하고 이 과정에서 

latch 라는 lock 보다는 가벼운 잠금장치를 수없이 획득하는 과정을 거친다. 

 

소프트 파싱 vs 하드 파싱

소프트 파싱 (Soft Parsing) 이라는 것은 이미 만들어진 실행계획을 캐시에서 찾아 재사용하는 과정이다. 

Shared Pool의 Library Cache에 저장된 SQL 실행 계획을 공유하여 최적화 과정을 생략한다.
실행계획 생성과정이 cpu 를 많이 소모하는 과정이기 때문에 하드 파싱에 비해 더 빠르고 리소스를 절약할 수 있다.

하드 파싱 (Hard Parsing) 캐시에 SQL 이 존재하지 않아 (library cache miss) 새로운 실행 계획을 생성해야 할 때 발.

최적화 및 로우 소스 생성 단계까지 모두 거친 후에 SQL 이 실행된다. 

소프트 파싱의 이점과 한계

소프트 파싱은 실행 계획을 반복적으로 생성하는 오버헤드를 줄여 성능을 향상 시켜주어 이롭지만( 특히 OLTP 환경에서 동시 접속자가 많을 때 효과적 ) 항상 최선의 선택은 아니다.

사용자 입력값을 바인드 변수 처리하면 같은 SQL ID 로 처리되어 소프트 파싱으로 처리되는 비율이 높아지지만 

SQL 최적화 시점에 컬럼의 히스토그램을 사용하지 못해 실행 계획이 고정 되고 실제 데이터의 분포도가 플랜에 적용이 안되는 경우가 생긴다. 

 

바인드 Peeking의 역효과 예시

다음과 같은 테이블과 쿼리가 있다고 가정해봅시다:
sql
SELECT * FROM 직원 WHERE 부서 = :dept

이 테이블에서 '영업부'는 전체 직원의 50%를 차지하고, 나머지 부서들은 각각 5% 미만의 직원을 가지고 있습니다.
첫 실행 시 :dept에 '인사부'가 바인딩되었다면:
옵티마이저는 인덱스 스캔을 선택합니다.
이 실행 계획이 캐시에 저장됩니다.
이후 '영업부'로 쿼리를 실행하면:
캐시된 실행 계획(인덱스 스캔)을 사용합니다.
하지만 영업부는 데이터의 50%를 차지하므로, 풀 테이블 스캔이 더 효율적일 수 있습니다.
이로 인해 '영업부' 쿼리의 성능이 크게 저하될 수 있습니다1.

소프트 파싱 한계에 대한 대안

1. 힌트 사용:
특정 쿼리에 대해 옵티마이저 힌트를 사용하여 원하는 실행 계획을 강제
SELECT /*+ FULL(직원) */ * FROM 직원 WHERE 부서 = :dept

2. 아웃라인 사용:
특정 쿼리에 대한 실행 계획을 고정
CREATE OUTLINE 직원_부서_조회 FOR
SELECT * FROM 직원 WHERE 부서 = :dept;

3. 적응적 커서 공유 활용:
Oracle 11g 이상에서는 적응적 커서 공유를 통해 바인드 변수 값에 따라 다른 실행 계획을 사용할 수 있다

4. 특정 SQL의 소프트 파싱 방지
SELECT /*+ NO_SHARED_CURSOR */ * FROM 직원 WHERE 부서 = :dept

5. CURSOR_SHARING 파라미터 조정:

세션 레벨에서 CURSOR_SHARING을 EXACT로 설정, 해당 세션의 모든 쿼리에 대해 소프트 파싱을 방지
ALTER SESSION SET CURSOR_SHARING = EXACT;