SQL - 라이브러리 캐시, 소프트 파싱과 하드 파싱, 캐싱 SQL, 바인드 변수 (개념, 사용하는 이유, 예시)

이해가 어렵다면 아래의 포스팅을 참고하여 주세요.

SQL - SQL 최적화, 옵티마이저(개념, 작동 과정, 옵티마이저의 종류)

SQL - SQL 최적화, 옵티마이저(개념, 작동 과정, 옵티마이저의 종류)

SQL 최적화 개념 DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 의미 SQL 옵티마이저를 통해 DBMS 내부 엔진에서 프로시저를 만듦 즉, SQL 옵티마이저는 SQL문

luvris2.tistory.com

라이브러리 캐시(Library Cache)

개념

시스템 공유 메모리에서 SQL과 실행계획이 캐싱되는 영역
SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 의미
Oracle 에서는 라이브러리 캐시, SQL Server 에서는 프로시저 캐시(Procedure Cache) 라고 부름
시스템 글로벌 영역(SGA, System Global Area)의 구성요소
해시구조로 관리되며 SQL마다 해시 값에 따라 여러 해시 버킷으로 나누어 저장, SQL을 찾을 때는 SQL 문장을 해시 함수에 입력해서 반환된 해시 값을 이용해 해당 해시버킷을 탐색

* 시스템 글로벌 영역(SGA, SystemGlobal Area) : 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어 구조를 캐싱하는 메모리 공간

라이브러리 캐시가 필요한 이유

SQL 옵티마이저가 최적화 과정을 진행하게 되면 무수히 많은 실행 경로를 도출하고, 짧은 순간에 딕셔너리와 통계정보를 읽어 효율성을 판단하게 된다.
- SQL 최적화 과정에 옵티마이저가 사용하는 정보
  - 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
  - 오브젝트 통계 : 테이블 통계, 인덱스 통계, 히스토그램을 포함한 컬럼 통계
  - 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
  - 옵티마이저 관련 파라미터
즉, 하드 파싱은 CPU를 많이 소비하는 작업 중 하나이다.

> 하드 파싱을 거쳐 생성한 내부 프로시저를 한 번만 사용하고 버린다면 효율적으로 좋지 않다.

>> 이런 과정을 거쳐 최적화된 SQL 실행계획을 여러 사용자가 공유하면서 재사용할 수 있도록 공유 메모리에 캐싱해두면 다음에 같은 SQL을 실행 시 캐시를 이용하여 최적화 과정 없이(=소프트 파싱) 빠르게 결과를 도출할 수 있다.

SQL 파싱 절차에 따른 소프트 파싱과 하드 파싱

사용자가 SQL을 실행
제일 먼저 SQL 파서(Parser)가 SQL 문장에 문법적 오류가 없는지를 검사(Syntax Check)
문법적 오류가 없으면 의미상 오류가 있는지를 검사(Semantic 검사)
오류 없이 검사를 마치면 사용자가 발행한 SQL과 그 실행계획이 라이브러리 캐시에 캐싱됐는지 확인
- 해시 함수로부터 반환된 해시 값으로 라이브러리 캐시 내 해시버킷 탐색
- 찾아간 해시버킷에 체인으로 연결된 엔트리를 차례로 스캔하면서 같은 SQL 문장 탐색
캐싱 여부에 따라 SQL문을 실행
- 캐싱되어 있을 경우 : 최적화 과정을 거치지 않고 저장된 실행계획을 가지고 곧바로 실행
- 캐싱되어 있지 않을 경우 : 최적화 과정을 진행하고 SQL 실행계획을 방금 탐색한 해시버킷 체인에 연결 후 실행

절차에 따른 파싱 방법을 용어로 정리하자면,

소프트 파싱(Soft Parsing) : SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것
하드 파싱(Hard Parsing) : 캐시가 존재하지 않아 최적화 및 로우 소스 생성 단계까지 모두 거치는 것

캐싱 SQL (=공유 가능 SQL)

캐싱된 SQL 사용 제약 조건

라이브러리 캐시에서 SQL을 찾기 위해서는 키 값이 SQL 문장 그 자체이다.

SQL 전체 텍스트 자체가 하나의 객체가 되며 조금이라도 수정되면 다른 객체가 새로 탄생하는 구조를 가진다.

즉, SQL 문장 중간에 작은 공백문자 하나만 추가되더라도 의미상으로는 모두 같지만, DBMS는 서로 다른 SQL 문장으로 인식하며 캐싱된 버전을 사용할 수 없다.

아래의 경우는 캐싱된 SQL을 재사용할 수 없는 조건이다.

