오라클 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 CacheShared 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 히트율을 높일 수 있으며, 어댑티브 커서 공유를 통해 더욱 세밀한 성능 튜닝이 가능합니다.






정규표현식의 유용한 패턴
#집합찾기 #반복찾기 #역참조 #조건달기
A 액티비티에서 B 액티비티로 데이터 전달하기
#WorkflowFoundation #InArgument #OutArugment #Variables
Microsoft UI Automation Framework 이해하기
#AutomationElement #TreeWalker #ControlPatterns
AppDomain 탐구
#AppDomain #격리 #어셈블리별도실행 #플러그인시스템
Chrome Extension 활용하여여 웹 페이지 XPath 정보 가져오기
#ChromeExtension #크롬확장프로그램 #XPath
Workflow 디자이너와 액티비티의 관계
#WorkflowFoundation #Designer #Activity #ModelItem
ExpressionTextBox와 ModelItem의 바인딩 관계
#WorkflowFoundation #ExpressionTextBox #ModelItem #Binding
Windows 레지스트리 간단 정리
#Windows #Registry #레지스트리
WPF MVVM 패턴, 그리고 Binding
#WPF #MVVM #Binding #Modle #View #ViewModel
오라클 SQL 성능 최적화 - 바인드 변수와 Shared Memory 이해하기
#Oracle #바인드변수 #SharedMemory #LibraryCache #SqlPlan
ActivityDesigner와 CodeActivity 이해하기
#WorkflowFoundation #ActivityDesigner #CodeActivity
Selenium Implicit vs Explicit - 웹 요소 기다리기
#Selenium #IWebDriver #Implicit #Explicit
.NET에서 Selenium 활용하기 - 3가지 실전 예제
#Selenium #ChromeDriver #FindElements
.NET에서 CommandBinding 활용하기
#.NET #CommandBinding #디자인패턴
워크플로우 파운데이션(Workflow Foundation) 소개
#WorkflowFoundation #소개 #기본개념
AWS SAA 준비 - (4) 비용에 최적화된 아키텍처 설계
(정리) Exam Readiness - AWS Solutions Architect Associate
AWS SAA 준비 - (3) 안전한 아키텍처
(정리) Exam Readiness - AWS Solutions Architect Associate
AWS SAA 준비 - (2) 성능이 뛰어난 아키텍처 설계
(정리) Exam Readiness - AWS Solutions Architect Associate
AWS SAA 준비 - (1) 복원력을 갖춘 아키텍처 설계
(정리) Exam Readiness - AWS Solutions Architect Associate
15분 안에 ToC를 구현해보자!
Vanilla JS로 Table of Contents 구현하기
모듈
모던 자바스크립트 Deep Dive | 48장 | 모듈
에러 처리
모던 자바스크립트 Deep Dive | 47장 | 에러 처리
제너레이터와 async/await
모던 자바스크립트 Deep Dive | 46장 | 제너레이터와 async/await
프로미스
모던 자바스크립트 Deep Dive | 45장 | 프로미스
REST API
모던 자바스크립트 Deep Dive | 44장 | REST API
Ajax
모던 자바스크립트 Deep Dive | 43장 | Ajax
비동기 프로그래밍
모던 자바스크립트 Deep Dive | 42장 | 비동기 프로그래밍
타이머
모던 자바스크립트 Deep Dive | 41장 | 타이머
Set과 Map
모던 자바스크립트 Deep Dive | 37장 | Set과 Map
디스트럭처링
모던 자바스크립트 Deep Dive | 36장 | 디스트럭처링
브라우저의 렌더링 과정
모던 자바스크립트 Deep Dive | 38장 | 브라우저의 렌더링 과정
스프레드 문법
모던 자바스크립트 Deep Dive | 35장 | 스프레드 문법
이터러블
모던 자바스크립트 Deep Dive | 34장 | 이터러블
7번째 데이터 타입 Symbol
모던 자바스크립트 Deep Dive | 33장 | 7번째 데이터 타입 Symbol
String
모던 자바스크립트 Deep Dive | 32장 | String
RegExp
모던 자바스크립트 Deep Dive | 31장 | RegExp
Date
모던 자바스크립트 Deep Dive | 30장 | Date
Math
모던 자바스크립트 Deep Dive | 29장 | Math
DOM
모던 자바스크립트 Deep Dive | 39장 | DOM
Number
모던 자바스크립트 Deep Dive | 28장 | Number
배열
모던 자바스크립트 Deep Dive | 27장 | 배열
이벤트
모던 자바스크립트 Deep Dive | 40장 | 이벤트
ES6 함수의 추가 기능
모던 자바스크립트 Deep Dive | 26장 | ES6 함수의 추가 기능
클래스
모던 자바스크립트 Deep Dive | 25장 | 클래스
this
모던 자바스크립트 Deep Dive | 22장 | this
빌트인 객체
모던 자바스크립트 Deep Dive | 21장 | 빌트인 객체
strict mode
모던 자바스크립트 Deep Dive | 20장 | strict mode
클로저
모던 자바스크립트 Deep Dive | 24장 | 클로저
프로토타입
모던 자바스크립트 Deep Dive | 19장 | 프로토타입
함수와 일급 객체
모던 자바스크립트 Deep Dive | 18장 | 함수와 일급 객체
실행 컨텍스트
모던 자바스크립트 Deep Dive | 23장 | 실행 컨텍스트
생성자 함수에 의한 객체 생성
모던 자바스크립트 Deep Dive | 17장 | 생성자 함수에 의한 객체 생성
프로퍼티 어트리뷰트
모던 자바스크립트 Deep Dive | 16장 | 프로퍼티 어트리뷰트
let, const 키워드와 블록 레벨 스코프
모던 자바스크립트 Deep Dive | 15장 | let, const 키워드와 블록 레벨 스코프
전역 변수의 문제점
모던 자바스크립트 Deep Dive | 14장 | 전역 변수의 문제점
스코프
모던 자바스크립트 Deep Dive | 13장 | 스코프
함수
모던 자바스크립트 Deep Dive | 12장 | 함수
원시 값과 객체의 비교
모던 자바스크립트 Deep Dive | 11장 | 원시 값과 객체의 비교
객체 리터럴
모던 자바스크립트 Deep Dive | 10장 | 객체 리터럴
타입 변환과 단축 평가
모던 자바스크립트 Deep Dive | 9장 | 타입 변환과 단축 평가
제어문
모던 자바스크립트 Deep Dive | 8장 | 제어문
연산자
모던 자바스크립트 Deep Dive | 7장 | 연산자
데이터 타입
모던 자바스크립트 Deep Dive | 6장 | 데이터 타입
표현식과 문
모던 자바스크립트 Deep Dive | 5장 | 표현식과 문
변수
모던 자바스크립트 Deep Dive | 4장 | 변수
Iteration와 Generator
코드스피츠 77 ES6+ 3화 참조
WHATWG 탄생 배경
WHATWG, W3C, HTML의 관련에 대한 역사
프론트엔드(FE) 면접 질문 정리
FE관련 면접 질문 및 답변 정리한 내용입니다.
쿠버네티스(kubernetes, k8s) 용어 정리
쿠버네티스(kubernetes, k8s) 용어 정리
젠킨스(Jenkins) 정리
젠킨스(Jenkins) 정리
Docker 용어 정리
Docker 용어 정리
Git 용어 정리
Git 용어 정리
반응형 웹 디자인(Responsive Web Design)
CSS responsive 에 대하여
JS this에 대하여
this에 대해 알아보자
SQL*PLUS에 대하여
SQL*PLUS 정의 및 사용방법
Oracle에서 SQL Plan 확인하기
Oracle에서 SQL Plan을 확인해보자