로컬 바인드 변수 사용 경우 실행계획이 이상할 떄가 있다!

1) Store Procedure에서 바인드 변수 사용 경우
2) 스칼라 변수 사용 경우
3) 로컬 바인드 변수 사용 경우

위와 같은 3가지 경우 1),2)는 올바른 실행계획을 사용하는데, 3)은 부적절한 실행계획을 사용할 수 있다.
왜?

* 3가지 경우 모두 실행 계획이 캐싱된다. SGA(System Global Area) > Shared Pool > Library Cache > Shared SQL Area에 실행 계획이 캐싱된다. (하드파싱 횟수 감소로 CPU 자원 효율 증대)

* 1)경우, 최초 파싱시 사용했던 변수의 분포도 사용(Prameter Sniffing)
* 2)경우, 최초 파싱시 사용했던 변수의 분포도 사용
* 3)경우, 최초 파싱시 변수의 분포도 사용 불가(Prameter Sniffing 작동 X)

    * 3)의 경우는 통계데이터만 사용가능. 컬럼 분포도 사용 불가

* Prameter Sniffing은 Stored Procedure의 입력 매개 변수에 대해서만 수행된다. 로컬 바인드 변수의 경우에는 Prameter Sniffing이 수행되지 않는다. (Oracle에서 유사한 기능을 하는 Peeking은 로컬 바인드 변수에도 작동O) 

 

 

* 바인드 변수를 사용한 쿼리의 파싱
Bind Variable을 사용한 SQL은 먼저 파싱(Parsing)과 최적화(Optimization)가 이루어진 후에 바인드 변수의 바인딩이 이루어진다.
따라서 최적화가 이루어지는 시점에는 **변수로 제공되는 컬럼의 분포가 균일하다는 가정**을 세운 후에 최적화를 수행하게 되고 분포가 균일하지 못한 컬럼(highly skewed column)에 바인드 변수를 사용하게 되면 최악의 실행계획을 생성할 수도 있다.
그래서 우리가 Hard Parsing의 부하를 줄이기 위해 Bind Variable을 사용하는 것이 좋다는 것을 알면서도 사실상 데이터의 분포가 고르지 못한 경우가 많기 때문에 현실적으로 사용하기 어려운 경우가 종종 있다.

 

* Variable Peeking & Parameter Sniffing
위와 같은 한계( 분포가 고르지 못한 경우가 많기 때문에 현실적으로 사용하기 어려운 경우 )를 극복하기 위해 Oracle9i부터 제공되기 시작한 것이 "Peeking"이라는 기능이다.
"Peek"라는 용어를 사전에서 찾아보면 "몰래 엿보다"라는 뜻을 갖는다.
Bind Variable을 사용한 SQL이 첫번째 실행될 때 SQL 옵티마이저는 사용자가 지정한 바인드 변수의 값을 "살짝 컨닝"함으로써, 조건절의 컬럼값이 상수로 제공될 때와 마찬가지로 해당 조건의 선택성(selectivity, ※ Unique 인덱스를 갖는 컬럼의 선택성이 가장 높다.)을 확인한 후에 최적화를 수행한다는 얘기이다.
그리고 나서 커서가 다음 번에 계속 실행될 때에는 변수를 다른 값으로 바인딩하더라도 더 이상 peeking이 발생하지 않으며 앞에서 생성된 실행계획을 그대로 사용한다는 것이다.
즉, 최초 Hard Parsing이 일어날 때 단 한번만 Bind Variable을 Peeking한다는 설명이고, 다시 말해서 첫번째 Binding 되는 값에 따라 SQL Plan이 고정된다는 것이다.

Prameter Sniffing은 Stored Procedure의 입력 매개 변수에 대해서만 수행된다.

* 데이터의 분포가 고르지 못한 경우가 많기 때문에 현실적으로 사용하기 어려운 경우가 종종 있다. 어떻게 해?
아래와 같은 힌트를 사용해서 컨트롤
OPTIMIZE FOR UNKNOWN
OPTIMIZE FOR (@variable = value)

 

 