공백 문자 또는 줄바꿈
대소문자 구분
주석(Comment)
테이블 Owner 명시
옵티마이저 힌트 사용
조건절 비교 값

예시)

-- 예시
select * from t where no = 1;

-- 공백 또는 줄바꿈
select * from t where no = 1 ;

-- 대소문자 구분
select * from T where NO = 1;

-- 주석
select /* 주석 */ * from t where no = 1;

-- 테이블 소유자 명시
select * from dbo.t where no = 1;

-- 옵티마이저 힌트 사용
select /*+ all_rows */ * from t where no = 1;

-- 조건 비교 값 : 비교값은 입력되는 상황에 따라 달라지는 경우
select * from t where no = '비교값';

바인드 변수(Bind Variable)

개념

SQL 쿼리에서 사용되는 변수
쿼리 텍스트의 특정 부분에 변수 값을 동적으로 할당하여 쿼리를 실행하는 기술
쿼리 재사용성을 높이기 위해서 사용

기술적으로 다시 바꿔 말하자면,

파라미터 Driven 방식으로 SQL을 작성하는 방법을 제공
하나의 프로시저를 공유하면서 반복 재사용을 할 수 있게 하기 위한 변수

* 파라미터 드리븐 방식(Parameter Driven) : 처리 과정에서 변화가 발생하면 프로세스에 영향을 주는 것이 아닌 데이터 팩터들을 데이터베이스로 관리해 업무 프로그램 내의 하드 코딩을 방지하는 방식

바인드 변수를 사용한 SQL 실행 절차

바인드 변수를 사용하여 처음 SQL 문장을 수행한 세션이 하드파싱을 통해 실행계획 생성
라이브러리 캐시에 최적화된 실행계획 캐시 저장
이후 세션들이 캐시에서 실행계획을 얻어 입력값만 새롭게 바인딩하여 실행

바인드 변수를 사용하는 이유

아래와 같은 유저 테이블에서 특정 이름을 검색하는 SQL 문장이 있다.

이는 유저의 이름 자체가 SQL 쿼리 내에 포함되어 있다.

즉, 다른 유저의 이름을 검색 시 쿼리를 수정하여야 하며,

변경된 이름이 담긴 SQL 쿼리가 실행될때마다 SQL 최적화 과정을 다시 한번 거쳐야 한다.

이는 곧 데이터베이스 시스템의 부하를 높일 수 있다.

select * from users where name = 'eunbyeol'

이 SQL 문장을 아래와 같이 바인드 변수를 사용하여 보다 유연하게 변경할 수 있다.

예시에서의 쿼리를 통해 exec GetUserInfo @값 의 형태를 가진 하나의 프로시저로 처리할 수 있다.

CREATE PROCEDURE GetUserInfo
	@userName NVARCHAR(20)
AS
BEGIN
	DECLARE @DynamicQuery NVARCHAR(MAX);
    SET @DynamicQuery = N'SELECT * FROM users WHERE name = @userName';
    
    EXEC sp_executesql @DynamicQuery, N'@userName NVARCHAR(20)', @userName;
END;

> SQL과 실행계획을 여러 개 캐싱하지 않고 하나를 반복 재사용하므로 파싱 소요시간과 메모리 사용량을 줄일 수 있다.

>> 궁극적으로 시스템 전반의 CPU와 메모리 사용률을 낮춰 데이터베이스 성능과 확장성을 높일 수 있다.

바인드 변수 사용 주의사항

옵티마이저는 조건절 컬럼의 데이터 분포가 균일하다는 가정을 세우고 최적화를 수행한다.

최적화를 수행할 때에는 컬럼에 대한 히스토그램 정보가 딕셔너리에 저장되어 있으면 이를 활용한다.

하지만 바인드 변수를 사용하면 최초 SQL이 수행될 때 최적화를 거친 실행계획을 사용하므로

컬럼에 대한 히스토그램 정보가 딕셔너리에 저장되어 있어도 이를 활용하지 못한다.

이는 즉, 나중에 반복 수행될 때 어떤 값이 입력될지 알 수 없기 때문에

조건절 컬럼의 데이터 분포가 균일하지 않다면 바인딩되는 값에 따라 쿼리 성능이 다르게 나타날 수 있다.

이럴 때는 바인드 변수를 사용하는 것보다 상수 값을 사용하는 것이 나을 수 있다.

그 값에 대한 컬럼 히스토그램 정보를 이용해 좀 더 최적의 실행계획을 수립할 가능성이 높기 때문이다.

참고

저자 조시형님 도서 - 친절한 SQL 튜닝

저작자표시 동일조건