오라클 SQL 성능 최적화: 바인드 변수와 Shared Memory 이해하기

Bind 변수를 활용한 SQL 작성은 조금 번거로운(귀찮은) 작업입니다.
그럼에도 Bind 변수를 활용한 SQL을 사용하는 이점에 대해 공유하고자 합니다.

1. 오라클 Shared Memory 개요

오라클 데이터베이스에서 Shared Memory는 다수의 사용자 세션에서 공유되는 메모리 영역입니다.
이 영역은 여러 구성 요소를 포함하며, 그 중 “Library Cache”와 “Shared SQL Area”는 SQL 및 PL/SQL 코드의 파싱 정보를 저장하는 공간입니다.

2. SQL 실행 과정: 파싱, 바인딩, 실행

오라클에서 SQL 쿼리 실행은 크게 세 가지 단계로 이루어집니다:

1. 파싱(Parsing): SQL 쿼리의 문법과 의미를 확인합니다. 실행 계획이 이 단계에서 생성됩니다.
2. 바인딩(Binding): 파싱 단계에서 생성된 실행 계획에 바인드 변수의 실제 값을 할당합니다.
3. 실행(Execution): 바인딩이 완료된 후, 실제 데이터를 조회하거나 변경합니다.

3. 바인드 변수의 중요성

바인드 변수는 SQL 쿼리의 성능을 크게 향상시킬 수 있습니다. 바인드 변수를 사용하면:

1. 파싱 오버헤드 감소: 같은 구조의 쿼리는 한 번만 파싱되며, 이후 실행에서는 파싱된 정보를 재사용합니다.
2. Library Cache 히트율 증가: 바인드 변수를 사용하면 파싱된 쿼리 정보가 Library Cache에 보관되어 재사용되므로 히트율이 증가합니다.
3. 성능 향상: 파싱 오버헤드가 감소하면 전체적인 쿼리 응답 시간도 향상됩니다.

4. 바인드 변수 피크와 어댑티브 커서 공유

바인드 변수를 사용할 때 주의해야 할 것 중 하나가 “바인드 변수 피크”입니다. 이는 처음 바인드 변수의 값을 “엿보고” 실행 계획을 생성하는 과정입니다.

그러나 이 계획이 이후 모든 쿼리 실행에 적합하지 않을 수 있습니다.

Oracle 11g 이후로는 이 문제를 해결하기 위한 “어댑티브 커서 공유” 기능이 도입되었습니다. 이 기능은 다른 바인드 값에 따라 최적의 실행 계획을 선택할 수 있게 해 줍니다.

5. 파싱, 바인딩, 실행 과정에서 Plan이 생성되는 시점은 언제일까요?

SQL 쿼리가 오라클 데이터베이스에서 실행될 때, 그 과정은 크게 “파싱(Parsing)”, “바인딩(Binding)”, 그리고 “실행(Execution)” 단계로 나뉩니다.
실행 계획(Plan)은 “파싱” 단계에서 생성됩니다.

1. 파싱(Parsing) 단계: 이 단계에서는 SQL 문의 문법을 체크하고, 테이블과 칼럼이 실제로 존재하는지, 사용자가 해당 객체에 접근 권한을 가지고 있는지 등을 확인합니다. 이러한 기본 검사 이후에 SQL 옵티마이저가 실행 계획을 생성합니다. 이 계획은 쿼리를 어떻게 실행할 것인지, 어떤 인덱스를 사용할 것인지, 조인 순서는 어떻게 될 것인지 등을 결정합니다.

2. 바인딩(Binding) 단계: 파싱 단계에서 생성된 실행 계획은 바인드 변수의 구체적인 값을 아직 알지 못합니다. 바인딩 단계에서 이러한 변수에 실제 값을 할당합니다. 그러나 실행 계획 자체는 이미 “파싱” 단계에서 결정되었기 때문에, 바인딩 단계에서는 실행 계획이 변경되지 않습니다. 예외적으로 어댑티브 커서 공유 같은 기능을 사용하는 경우, 다른 바인드 변수 값에 따라 다른 실행 계획을 선택할 수 있습니다.