---

1. Stored Procedure에서 바인드 변수란?
바인드 변수는 stored procedure에서 사용되는 변수로, 쿼리의 실행 시점에 특정 값을 할당받아 사용된다. 이를 통해 SQL 쿼리의 재사용성을 높이고, SQL 인젝션 공격으로부터 보호하는 등의 이점이 있다.
예시: Stored procedure AddEmployee가 있다고 가정. 이 프로시저는 새로운 직원을 추가하는 데 사용되며, 이름과 이메일 주소를 입력으로 받는다.

CREATE PROCEDURE AddEmployee (@Name VARCHAR(100), @Email VARCHAR(100))
AS
BEGIN
    INSERT INTO Employees (Name, Email) VALUES (@Name, @Email);
END

 

 

2.스칼라 변수란?
스칼라 변수는 단일 값을 저장하는 변수를 의미한다. 데이터베이스 프로그래밍에서 스칼라 변수는 보통 숫자, 문자열, 날짜와 같은 기본 데이터 타입의 값을 저장한다.
예시: 간단한 SQL 스크립트에서 사용되는 변수를 생각해 볼 수 있다.

DECLARE @EmployeeCount INT;
SELECT @EmployeeCount = COUNT(*) FROM Employees;
PRINT @EmployeeCount;

 

 

3. 로컬 바인드 변수란?
로컬 바인드 변수는 특정 프로시저 또는 함수 내에서만 유효한 변수로, 해당 프로시저나 함수의 실행 동안에만 존재하고 값이 유지된다. 이러한 변수는 프로시저나 함수의 로컬 영역에서 선언되며, 외부에서는 접근할 수 없다.
예시: Stored procedure 내에서 사용되는 로컬 변수를 예로 들 수 있다.

CREATE PROCEDURE CalculateBonus
AS
BEGIN
    DECLARE @SalesTotal INT;
    SELECT @SalesTotal = SUM(Sales) FROM Orders;
    -- 여기서 @SalesTotal은 로컬 바인드 변수.
END

 

4. 스칼라 변수 vs 로컬 바인드 변수
* 스칼라: 이는 단일 값을 저장하는 변수를 의미한다. 스칼라 변수는 숫자, 문자열, 날짜 등과 같은 단일 데이터 타입의 값을 저장할 수 있다. 스칼라 변수는 저장 프로시저, 함수, 스크립트 등 다양한 SQL 컨텍스트에서 사용될 수 있다.
* 로컬 바인드: 이는 특정 저장 프로시저나 함수 내에서만 사용되는 변수를 말한다. 이들 변수는 해당 프로시저나 함수의 로컬 영역 내에서 선언되고, 그 영역을 벗어나면 더 이상 사용할 수 없다.

* 기본적으로, 모든 로컬 바인드 변수는 스칼라 변수이다(단일 값을 저장하기 때문에). 하지만 모든 스칼라 변수가 로컬 바인드 변수는 아니다. 스칼라 변수는 로컬 변수일 수도 있고, 글로벌 변수일 수도 있으며, 저장 프로시저나 함수의 매개변수로도 사용될 수 있다. 따라서, 이 둘은 서로 관련이 있지만 반드시 동일한 것은 아니다.

 

5. Hard Parsing과 바인드 변수:
Hard Parsing 과정에서 바인드 변수를 사용하면 SQL 쿼리의 재사용성이 증가한다. 바인드 변수를 사용하는 쿼리는 쿼리 텍스트가 동일하면서 값만 바뀌는 경우에 유용하다. 이렇게 하면 데이터베이스는 같은 쿼리 구조에 대해 한 번만 Hard Parsing을 수행하고, 이후 실행에서는 Soft Parsing(쿼리를 다시 컴파일하지 않고 재사용)을 할 수 있다. 이는 성능을 향상시키고, 서버 부하를 줄이는 데 도움이 된다.