3. 실행(Execution) 단계: 바인딩이 완료된 후, 실제로 데이터를 조회하거나 변경하는 작업이 이루어집니다. 이 단계에서는 파싱 단계에서 생성된 실행 계획을 따릅니다.

따라서 실행 계획은 “파싱” 단계에서 생성되며, 이 계획은 일반적으로 “바인딩”과 “실행” 단계에서 변경되지 않습니다.

6. Bind 값에 따라 SQL Plan도 변경되어야 하는 것 아닌가?

바인드 변수를 사용할 때 주의할 점 중 하나는 바로 “바인드 변수 피크”(Bind Variable Peeking) 또는 오라클 11g 이후에서는 “어댑티브 커서 공유”(Adaptive Cursor Sharing)라고 불리는 현상입니다.

바인드 변수 피크는 옵티마이저가 처음 바인드 변수의 값을 “엿보고”(peek) 실행 계획을 생성하는 과정입니다. 이렇게 생성된 실행 계획은 그 이후 같은 쿼리에 대해서도 재사용되므로, 첫 번째 바인드 값에 따라 성능이 좋을 수도, 나쁠 수도 있습니다.

예를 들어, 어떤 쿼리가 90%의 경우에는 특정 인덱스를 사용하는 것이 빠르지만, 나머지 10%의 경우에는 테이블 풀 스캔이 빠르다면, 처음 바인드 값에 따라 불필요하게 느려질 수 있습니다.

이러한 문제를 해결하기 위해 Oracle 11g 이후로는 “어댑티브 커서 공유” 기능이 도입되었습니다. 이 기능은 같은 쿼리라도 다른 바인드 값에 따라 최적의 실행 계획을 선택할 수 있도록 해 줍니다.

따라서 바인드 변수를 사용할 때에는 이러한 특성과 성능 변화를 주의 깊게 관찰해야 하며, 경우에 따라 힌트나 다른 옵티마이저 설정을 이용하여 성능을 최적화할 필요가 있을 수 있습니다.

7. Library Cache는 무엇인가요?

오라클 데이터베이스에서 Library Cache는 Shared Pool의 한 부분으로, SQL 및 PL/SQL 코드와 같은 실행 가능한 코드의 파싱, 컴파일 및 실행에 필요한 정보를 저장합니다.
Library Cache는 데이터베이스의 성능 및 처리 효율성을 향상시키는 데 중요한 역할을 합니다.

Library Cache에는 다음과 같은 주요 구성요소와 정보가 포함됩니다:

1. 커서(Cursor): SQL 문장에 대한 실행 정보.
2. PL/SQL 함수 및 프로시저: PL/SQL 블록의 파싱 및 실행 정보.
3. 패키지 및 패키지 바디: PL/SQL 패키지와 관련된 정보.
4. 트리거(Trigger): 트리거에 대한 파싱 및 실행 정보.
5. 이름매핑 및 SQL 문장: 사용자가 제출한 SQL 및 PL/SQL 문장과 해당 실행계획 사이의 매핑.

데이터베이스는 SQL 또는 PL/SQL 문장이 실행될 때 해당 문장을 먼저 Library Cache에서 찾습니다. 문장이 이미 존재하면 (즉, 이전에 같은 문장이 파싱되었으면), 데이터베이스는 그 정보를 재사용하여 파싱 과정의 오버헤드를 줄일 수 있습니다.

이런 재사용을 통해 오라클은 성능 향상을 달성합니다.

그러나 Library Cache의 크기는 제한적이므로, 많은 SQL/PLSQL 코드가 실행되면 일부 정보가 오래된 것으로 간주되고 캐시에서 제거될 수 있습니다.

이런 이유로 오라클 DBA는 Shared Pool 및 Library Cache의 성능을 주기적으로 모니터링하고 최적화 작업을 수행해야 합니다.

8. 결론

바인드 변수와 Shared Memory는 오라클 데이터베이스 성능 최적화에서 중요한 역할을 합니다.

바인드 변수를 적절히 사용하면 파싱 오버헤드를 줄이고 Library Cache 히트율을 높일 수 있으며, 어댑티브 커서 공유를 통해 더욱 세밀한 성능 튜닝이 가능합니다.