새롭게 안 사실들
옵티마이저
옵티마이저를 처음 이 책을 공부할 땐 머리속에 남지 않았는데 옵티마이져 관련 이슈를 경험하고 나서 눈에 제대로 들어오기 시작했다.
아우트라인
엑세스, 스캔에 대한 내용들
처음 들어 보는 조인
세미 조인(Semi Join), 카티젼 조인Cartesian Join), 내포 조인(Nested loop Join), 정렬 병합 조인(Sort Merge Join), 해쉬 조인(Hash Join), 등등
공부한 내용들
제 3장 SQL의 실행계획
SQL의 실행 계획은 데이터베이스 엑세스 효율에 가장 큰 영향을 미치는 요소로, SQL의 수행 방식을 미리 계획하여 성능 최적화를 도모하는 데 핵심적인 역할을 한다.
실행 계획은 사용 중인 SQL의 형태, 데이터 및 인덱스 구조, DBMS 버전, 통계 정보 등을 기반으로 생성되며, 이러한 요인들이 적절히 준비되어야 좋은 실행 계획이 도출된다.
실행계획의 중요성
실행계획은 SQL이 데이터베이스에서 어떤 경로로 데이터를 접근할지를 결정하며, 잘못된 실행계획은 성능 저하를 초래한다. 예를 들어, 올바르지 않은 방법으로 최적화를 시도하거나 SQL의 작성 방식에 문제가 있다면 많은 리소스를 낭비할 수 있다. 따라서 SQL을 작성할 때부터 실행계획을 염두에 두고 설계하는 것이 매우 중요하다.
실무에서의 비효율 사례
- 실제 환경에서는 옵티마이저가 비효율적인 실행계획을 생성하는 경우가 종종 발생한다.
- 이러한 문제는 주로 SQL 작성 방식이나 옵티마이저의 설정이 적절히 조정되지 않았을 때 나타난다.
- 사용자가 원하는 결과를 얻었다고 해서 반드시 효율적인 실행계획이 도출된 것은 아니다. 따라서불필요한 리소스를 소모하지 않도록 주의해야 한다.
실행계획의 최적화
- 실행계획 최적화를 위해 SQL 작성 시 올바른 문법과 최적화 기법을 활용해야 한다.
- 실행계획의 구조를 이해하는 것은 효율적인 SQL 작성의 필수적인 부분이다.
- 통계 정보는 SQL의 성능을 좌우하는 중요한 요소로, 이를 정기적으로 갱신 및 관리해야 한다.
실행계획 분석 방법
- 실행계획은 주로 EXPLAIN PLAN 명령어를 통해 확인할 수 있다.
- 실행계획의 각 단계는 SQL이 데이터베이스와 상호작용하는 방식을 명확히 보여주며, 이를 기반으로 개선 사항을 도출할 수 있다.
- 최적화된 실행계획은 단순히 결과를 빠르게 가져오는 것뿐 아니라, 리소스 사용량을 최소화하고 시스템의 안정성을 높인다.
3.1. SQL과 옵티마이져
관계형 데이터베이스는 SQL을 통해 데이터를 처리하며, 사용자가 요구하는 데이터를 정확히 처리하기 위해 옵티마이저(Optimizer)가 필요하다.
옵티마이저는 SQL 실행 시 최적의 처리 경로를 생성하여 실행 계획을 수립하여 데이터 검색과 처리가 효율적으로 이루어질 수 있다.
옵티마이저의 역할과 중요성
- SQL 실행 과정
- SQL은 사용자의 요구를 표현한 일종의 문장일 뿐으로, 실제 데이터를 처리하기 위해서는 구체적인 실행 계획이 필요한데 이 실행 계획을 수립하는 과정에서 옵티마이저가 중요한 역할을 한다.
- 옵티마이저는 SQL 문장에서 사용된 조건과 통계 정보를 바탕으로 실행에 적합한 경로를 선택하게 되며 이를 통해 최적화된 실행 계획을 제공한다.
- 실행 계획의 영향
- 옵티마이저는 인덱스, 통계 정보, 테이블 구조, 네트워크 상태 등 다양한 요소를 종합적으로 고려하여 SQL의 실행 경로를 결정한다.
- 동일한 SQL이라도 통계 정보, 데이터의 분포, 인덱스의 사용 여부 등에 따라 실행 계획이 달라질 수 있다.
- 선택성과 효율성
- 선택성(Selectivity)은 SQL의 실행 계획에 가장 중요한 영향을 미치는 요소다. 선택성이 낮은 조건은 많은 데이터를 검색해야 하므로 효율성이 떨어지고, 선택성이 높은 조건은 검색 범위를 줄여 효율성을 높일 수 있다.
옵티마이저의 동작 원리
- SQL의 실행 계획 수립 과정
- 옵티마이저는 다양한 실행 경로를 시뮬레이션(Simulation)하여 최적의 실행 계획을 결정한다.
- 이를 통해 처리 범위를 최소화하고 데이터 접근 경로를 최적화하는 방법을 모색하며, SQL의 성능을 향상시킨다.
- 실행 계획 결정에 영향을 미치는 요소
- 통계 정보 - 테이블의 데이터 분포, 클러스터링 정도, 인덱스의 존재 여부 등에 따라 실행 계획이 달라질 수 있습니다.
- DBMS 특성 - 옵티마이저는 DBMS 제품별로 동작 방식이 다르며, 이는 동일한 SQL이라도 다른 실행 계획을 수립하게 만듭니다.
- 제약사항과 한계
- 옵티마이저는 완벽하지 않고, 경우에 따라 잘못된 실행 계획을 수립할 수가 있는데 이 때 통계 정보의 부정확성, 데이터 분포의 변화, 또는 DBMS의 버그로 인해 발생할 수 있다.
옵티마이저는 SQL 성능을 결정짓는 핵심 요소로, 이를 이해하고 활용하는 능력이 중요하다.
3.1.1. 옵티마이저와 우리의 역할
옵티마이저의 존재 이유와 역할
- 관계형 데이터베이스의 출현 배경은 데이터 간 물리적 연결고리를 제거하여 논리적 연결만으로 데이터를 엑세스 할 수 있게 하는데 있다.
- 옵티마이저는 사용자가 작성한 SQL을 기준으로 최적의 처리 경로(Execution Path)을 찾아주는 역할을 수행한다.
- 과거에는 데이터베이스 설계나 쿼리 작성에서 데이터의 물리적 연결을 고려해야 했지만, 현재는 이러한 작업을 옵티마이저가 대체하고 있다.
옵티마이저의 발전과 역할의 중요성
- 초기 관계형 데이터베이스는 물리적 연결 없이도 데이터를 자유롭게 엑세스 할 수 있도록 설계 되었으나, 성능 문제가 발생할 가능성이 컸다.
- 옵티마이저의 발전은 데이터베이스 성능 향상에 큰 기여를 했으며, 현대의 관계형 데이터베이스는 옵티마이저의 도움으로 효율적인 데이터를 처리할 수 있다.
- 사용자 입장에서 옵티마이저는 다양한 옵션을 제공하지만, 결국 선택된 처리 경로는 사용자가 사전에 제공한 데이터와 관련된 정보(통계 정보 등)에 따라 결정된다.
사용자와 옵티마이저의 관계
- 옵티마이저는 이미 존재하는 여러 경로 중 하나를 선택하는 역할을 한다.
- 사용자는 데이터의 구조와 통계 정보를 사전에 적절히 설정하여 옵티마이저가 최적의 경로를 선택할 수 있도록 돕는 역할을 해야 한다.
- 효과적인 데이터 처리 전략을 위해 사용자는 옵티마이저에 필요한 정보를 미리 준비하고 제공해야 하며, 이는 최적의 SQL 작성으로 연결된다.
옵티마이저의 한계와 우리의 역할
- 옵티마이저는 모든 문제를 완벽히 해결하지 못할 수도 있다. 사용자는 옵티마이저가 선택한 경로를 이해하고 필요할 경우 수정할 준비가 되어 있어야 한다.
- 옵티마이저의 효율성은 사용자가 사전에 제공한 데이터 모델링과 통계 정보의 정확성에 크게 의존한다.
- 최적의 결과를 얻기 위해 사용자는 올바른 데이터 설계와 적합한 SQL 작성을 통해 옵티마이저의 역할을 보완해야 한다.
최적의 결과를 위한 방향
- 최소한의 시스템 자원으로 최대한의 효과를 내는 것이 최종 목표이다.
- 시용자는 단순히 SQL을 작성하는 것을 넘어 데이터베이스의 동작 원리를 이해하고, 옵티마이저가 효율적으로 작동할 수 있도록 준비해야 한다.
- 관계형 데이터베이스의 성능 최적화는 사용자와 옵티마이저 간의 협력을 통해 이루어진다.
3.1.2 옵티마이저의 형태
옵티마이저는 규칙기준 옵티마이저(RBO)와 비용기준 옵티마이저(CBO)로 나뉜다. 이 둘은 최적의 실행 계획을 선택하는 기준에서 차이가 있다.
규칙기준 옵티마이저 (RBO)
- 사전에 정의된 규칙과 순위를 기준으로 실행 경로를 결정.
- 인덱스 상태, 연산자 형태, 데이터 타입 등에 기반해 고정된 순위를 매김.
- 테이블 크기나 데이터 분포를 반영하지 못한다는 점이 단점임.
비용기준 옵티마이저 (CBO)
- 통계정보를 바탕으로 각 처리 경로의 비용을 계산해 최적의 경로를 선택.
- 통계정보는 데이터 분포, 테이블 크기, 인덱스 상태 등을 포함.
- 더 많은 상황을 반영해 유연하고 효율적인 실행 경로 제공하는 특징을 가지고 있음.
- 통계정보가 부정확하거나 최신이 아닐 경우 잘못된 결과를 낼 수 있다는 점이 단점임.
RBO와 CBO의 비교
- RBO는 간단하지만, 현대 환경에서는 적합하지 않은 경우가 많음.
- CBO는 더 복잡하지만 유연하고 현실적인 최적화를 제공.
- 현대 데이터베이스에서는 일반적으로 CBO를 사용함.
3.1.2.1. 규칙기준 옵티마이저
규칙기준 옵티마이저(Rule-Based Optimizer, RBO)는 초기 관계형 데이터베이스에서 이상형으로 추구하던 단순하고 명료한 형태의 옵티마이저이다. 데이터베이스의 기술 수준이 발전하기 전임시로 사용되던 방식이지만, 현재에도 일부 데이터베이스에서 사용되고 있다.
규칙기준 옵티마이저는 통계 정보 없이 인덱스 구조와 비교 연산자에 따라 순위를 부여하고, 이 순위를 기반으로 최적의 경로를 결정하는 특징을 가지고 있다.
이 때 최적의 경로를 결정하는 순위는 다음과 같다.
- ROWID 로 직접 액세스
- 클러스터 조인에 의한 1 로우 액세스
- Unique HASH Cluster Key
- Unique INDEX에 의한 1 로우 액세스
- CLUSTER 조인
- Non-Unique HASH Cluster Key
- Non-Unique Cluster Key
- Non-Unique 결합 인덱스
- Non-Unique 한 컬럼 인덱스
- 인덱스에 의한 범위 처리
- 인덱스에 의한 전체 범위 처리
- Sort Merge 조인
- 인덱스 컬럼의 MIN, MAX 처리
- 인덱스 컬럼의 ORDER BY
- 전체 테이블 스캔
규칙기준 옵티마이저의 한계와 발전방향
- 규칙기준 옵티마이저는 테이블의 크기, 인덱스 내 칼럼 값의 분포 등의 통계 정보를 고려하지 않기 때문에 효율성이 떨어질 수 있는 한계가 있다.
- 하지만 단순 명료함 덕분에 이해하기 쉬운 원리를 가지고 있어 약간의 숙련만으로도 일정 수준의 제어가 가능한 장점이 있다.
- 비용기준 옵티마이저의 등장과 함께 자동화를 통해 더 발전된 방식으로 대체되고 있다. 특히 RBO는 수동 카메라에 비유할 수 있는데 사용자가 직접 설정한 규칙과 순위에 따라 경로를 결정한다.
RBO는 수동 카메라처럼 사용자가 직접 초점을 맞추고 설정해야 하며, 자동화된 기능이 부족하다.
CBO는 자동 카메라처럼 데이터에 기반한 자동 설정과 최적화를 수행한다.
규칙기준 옵티마이저는 간단한 원칙에 다라 작동하지만, 현대 데이터베이스 환경에서는 비용기준 옵티마이저로 대체되는 추세다.
규칙기준 옵티마이저의 단점
- 통계정보의 부재로 인한 판단 오류
규칙기준 옵티마이저는 통계 정보를 사용하지 않기 때문에 현실적으로 발생하는 데이터의 분포나 로우 수에 따라 최적 경로를 판단하지 못하는 경우가 있다.
- 예를 들어 TAB1 테이블에 1,00,000개의 로우가 있고 TAB2 테이블에 100개의 로우가 있다고 가정할 때 전체 테이블을 스캔하는 것이 유리한지 판단하기 어려워 함.
- 분포의 편향성 무시
데이터 값이 고르게 분포되지 않았을 경우, 규칙기준 옵티마이저는 효율적이지 못한 경로를 선택할 수 있다.- 예를 들어 특정 조건에서 평균 분포도가 0.01%인 데이터를 대상으로 'LIKE' 조건을 사용할 경우 비효율적인 전체 스캔이 발생할 가능성이 높다.
- 실제 테스트의 부재
규칙기준 옵티마이저는 현실 데이터에 대한 테스트 없이 미리 정의된 규칙만으로 판단하므로 실제 처리 성능과의 괴리가 발생할 수 있어 실제 데이터 환경과 맞지 않는 실행계획을 수립하는 경우가 발생한다. - 수동 튜닝의 필요성
규칙기준 옵티마이저는 사용자가 수동으로 튜닝하거나 실행 계획을 변경하지 않으면 최적의 경로를 찾기 어렵기 때문에 시스템에 대한 경험과 이해가 부족한 사람에게는 큰 단점으로 작용된다. - 악성 실행 계획의 가능성
규칙기준 옵티마이저는 상황에 따라 악성 실행계획을 수립할 가능성이 있다.- 예를 들어, 잘못된 실행계획이 선택되면 처리 성능이 급격히 저하될 수 있다.
규칙기준 옵티마이저의 장점
- 명확성과 예측 가능성
규칙기준 옵티마이저는 규칙에 따라 실행계획을 수립하기 때문에 사용자가 결과를 아주 명확히 예측 할 수있어 예측 가능한 방식으로 실행계획을 수립해 문제를 미리 예측하고 대응할 수 있는 유연성을 제공하게 된다.
그리고 수동 카메라처럼 사용자 의도에 맞춘 결과를 미리 조율 할수 있도록 설계되어 있기 때문에 초기 설정만으로도 높은 성능과 안정적인 결과를 제공한다. - 사용자 통제 강화
사용자가 SQL 실행계획에 영향을 미칠 수 있도록 충분히 제어할 수 있는 구조를 지원하여 사용자 의도에 맞게 세부적인 실행계획을 조정할 수 있어 자유도가 높다. - 단순하고 효율적인 구조
비교적 단순한 구조를 바탕으로 작동하므로 처리 속도가 빠르고 간결하다. 이 덕에 초기 데이터를 분석하고 설계하는 데 유용하며, 정확성과 단순성을 기반으로 효과적인 해결책을 제공한다. - 전략적 인덱스 구성 가능성
전략적으로 인덱스를 설계하고 SQL을 작성하면 높은 확률로 최적화된 실행 계획을 얻을 수 있다. 따라서 사용자가 SQL 작성 단계에서 규칙에 대한 이해를 바탕으로 사전에 준비할 경우에 최적의 성능을 달성할 가능성이 높아진다. - 실무 중심의 접근성
실행 계획의 복잡성을 줄이고, 단순한 규칙을 통해 실무에서 쉽게 적용 할 수 있기 때문에 데이터베이스 구조와 SQL 작성 시 규칙을 활용하면 성능 최적화를 손쉽게 달성 할 수 있게 된다. - 테스트와 조정의 용이성
규칙 기반 구조는 실시간으로 SQL 실행 결과를 추적하고 조정하기 쉬워 실무에서 신속하게 문제를 해결 할 수 있다. 또한 TRACE 도구 등을 사용해 예상치 못한 결과를 확인하고 수정할 수 있다.
규칙기반 옵티마이저는 비록 통계 정보를 활용하는 비용기준 옵티마이저에 비해 한계가 있을 수 있지만, 규칙기준 옵티마이저는 단순성과 통제력을 기반으로 실무에서 여전히 강력한 도구로 활용될 수 있다. 특히 사전 설계와 예측 가능한 성능 최적화가 필요한 상황에서는 규칙기준 옵티마이저의 장점이 더욱 부각된다.
3.1.2.2. 비용기준 옵티마이저
비용기준 옵티마이저(CBO, Cost-Based Optimizer)는 관계형 데이터베이스에서 최적의 실행계획을 도출하기 위해 다양한 통계 정보를 기반으로 각 처리 방법에 대한 비용을 계산하고, 가장 비용이 적게 드는 경로를 선택하는 방식으로 작동한다. 이는 관계형 데이터베이스의 이상형에 부합하며, 대부분의 최신 DBMS가 채택하고 있는 형태다.
이를 위해서 미리 작성된 다양한 통계 정보를 참조하며, 이 통계 정보는 DBMS 제품과 버전에 따라 다소 차이가 있는데 주요 통계 정보는 아래와 같다.
- 테이블의 로우 수, 블록 수
- 로우당 평균 크기, 컬럼별 상수값의 종류 및 분포
- 컬럼의 NULL 값 비율
- 인덱스의 깊이(Depth), 레벨(Level), 최대/최소값, 리프(Leaf) 블록 수
- 클러스터링 패턴, 자동 시스템 I/O 및 CPU 사용량 등
비용기준 옵티마이저는 다양한 요소를 활용해 최적화를 시도하나, 각 요소의 상호작용이 복잡하며, 결과값은 데이터 값에 따라 크게 달라질 수 있다. 따라서 계산된 통계 정보가 실제 데이터와 일치하지 않으면 예측한 결과와 다를 수 있다.
통계 정보를 기반으로 판단하기 때문에 현실적인 요소를 충분히 반영하지 못할 가능성도 있어 통계 정보의 신뢰도가 낮은 경우 최적화된 실행계획 도출에 오류가 발생할 수 있다.
비용기준 옵티마이저의 장점
최대의 장점으로 현실을 감안한 판단을 할 수 있다.
비용기준 옵티마이저(CBO)는 조건의 분포도를 기반으로 최적화된 실행 계획을 수립한다.
예를 들어, 컬럼 A는 평균 분포도가 50%인 값들로 구성된 인덱스를, 컬럼 B는 10,000개의 값으로 구성되며 평균 분포도가 0.01%인 인덱스를 가지고 있다고 가정하자. WHERE 절 조건으로 A = '10' AND B LIKE '123%'를 부여했을 때, 규칙기준 옵티마이저(RBO)는 모든 조건을 단순하게 처리하려 하지만, 비용기준 옵티마이저(CBO)는 분포도가 좁은 컬럼 B를 우선적으로 처리하는 실행 계획을 선택해 보다 효율적인 결과를 도출한다.
분포도는 처리 범위를 최소화하고 실행 효율을 높이는 핵심 요소다. 분포도가 낮은 컬럼 B는 처리 대상이 적기 때문에 좁은 범위를 처리하여 최적화 성능을 향상시킨다. 그러나 단일 컬럼 인덱스의 분포도 계산은 상대적으로 간단한 반면, 여러 컬럼이 결합된 인덱스는 상호작용까지 고려해야 하므로 계산이 복잡해질 수 있다.
옵티마이저는 이러한 문제를 해결하기 위해 컬럼 값의 분포를 분석하는 히스토그램을 활용한다. 대표적인 방법으로는 넓이 균등 히스토그램과 높이 균등 히스토그램이 있다. 넓이 균등 히스토그램은 값을 일정한 범위로 나누어 저장하고, 높이 균등 히스토그램은 각 범위가 동일한 데이터 수를 가지도록 나눈다. 특히 높이 균등 히스토그램이 더 효율적인 경우가 많다.
DBMS는 dbms_stats와 같은 도구를 통해 이러한 통계 정보를 자동으로 수집하고 관리하며, 사용자가 직접 설정하지 않아도 기본적으로 최적화된 값을 제공한다. 이로 인해 사용자는 통계 분석의 부담을 덜고, 실행 계획의 최적화를 효과적으로 지원받을 수 있다.
비용기준 옵티마이저는 조건의 분포도를 분석하여 처리 범위를 최소화하고 최적화된 실행 경로를 선택하는 데 중요한 역할을 하지만, 통계 정보의 품질과 정확성이 최적화 결과에 직접적인 영향을 미친다.
통계정보의 관리를 통해 최적화를 제어 할 수 있다.
비용기준 옵티마이저를 효율적으로 활용하려면 통계정보를 체계적으로 관리하고 제어해야 한다.
통계정보는 데이터베이스의 최적화된 실행계획을 수립하는 데 필수적인 요소로, 데이터 변화에 따라 주기적으로 갱신해야 한다. 테이블의 데이터는 삽입(Insert), 갱신(Update), 삭제(Delete) 작업에 의해 변동되므로, 최신 상태를 반영하지 않은 통계정보는 실행계획의 신뢰성을 저하시킬 수 있다.
이를 해결하기 위해 DBMS에서 제공하는 GATHER_DATABASE_STAT와 같은 통계정보 수집 프로시저를 활용하거나, 작업 스케줄러를 통해 자동화된 주기적 갱신을 수행하는 것이 중요하다.
통계정보를 갱신하는 주기는 데이터 변화의 빈도에 따라 조정할 필요가 있다. 변동이 잦은 테이블의 경우 짧은 주기로 통계정보를 갱신하고, 변동이 적은 테이블은 갱신 주기를 길게 설정하는 것이 적절하다.
그러나 통계정보를 너무 자주 갱신하면 시스템 부하가 증가할 수 있으므로, 상황에 맞는 균형 잡힌 주기 설정이 필요하다.
특수한 경우에는 통계정보를 수집하지 않고 기존 실행계획을 유지하는 전략이 유용하다. 예를 들어, 이미 튜닝이 완료된 실행계획의 변경을 피하고자 할 때는 통계정보 갱신을 생략함으로써 실행계획의 안정성을 유지할 수 있다. 반면, 동적 SQL의 경우 변수 값에 따라 실행계획이 매번 달라질 수 있으므로 통계정보만으로 최적화가 어렵다. 이 경우 변수 값을 기반으로 유연한 실행계획을 수립하는 방식을 활용해야 한다.
또한, 통계정보가 부족하거나 부정확한 경우, 규칙기준 옵티마이저(RBO)를 활용하거나 SQL 힌트를 사용해 실행계획을 고정하는 방법도 실질적인 대안이 될 수 있다. 특히 대용량 데이터를 처리할 때는 실행계획 변경으로 인한 성능 저하를 방지하기 위해 논리적으로 최적화된 실행계획을 고정하는 것이 현실적이고 효과적인 방법이다.
비용기준 옵티마이저의 성능을 극대화하려면 통계정보를 적절히 관리하고 갱신하며, 필요에 따라 실행계획을 고정하거나 유연하게 조정해야 한다. 이를 통해 데이터베이스 성능을 안정적으로 유지하고 최적화된 실행계획을 지속적으로 적용할 수 있다.
옵티마이저를 깊이 이해하고 있지 않더라도 최소한의 성능이 보장된다.
비용기준 옵티마이저의 주요 장점 중 하나는 실행계획에서 최악의 상황이 발생할 확률을 감소시킨다는 점이다.
사용자가 작성한 SQL의 품질이 낮더라도, 옵티마이저는 다양한 통계 정보를 활용해 실행 경로를 최적화함으로써 안정적인 수행 성능을 보장할 가능성을 높인다. 마치 자동 카메라가 전문적이지 않은 사용자가도 높은 품질의 사진을 얻을 수 있도록 돕는 것처럼, 비용기준 옵티마이저는 사용자의 실수로 인한 실행 계획의 오류를 최소화하는 역할을 한다.
적절히 설계되지 않은 인덱스 구조나 품질이 낮은 SQL이라도, 비용기준 옵티마이저는 모든 가능성을 분석해 최소 비용의 경로를 선택하려 노력한다. 이를 통해 최적화된 실행 계획이 자동으로 생성되며, 결과적으로 최악의 상황이 발생할 확률이 크게 줄어든다. 또한, 사용자가 쿼리를 재작성하거나 변경하는 과정에서 옵티마이저는 새로운 계획을 재수립하여 실행 성능을 유지하거나 향상시킨다.
그러나 비용기준 옵티마이저가 통계 정보를 활용하여 최적화를 수행한다고 하더라도, 최적의 실행 경로를 보장하기 위해서는 사용자의 전략적 접근도 필요하다. 옵티마이저는 이미 존재하는 경로 중 가장 효율적인 것을 선택할 수는 있지만, 논리적으로 최적의 경로를 새롭게 설계해 주는 것은 아니다. 따라서 사용자는 전략적인 인덱스 구성과 효율적인 SQL 작성을 통해 옵티마이저의 기능을 최대한 활용해야 한다.
비용기준 옵티마이저의 단점
실행예측이 어려움
비용기준 옵티마이저는 실행 계획을 최적화하기 위한 강력한 도구이지만, 실행 계획을 미리 예측하기 어려운 한계를 가진다. SQL에서 사용하는 테이블과 컬럼의 통계 정보를 미리 알고 있다고 하더라도 변수값이나 조건에 따라 실행 계획이 달라질 수 있다. 예를 들어, 특정 인덱스가 추가되거나 통계 정보가 갱신될 경우, 과거와는 다른 실행 계획이 생성되어 예상하지 못한 성능 문제가 발생할 수 있다.
또한, WHERE 절에서 지정된 변수값에 따라 실행 계획이 크게 변할 수 있으며, 시스템 상태(메모리, CPU 등)와의 조합으로 더욱 복잡한 결과를 초래할 수 있다. 통계 정보가 변경되거나 재생성된 경우에도 기존의 실행 계획과 다른 결과가 나타날 가능성이 크기 때문에, 사용자가 실행 계획을 사전에 예측하고 준비하기 어렵다.
이러한 불확실성은 실행 계획의 적절성을 판단할 능력이 부족한 사용자에게 더 큰 문제로 작용하며, 최적화를 온전히 옵티마이저에 의존해야 하는 상황을 만든다. 더불어, 실행 계획이 상황에 따라 수시로 변하기 때문에 실시간으로 이를 예측하고 대응하는 데에도 한계가 있다. 결과적으로, 사용자는 실행 계획의 변경으로 인한 성능 변화를 대비하기 위해 통계 정보를 주기적으로 관리하고, 실행 계획을 면밀히 검토하는 노력이 필요하다.
버전에 따른 변화
비용기준 옵티마이저의 최적화 결과는 사용 중인 데이터베이스 제품이나 버전에 따라 달라질 수 있다. 이러한 변화는 버전 업그레이드 이후 기존 실행계획이 변경되거나 예기치 못한 시스템 성능 저하로 이어질 가능성을 포함한다. 과거에 실행되던 실행계획과 동일한 결과를 보장하지 못하는 경우도 있다.
DBMS 업체들은 사용자들에게 옵티마이저의 최적화 절차와 변경 사항에 대한 구체적인 정보를 제공하지 않는 경우가 많아 사용자들이 변경된 실행계획의 원인을 직접 파악하기 어려울 수 있다. 이러한 변화를 통해 기존의 실행계획을 고정시키거나 새로운 최적화 경로를 수립하는 방식으로 대응해야 한다.
결론적으로, 비용기준 옵티마이저는 계속 발전 중이며, 이러한 변화는 시스템 최적화 과정에서 일정한 영향을 미칠 수 있다. 따라서 버전에 따른 최적화 변화에 대한 이해와 준비가 필요하다.
실행계획 제어가 어려움
규칙기준 옵티마이저는 사용자들이 명시적으로 규칙을 설정하여 실행계획을 예상하고 제어할 수 있는 반면, 비용기준 옵티마이저는 다양한 통계 정보를 활용하여 최적화가 이루어지기 때문에 사용자가 제어하기 어렵다.
특히, 사용자 쿼리가 필요에 따라 다시 변형되거나 최적화 과정에서 변형되는 경우, 실행 환경이 달라지면서 기존 예측과 다른 실행계획이 도출될 가능성이 있다. 이는 실행계획 제어를 더욱 복잡하게 만드는 요인이다.
결론적으로, 실행계획의 제어는 비용기준 옵티마이저의 특성상 사용자가 모든 것을 명확히 예상하기 어렵다는 점에서 제약이 따른다. 이를 보완하기 위해 사용자들은 SQL 작성 시 옵티마이저의 작동 원리를 최대한 이해하고, 실행계획을 명확히 하기 위한 힌트를 적절히 활용해야 한다.
옵티마이저의 발전 방향
옵티마이저는 비용기준(CBO)을 중심으로 발전하고 있으며, 완벽한 통계 정보를 기반으로 높은 확률의 최적화를 가능하게 하는 것이 목표이다. 과거 규칙기준과 비용기준이 공존하던 시절에는 통계 정보의 활용이 제한적이었으나, 현재는 테이블과 데이터의 특성을 세밀하게 반영하는 수준으로 진화하고 있다.
통계정보의 중요성
테이블의 특성과 데이터의 분포는 통계 정보의 수집과 관리를 통해 예측 가능한 방식으로 변화를 반영할 수 있다. 특히, 대용량 데이터나 변화가 잦은 데이터를 효과적으로 관리하는 것은 필수적이며, 통계 정보를 테이블이나 인덱스 단위로 관리하는 것이 더 효율적이다. 이러한 방식은 실질적으로 관리 단위를 최적화하여 높은 최적화 확률을 유도하는 방향으로 나아가고 있다.
향후 과제
향후 옵티마이저는 더 세분화된 통계 정보와 사용자의 의도를 반영하는 세밀한 기능을 필요로 한다. 특히 전략적 인덱스 구성과 효율적인 SQL 작성은 사용자의 몫으로 남아 있다. 마치 전쟁에서 전략적 배치로 승리를 쟁취하는 것처럼, 단위 SQL 수준에서 전략적 접근이 요구된다.
통계정보 관리를 위한 제언
관리자의 역할과 접근 방식
통계 정보 관리는 단순히 사용자의 부재를 통해 자동화된 방식으로 처리되는 것뿐만 아니라, 설계자와 데이터 아키텍트가 능동적으로 시스템 통계를 관리할 필요가 있다. 이러한 관리가 시스템 성능 최적화의 중요한 요인으로 작용한다.
통계정보 관리의 구체적 방법
DBMS_STATS 활용
DBMS_STATS 패키지는 테이블과 인덱스 통계 정보를 효율적으로 관리할 수 있도록 돕는다. 일반적으로 전체 데이터의 5% 이하를 샘플로 설정해 통계 정보를 수집하며, ESTIMATE_PERCENT 파라미터를 활용하면 자동으로 최적의 샘플링 비율을 설정할 수 있다.
시스템 통계의 도입
초기 비용기준 옵티마이저는 하드웨어 성능을 충분히 반영하지 못했으나, 이제는 CPU와 I/O 성능과 같은 시스템 성능 요소를 통계로 반영할 수 있다. DBMS_STATS.GATHER_SYSTEM_STATS 프로시저를 사용하면 정기적으로 시스템 통계를 수집하고 이를 실행 계획에 반영할 수 있다.
자동화된 프로세스 활용
IMPORT_SYSTEM_STATS와 같은 자동화된 프로시저를 통해 통계 정보를 효과적으로 관리하고 교체할 수 있다. 이 기능은 사용자의 개입 없이도 시스템이 최적화된 상태를 유지하도록 지원한다.
통계정보 관리의 최적화 사례
공유 풀(Shared Pool) 자원 사용 최소화
기존 통계 정보와 새로운 통계 정보 간의 균형을 유지해 자원의 과도한 사용을 방지해야 한다. 이를 통해 시스템 리소스의 낭비를 줄일 수 있다.
통계 정보의 자동 저장 및 복원
새로운 통계 정보를 생성할 경우, 이전 버전을 자동으로 저장하여 필요 시 복원할 수 있다. RESTORE 프로시저를 활용하면 이전 통계 정보를 복원해 안정적으로 시스템을 운영할 수 있다.
3.1.2.3. 옵티마이저 모표(Goal) 선택
옵티마이저 목표의 개념
옵티마이저는 주어진 조건에서 최적화 작업을 수행하며, 다양한 조건에서 최적의 실행 계획을 도출하는 것을 목표로 한다. 이를 위해 시뮬레이션을 통해 현실 상황을 모델링하고, 가상의 실행 시뮬레이션을 통해 결과를 예측한다. 입력 데이터와 통계 정보의 정확도에 따라 실행 계획이 크게 달라질 수 있다.
목표 설정의 중요성
최적화 목표는 환경에 따라 다르게 설정된다. 단기적 최적화를 원할 경우 제한된 범위 내에서 가장 빠른 실행 계획을 선택하며, 장기적으로는 모든 데이터를 처리해야 하는 상황에서 전체 범위의 효율성을 고려해야 한다.
목표 설정의 기준
목표 설정은 우선순위와 전체 범위라는 기준에 따라 다르게 적용된다. 우선순위 기반 최적화는 특정 범위나 데이터를 먼저 처리하는 것을 목표로 하며, 전체 범위 기반 최적화는 전체 데이터를 처리하면서 효율성을 높이는 데 중점을 둔다.
ALL_ROWS 모드
ALL_ROWS 모드는 모든 데이터를 처리하는 데 최적화된 실행 계획을 제공한다. 배치 처리(Batch Processing)에 적합하며, 높은 처리량(Best Throughput)을 목표로 한다. 이 모드는 주로 OLAP(Online Analytical Processing)과 같은 대규모 데이터 처리 환경에서 사용된다.
FIRST_ROWS 모드
FIRST_ROWS 모드는 제한된 데이터 범위에서 빠른 결과를 제공하는 데 초점을 맞춘다. OLTP(Online Transaction Processing) 환경에 적합하며, 즉각적인 응답이 중요한 작업에 사용된다. 예를 들어 특정 고객의 데이터를 검색하거나 소규모 조회 작업에서 효율적이다.
FIRST_ROWS_n 모드
FIRST_ROWS_n 모드는 n개의 데이터를 먼저 처리하는 데 최적화된 실행 계획을 제공한다. 특정 조건에서 초기 데이터에 대한 빠른 처리가 필요할 때 활용되며, OLTP와 OLAP 환경 모두에 적합하지만 주로 응답 시간을 목표로 설정할 때 사용된다.
CHOOSE 모드
CHOOSE 모드는 과거 옵티마이저의 기본 모드로, 규칙기준과 비용기준 방식을 혼합하여 최적화를 수행한다. 하지만 현재는 대부분의 DBMS에서 ALL_ROWS 또는 FIRST_ROWS 모드로 대체되었다.
최적화 목표와 모드 설정의 연관성
실행 환경에 따라 모드를 적절히 선택해야 한다. OLTP 환경에서는 FIRST_ROWS 모드가 주로 사용되며, OLAP 환경에서는 ALL_ROWS 모드가 일반적으로 사용된다. 모드 설정에 따라 동일한 SQL 문이라도 다른 실행 계획이 도출될 수 있으며, 실제 환경에서는 기본적으로 ALL_ROWS 모드가 사용되지만 응답 속도가 중요한 경우 FIRST_ROWS 모드로 전환해 최적화를 진행할 수 있다.
옵티마이저 모드의 결정 기준
OLTP 시스템에서의 모드 선택
OLTP(Online Transaction Processing) 시스템에서는 빠른 응답 속도가 중요하기 때문에 FIRST_ROWS 모드를 선택하는 것이 적합하다. 이 모드는 주어진 실행 계획을 가능한 한 빠르게 처리해 즉각적인 결과를 제공한다.
OLAP 시스템에서의 모드 선택
OLAP(Online Analytical Processing) 시스템에서는 대량의 데이터를 처리하고 분석하는 것이 중요하다. 따라서 ALL_ROWS 모드를 선택하여 전체 데이터를 효율적으로 처리하고 최적의 처리량(Best Throughput)을 달성해야 한다.
운영 중 모드 변경의 위험성
운영 중에 옵티마이저 모드를 변경하는 것은 시스템 안정성에 큰 영향을 미칠 수 있으므로 주의가 필요하다. 예를 들어, 운동 경기 도중 전략을 바꾸는 것처럼 실행 중인 시스템에서 모드를 변경하는 것은 대부분의 경우 적합하지 않다.
모드 변경으로 인한 대책 마련
모드를 변경하거나 DBMS를 업그레이드하면서 옵티마이저가 변경된 경우, 변경의 충격을 흡수할 수 있는 충분한 대책이 필요하다. 기존에 사용하던 실행 계획을 유지하거나 적절한 파라미터를 설정해 혼란을 방지해야 한다.
실행 계획 유지 방법
실행 계획의 일관성을 유지하기 위한 두 가지 주요 방법이 있다.
- 초기 설정 파라미터로 기존의 옵티마이저 버전을 지정한다.
- 실행 계획 요약본(아우트라인)을 생성해 이를 참조하도록 설정한다.
OPTIMIZER_FEATURES_ENABLE 파라미터의 활용
이 파라미터는 과거에 사용했던 옵티마이저 버전을 지정하여 업그레이드로 인한 불필요한 변경을 방지하는 데 유용하다. 기본적으로는 자동으로 최신 버전으로 전환되지만, 안정성을 유지하려면 직접 설정하는 것이 좋다.
새로운 기술 수용에 대한 고려
새로운 기술을 무조건적으로 수용하는 것은 바람직하지 않다. 새로운 변화가 필요하지 않은 안정적인 환경에서는 기존의 안정된 실행 계획을 유지하는 것이 적합하며, 무리하게 새로운 기능을 도입하지 않아야 한다.
옵티마이져 모드와 관련 파라미터 지정
CURSOR_SHARING
CURSOR_SHARING 파라미터는 SQL 문장에서 상수값을 변수로 변환해 파싱 효율성을 높이는 데 사용된다. 기본값은 EXACT로, 상수값을 그대로 유지하지만, FORCE 또는 SIMILAR로 설정하면 변수화를 통해 실행 계획을 공유할 수 있다. 특히, SIMILAR 설정은 유사한 SQL 문에서도 동일한 실행 계획을 재사용할 수 있어 효율적이다.
DB_FILE_MULTIBLOCK_READ_COUNT
이 파라미터는 테이블 전체 스캔 또는 인덱스 고속 스캔 시 I/O 작업의 효율성을 조정하는 데 사용된다. 기본값은 시스템에 따라 다르지만, 입출력 성능이 높은 환경에서는 이 값을 증가시켜 더 많은 블록을 한 번에 읽도록 설정할 수 있다.
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_CACHING은 인덱스 Nested Loops 조인에서 인덱스 블록이 캐시에 유지될 확률을 조정한다. 기본값은 0이며, 인덱스가 재사용될 가능성이 높은 환경에서는 이 값을 높여 인덱스 액세스 효율을 높일 수 있다. 단, 과도한 설정은 옵티마이저의 실행 계획에 부정적 영향을 줄 수 있으므로 주의해야 한다.
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_INDEX_COST_ADJ는 인덱스 접근 방식의 비용을 조정하는 파라미터로, 기본값은 100이다. 값을 낮추면 인덱스 접근 방식의 우선순위를 높일 수 있어, 특정 환경에서 더 빠른 실행 계획을 유도할 수 있다.
Dynamic Sampling (동적 표본화)
Dynamic Sampling은 통계 정보가 부족한 경우 실행 시점에서 표본 데이터를 수집해 통계 정보를 보완하는 기능이다. 기본값은 0이며 기능이 비활성화된 상태지만, 특정 상황에서는 표본 추출 수준을 높여 실행 계획의 정확성을 개선할 수 있다. 일반적으로 1~2로 설정하는 것이 적절하며, 과도한 표본 추출은 전체 실행 속도를 저하시킬 수 있다.
3.1.2.4. 실행계획의 고정화(Stability)
실행계획은 환경과 조건에 따라 동적으로 최적화되지만, 예상치 못한 변경이 발생하면 성능 저하가 발생할 수 있다. 특히 동일한 SQL 문이 실행될 때마다 다른 실행계획이 적용되면 운영의 예측 가능성이 낮아지고, 튜닝된 쿼리도 불안정해질 위험이 있다. 이러한 문제를 방지하기 위해 실행계획을 고정하는 방법이 필요하다.
이를 위한 대표적인 방법으로 아웃라인(Outline) 기능을 활용할 수 있다. 실행계획을 미리 저장해 동일한 SQL 문이 실행될 때 참조하도록 하면 불필요한 변경을 방지하고 성능을 안정적으로 유지할 수 있다. 하지만 실행계획을 지나치게 고정하면 새로운 최적화 기회를 차단할 위험이 있으므로, 필요에 따라 적절히 조정해야 한다.
보다 유연한 관리 방법으로 카테고리(Category) 개념을 도입할 수도 있다. 예를 들어, 주간과 야간에 실행되는 SQL을 각각 분류하여 최적화된 실행계획을 적용하면 업무 성격이 다른 환경에서도 성능을 일정하게 유지할 수 있다.
빈번한 통계정보 갱신이 필요한 경우에도 실행계획 고정화는 유용하다. 완벽하게 튜닝된 시스템에서는 불필요한 실행계획 변경을 방지하는 것이 성능 안정성 유지에 필수적이기 때문이다. 따라서 실행계획 고정화는 예측 가능한 성능을 보장하고, 안정적인 운영을 위한 중요한 전략으로 활용될 수 있다.
가) 아우트라인의 생성과 조정
SQL 실행 계획은 데이터베이스 환경과 조건 변화에 따라 달라질 수 있으며, 이로 인해 예상치 못한 성능 저하가 발생할 가능성이 있다. 이를 방지하기 위해 아우트라인(Outline)을 활용하면 특정 SQL 문에 대해 일관된 실행 계획을 유지할 수 있다. 아우트라인을 적용하면 동일한 SQL 문이 실행될 때마다 예측 가능한 결과를 보장하고, 불필요한 실행 계획 변경을 방지할 수 있어 안정적인 성능을 유지할 수 있다.
이를 위해 DBMS_OUTLN과 DBMS_OUTLN_EDIT 패키지가 제공되며, 이를 활용하면 실행 계획을 저장하고, 필요에 따라 수정하거나 삭제할 수도 있다. 주요 기능으로는 CREATE_OUTLINE, DROP_BY_CAT, DROP_UNUSED, UPDATE_BY_CAT 등의 프로시저가 있으며, 이를 통해 새로운 아우트라인을 생성하고 기존 아우트라인을 삭제하거나 변경할 수 있다. 또한, 특정 SQL 문에 대한 실행 계획을 관리하기 위해 GENERATE_SIGNATURE를 활용할 수도 있다.
아우트라인은 모든 SQL 문에 대해 자동으로 생성될 수도 있고, 특정 SQL 문에 대해서만 개별적으로 생성할 수도 있다. 일반적으로 CREATE_STORED_OUTLINES 파라미터를 설정하여 아우트라인을 생성하고 저장하는 방식이 사용된다.
'TRUE'로 설정하면 'DEFAULT'라는 카테고리에 아우트라인이 자동 생성되며, 새로운 실행 계획이 추가될 때 이를 자동으로 저장할 수 있다. 'FALSE'로 설정하면 기존 아우트라인만 유지되며, 새로운 실행 계획은 추가되지 않는다.
생성된 아우트라인을 실제 환경에서 적용하려면 USE_STORED_OUTLINES 파라미터를 TRUE로 설정하여 실행 계획이 항상 해당 아우트라인을 따르도록 해야 한다. 또한, 실행 계획을 수정해야 할 경우 ALTER OUTLINE 명령어를 사용하면 기존 아우트라인의 이름을 변경하거나 특정 값으로 재생성할 수도 있다.
일반적으로 생성된 아우트라인은 모든 세션에 적용되지만, 특정 세션에서만 실행 계획을 고정하고 싶다면 개별 아우트라인(Private Outlines)을 사용할 수 있다. 개별 아우트라인은 특정 세션에서만 유효하며, 다른 세션에는 영향을 미치지 않기 때문에 특정 실행 계획을 유지하면서도 수정이 필요한 경우 유용하게 활용할 수 있다.
그 다음은 개별 아우트라인을 생성하는 과정을 살펴보려 한다. 개별 아우트라인을 생성하는 과정은 다음과 같다.
먼저 기존 아우트라인에서 새로운 개별 아우트라인을 복제한다.
CREATE PRIVATE OUTLINE prv_ol_1 FROM outline1;그 후 DBMS_OUTLN_EDIT 패키지를 활용하여 필요에 따라 아우트라인을 수정하거나 조정한다.
수정이나 조정이 끝났으면 조정된 아우트라인을 실제 적용하기 전에 USE_PRIVATE_OUTLINES을 TRUE로 설정하여 테스트를 수행한다.
그 후 검증이 완료되면 기존 아우트라인을 새로운 아우트라인으로 대체해준다.
CREATE OR REPLACE OUTLINE outline1 FROM PRIVATE prv_ol_1;적용이 완료되면 USE_PRIVATE_OUTLINES을 FALSE로 설정하여 개별 아우트라인 사용을 종료한다.
나) 아우트라인의 관찰
아우트라인은 데이터베이스의 딕셔너리 테이블에 저장되며, 제공된 뷰를 통해 내용을 확인할 수 있다. USER_OUTLINES나 USER_OUTLINE_HINTS와 같은 뷰를 활용하면 생성된 아우트라인의 세부 정보를 조회할 수 있으며, 권한에 따라 ALL 또는 DBA 뷰를 활용하여 보다 포괄적인 정보를 확인할 수도 있다.
아래는 특정 SQL 문에 대해 생성된 아우트라인을 조회한 결과를 나타낸다.
select empno, ename, job, loc
from emp e, dept d
where e.deptno = d.deptno and e.empno = 7856
order by loc;이 쿼리에 대해 생성된 아우트라인의 주요 정보는 다음과 같다.
- NAME - 자동으로 생성된 아우트라인의 명칭이며, 테이블의 식별자로 사용된다.
- CATEGORY - 아우트라인이 속하는 카테고리를 나타낸다.
- USED - 해당 아우트라인이 사용된 여부를 나타낸다.
- TIMESTAMP - 아우트라인이 생성된 시간을 기록한다.
- VERSION - 아우트라인이 적용된 데이터베이스 버전을 표시한다.
- SQL_TEXT - 실행 계획을 저장하는 SQL 문을 나타낸다.
- SIGNATURE - 아우트라인의 고유한 식별자로 사용된다.
그리고 아우트라인은 실행된 SQL 문을 기반으로 자동 생성될 수 있고, 특정 SQL문이 동일한 실행 계획을 유지하도록 관리되게 된다. 자동 생성된 아우트라인의 특징이 있는데 CURSOR_SHARING이 SIMILAR로 설정된 경우, 조건절의 상수 값이 바인드 변수로 변환되어 저장될 수 있다.
그리고 SQL 문이 단순한 텍스트가 아니라 실행 시 변환되는 형태(예: RAW)로 저장되며, 동일한 SQL이라도 다른 카테고리에서 생성될 경우 명칭이 달라질 수 있다.
아우트라인이 저장하는 실행 계획은 완전한 실행 계획의 형태는 아니지만, 조인 방식, 인덱스 사용 여부, 비용 정보 등을 포함한다. 아래는 저장된 아우트라인의 힌트 정보를 조회한 결과이다.
| HINT# | HINT_TEXT | NODE# | STAGE# | TABLE_POS | TABLE_NAME | COST | CARDINALITY |
| 1 | NO_EXPAND | 1 | 3 | 0 | DEPT | 0 | 0 |
| 2 | PQ_DISTRIBUTE(D NONE NONE) | 1 | 3 | 0 | DEPT | 0 | 0 |
| 3 | USE_NL(D) | 1 | 3 | 0 | DEPT | 2 | 1 |
| 4 | ORDERED | 1 | 3 | 0 | DEPT | 0 | 0 |
| 5 | NO_FACT(D) | 1 | 3 | 0 | DEPT | 0 | 0 |
| 6 | NO_FACT(E) | 1 | 3 | 2 | EMP | 1 | 27 |
| 7 | INDEX(D PK_DEPT) | 1 | 3 | 2 | EMP | 1 | 1 |
| 8 | INDEX(E PK_EMP) | 1 | 3 | 2 | EMP | 1 | 1 |
| 9 | NOREWRITE | 1 | 1 | 0 | 0 | 0 |
이 테이블에서 볼 수 있듯이, 특정 테이블에 대한 조인 방법, 인덱스 활용 방식, 그리고 실행 비용과 관련된 정보가 포함되어 있다. 하지만 이 데이터는 실행 계획의 전체적인 모습을 나타내지는 않으며, 저장된 힌트를 기반으로 실제 실행 계획이 결정된다.
다) 옵티마이저 업그레이드 시의 적용
옵티마이저는 지속적으로 발전하며 새로운 최적화 기법과 처리 경로를 추가하지만, 이러한 변화가 시스템 안정성에 미치는 영향은 결코 가볍지 않다. 기존에 안정적으로 운영되던 시스템이 업그레이드 이후 예상치 못한 성능 저하를 겪을 수 있으며, 특히 실행 계획의 변경으로 인해 업무 처리 속도가 급격히 변할 가능성이 있다. 이러한 변화를 효과적으로 관리하지 않으면 시스템 성능이 불안정해지고, 운영에 혼란이 발생할 수 있다.
업그레이드는 필연적으로 시스템의 개선을 위한 과정이지만, 운영 환경에서는 갑작스러운 변화가 부담으로 작용할 수 있다. 사회적 변화가 제도적 혼란을 초래하는 것처럼, 옵티마이저의 변화도 실행 계획을 예측하기 어렵게 만들어 시스템 성능에 악영향을 미칠 수 있다. 따라서 업그레이드로 인한 혼란을 방지하기 위해서는 미리 대비하고, 문제 발생 시 즉각적으로 대응할 수 있는 체계적인 전략이 필요하다.
가장 효과적인 방법은 업그레이드된 옵티마이저가 기존 실행 계획에 미치는 영향을 최소화하는 것이다. 이를 위해 운영 환경과 동일한 테스트 환경을 구축하고 철저한 검증을 거친 후 업그레이드를 적용하는 것이 바람직하다. 하지만 현실적으로 운영 환경과 완전히 동일한 테스트 환경을 구성하는 것은 쉽지 않으며, 실제 적용 과정에서 발생하는 다양한 변수들을 고려해야 한다.
한 가지 현실적인 대안은 기존 실행 계획을 그대로 유지하면서 점진적으로 변화를 적용하는 것이다. 이를 위해 일정 기간 동안 기존 규칙기준 옵티마이저(Rule-Based Optimizer, RBO)를 유지한 후 비용기준 옵티마이저(Cost-Based Optimizer, CBO)로 전환하는 방식을 고려할 수 있다. 규칙기준 옵티마이저를 사용하면 기존 실행 계획이 유지되므로 예상치 못한 성능 저하를 방지할 수 있다.
현재 사용 중인 SQL에 대해 특정 카테고리를 설정하여 아우트라인을 생성하면 업그레이드 전후 실행 계획을 비교하고 유지할 수 있다.
ALTER SESSION SET CREATE_STORED_OUTLINES = category_name;필요하다면 전체 세션에 적용하여 일괄적으로 관리할 수도 있다.
중요한 SQL 문에 대해서는 아우트라인을 생성하고 유지하면서 장기적으로 비용기준 옵티마이저로 전환하는 계획을 세운다. 일정 기간 동안 아우트라인을 생성한 후, 새로운 실행 계획이 생성되지 않도록 다음 명령어를 사용하여 아우트라인 생성을 중지한다.
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;옵티마이저가 최신 상태의 실행 계획을 정확하게 도출하도록 하기 위해 통계 정보를 정기적으로 갱신하고, 실행 환경의 변화를 반영할 필요가 있다. 기존 환경에서 규칙기준 옵티마이저를 사용하고 있었다면, 일정한 검증을 거친 후 비용기준 옵티마이저로 전환하여 새로운 실행 계획이 적용될 수 있도록 한다.
업그레이드 후 예상치 못한 실행 계획 변경을 방지하기 위해 기존에 생성된 아우트라인을 적용할 수 있다.
ALTER SESSION SET USE_STORED_OUTLINES = category_name;또한, 기존 실행 계획을 일괄적으로 적용하려면 다음 명령어를 사용할 수 있다.
ALTER SESSION SET USE_STORED_OUTLINES = our_category_name;3.1.2.5. 옵티마이져의 한계
옵티마이저는 최적의 실행 계획을 찾기 위해 다양한 통계 정보를 활용하지만, 근본적인 한계를 가지고 있다. 가장 큰 문제는 미래의 데이터를 정확하게 예측할 수 없다는 점이다. 예를 들어, 특정 회사의 주가가 내일 오를지 내릴지를 정확하게 알 수 없는 것처럼, 데이터 분포와 실행 계획이 항상 일관되게 유지된다는 보장은 없다.
비록 옵티마이저가 많은 정보를 가지고 있다 하더라도, 특정 컬럼의 값이 일정한 분포를 따를 것이라고 단정할 수 없다. 어떤 경우에는 특정 값들이 특정 범위에 몰려 있거나, 예외적으로 높은 빈도를 보일 수 있다. 이러한 경우 실행 계획이 예상과 다르게 동작할 가능성이 크며, 처리 성능이 극단적으로 차이가 날 수 있다.
컬럼의 값에 대한 분포를 명확히 알고 있다면 실행 계획을 더욱 정교하게 수립할 수 있겠지만, 현실적으로 이는 불가능하다. 각 컬럼의 값이 단순한 ‘=’ 연산이 아니라 ‘LIKE’, ‘BETWEEN’ 등의 연산을 포함할 경우, 분포를 예측하는 것은 더욱 어려워진다. 또한, 옵티마이저는 실행 계획을 수립할 때 변수의 값을 미리 알 수 없는 경우가 많다. 즉, 실행 시점에서 바인딩된 변수 값에 따라 실행 계획이 결정되기 때문에, 사전에 정확한 계획을 세우는 것이 어렵다.
이 문제를 해결하기 위해 히스토그램이 도입되었지만, 모든 데이터의 정확한 분포를 반영하지는 못한다. 특히, 컬럼 값의 범위가 넓거나, 예상치 못한 값이 등장할 경우 옵티마이저의 판단이 흔들릴 수 있다. 또한, 컬럼들이 조합되어 특정한 인덱스를 구성할 때, 각각의 컬럼이 독립적으로 분포하는 것이 아니라 서로 영향을 미칠 수 있다. 따라서, 옵티마이저가 모든 경우의 수를 고려하여 최적의 실행 계획을 찾는 것은 사실상 불가능하다.
실제 데이터베이스 환경에서는 컬럼 간의 관계가 복잡하며, 조인되는 테이블의 크기나 필터링 조건에 따라 최적의 실행 계획이 달라진다. 예를 들어, 300개의 ‘부서’와 10개의 ‘사업장’이 존재한다고 가정하면, 일반적인 결합 방식에서는 3000개의 조합이 만들어질 수 있다. 하지만 특정한 사업장은 특정한 부서와만 결합하는 경향이 있다면, 옵티마이저는 이를 반영하지 못하고 잘못된 실행 계획을 선택할 수도 있다.
또한, 히스토그램을 사용하여 컬럼 분포를 예측한다고 해도, 실제 실행되는 SQL 문이 조합된 컬럼을 기반으로 동작할 경우 옵티마이저는 예측 오류를 범할 가능성이 크다. 결합된 컬럼들의 분포가 단순한 곱셈으로 계산될 수 없기 때문이다. 이러한 문제를 해결하기 위해 일부 데이터베이스에서는 결합된 컬럼의 히스토그램을 따로 관리하기도 하지만, 이 역시 완벽한 해결책은 아니다.
따라서, 옵티마이저가 최적의 실행 계획을 수립하는 데에는 한계가 존재하며, 이를 보완하기 위해서는 단순히 옵티마이저의 판단에 의존하는 것이 아니라, 실행 계획을 직접 검토하고 조정할 필요가 있다. 특정 SQL 문에서 옵티마이저가 예측하지 못하는 패턴이 반복적으로 나타난다면, 실행 계획을 고정하거나 힌트를 사용하여 수동으로 조정하는 것이 필요하다.
이와 같은 한계를 극복하기 위해서는 통계 정보를 지속적으로 갱신하고, 실행 계획을 모니터링하여 문제 발생 시 즉각적인 대응이 가능하도록 해야 한다. 또한, 단순히 비용기준 옵티마이저의 판단에 의존하기보다는, 데이터베이스 구조와 인덱스 설계를 최적화하여 옵티마이저가 보다 정확한 판단을 내릴 수 있도록 유도하는 것이 중요하다.
3.1.3. 옵티마이저의 최적화 절차
옵티마이저는 사용자가 원하는 결과를 최적의 방법으로 도출하기 위해 다양한 실행 계획을 분석하고 선택하는 역할을 한다. 가장 효율적인 처리 방식을 찾는 과정은 단순하지 않으며, 많은 경우 여러 가지 실행 계획을 비교하며 최적의 경로를 예측해야 한다. 하지만 미래의 처리 비용을 정확하게 예측하는 것은 어렵기 때문에 옵티마이저는 수집된 통계 정보와 알고리즘을 활용하여 최적의 방법을 추론한다.
SQL 문이 실행되면 옵티마이저는 데이터 딕셔너리에 저장된 테이블과 인덱스 정보를 바탕으로 가능한 실행 계획을 도출한다. 이 과정에서 아우트라인과 힌트 등의 요소도 참고하여 최적의 처리 방식을 결정하게 된다. 초기 실행 단계에서 SQL은 데이터 딕셔너리를 참조하여 분석되며, 옵티마이저는 이 분석된 결과를 기반으로 논리적으로 적용 가능한 실행 계획을 도출한다. 이러한 과정은 일차적으로 가설적인 실행 계획을 수립하는 형태로 진행된다.
옵티마이저는 데이터의 저장 구조, 인덱스 활용도, 연산 비용 등을 종합적으로 고려하여 각 실행 계획의 비용을 계산한다. 이 과정에서 통계 정보를 활용하여 데이터 분포도 및 테이블의 저장 방식이 실행 계획에 미치는 영향을 분석하며, 실행 가능한 모든 계획 중 가장 낮은 비용을 가진 실행 계획을 선택한다. 그러나 이 선택 과정에서도 항상 최적의 결정이 이루어지는 것은 아니다. 마치 최적 입찰 방식이 반드시 최상의 결과를 보장하지 않는 것과 유사하게, 옵티마이저의 결정이 항상 최적의 결과를 도출한다고 단정할 수는 없다. 이 때문에 힌트 등을 활용하여 옵티마이저의 선택을 조정하는 방법도 사용된다.
옵티마이저의 최적화 절차를 더 구체적으로 살펴보면, 먼저 SQL 문이 입력되면 질의 변환기(Query Transformer)를 통해 분석이 진행된다. 이후 비용 산정기(Estimator)가 통계 정보를 바탕으로 각 실행 계획의 비용을 평가하고, 실행 계획 생성기(Plan Generator)가 이를 바탕으로 최적의 실행 계획을 선택하는 방식으로 동작한다. 사용자가 실행한 SQL 문이 단순한 텍스트 문자열이 아니라, 테이블과 컬럼 정보를 참조하여 최적의 경로를 결정해야 하기 때문에 옵티마이저는 이를 내부적으로 구조화하여 최적화 작업을 수행한다.
이 과정에서 옵티마이저는 쿼리 블록(Query Block) 형태로 실행 계획을 수립하며, 논리적 관계를 분석하여 실행 순서를 결정한다. 테이블 간 조인 방식, 인덱스 활용 여부, 연산 비용 등이 모두 고려되며, 최종적으로 실행 계획이 도출된다. 이러한 절차를 거쳐 옵티마이저는 가장 효율적인 실행 경로를 선택하지만, 사용자의 힌트 제공 여부, 통계 정보의 정확성, 데이터의 분포 등에 따라 최적화 결과는 달라질 수 있다. 따라서 옵티마이저의 최적화 절차를 이해하고 필요에 따라 적절한 튜닝을 수행하는 것이 중요하다.
가) 질의 변환기
옵티마이저의 질의 변환 기능은 보다 효율적인 실행 계획을 수립하기 위해 SQL 문을 변형하는 역할을 한다. 이를 위해 다양한 변환 기법이 적용되는데, 대표적인 방식으로는 뷰 병합, 조건절 진입, 서브쿼리 비표조화, 실체뷰 쿼리 재작성, OR 조건의 전개, 사용자 정의 바인드 변수 엿보기 등이 있다.
뷰 병합은 사용자가 정의한 뷰를 기반으로 실행되는 SQL 문을 실제 테이블을 액세스하는 쿼리로 변환하는 기법이다. 이를 통해 불필요한 뷰의 중첩을 제거하여 실행 성능을 높일 수 있다. 다만, 특정한 제약 조건이 있는 경우 병합이 불가능할 수도 있다.
조건절 진입은 쿼리 내부의 서브쿼리에 존재하는 조건을 메인 쿼리로 이동시켜 실행 계획을 최적화하는 기법이다. 이를 통해 불필요한 연산을 줄이고, 보다 효율적인 인덱스 활용이 가능하도록 유도할 수 있다.
서브쿼리 비표조화는 서브쿼리를 포함한 SQL 문에서 불필요한 중첩 관계를 해소하여 실행 성능을 향상시키는 방식이다. 서브쿼리가 비표조화될 경우, 옵티마이저는 쿼리를 보다 단순한 형태로 변환하여 실행할 수 있으며, 불필요한 연산을 줄일 수 있다.
실체뷰 쿼리 재작성은 실체뷰를 물리적 테이블처럼 처리하는 방식으로, 실체뷰가 저장한 데이터를 직접 조회함으로써 성능을 개선하는 기법이다. 원래 실체뷰는 논리적인 집합이지만, 옵티마이저는 이를 활용하여 보다 효율적인 물리적 데이터 접근을 가능하게 한다.
OR 조건의 전개는 OR 연산자를 사용한 조건을 개별적인 쿼리로 변환한 후, UNION ALL을 통해 결합하는 방식이다. 이를 통해 병렬 처리나 특정 인덱스를 활용하는 실행 계획을 선택할 가능성이 높아진다. 그러나 단순한 OR 조건을 무분별하게 전개하면 성능이 저하될 수도 있기 때문에, 옵티마이저는 비용을 기반으로 이를 결정한다.
사용자 정의 바인드 변수 엿보기(Peeking)는 쿼리에서 바인드 변수를 사용할 때, 초기 실행 시 변수 값을 참조하여 실행 계획을 수립하는 기법이다. 이를 통해 실행 계획이 보다 최적화될 수 있지만, 바인드 변수 값이 자주 변경되는 환경에서는 계획이 일정하게 유지되지 않을 수 있다. 따라서, 특정 환경에서는 바인드 변수 대신 리터럴 값을 활용하는 것이 보다 안정적인 성능을 보장할 수도 있다.
이러한 질의 변환 기법들은 SQL 문을 보다 최적화된 형태로 변형하여 실행 성능을 향상시키는 데 중요한 역할을 하며, 옵티마이저는 이러한 변환을 비용과 성능을 고려하여 자동으로 적용한다.
나) 비용 산정기
옵티마이저는 실행 계획을 최적화하기 위해 다양한 비용 산정 기법을 활용한다. 주요 평가 요소로는 선택도(Selectivity), 카디널리티(Cardinality), 비용(Cost) 등이 있으며, 이들은 서로 밀접하게 연관되어 있다. 선택도는 특정 조건을 만족하는 데이터의 비율을 의미하며, 실행 계획 수립에 중요한 영향을 미친다. 예를 들어, 특정 컬럼이 인덱스를 갖고 있고 조건을 만족하는 데이터 비율이 낮다면 선택도가 높다고 판단할 수 있다. 반대로, 조건을 만족하는 데이터가 많다면 선택도는 낮아지게 된다.
옵티마이저는 보다 정확한 선택도를 추정하기 위해 통계 정보를 활용하지만, 개별 컬럼에 대한 정보만을 기준으로 계산하기 때문에 복합적인 조건이 적용될 경우 정확한 예측이 어려울 수 있다. 이러한 문제를 보완하기 위해 히스토그램 정보를 사용하거나 동적 표본을 이용한 통계 정보 수집 방식이 활용되기도 한다. 하지만 모든 경우에 완벽한 예측이 가능한 것은 아니며, 특정 연산자나 조건 형태에 따라 오차가 발생할 수도 있다.
카디널리티는 특정 실행 계획 내에서 처리해야 할 데이터의 양을 의미하며, 앞서 계산된 선택도와 전체 로우 수를 기반으로 결정된다. 선택도와 마찬가지로 카디널리티 역시 상대적인 개념이며, 옵티마이저는 이를 통해 처리할 데이터의 규모를 예측하여 최적의 실행 계획을 도출한다. 하지만 선택도와 카디널리티를 단순히 비율로만 계산하는 것은 한계가 있으며, 조인 순서나 데이터의 구조를 고려해야 한다.
비용(Cost)은 실행 계획에서 각 연산이 차지하는 자원 소모량을 의미하며, CPU, 메모리, 디스크 I/O 등의 요소를 고려하여 산정된다. 일반적으로 비용이 낮은 실행 계획이 선택되지만, 정확한 예측이 어렵기 때문에 최적의 실행 계획이 항상 보장되는 것은 아니다. 또한, 같은 평가 기준을 적용하더라도 환경에 따라 결과가 달라질 수 있으며, 실행 계획이 변경될 가능성도 존재한다.
결과적으로 옵티마이저는 선택도, 카디널리티, 비용을 종합적으로 분석하여 최적의 실행 계획을 수립하려 하지만, 여전히 한계를 가지고 있다. 이를 보완하기 위해 힌트나 초기화 파라미터를 활용하여 실행 계획을 조정하는 방법이 추가적으로 사용되고 있다.
다) 실행계획 생성기
실행 계획 생성기의 주요 역할은 주어진 쿼리를 처리할 수 있는 실행 계획을 생성하고, 여러 계획을 비교하여 가장 최적의 비용을 가진 계획을 선택하는 것이다. 실행 계획은 다양한 액세스 경로의 조합으로 이루어지며, 동일한 조인을 다르게 구성할 수도 있다. 하지만 실행 계획에 따라 처리 성능이 크게 차이 날 수 있으므로, 최적의 실행 계획을 선택하는 과정이 중요하다.
하위 쿼리의 실행 계획은 우선적으로 생성된다. 서브쿼리나 병합이 불가능한 부등호 조인은 상위 쿼리의 실행 계획에 직접적인 영향을 미치므로, 먼저 최적화를 수행한 후 메인 쿼리에 반영된다. 이러한 처리 방식은 SQL 문을 내부적으로 여러 개의 처리 단위(Query Block)로 나누어 처리하는 방식과 관련이 있다.
실행 계획을 최적화하기 위해 옵티마이저는 여러 실행 계획을 평가하는데, 모든 가능한 실행 계획을 고려하기보다는 일정한 제한 시간 내에 최적의 결과를 도출해야 한다. 바둑에서 제한된 시간 내에 최적의 수를 두는 것처럼, 옵티마이저도 주어진 자원 내에서 가장 효율적인 실행 계획을 찾아야 한다. 이를 위해 적응적 탐색(Adaptive Search) 및 경험적 휴리스틱(Heuristic) 기법을 활용한다.
- 적응적 탐색(Adaptive Search) - 제한 시간이 있는 경우, 가능한 실행 계획을 평가하면서 점진적으로 더 나은 계획을 찾아간다.
- 경험적 휴리스틱(Heuristic) - 과거의 실행 패턴을 분석하여 초기 실행 계획을 선택하고, 이를 기반으로 최적화를 수행한다.
이러한 전략을 통해 옵티마이저는 최적의 실행 계획을 찾을 확률을 높인다. 하지만 주어진 시간 내에 반드시 최적의 실행 계획을 찾을 수 있는 것은 아니므로, 힌트를 활용하여 옵티마이저의 결정을 조정하는 것이 중요하다.
실행 계획 생성 과정은 바둑 경기와 유사하다. 초보자와 달리 숙련된 선수들은 제한된 시간 내에서도 가장 적절한 수를 찾을 수 있다. 옵티마이저도 마찬가지로 단순히 규칙만 따르는 것이 아니라, 다양한 힌트를 활용하여 최적의 실행 계획을 선택하는 능력을 갖추고 있다. 하지만 아무리 우수한 옵티마이저라도 완벽할 수는 없으며, 이를 보완하기 위해서는 튜닝 전문가의 개입이 필요하다.
3.1.4.질의의 변환 (Query Transforming)
옵티마이저는 가능한 모든 수식의 값을 미리 구한다. 예를 들어, 상수의 연산은 실행될 때마다 이루어지는 것이 아니라 질의 변환 과정에서 한 번만 수행된다. 다음과 같은 조건절이 있다고 가정해 보자.
- sales_qty > 1200/12
- sales_qty > 100
- sales_qty*12 > 1200
첫 번째 조건식은 사전에 최대한 연산을 수행하여 두 번째 조건식으로 변환된다. 따라서 ①과 ②는 동일하게 취급된다. 하지만 ③처럼 비교 연산자가 추가된 경우에는 좌항과 우항을 단순히 변환하지 않는다.
또한, LIKE 연산자가 포함된 경우에도 변환이 가능하다. 예를 들어
- job like 'SALESMAN'
- job = 'SALESMAN'
데이터 타입이 고정길이로 지정된 경우, 이러한 변환이 다르게 처리될 수도 있다. 특히 IN 연산자의 경우, 논리 연산자를 사용하여 변환될 수 있다.
- job IN ('CLERK', 'MANAGER')
- job = 'CLERK' OR job = 'MANAGER'
ANY 또는 SOME 연산자를 포함한 일반적인 조건식도 변환될 수 있다.
- sales_qty > ANY (:in_qty1, :in_qty2)
- sales_qty > :in_qty1 OR sales_qty > :in_qty2
만약 ANY 또는 SOME 뒤에 서브쿼리가 포함된 경우, EXISTS 연산자로 변환될 수 있다.
WHERE 100000 > ANY (SELECT sal FROM emp WHERE job = 'CLERK')WHERE EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 100000 > sal)ALL을 사용하는 경우에도 변환이 가능하며, NOT ANY는 NOT EXISTS로 변환될 수 있다.
WHERE 100000 > ALL (SELECT sal FROM emp WHERE job = 'CLERK')WHERE NOT (100000 > ANY (SELECT sal FROM emp WHERE job = 'CLERK'))WHERE NOT EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 100000 <= sal)
BETWEEN 연산자는 비교 연산자로 변환될 수 있다.
- sales_qty BETWEEN 100 AND 200
- sales_qty >= 100 AND sales_qty <= 200
NOT 연산자가 포함된 경우, 논리 연산자를 제거하고 비교 연산자를 찾아 대체한다.
- NOT (sal < 30000 OR comm IS NULL)
- NOT sal < 30000 AND comm IS NOT NULL
- sal >= 30000 AND comm IS NOT NULL
서브쿼리에 사용된 NOT도 변환될 수 있다.
NOT deptno = (SELECT deptno FROM emp WHERE empno = 7689)deptno <> (SELECT deptno FROM emp WHERE empno = 7689)3.1.4.1. 이행성 규칙
옵티마이저는 실행 계획을 최적화하기 위해 기존 조건식을 변형하여 새로운 조건식을 생성하는데, 이를 이행성 규칙이라 한다. 이 규칙을 적용하면 원래 조건식에서는 인덱스를 활용하지 못하던 경우에도 최적화된 실행 계획이 도출될 수 있다.
예를 들어 다음과 같은 조건식이 있다고 가정해보자.
WHERE column1 comparison_operators constant
AND column1 = column2;여기서 comparison_operators는 =, !=, <, >, <=, >= 등의 연산자를 의미하며, constant는 상수 값, SQL 변수, 문자열, 바인드 변수 등을 포함할 수 있다.
우리가 알고 있는 기본 논리 A = B이고, B = C라면 A = C라는 원리가 적용되듯 옵티마이저도 이를 기반으로 다음과 같은 새로운 조건을 유추할 수 있다.
column2 comparison_operators constant이행성 규칙의 적용 효과
이행성 규칙은 비용 기반 옵티마이저(CBO, Cost-Based Optimizer)에서만 적용되며, 이는 SQL 실행 시 불필요한 연산을 줄이고, 보다 효율적인 실행 계획을 선택할 수 있도록 돕는다.
예를 들어, Nested Loops 조인을 사용할 경우 선행 테이블의 조건을 최대한 활용하여 후행 테이블의 처리량을 최소화하는 것이 중요하다.
반면 Sort Merge 조인을 사용하는 경우 각 테이블의 집합 크기를 최소화하는 것이 더 효율적인 실행 계획을 만드는 데 도움이 된다.
예를 들어, 다음과 같은 SQL을 고려해보자.
SELECT *
FROM emp e, dept d
WHERE e.deptno = 20
AND e.deptno = d.deptno;이때, 옵티마이저는 이행성 규칙을 적용하여 d.deptno = 20을 자동으로 유추할 수 있다.
즉, deptno에 인덱스가 존재하지 않는 경우라도 실행 계획을 최적화하여 먼저 인덱스를 활용할 수 있도록 조정한다.
이행성 규칙이 적용되지 않는 경우
그러나 이행성 규칙이 항상 적용되는 것은 아니다. 예를 들어, 비교 대상이 상수 수식이 아니라 컬럼일 경우 이 규칙이 적용되지 않는다.
WHERE column1 comparison_operators column3
AND column1 = column2;이 경우, 옵티마이저는 column2 comparison_operators column3을 자동으로 유추하지 않는다. 이와 같은 문제를 해결하기 위해, 경우에 따라 UNION ALL을 활용하여 변환하는 방식이 적용될 수도 있다.
예를 들어, WHERE 절에 OR 연산자가 포함된 경우 옵티마이저는 이를 UNION ALL로 변환하여 보다 최적화된 실행 계획을 수립할 수 있다.
SELECT * FROM emp WHERE job = 'CLERK'
UNION ALL
SELECT * FROM emp WHERE deptno = 10 AND job <> 'CLERK';서브쿼리 변환 적용
옵티마이저는 경우에 따라 서브쿼리를 조인 문장으로 변환할 수도 있다. 예를 들어, 다음과 같은 SQL이 있을 때
SELECT *
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK');만약 DEPT 테이블의 DEPTNO가 기본키(Unique Index)라면 옵티마이저는 이를 다음과 같이 변환하여 실행할 수 있다.
SELECT emp.*
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dept.loc = 'NEW YORK';이러한 변환을 통해 서브쿼리를 조인 문장으로 변환하면 실행 계획을 보다 효율적으로 최적화할 수 있다.
실행 계획 선택
옵티마이저가 다음과 같은 조인 문으로 변환할 수 없다고 판단할 경우, 메인 쿼리와 서브쿼리에 대한 실행 계획을 선택하게 된다.
SELECT *
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 20);이 경우, 실행 계획은 다음과 같이 결정된다.
- 서브쿼리의 실행 결과를 먼저 생성하여 메인 쿼리에 적용할 수 있는 경우 → 먼저 수행
- 그렇지 않은 경우, 메인 쿼리를 실행하면서 서브쿼리를 확인하는 방식 → 필터링 처리 또는 해시 조인 방식으로 실행
3.1.4.2. 뷰 병합(View Merging)
뷰 병합(View Merging)은 옵티마이저가 뷰를 포함하는 SQL 문을 최적화하기 위해 사용하는 변환 기법이다.
뷰는 크게 뷰 쿼리(View Query)와 액세스 쿼리(Access Query)로 나뉜다.
- 뷰 쿼리(View Query) - 뷰를 생성할 때 사용한 SELECT 문, 즉 데이터 딕셔너리에 저장된 SQL 문.
- 액세스 쿼리(Access Query) - 뷰를 실행할 때 실제 수행되는 SQL 문.
뷰 병합이 발생하면, 액세스 쿼리에서 뷰를 원래의 테이블로 변환하여 직접 액세스하는 형태로 실행 계획이 변경된다. 이를 통해 불필요한 뷰 연산을 제거하고 성능을 최적화할 수 있다.
뷰 병합 방식
뷰 병합 방식에는 다음과 같은 두 가지 방법이 있다.
뷰 병합(View Merging)
뷰의 SELECT 문을 액세스 쿼리로 병합하는 방식으로 예를 들어, 다음과 같은 뷰를 정의했다고 가정해볼 때
CREATE VIEW emp_10 AS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp WHERE deptno = 10;이 뷰를 사용하는 SQL이 아래와 같다면
SELECT e_no, e_name, salary, hire_date
FROM emp_10
WHERE salary > 1000000;옵티마이저는 이를 아래와 같이 변환하여 실행한다.
SELECT empno, ename, sal, hiredate
FROM emp
WHERE deptno = 10
AND sal > 1000000;
즉, 뷰를 제거하고 원본 테이블(emp)을 직접 조회하는 방식으로 변환된다.
조건절 전입(Pushing Predicate)
뷰 병합이 불가능한 경우, 뷰에 포함된 조건을 액세스 쿼리에 전입(삽입)하여 최적화하는 방식으로 예를 들어, UNION, UNION ALL, INTERSECT, MINUS 같은 집합 연산이 포함된 뷰는 병합이 어렵지만, WHERE 조건을 개별 쿼리에 삽입하여 실행 계획을 최적화할 수 있다.
뷰 병합이 어려운 경우
- 뷰 병합이 항상 가능한 것은 아니다. 다음과 같은 경우에는 뷰 병합이 어렵거나 불가능할 수 있다.
- CONNECT BY를 사용한 경우
- ROWNUM을 포함한 경우
- GROUP BY나 DISTINCT가 포함된 경우 (단, 특정 조건에서는 병합 가능)
- 집합 연산 (UNION, UNION ALL, INTERSECT, MINUS)이 포함된 경우
뷰 병합이 불가능한 경우 옵티마이저는 조건절 전입(Pushing Predicate)을 수행하여 최적화할 수 있다.
GROUP BY와 병합
GROUP BY가 포함된 뷰는 병합이 어려울 수 있다. 하지만 옵티마이저가 Complex View Merging, Optimizer Secure View Merging 옵션이 활성화된 경우 GROUP BY와 DISTINCT가 포함된 뷰도 병합이 가능하다.
예를 들어, 아래와 같은 뷰가 있다고 할 때
CREATE VIEW emp_group_by_deptno AS
SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal
FROM emp GROUP BY deptno;이를 사용하는 SQL이 아래와 같을 때
SELECT *
FROM emp_group_by_deptno
WHERE deptno = 10;옵티마이저는 이를 아래처럼 변환할 수 있다.
SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal
FROM emp
WHERE deptno = 10
GROUP BY deptno;즉, GROUP BY를 유지하면서도 필요한 조건만 반영하여 최적화된 실행 계획을 생성한다.
옵티마이저 힌트와 병합 제어
뷰 병합이 적절하지 않을 경우, 다음과 같은 힌트를 사용하여 병합을 강제하거나 방지할 수 있다.
- MERGE 힌트 - 뷰 병합을 강제 수행.
- NO_MERGE 힌트 - 뷰 병합을 방지.
SELECT /*+ NO_MERGE(v) */ v.loc, v.avg_sal
FROM dept d, emp_group_by_deptno v
WHERE d.deptno = v.deptno
AND d.loc = 'London';위 SQL에서는 NO_MERGE 힌트를 사용하여 뷰 병합을 방지하고, 원래 뷰 형태로 실행하도록 강제하지만 반대로 MERGE 힌트를 사용하면 강제로 뷰 병합이 이루어진다.
뷰 병합의 활용
뷰 병합을 통해 불필요한 연산을 줄이고 최적화할 수 있지만 조건이 많거나 복잡한 경우 뷰 병합이 오히려 성능을 저하시킬 수 있다.
일반적으로 뷰 병합이 유리한 경우는 뷰가 단순한 SELECT문을 포함할 때, 뷰 내부의 조건이 엑세스 쿼리에 유용하게 활용 될 때이고,
반대로 뷰 병합이 유리하지 않는 경우는 GROUP BY, DISTINCT, UNION ALL 같은 연산이 많을 때나 뷰가 복잡하여 병합이 실행 계획을 오히려 비효율적으로 만들 때이다.
따라서 옵티마이저가 뷰 병합을 자동으로 수행하는지 여부를 확인하고, 필요 시 NO_MERGE 힌트를 활용하여 실행 계획을 조정하는 것이 중요하다.
3.1.4.3 사용자 정의 바인드 변수의 엿보기(Peeking)
바인드 변수를 사용한 쿼리의 최적화는 파싱 시점에서 이루어지며, 파싱 이후에 변수가 바인딩 된다. 즉, 최적화 시점에는 실제 변수값 없이 통계 정보만으로 최적화를 수행해야 한다.
데이터가 균일하지 않고 일부 값만 집중적으로 분포된 칼럼(Highly skewed column)의 경우, 균일 분포를 가정한 최적화 방식으로는 성능 문제가 발생할 가능성이 높다. 이런 경우 특정 바인드 변수를 사용하면 매우 비효율적인 실행 계획이 만들어질 수 있다.
이를 해결하기 위한 기능이 바인드 변수의 엿보기(PEEKING)이다. 이 기능은 쿼리가 최초로 실행될 때 사용자가 지정한 바인드 변수 값을 옵티마이저가 미리 확인하고, 이를 바탕으로 더 정확한 선택도(Selectivity)를 계산하여 최적화를 수행한다.
최초 실행 시 결정된 실행 계획은 이후 다른 값이 바인딩되더라도 그대로 유지된다. 이는 첫 실행의 바인딩 값이 실행 계획 결정에 매우 중요하다는 것을 의미한다.
PEEKING 기능은 칼럼의 실제 데이터 분포를 반영하여 평균적인 분포 가정을 극복하지만, 첫 번째 바인딩 값이 전체 데이터 분포를 대표하지 않을 경우 오히려 편향된 실행 계획이 발생할 가능성도 존재한다.
예를 들어, 성별 컬럼에서 'M'이 99%, 'F'가 1%라면 평균적으로는 인덱스를 사용하지 않는 것이 효율적이다. 하지만 PEEKING을 사용할 경우 첫 번째 값이 'F'일 때는 인덱스를 사용하고, 'M'일 때는 전체 테이블 스캔을 하는 방식으로 각기 다른 실행 계획을 선택할 수 있다.
이 방법은 효율성을 높이지만, 특정 값에 따라 극단적인 편향이 발생할 위험이 있고, 항상 최적의 실행 계획을 보장하지는 않는다.
PEEKING의 활성화 여부는 '_OPTIM_PEEK_USER_BINDS' 파라미터로 제어할 수 있으며, 기본값은 'TRUE'이다. 이 기능을 원치 않으면 'FALSE'로 설정할 수 있다.
최초 파싱 시점의 바인딩 값에 따라 실행 계획이 달라질 수 있으므로, 실제 쿼리의 실행 계획을 분석하고 문제점을 찾기 위해서는 SQL TRACE 등의 도구를 사용하는 것이 필요하다.
3.1.5 개발자의 역할
이미 작성된 SQL을 통해 실행계획을 개선하고 성능을 향상시키는 것은 중요하지만, 그보다 먼저 개발자들은 SQL을 잘 활용하는 방법을 익히는 것이 필요하다. SQL을 제대로 활용하면 기존의 처리 방식에 근본적인 변화를 가져오게 되며, 단순한 처리 과정이 아닌 사고의 혁신을 의미한다. 그러나 대부분의 개발자들은 아직도 절차적인 사고에 익숙해서 비절차적이고 집합적인 SQL 처리 방식에 대해 어려움을 겪고 있다.
SQL은 단순한 명령어의 나열이 아니라 그 자체로 하나의 애플리케이션이다. 따라서 SQL의 성능이 떨어진다고 하더라도 현실 업무의 복잡성을 모두 처리하지 못한다고 비판해서는 안 된다. SQL을 무작정 어렵고 복잡한 방식으로 사용하거나, 지나치게 단순한 처리로만 접근해서도 안 된다. 즉, 주어진 문제에 대해 최소한의 처리 비용과 자원을 사용하여 원하는 결과를 효율적으로 얻는 방식을 고민해야 한다.
대부분의 개발자들은 일상적으로 사용하는 절차적인 처리 방식에 따라 SQL을 사용하려는 경향이 있다. 그러나 관계형 데이터베이스를 효과적으로 이용하려면 절차적 접근 방식보다는 집합적이고 논리적인 접근 방식이 필요하다. 이 과정에서 개발자는 데이터베이스의 규정과 규칙을 준수하는 동시에, SQL의 비절차적 처리 방식을 능숙하게 다룰 수 있어야 한다.
SQL을 제대로 활용하면 마치 비행기를 타고 하늘에서 자유롭게 목적지를 향해 나아가는 것과 같지만, 절차적인 방식만을 고집한다면 마치 비행기를 육로에서 운전하는 것처럼 불필요한 낭비와 비효율을 초래할 수 있다. 결국 개발자는 데이터베이스와 SQL의 특성을 잘 이해하고 활용하는 법을 익혀, 최소한의 자원으로 최적의 성능을 얻을 수 있는 실행계획을 작성할 책임이 있다. 이를 위해서는 복잡한 문제도 간단한 SQL을 활용하여 효율적으로 처리할 수 있는 능력을 갖추는 것이 필수적이다.
하지만 단지 SQL만 잘 활용한다고 모든 문제가 해결되는 것은 아니다. 개발자는 자신이 작성한 SQL이 어느 정도의 성능을 가지는지 파악할 수 있어야 하며, 수행 과정에서 발생할 수 있는 성능 문제를 예측하고 미리 대비하는 능력을 갖추어야 한다. 또한 지나치게 복잡한 SQL을 사용하거나 옵티마이저에 무지한 상태에서 억지로 성능을 끌어내려는 태도는 바람직하지 않다. 따라서 조직 내에는 SQL과 옵티마이저를 잘 이해하고 적절히 활용할 수 있는 전문가가 반드시 필요하다.
Start : 25. 01. 22
End: 25.02.15
'프로그래밍(Web) > 공부일기' 카테고리의 다른 글
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 3장 SQL의 실행계획 - 2 (0) | 2025.04.04 |
|---|---|
| [바미] 옵티마이저(Optimizer)란 무엇인가? (0) | 2025.02.02 |
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 2장 인덱스 유형과 특징 (0) | 2024.12.30 |
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 1장 데이터 저장구조와 특징 (0) | 2024.12.17 |
| [바미] 해시 충돌 처리 방법 (2) | 2024.10.12 |
새롭게 안 사실들
옵티마이저
옵티마이저를 처음 이 책을 공부할 땐 머리속에 남지 않았는데 옵티마이져 관련 이슈를 경험하고 나서 눈에 제대로 들어오기 시작했다.
아우트라인
엑세스, 스캔에 대한 내용들
처음 들어 보는 조인
세미 조인(Semi Join), 카티젼 조인Cartesian Join), 내포 조인(Nested loop Join), 정렬 병합 조인(Sort Merge Join), 해쉬 조인(Hash Join), 등등
공부한 내용들
제 3장 SQL의 실행계획
SQL의 실행 계획은 데이터베이스 엑세스 효율에 가장 큰 영향을 미치는 요소로, SQL의 수행 방식을 미리 계획하여 성능 최적화를 도모하는 데 핵심적인 역할을 한다.
실행 계획은 사용 중인 SQL의 형태, 데이터 및 인덱스 구조, DBMS 버전, 통계 정보 등을 기반으로 생성되며, 이러한 요인들이 적절히 준비되어야 좋은 실행 계획이 도출된다.
실행계획의 중요성
실행계획은 SQL이 데이터베이스에서 어떤 경로로 데이터를 접근할지를 결정하며, 잘못된 실행계획은 성능 저하를 초래한다. 예를 들어, 올바르지 않은 방법으로 최적화를 시도하거나 SQL의 작성 방식에 문제가 있다면 많은 리소스를 낭비할 수 있다. 따라서 SQL을 작성할 때부터 실행계획을 염두에 두고 설계하는 것이 매우 중요하다.
실무에서의 비효율 사례
- 실제 환경에서는 옵티마이저가 비효율적인 실행계획을 생성하는 경우가 종종 발생한다.
- 이러한 문제는 주로 SQL 작성 방식이나 옵티마이저의 설정이 적절히 조정되지 않았을 때 나타난다.
- 사용자가 원하는 결과를 얻었다고 해서 반드시 효율적인 실행계획이 도출된 것은 아니다. 따라서불필요한 리소스를 소모하지 않도록 주의해야 한다.
실행계획의 최적화
- 실행계획 최적화를 위해 SQL 작성 시 올바른 문법과 최적화 기법을 활용해야 한다.
- 실행계획의 구조를 이해하는 것은 효율적인 SQL 작성의 필수적인 부분이다.
- 통계 정보는 SQL의 성능을 좌우하는 중요한 요소로, 이를 정기적으로 갱신 및 관리해야 한다.
실행계획 분석 방법
- 실행계획은 주로 EXPLAIN PLAN 명령어를 통해 확인할 수 있다.
- 실행계획의 각 단계는 SQL이 데이터베이스와 상호작용하는 방식을 명확히 보여주며, 이를 기반으로 개선 사항을 도출할 수 있다.
- 최적화된 실행계획은 단순히 결과를 빠르게 가져오는 것뿐 아니라, 리소스 사용량을 최소화하고 시스템의 안정성을 높인다.
3.1. SQL과 옵티마이져
관계형 데이터베이스는 SQL을 통해 데이터를 처리하며, 사용자가 요구하는 데이터를 정확히 처리하기 위해 옵티마이저(Optimizer)가 필요하다.
옵티마이저는 SQL 실행 시 최적의 처리 경로를 생성하여 실행 계획을 수립하여 데이터 검색과 처리가 효율적으로 이루어질 수 있다.
옵티마이저의 역할과 중요성
- SQL 실행 과정
- SQL은 사용자의 요구를 표현한 일종의 문장일 뿐으로, 실제 데이터를 처리하기 위해서는 구체적인 실행 계획이 필요한데 이 실행 계획을 수립하는 과정에서 옵티마이저가 중요한 역할을 한다.
- 옵티마이저는 SQL 문장에서 사용된 조건과 통계 정보를 바탕으로 실행에 적합한 경로를 선택하게 되며 이를 통해 최적화된 실행 계획을 제공한다.
- 실행 계획의 영향
- 옵티마이저는 인덱스, 통계 정보, 테이블 구조, 네트워크 상태 등 다양한 요소를 종합적으로 고려하여 SQL의 실행 경로를 결정한다.
- 동일한 SQL이라도 통계 정보, 데이터의 분포, 인덱스의 사용 여부 등에 따라 실행 계획이 달라질 수 있다.
- 선택성과 효율성
- 선택성(Selectivity)은 SQL의 실행 계획에 가장 중요한 영향을 미치는 요소다. 선택성이 낮은 조건은 많은 데이터를 검색해야 하므로 효율성이 떨어지고, 선택성이 높은 조건은 검색 범위를 줄여 효율성을 높일 수 있다.
옵티마이저의 동작 원리
- SQL의 실행 계획 수립 과정
- 옵티마이저는 다양한 실행 경로를 시뮬레이션(Simulation)하여 최적의 실행 계획을 결정한다.
- 이를 통해 처리 범위를 최소화하고 데이터 접근 경로를 최적화하는 방법을 모색하며, SQL의 성능을 향상시킨다.
- 실행 계획 결정에 영향을 미치는 요소
- 통계 정보 - 테이블의 데이터 분포, 클러스터링 정도, 인덱스의 존재 여부 등에 따라 실행 계획이 달라질 수 있습니다.
- DBMS 특성 - 옵티마이저는 DBMS 제품별로 동작 방식이 다르며, 이는 동일한 SQL이라도 다른 실행 계획을 수립하게 만듭니다.
- 제약사항과 한계
- 옵티마이저는 완벽하지 않고, 경우에 따라 잘못된 실행 계획을 수립할 수가 있는데 이 때 통계 정보의 부정확성, 데이터 분포의 변화, 또는 DBMS의 버그로 인해 발생할 수 있다.
옵티마이저는 SQL 성능을 결정짓는 핵심 요소로, 이를 이해하고 활용하는 능력이 중요하다.
3.1.1. 옵티마이저와 우리의 역할
옵티마이저의 존재 이유와 역할
- 관계형 데이터베이스의 출현 배경은 데이터 간 물리적 연결고리를 제거하여 논리적 연결만으로 데이터를 엑세스 할 수 있게 하는데 있다.
- 옵티마이저는 사용자가 작성한 SQL을 기준으로 최적의 처리 경로(Execution Path)을 찾아주는 역할을 수행한다.
- 과거에는 데이터베이스 설계나 쿼리 작성에서 데이터의 물리적 연결을 고려해야 했지만, 현재는 이러한 작업을 옵티마이저가 대체하고 있다.
옵티마이저의 발전과 역할의 중요성
- 초기 관계형 데이터베이스는 물리적 연결 없이도 데이터를 자유롭게 엑세스 할 수 있도록 설계 되었으나, 성능 문제가 발생할 가능성이 컸다.
- 옵티마이저의 발전은 데이터베이스 성능 향상에 큰 기여를 했으며, 현대의 관계형 데이터베이스는 옵티마이저의 도움으로 효율적인 데이터를 처리할 수 있다.
- 사용자 입장에서 옵티마이저는 다양한 옵션을 제공하지만, 결국 선택된 처리 경로는 사용자가 사전에 제공한 데이터와 관련된 정보(통계 정보 등)에 따라 결정된다.
사용자와 옵티마이저의 관계
- 옵티마이저는 이미 존재하는 여러 경로 중 하나를 선택하는 역할을 한다.
- 사용자는 데이터의 구조와 통계 정보를 사전에 적절히 설정하여 옵티마이저가 최적의 경로를 선택할 수 있도록 돕는 역할을 해야 한다.
- 효과적인 데이터 처리 전략을 위해 사용자는 옵티마이저에 필요한 정보를 미리 준비하고 제공해야 하며, 이는 최적의 SQL 작성으로 연결된다.
옵티마이저의 한계와 우리의 역할
- 옵티마이저는 모든 문제를 완벽히 해결하지 못할 수도 있다. 사용자는 옵티마이저가 선택한 경로를 이해하고 필요할 경우 수정할 준비가 되어 있어야 한다.
- 옵티마이저의 효율성은 사용자가 사전에 제공한 데이터 모델링과 통계 정보의 정확성에 크게 의존한다.
- 최적의 결과를 얻기 위해 사용자는 올바른 데이터 설계와 적합한 SQL 작성을 통해 옵티마이저의 역할을 보완해야 한다.
최적의 결과를 위한 방향
- 최소한의 시스템 자원으로 최대한의 효과를 내는 것이 최종 목표이다.
- 시용자는 단순히 SQL을 작성하는 것을 넘어 데이터베이스의 동작 원리를 이해하고, 옵티마이저가 효율적으로 작동할 수 있도록 준비해야 한다.
- 관계형 데이터베이스의 성능 최적화는 사용자와 옵티마이저 간의 협력을 통해 이루어진다.
3.1.2 옵티마이저의 형태
옵티마이저는 규칙기준 옵티마이저(RBO)와 비용기준 옵티마이저(CBO)로 나뉜다. 이 둘은 최적의 실행 계획을 선택하는 기준에서 차이가 있다.
규칙기준 옵티마이저 (RBO)
- 사전에 정의된 규칙과 순위를 기준으로 실행 경로를 결정.
- 인덱스 상태, 연산자 형태, 데이터 타입 등에 기반해 고정된 순위를 매김.
- 테이블 크기나 데이터 분포를 반영하지 못한다는 점이 단점임.
비용기준 옵티마이저 (CBO)
- 통계정보를 바탕으로 각 처리 경로의 비용을 계산해 최적의 경로를 선택.
- 통계정보는 데이터 분포, 테이블 크기, 인덱스 상태 등을 포함.
- 더 많은 상황을 반영해 유연하고 효율적인 실행 경로 제공하는 특징을 가지고 있음.
- 통계정보가 부정확하거나 최신이 아닐 경우 잘못된 결과를 낼 수 있다는 점이 단점임.
RBO와 CBO의 비교
- RBO는 간단하지만, 현대 환경에서는 적합하지 않은 경우가 많음.
- CBO는 더 복잡하지만 유연하고 현실적인 최적화를 제공.
- 현대 데이터베이스에서는 일반적으로 CBO를 사용함.
3.1.2.1. 규칙기준 옵티마이저
규칙기준 옵티마이저(Rule-Based Optimizer, RBO)는 초기 관계형 데이터베이스에서 이상형으로 추구하던 단순하고 명료한 형태의 옵티마이저이다. 데이터베이스의 기술 수준이 발전하기 전임시로 사용되던 방식이지만, 현재에도 일부 데이터베이스에서 사용되고 있다.
규칙기준 옵티마이저는 통계 정보 없이 인덱스 구조와 비교 연산자에 따라 순위를 부여하고, 이 순위를 기반으로 최적의 경로를 결정하는 특징을 가지고 있다.
이 때 최적의 경로를 결정하는 순위는 다음과 같다.
- ROWID 로 직접 액세스
- 클러스터 조인에 의한 1 로우 액세스
- Unique HASH Cluster Key
- Unique INDEX에 의한 1 로우 액세스
- CLUSTER 조인
- Non-Unique HASH Cluster Key
- Non-Unique Cluster Key
- Non-Unique 결합 인덱스
- Non-Unique 한 컬럼 인덱스
- 인덱스에 의한 범위 처리
- 인덱스에 의한 전체 범위 처리
- Sort Merge 조인
- 인덱스 컬럼의 MIN, MAX 처리
- 인덱스 컬럼의 ORDER BY
- 전체 테이블 스캔
규칙기준 옵티마이저의 한계와 발전방향
- 규칙기준 옵티마이저는 테이블의 크기, 인덱스 내 칼럼 값의 분포 등의 통계 정보를 고려하지 않기 때문에 효율성이 떨어질 수 있는 한계가 있다.
- 하지만 단순 명료함 덕분에 이해하기 쉬운 원리를 가지고 있어 약간의 숙련만으로도 일정 수준의 제어가 가능한 장점이 있다.
- 비용기준 옵티마이저의 등장과 함께 자동화를 통해 더 발전된 방식으로 대체되고 있다. 특히 RBO는 수동 카메라에 비유할 수 있는데 사용자가 직접 설정한 규칙과 순위에 따라 경로를 결정한다.
RBO는 수동 카메라처럼 사용자가 직접 초점을 맞추고 설정해야 하며, 자동화된 기능이 부족하다.
CBO는 자동 카메라처럼 데이터에 기반한 자동 설정과 최적화를 수행한다.
규칙기준 옵티마이저는 간단한 원칙에 다라 작동하지만, 현대 데이터베이스 환경에서는 비용기준 옵티마이저로 대체되는 추세다.
규칙기준 옵티마이저의 단점
- 통계정보의 부재로 인한 판단 오류
규칙기준 옵티마이저는 통계 정보를 사용하지 않기 때문에 현실적으로 발생하는 데이터의 분포나 로우 수에 따라 최적 경로를 판단하지 못하는 경우가 있다.
- 예를 들어 TAB1 테이블에 1,00,000개의 로우가 있고 TAB2 테이블에 100개의 로우가 있다고 가정할 때 전체 테이블을 스캔하는 것이 유리한지 판단하기 어려워 함.
- 분포의 편향성 무시
데이터 값이 고르게 분포되지 않았을 경우, 규칙기준 옵티마이저는 효율적이지 못한 경로를 선택할 수 있다.- 예를 들어 특정 조건에서 평균 분포도가 0.01%인 데이터를 대상으로 'LIKE' 조건을 사용할 경우 비효율적인 전체 스캔이 발생할 가능성이 높다.
- 실제 테스트의 부재
규칙기준 옵티마이저는 현실 데이터에 대한 테스트 없이 미리 정의된 규칙만으로 판단하므로 실제 처리 성능과의 괴리가 발생할 수 있어 실제 데이터 환경과 맞지 않는 실행계획을 수립하는 경우가 발생한다. - 수동 튜닝의 필요성
규칙기준 옵티마이저는 사용자가 수동으로 튜닝하거나 실행 계획을 변경하지 않으면 최적의 경로를 찾기 어렵기 때문에 시스템에 대한 경험과 이해가 부족한 사람에게는 큰 단점으로 작용된다. - 악성 실행 계획의 가능성
규칙기준 옵티마이저는 상황에 따라 악성 실행계획을 수립할 가능성이 있다.- 예를 들어, 잘못된 실행계획이 선택되면 처리 성능이 급격히 저하될 수 있다.
규칙기준 옵티마이저의 장점
- 명확성과 예측 가능성
규칙기준 옵티마이저는 규칙에 따라 실행계획을 수립하기 때문에 사용자가 결과를 아주 명확히 예측 할 수있어 예측 가능한 방식으로 실행계획을 수립해 문제를 미리 예측하고 대응할 수 있는 유연성을 제공하게 된다.
그리고 수동 카메라처럼 사용자 의도에 맞춘 결과를 미리 조율 할수 있도록 설계되어 있기 때문에 초기 설정만으로도 높은 성능과 안정적인 결과를 제공한다. - 사용자 통제 강화
사용자가 SQL 실행계획에 영향을 미칠 수 있도록 충분히 제어할 수 있는 구조를 지원하여 사용자 의도에 맞게 세부적인 실행계획을 조정할 수 있어 자유도가 높다. - 단순하고 효율적인 구조
비교적 단순한 구조를 바탕으로 작동하므로 처리 속도가 빠르고 간결하다. 이 덕에 초기 데이터를 분석하고 설계하는 데 유용하며, 정확성과 단순성을 기반으로 효과적인 해결책을 제공한다. - 전략적 인덱스 구성 가능성
전략적으로 인덱스를 설계하고 SQL을 작성하면 높은 확률로 최적화된 실행 계획을 얻을 수 있다. 따라서 사용자가 SQL 작성 단계에서 규칙에 대한 이해를 바탕으로 사전에 준비할 경우에 최적의 성능을 달성할 가능성이 높아진다. - 실무 중심의 접근성
실행 계획의 복잡성을 줄이고, 단순한 규칙을 통해 실무에서 쉽게 적용 할 수 있기 때문에 데이터베이스 구조와 SQL 작성 시 규칙을 활용하면 성능 최적화를 손쉽게 달성 할 수 있게 된다. - 테스트와 조정의 용이성
규칙 기반 구조는 실시간으로 SQL 실행 결과를 추적하고 조정하기 쉬워 실무에서 신속하게 문제를 해결 할 수 있다. 또한 TRACE 도구 등을 사용해 예상치 못한 결과를 확인하고 수정할 수 있다.
규칙기반 옵티마이저는 비록 통계 정보를 활용하는 비용기준 옵티마이저에 비해 한계가 있을 수 있지만, 규칙기준 옵티마이저는 단순성과 통제력을 기반으로 실무에서 여전히 강력한 도구로 활용될 수 있다. 특히 사전 설계와 예측 가능한 성능 최적화가 필요한 상황에서는 규칙기준 옵티마이저의 장점이 더욱 부각된다.
3.1.2.2. 비용기준 옵티마이저
비용기준 옵티마이저(CBO, Cost-Based Optimizer)는 관계형 데이터베이스에서 최적의 실행계획을 도출하기 위해 다양한 통계 정보를 기반으로 각 처리 방법에 대한 비용을 계산하고, 가장 비용이 적게 드는 경로를 선택하는 방식으로 작동한다. 이는 관계형 데이터베이스의 이상형에 부합하며, 대부분의 최신 DBMS가 채택하고 있는 형태다.
이를 위해서 미리 작성된 다양한 통계 정보를 참조하며, 이 통계 정보는 DBMS 제품과 버전에 따라 다소 차이가 있는데 주요 통계 정보는 아래와 같다.
- 테이블의 로우 수, 블록 수
- 로우당 평균 크기, 컬럼별 상수값의 종류 및 분포
- 컬럼의 NULL 값 비율
- 인덱스의 깊이(Depth), 레벨(Level), 최대/최소값, 리프(Leaf) 블록 수
- 클러스터링 패턴, 자동 시스템 I/O 및 CPU 사용량 등
비용기준 옵티마이저는 다양한 요소를 활용해 최적화를 시도하나, 각 요소의 상호작용이 복잡하며, 결과값은 데이터 값에 따라 크게 달라질 수 있다. 따라서 계산된 통계 정보가 실제 데이터와 일치하지 않으면 예측한 결과와 다를 수 있다.
통계 정보를 기반으로 판단하기 때문에 현실적인 요소를 충분히 반영하지 못할 가능성도 있어 통계 정보의 신뢰도가 낮은 경우 최적화된 실행계획 도출에 오류가 발생할 수 있다.
비용기준 옵티마이저의 장점
최대의 장점으로 현실을 감안한 판단을 할 수 있다.
비용기준 옵티마이저(CBO)는 조건의 분포도를 기반으로 최적화된 실행 계획을 수립한다.
예를 들어, 컬럼 A는 평균 분포도가 50%인 값들로 구성된 인덱스를, 컬럼 B는 10,000개의 값으로 구성되며 평균 분포도가 0.01%인 인덱스를 가지고 있다고 가정하자. WHERE 절 조건으로 A = '10' AND B LIKE '123%'를 부여했을 때, 규칙기준 옵티마이저(RBO)는 모든 조건을 단순하게 처리하려 하지만, 비용기준 옵티마이저(CBO)는 분포도가 좁은 컬럼 B를 우선적으로 처리하는 실행 계획을 선택해 보다 효율적인 결과를 도출한다.
분포도는 처리 범위를 최소화하고 실행 효율을 높이는 핵심 요소다. 분포도가 낮은 컬럼 B는 처리 대상이 적기 때문에 좁은 범위를 처리하여 최적화 성능을 향상시킨다. 그러나 단일 컬럼 인덱스의 분포도 계산은 상대적으로 간단한 반면, 여러 컬럼이 결합된 인덱스는 상호작용까지 고려해야 하므로 계산이 복잡해질 수 있다.
옵티마이저는 이러한 문제를 해결하기 위해 컬럼 값의 분포를 분석하는 히스토그램을 활용한다. 대표적인 방법으로는 넓이 균등 히스토그램과 높이 균등 히스토그램이 있다. 넓이 균등 히스토그램은 값을 일정한 범위로 나누어 저장하고, 높이 균등 히스토그램은 각 범위가 동일한 데이터 수를 가지도록 나눈다. 특히 높이 균등 히스토그램이 더 효율적인 경우가 많다.
DBMS는 dbms_stats와 같은 도구를 통해 이러한 통계 정보를 자동으로 수집하고 관리하며, 사용자가 직접 설정하지 않아도 기본적으로 최적화된 값을 제공한다. 이로 인해 사용자는 통계 분석의 부담을 덜고, 실행 계획의 최적화를 효과적으로 지원받을 수 있다.
비용기준 옵티마이저는 조건의 분포도를 분석하여 처리 범위를 최소화하고 최적화된 실행 경로를 선택하는 데 중요한 역할을 하지만, 통계 정보의 품질과 정확성이 최적화 결과에 직접적인 영향을 미친다.
통계정보의 관리를 통해 최적화를 제어 할 수 있다.
비용기준 옵티마이저를 효율적으로 활용하려면 통계정보를 체계적으로 관리하고 제어해야 한다.
통계정보는 데이터베이스의 최적화된 실행계획을 수립하는 데 필수적인 요소로, 데이터 변화에 따라 주기적으로 갱신해야 한다. 테이블의 데이터는 삽입(Insert), 갱신(Update), 삭제(Delete) 작업에 의해 변동되므로, 최신 상태를 반영하지 않은 통계정보는 실행계획의 신뢰성을 저하시킬 수 있다.
이를 해결하기 위해 DBMS에서 제공하는 GATHER_DATABASE_STAT와 같은 통계정보 수집 프로시저를 활용하거나, 작업 스케줄러를 통해 자동화된 주기적 갱신을 수행하는 것이 중요하다.
통계정보를 갱신하는 주기는 데이터 변화의 빈도에 따라 조정할 필요가 있다. 변동이 잦은 테이블의 경우 짧은 주기로 통계정보를 갱신하고, 변동이 적은 테이블은 갱신 주기를 길게 설정하는 것이 적절하다.
그러나 통계정보를 너무 자주 갱신하면 시스템 부하가 증가할 수 있으므로, 상황에 맞는 균형 잡힌 주기 설정이 필요하다.
특수한 경우에는 통계정보를 수집하지 않고 기존 실행계획을 유지하는 전략이 유용하다. 예를 들어, 이미 튜닝이 완료된 실행계획의 변경을 피하고자 할 때는 통계정보 갱신을 생략함으로써 실행계획의 안정성을 유지할 수 있다. 반면, 동적 SQL의 경우 변수 값에 따라 실행계획이 매번 달라질 수 있으므로 통계정보만으로 최적화가 어렵다. 이 경우 변수 값을 기반으로 유연한 실행계획을 수립하는 방식을 활용해야 한다.
또한, 통계정보가 부족하거나 부정확한 경우, 규칙기준 옵티마이저(RBO)를 활용하거나 SQL 힌트를 사용해 실행계획을 고정하는 방법도 실질적인 대안이 될 수 있다. 특히 대용량 데이터를 처리할 때는 실행계획 변경으로 인한 성능 저하를 방지하기 위해 논리적으로 최적화된 실행계획을 고정하는 것이 현실적이고 효과적인 방법이다.
비용기준 옵티마이저의 성능을 극대화하려면 통계정보를 적절히 관리하고 갱신하며, 필요에 따라 실행계획을 고정하거나 유연하게 조정해야 한다. 이를 통해 데이터베이스 성능을 안정적으로 유지하고 최적화된 실행계획을 지속적으로 적용할 수 있다.
옵티마이저를 깊이 이해하고 있지 않더라도 최소한의 성능이 보장된다.
비용기준 옵티마이저의 주요 장점 중 하나는 실행계획에서 최악의 상황이 발생할 확률을 감소시킨다는 점이다.
사용자가 작성한 SQL의 품질이 낮더라도, 옵티마이저는 다양한 통계 정보를 활용해 실행 경로를 최적화함으로써 안정적인 수행 성능을 보장할 가능성을 높인다. 마치 자동 카메라가 전문적이지 않은 사용자가도 높은 품질의 사진을 얻을 수 있도록 돕는 것처럼, 비용기준 옵티마이저는 사용자의 실수로 인한 실행 계획의 오류를 최소화하는 역할을 한다.
적절히 설계되지 않은 인덱스 구조나 품질이 낮은 SQL이라도, 비용기준 옵티마이저는 모든 가능성을 분석해 최소 비용의 경로를 선택하려 노력한다. 이를 통해 최적화된 실행 계획이 자동으로 생성되며, 결과적으로 최악의 상황이 발생할 확률이 크게 줄어든다. 또한, 사용자가 쿼리를 재작성하거나 변경하는 과정에서 옵티마이저는 새로운 계획을 재수립하여 실행 성능을 유지하거나 향상시킨다.
그러나 비용기준 옵티마이저가 통계 정보를 활용하여 최적화를 수행한다고 하더라도, 최적의 실행 경로를 보장하기 위해서는 사용자의 전략적 접근도 필요하다. 옵티마이저는 이미 존재하는 경로 중 가장 효율적인 것을 선택할 수는 있지만, 논리적으로 최적의 경로를 새롭게 설계해 주는 것은 아니다. 따라서 사용자는 전략적인 인덱스 구성과 효율적인 SQL 작성을 통해 옵티마이저의 기능을 최대한 활용해야 한다.
비용기준 옵티마이저의 단점
실행예측이 어려움
비용기준 옵티마이저는 실행 계획을 최적화하기 위한 강력한 도구이지만, 실행 계획을 미리 예측하기 어려운 한계를 가진다. SQL에서 사용하는 테이블과 컬럼의 통계 정보를 미리 알고 있다고 하더라도 변수값이나 조건에 따라 실행 계획이 달라질 수 있다. 예를 들어, 특정 인덱스가 추가되거나 통계 정보가 갱신될 경우, 과거와는 다른 실행 계획이 생성되어 예상하지 못한 성능 문제가 발생할 수 있다.
또한, WHERE 절에서 지정된 변수값에 따라 실행 계획이 크게 변할 수 있으며, 시스템 상태(메모리, CPU 등)와의 조합으로 더욱 복잡한 결과를 초래할 수 있다. 통계 정보가 변경되거나 재생성된 경우에도 기존의 실행 계획과 다른 결과가 나타날 가능성이 크기 때문에, 사용자가 실행 계획을 사전에 예측하고 준비하기 어렵다.
이러한 불확실성은 실행 계획의 적절성을 판단할 능력이 부족한 사용자에게 더 큰 문제로 작용하며, 최적화를 온전히 옵티마이저에 의존해야 하는 상황을 만든다. 더불어, 실행 계획이 상황에 따라 수시로 변하기 때문에 실시간으로 이를 예측하고 대응하는 데에도 한계가 있다. 결과적으로, 사용자는 실행 계획의 변경으로 인한 성능 변화를 대비하기 위해 통계 정보를 주기적으로 관리하고, 실행 계획을 면밀히 검토하는 노력이 필요하다.
버전에 따른 변화
비용기준 옵티마이저의 최적화 결과는 사용 중인 데이터베이스 제품이나 버전에 따라 달라질 수 있다. 이러한 변화는 버전 업그레이드 이후 기존 실행계획이 변경되거나 예기치 못한 시스템 성능 저하로 이어질 가능성을 포함한다. 과거에 실행되던 실행계획과 동일한 결과를 보장하지 못하는 경우도 있다.
DBMS 업체들은 사용자들에게 옵티마이저의 최적화 절차와 변경 사항에 대한 구체적인 정보를 제공하지 않는 경우가 많아 사용자들이 변경된 실행계획의 원인을 직접 파악하기 어려울 수 있다. 이러한 변화를 통해 기존의 실행계획을 고정시키거나 새로운 최적화 경로를 수립하는 방식으로 대응해야 한다.
결론적으로, 비용기준 옵티마이저는 계속 발전 중이며, 이러한 변화는 시스템 최적화 과정에서 일정한 영향을 미칠 수 있다. 따라서 버전에 따른 최적화 변화에 대한 이해와 준비가 필요하다.
실행계획 제어가 어려움
규칙기준 옵티마이저는 사용자들이 명시적으로 규칙을 설정하여 실행계획을 예상하고 제어할 수 있는 반면, 비용기준 옵티마이저는 다양한 통계 정보를 활용하여 최적화가 이루어지기 때문에 사용자가 제어하기 어렵다.
특히, 사용자 쿼리가 필요에 따라 다시 변형되거나 최적화 과정에서 변형되는 경우, 실행 환경이 달라지면서 기존 예측과 다른 실행계획이 도출될 가능성이 있다. 이는 실행계획 제어를 더욱 복잡하게 만드는 요인이다.
결론적으로, 실행계획의 제어는 비용기준 옵티마이저의 특성상 사용자가 모든 것을 명확히 예상하기 어렵다는 점에서 제약이 따른다. 이를 보완하기 위해 사용자들은 SQL 작성 시 옵티마이저의 작동 원리를 최대한 이해하고, 실행계획을 명확히 하기 위한 힌트를 적절히 활용해야 한다.
옵티마이저의 발전 방향
옵티마이저는 비용기준(CBO)을 중심으로 발전하고 있으며, 완벽한 통계 정보를 기반으로 높은 확률의 최적화를 가능하게 하는 것이 목표이다. 과거 규칙기준과 비용기준이 공존하던 시절에는 통계 정보의 활용이 제한적이었으나, 현재는 테이블과 데이터의 특성을 세밀하게 반영하는 수준으로 진화하고 있다.
통계정보의 중요성
테이블의 특성과 데이터의 분포는 통계 정보의 수집과 관리를 통해 예측 가능한 방식으로 변화를 반영할 수 있다. 특히, 대용량 데이터나 변화가 잦은 데이터를 효과적으로 관리하는 것은 필수적이며, 통계 정보를 테이블이나 인덱스 단위로 관리하는 것이 더 효율적이다. 이러한 방식은 실질적으로 관리 단위를 최적화하여 높은 최적화 확률을 유도하는 방향으로 나아가고 있다.
향후 과제
향후 옵티마이저는 더 세분화된 통계 정보와 사용자의 의도를 반영하는 세밀한 기능을 필요로 한다. 특히 전략적 인덱스 구성과 효율적인 SQL 작성은 사용자의 몫으로 남아 있다. 마치 전쟁에서 전략적 배치로 승리를 쟁취하는 것처럼, 단위 SQL 수준에서 전략적 접근이 요구된다.
통계정보 관리를 위한 제언
관리자의 역할과 접근 방식
통계 정보 관리는 단순히 사용자의 부재를 통해 자동화된 방식으로 처리되는 것뿐만 아니라, 설계자와 데이터 아키텍트가 능동적으로 시스템 통계를 관리할 필요가 있다. 이러한 관리가 시스템 성능 최적화의 중요한 요인으로 작용한다.
통계정보 관리의 구체적 방법
DBMS_STATS 활용
DBMS_STATS 패키지는 테이블과 인덱스 통계 정보를 효율적으로 관리할 수 있도록 돕는다. 일반적으로 전체 데이터의 5% 이하를 샘플로 설정해 통계 정보를 수집하며, ESTIMATE_PERCENT 파라미터를 활용하면 자동으로 최적의 샘플링 비율을 설정할 수 있다.
시스템 통계의 도입
초기 비용기준 옵티마이저는 하드웨어 성능을 충분히 반영하지 못했으나, 이제는 CPU와 I/O 성능과 같은 시스템 성능 요소를 통계로 반영할 수 있다. DBMS_STATS.GATHER_SYSTEM_STATS 프로시저를 사용하면 정기적으로 시스템 통계를 수집하고 이를 실행 계획에 반영할 수 있다.
자동화된 프로세스 활용
IMPORT_SYSTEM_STATS와 같은 자동화된 프로시저를 통해 통계 정보를 효과적으로 관리하고 교체할 수 있다. 이 기능은 사용자의 개입 없이도 시스템이 최적화된 상태를 유지하도록 지원한다.
통계정보 관리의 최적화 사례
공유 풀(Shared Pool) 자원 사용 최소화
기존 통계 정보와 새로운 통계 정보 간의 균형을 유지해 자원의 과도한 사용을 방지해야 한다. 이를 통해 시스템 리소스의 낭비를 줄일 수 있다.
통계 정보의 자동 저장 및 복원
새로운 통계 정보를 생성할 경우, 이전 버전을 자동으로 저장하여 필요 시 복원할 수 있다. RESTORE 프로시저를 활용하면 이전 통계 정보를 복원해 안정적으로 시스템을 운영할 수 있다.
3.1.2.3. 옵티마이저 모표(Goal) 선택
옵티마이저 목표의 개념
옵티마이저는 주어진 조건에서 최적화 작업을 수행하며, 다양한 조건에서 최적의 실행 계획을 도출하는 것을 목표로 한다. 이를 위해 시뮬레이션을 통해 현실 상황을 모델링하고, 가상의 실행 시뮬레이션을 통해 결과를 예측한다. 입력 데이터와 통계 정보의 정확도에 따라 실행 계획이 크게 달라질 수 있다.
목표 설정의 중요성
최적화 목표는 환경에 따라 다르게 설정된다. 단기적 최적화를 원할 경우 제한된 범위 내에서 가장 빠른 실행 계획을 선택하며, 장기적으로는 모든 데이터를 처리해야 하는 상황에서 전체 범위의 효율성을 고려해야 한다.
목표 설정의 기준
목표 설정은 우선순위와 전체 범위라는 기준에 따라 다르게 적용된다. 우선순위 기반 최적화는 특정 범위나 데이터를 먼저 처리하는 것을 목표로 하며, 전체 범위 기반 최적화는 전체 데이터를 처리하면서 효율성을 높이는 데 중점을 둔다.
ALL_ROWS 모드
ALL_ROWS 모드는 모든 데이터를 처리하는 데 최적화된 실행 계획을 제공한다. 배치 처리(Batch Processing)에 적합하며, 높은 처리량(Best Throughput)을 목표로 한다. 이 모드는 주로 OLAP(Online Analytical Processing)과 같은 대규모 데이터 처리 환경에서 사용된다.
FIRST_ROWS 모드
FIRST_ROWS 모드는 제한된 데이터 범위에서 빠른 결과를 제공하는 데 초점을 맞춘다. OLTP(Online Transaction Processing) 환경에 적합하며, 즉각적인 응답이 중요한 작업에 사용된다. 예를 들어 특정 고객의 데이터를 검색하거나 소규모 조회 작업에서 효율적이다.
FIRST_ROWS_n 모드
FIRST_ROWS_n 모드는 n개의 데이터를 먼저 처리하는 데 최적화된 실행 계획을 제공한다. 특정 조건에서 초기 데이터에 대한 빠른 처리가 필요할 때 활용되며, OLTP와 OLAP 환경 모두에 적합하지만 주로 응답 시간을 목표로 설정할 때 사용된다.
CHOOSE 모드
CHOOSE 모드는 과거 옵티마이저의 기본 모드로, 규칙기준과 비용기준 방식을 혼합하여 최적화를 수행한다. 하지만 현재는 대부분의 DBMS에서 ALL_ROWS 또는 FIRST_ROWS 모드로 대체되었다.
최적화 목표와 모드 설정의 연관성
실행 환경에 따라 모드를 적절히 선택해야 한다. OLTP 환경에서는 FIRST_ROWS 모드가 주로 사용되며, OLAP 환경에서는 ALL_ROWS 모드가 일반적으로 사용된다. 모드 설정에 따라 동일한 SQL 문이라도 다른 실행 계획이 도출될 수 있으며, 실제 환경에서는 기본적으로 ALL_ROWS 모드가 사용되지만 응답 속도가 중요한 경우 FIRST_ROWS 모드로 전환해 최적화를 진행할 수 있다.
옵티마이저 모드의 결정 기준
OLTP 시스템에서의 모드 선택
OLTP(Online Transaction Processing) 시스템에서는 빠른 응답 속도가 중요하기 때문에 FIRST_ROWS 모드를 선택하는 것이 적합하다. 이 모드는 주어진 실행 계획을 가능한 한 빠르게 처리해 즉각적인 결과를 제공한다.
OLAP 시스템에서의 모드 선택
OLAP(Online Analytical Processing) 시스템에서는 대량의 데이터를 처리하고 분석하는 것이 중요하다. 따라서 ALL_ROWS 모드를 선택하여 전체 데이터를 효율적으로 처리하고 최적의 처리량(Best Throughput)을 달성해야 한다.
운영 중 모드 변경의 위험성
운영 중에 옵티마이저 모드를 변경하는 것은 시스템 안정성에 큰 영향을 미칠 수 있으므로 주의가 필요하다. 예를 들어, 운동 경기 도중 전략을 바꾸는 것처럼 실행 중인 시스템에서 모드를 변경하는 것은 대부분의 경우 적합하지 않다.
모드 변경으로 인한 대책 마련
모드를 변경하거나 DBMS를 업그레이드하면서 옵티마이저가 변경된 경우, 변경의 충격을 흡수할 수 있는 충분한 대책이 필요하다. 기존에 사용하던 실행 계획을 유지하거나 적절한 파라미터를 설정해 혼란을 방지해야 한다.
실행 계획 유지 방법
실행 계획의 일관성을 유지하기 위한 두 가지 주요 방법이 있다.
- 초기 설정 파라미터로 기존의 옵티마이저 버전을 지정한다.
- 실행 계획 요약본(아우트라인)을 생성해 이를 참조하도록 설정한다.
OPTIMIZER_FEATURES_ENABLE 파라미터의 활용
이 파라미터는 과거에 사용했던 옵티마이저 버전을 지정하여 업그레이드로 인한 불필요한 변경을 방지하는 데 유용하다. 기본적으로는 자동으로 최신 버전으로 전환되지만, 안정성을 유지하려면 직접 설정하는 것이 좋다.
새로운 기술 수용에 대한 고려
새로운 기술을 무조건적으로 수용하는 것은 바람직하지 않다. 새로운 변화가 필요하지 않은 안정적인 환경에서는 기존의 안정된 실행 계획을 유지하는 것이 적합하며, 무리하게 새로운 기능을 도입하지 않아야 한다.
옵티마이져 모드와 관련 파라미터 지정
CURSOR_SHARING
CURSOR_SHARING 파라미터는 SQL 문장에서 상수값을 변수로 변환해 파싱 효율성을 높이는 데 사용된다. 기본값은 EXACT로, 상수값을 그대로 유지하지만, FORCE 또는 SIMILAR로 설정하면 변수화를 통해 실행 계획을 공유할 수 있다. 특히, SIMILAR 설정은 유사한 SQL 문에서도 동일한 실행 계획을 재사용할 수 있어 효율적이다.
DB_FILE_MULTIBLOCK_READ_COUNT
이 파라미터는 테이블 전체 스캔 또는 인덱스 고속 스캔 시 I/O 작업의 효율성을 조정하는 데 사용된다. 기본값은 시스템에 따라 다르지만, 입출력 성능이 높은 환경에서는 이 값을 증가시켜 더 많은 블록을 한 번에 읽도록 설정할 수 있다.
OPTIMIZER_INDEX_CACHING
OPTIMIZER_INDEX_CACHING은 인덱스 Nested Loops 조인에서 인덱스 블록이 캐시에 유지될 확률을 조정한다. 기본값은 0이며, 인덱스가 재사용될 가능성이 높은 환경에서는 이 값을 높여 인덱스 액세스 효율을 높일 수 있다. 단, 과도한 설정은 옵티마이저의 실행 계획에 부정적 영향을 줄 수 있으므로 주의해야 한다.
OPTIMIZER_INDEX_COST_ADJ
OPTIMIZER_INDEX_COST_ADJ는 인덱스 접근 방식의 비용을 조정하는 파라미터로, 기본값은 100이다. 값을 낮추면 인덱스 접근 방식의 우선순위를 높일 수 있어, 특정 환경에서 더 빠른 실행 계획을 유도할 수 있다.
Dynamic Sampling (동적 표본화)
Dynamic Sampling은 통계 정보가 부족한 경우 실행 시점에서 표본 데이터를 수집해 통계 정보를 보완하는 기능이다. 기본값은 0이며 기능이 비활성화된 상태지만, 특정 상황에서는 표본 추출 수준을 높여 실행 계획의 정확성을 개선할 수 있다. 일반적으로 1~2로 설정하는 것이 적절하며, 과도한 표본 추출은 전체 실행 속도를 저하시킬 수 있다.
3.1.2.4. 실행계획의 고정화(Stability)
실행계획은 환경과 조건에 따라 동적으로 최적화되지만, 예상치 못한 변경이 발생하면 성능 저하가 발생할 수 있다. 특히 동일한 SQL 문이 실행될 때마다 다른 실행계획이 적용되면 운영의 예측 가능성이 낮아지고, 튜닝된 쿼리도 불안정해질 위험이 있다. 이러한 문제를 방지하기 위해 실행계획을 고정하는 방법이 필요하다.
이를 위한 대표적인 방법으로 아웃라인(Outline) 기능을 활용할 수 있다. 실행계획을 미리 저장해 동일한 SQL 문이 실행될 때 참조하도록 하면 불필요한 변경을 방지하고 성능을 안정적으로 유지할 수 있다. 하지만 실행계획을 지나치게 고정하면 새로운 최적화 기회를 차단할 위험이 있으므로, 필요에 따라 적절히 조정해야 한다.
보다 유연한 관리 방법으로 카테고리(Category) 개념을 도입할 수도 있다. 예를 들어, 주간과 야간에 실행되는 SQL을 각각 분류하여 최적화된 실행계획을 적용하면 업무 성격이 다른 환경에서도 성능을 일정하게 유지할 수 있다.
빈번한 통계정보 갱신이 필요한 경우에도 실행계획 고정화는 유용하다. 완벽하게 튜닝된 시스템에서는 불필요한 실행계획 변경을 방지하는 것이 성능 안정성 유지에 필수적이기 때문이다. 따라서 실행계획 고정화는 예측 가능한 성능을 보장하고, 안정적인 운영을 위한 중요한 전략으로 활용될 수 있다.
가) 아우트라인의 생성과 조정
SQL 실행 계획은 데이터베이스 환경과 조건 변화에 따라 달라질 수 있으며, 이로 인해 예상치 못한 성능 저하가 발생할 가능성이 있다. 이를 방지하기 위해 아우트라인(Outline)을 활용하면 특정 SQL 문에 대해 일관된 실행 계획을 유지할 수 있다. 아우트라인을 적용하면 동일한 SQL 문이 실행될 때마다 예측 가능한 결과를 보장하고, 불필요한 실행 계획 변경을 방지할 수 있어 안정적인 성능을 유지할 수 있다.
이를 위해 DBMS_OUTLN과 DBMS_OUTLN_EDIT 패키지가 제공되며, 이를 활용하면 실행 계획을 저장하고, 필요에 따라 수정하거나 삭제할 수도 있다. 주요 기능으로는 CREATE_OUTLINE, DROP_BY_CAT, DROP_UNUSED, UPDATE_BY_CAT 등의 프로시저가 있으며, 이를 통해 새로운 아우트라인을 생성하고 기존 아우트라인을 삭제하거나 변경할 수 있다. 또한, 특정 SQL 문에 대한 실행 계획을 관리하기 위해 GENERATE_SIGNATURE를 활용할 수도 있다.
아우트라인은 모든 SQL 문에 대해 자동으로 생성될 수도 있고, 특정 SQL 문에 대해서만 개별적으로 생성할 수도 있다. 일반적으로 CREATE_STORED_OUTLINES 파라미터를 설정하여 아우트라인을 생성하고 저장하는 방식이 사용된다.
'TRUE'로 설정하면 'DEFAULT'라는 카테고리에 아우트라인이 자동 생성되며, 새로운 실행 계획이 추가될 때 이를 자동으로 저장할 수 있다. 'FALSE'로 설정하면 기존 아우트라인만 유지되며, 새로운 실행 계획은 추가되지 않는다.
생성된 아우트라인을 실제 환경에서 적용하려면 USE_STORED_OUTLINES 파라미터를 TRUE로 설정하여 실행 계획이 항상 해당 아우트라인을 따르도록 해야 한다. 또한, 실행 계획을 수정해야 할 경우 ALTER OUTLINE 명령어를 사용하면 기존 아우트라인의 이름을 변경하거나 특정 값으로 재생성할 수도 있다.
일반적으로 생성된 아우트라인은 모든 세션에 적용되지만, 특정 세션에서만 실행 계획을 고정하고 싶다면 개별 아우트라인(Private Outlines)을 사용할 수 있다. 개별 아우트라인은 특정 세션에서만 유효하며, 다른 세션에는 영향을 미치지 않기 때문에 특정 실행 계획을 유지하면서도 수정이 필요한 경우 유용하게 활용할 수 있다.
그 다음은 개별 아우트라인을 생성하는 과정을 살펴보려 한다. 개별 아우트라인을 생성하는 과정은 다음과 같다.
먼저 기존 아우트라인에서 새로운 개별 아우트라인을 복제한다.
CREATE PRIVATE OUTLINE prv_ol_1 FROM outline1;그 후 DBMS_OUTLN_EDIT 패키지를 활용하여 필요에 따라 아우트라인을 수정하거나 조정한다.
수정이나 조정이 끝났으면 조정된 아우트라인을 실제 적용하기 전에 USE_PRIVATE_OUTLINES을 TRUE로 설정하여 테스트를 수행한다.
그 후 검증이 완료되면 기존 아우트라인을 새로운 아우트라인으로 대체해준다.
CREATE OR REPLACE OUTLINE outline1 FROM PRIVATE prv_ol_1;적용이 완료되면 USE_PRIVATE_OUTLINES을 FALSE로 설정하여 개별 아우트라인 사용을 종료한다.
나) 아우트라인의 관찰
아우트라인은 데이터베이스의 딕셔너리 테이블에 저장되며, 제공된 뷰를 통해 내용을 확인할 수 있다. USER_OUTLINES나 USER_OUTLINE_HINTS와 같은 뷰를 활용하면 생성된 아우트라인의 세부 정보를 조회할 수 있으며, 권한에 따라 ALL 또는 DBA 뷰를 활용하여 보다 포괄적인 정보를 확인할 수도 있다.
아래는 특정 SQL 문에 대해 생성된 아우트라인을 조회한 결과를 나타낸다.
select empno, ename, job, loc
from emp e, dept d
where e.deptno = d.deptno and e.empno = 7856
order by loc;이 쿼리에 대해 생성된 아우트라인의 주요 정보는 다음과 같다.
- NAME - 자동으로 생성된 아우트라인의 명칭이며, 테이블의 식별자로 사용된다.
- CATEGORY - 아우트라인이 속하는 카테고리를 나타낸다.
- USED - 해당 아우트라인이 사용된 여부를 나타낸다.
- TIMESTAMP - 아우트라인이 생성된 시간을 기록한다.
- VERSION - 아우트라인이 적용된 데이터베이스 버전을 표시한다.
- SQL_TEXT - 실행 계획을 저장하는 SQL 문을 나타낸다.
- SIGNATURE - 아우트라인의 고유한 식별자로 사용된다.
그리고 아우트라인은 실행된 SQL 문을 기반으로 자동 생성될 수 있고, 특정 SQL문이 동일한 실행 계획을 유지하도록 관리되게 된다. 자동 생성된 아우트라인의 특징이 있는데 CURSOR_SHARING이 SIMILAR로 설정된 경우, 조건절의 상수 값이 바인드 변수로 변환되어 저장될 수 있다.
그리고 SQL 문이 단순한 텍스트가 아니라 실행 시 변환되는 형태(예: RAW)로 저장되며, 동일한 SQL이라도 다른 카테고리에서 생성될 경우 명칭이 달라질 수 있다.
아우트라인이 저장하는 실행 계획은 완전한 실행 계획의 형태는 아니지만, 조인 방식, 인덱스 사용 여부, 비용 정보 등을 포함한다. 아래는 저장된 아우트라인의 힌트 정보를 조회한 결과이다.
| HINT# | HINT_TEXT | NODE# | STAGE# | TABLE_POS | TABLE_NAME | COST | CARDINALITY |
| 1 | NO_EXPAND | 1 | 3 | 0 | DEPT | 0 | 0 |
| 2 | PQ_DISTRIBUTE(D NONE NONE) | 1 | 3 | 0 | DEPT | 0 | 0 |
| 3 | USE_NL(D) | 1 | 3 | 0 | DEPT | 2 | 1 |
| 4 | ORDERED | 1 | 3 | 0 | DEPT | 0 | 0 |
| 5 | NO_FACT(D) | 1 | 3 | 0 | DEPT | 0 | 0 |
| 6 | NO_FACT(E) | 1 | 3 | 2 | EMP | 1 | 27 |
| 7 | INDEX(D PK_DEPT) | 1 | 3 | 2 | EMP | 1 | 1 |
| 8 | INDEX(E PK_EMP) | 1 | 3 | 2 | EMP | 1 | 1 |
| 9 | NOREWRITE | 1 | 1 | 0 | 0 | 0 |
이 테이블에서 볼 수 있듯이, 특정 테이블에 대한 조인 방법, 인덱스 활용 방식, 그리고 실행 비용과 관련된 정보가 포함되어 있다. 하지만 이 데이터는 실행 계획의 전체적인 모습을 나타내지는 않으며, 저장된 힌트를 기반으로 실제 실행 계획이 결정된다.
다) 옵티마이저 업그레이드 시의 적용
옵티마이저는 지속적으로 발전하며 새로운 최적화 기법과 처리 경로를 추가하지만, 이러한 변화가 시스템 안정성에 미치는 영향은 결코 가볍지 않다. 기존에 안정적으로 운영되던 시스템이 업그레이드 이후 예상치 못한 성능 저하를 겪을 수 있으며, 특히 실행 계획의 변경으로 인해 업무 처리 속도가 급격히 변할 가능성이 있다. 이러한 변화를 효과적으로 관리하지 않으면 시스템 성능이 불안정해지고, 운영에 혼란이 발생할 수 있다.
업그레이드는 필연적으로 시스템의 개선을 위한 과정이지만, 운영 환경에서는 갑작스러운 변화가 부담으로 작용할 수 있다. 사회적 변화가 제도적 혼란을 초래하는 것처럼, 옵티마이저의 변화도 실행 계획을 예측하기 어렵게 만들어 시스템 성능에 악영향을 미칠 수 있다. 따라서 업그레이드로 인한 혼란을 방지하기 위해서는 미리 대비하고, 문제 발생 시 즉각적으로 대응할 수 있는 체계적인 전략이 필요하다.
가장 효과적인 방법은 업그레이드된 옵티마이저가 기존 실행 계획에 미치는 영향을 최소화하는 것이다. 이를 위해 운영 환경과 동일한 테스트 환경을 구축하고 철저한 검증을 거친 후 업그레이드를 적용하는 것이 바람직하다. 하지만 현실적으로 운영 환경과 완전히 동일한 테스트 환경을 구성하는 것은 쉽지 않으며, 실제 적용 과정에서 발생하는 다양한 변수들을 고려해야 한다.
한 가지 현실적인 대안은 기존 실행 계획을 그대로 유지하면서 점진적으로 변화를 적용하는 것이다. 이를 위해 일정 기간 동안 기존 규칙기준 옵티마이저(Rule-Based Optimizer, RBO)를 유지한 후 비용기준 옵티마이저(Cost-Based Optimizer, CBO)로 전환하는 방식을 고려할 수 있다. 규칙기준 옵티마이저를 사용하면 기존 실행 계획이 유지되므로 예상치 못한 성능 저하를 방지할 수 있다.
현재 사용 중인 SQL에 대해 특정 카테고리를 설정하여 아우트라인을 생성하면 업그레이드 전후 실행 계획을 비교하고 유지할 수 있다.
ALTER SESSION SET CREATE_STORED_OUTLINES = category_name;필요하다면 전체 세션에 적용하여 일괄적으로 관리할 수도 있다.
중요한 SQL 문에 대해서는 아우트라인을 생성하고 유지하면서 장기적으로 비용기준 옵티마이저로 전환하는 계획을 세운다. 일정 기간 동안 아우트라인을 생성한 후, 새로운 실행 계획이 생성되지 않도록 다음 명령어를 사용하여 아우트라인 생성을 중지한다.
ALTER SESSION SET CREATE_STORED_OUTLINES = FALSE;옵티마이저가 최신 상태의 실행 계획을 정확하게 도출하도록 하기 위해 통계 정보를 정기적으로 갱신하고, 실행 환경의 변화를 반영할 필요가 있다. 기존 환경에서 규칙기준 옵티마이저를 사용하고 있었다면, 일정한 검증을 거친 후 비용기준 옵티마이저로 전환하여 새로운 실행 계획이 적용될 수 있도록 한다.
업그레이드 후 예상치 못한 실행 계획 변경을 방지하기 위해 기존에 생성된 아우트라인을 적용할 수 있다.
ALTER SESSION SET USE_STORED_OUTLINES = category_name;또한, 기존 실행 계획을 일괄적으로 적용하려면 다음 명령어를 사용할 수 있다.
ALTER SESSION SET USE_STORED_OUTLINES = our_category_name;3.1.2.5. 옵티마이져의 한계
옵티마이저는 최적의 실행 계획을 찾기 위해 다양한 통계 정보를 활용하지만, 근본적인 한계를 가지고 있다. 가장 큰 문제는 미래의 데이터를 정확하게 예측할 수 없다는 점이다. 예를 들어, 특정 회사의 주가가 내일 오를지 내릴지를 정확하게 알 수 없는 것처럼, 데이터 분포와 실행 계획이 항상 일관되게 유지된다는 보장은 없다.
비록 옵티마이저가 많은 정보를 가지고 있다 하더라도, 특정 컬럼의 값이 일정한 분포를 따를 것이라고 단정할 수 없다. 어떤 경우에는 특정 값들이 특정 범위에 몰려 있거나, 예외적으로 높은 빈도를 보일 수 있다. 이러한 경우 실행 계획이 예상과 다르게 동작할 가능성이 크며, 처리 성능이 극단적으로 차이가 날 수 있다.
컬럼의 값에 대한 분포를 명확히 알고 있다면 실행 계획을 더욱 정교하게 수립할 수 있겠지만, 현실적으로 이는 불가능하다. 각 컬럼의 값이 단순한 ‘=’ 연산이 아니라 ‘LIKE’, ‘BETWEEN’ 등의 연산을 포함할 경우, 분포를 예측하는 것은 더욱 어려워진다. 또한, 옵티마이저는 실행 계획을 수립할 때 변수의 값을 미리 알 수 없는 경우가 많다. 즉, 실행 시점에서 바인딩된 변수 값에 따라 실행 계획이 결정되기 때문에, 사전에 정확한 계획을 세우는 것이 어렵다.
이 문제를 해결하기 위해 히스토그램이 도입되었지만, 모든 데이터의 정확한 분포를 반영하지는 못한다. 특히, 컬럼 값의 범위가 넓거나, 예상치 못한 값이 등장할 경우 옵티마이저의 판단이 흔들릴 수 있다. 또한, 컬럼들이 조합되어 특정한 인덱스를 구성할 때, 각각의 컬럼이 독립적으로 분포하는 것이 아니라 서로 영향을 미칠 수 있다. 따라서, 옵티마이저가 모든 경우의 수를 고려하여 최적의 실행 계획을 찾는 것은 사실상 불가능하다.
실제 데이터베이스 환경에서는 컬럼 간의 관계가 복잡하며, 조인되는 테이블의 크기나 필터링 조건에 따라 최적의 실행 계획이 달라진다. 예를 들어, 300개의 ‘부서’와 10개의 ‘사업장’이 존재한다고 가정하면, 일반적인 결합 방식에서는 3000개의 조합이 만들어질 수 있다. 하지만 특정한 사업장은 특정한 부서와만 결합하는 경향이 있다면, 옵티마이저는 이를 반영하지 못하고 잘못된 실행 계획을 선택할 수도 있다.
또한, 히스토그램을 사용하여 컬럼 분포를 예측한다고 해도, 실제 실행되는 SQL 문이 조합된 컬럼을 기반으로 동작할 경우 옵티마이저는 예측 오류를 범할 가능성이 크다. 결합된 컬럼들의 분포가 단순한 곱셈으로 계산될 수 없기 때문이다. 이러한 문제를 해결하기 위해 일부 데이터베이스에서는 결합된 컬럼의 히스토그램을 따로 관리하기도 하지만, 이 역시 완벽한 해결책은 아니다.
따라서, 옵티마이저가 최적의 실행 계획을 수립하는 데에는 한계가 존재하며, 이를 보완하기 위해서는 단순히 옵티마이저의 판단에 의존하는 것이 아니라, 실행 계획을 직접 검토하고 조정할 필요가 있다. 특정 SQL 문에서 옵티마이저가 예측하지 못하는 패턴이 반복적으로 나타난다면, 실행 계획을 고정하거나 힌트를 사용하여 수동으로 조정하는 것이 필요하다.
이와 같은 한계를 극복하기 위해서는 통계 정보를 지속적으로 갱신하고, 실행 계획을 모니터링하여 문제 발생 시 즉각적인 대응이 가능하도록 해야 한다. 또한, 단순히 비용기준 옵티마이저의 판단에 의존하기보다는, 데이터베이스 구조와 인덱스 설계를 최적화하여 옵티마이저가 보다 정확한 판단을 내릴 수 있도록 유도하는 것이 중요하다.
3.1.3. 옵티마이저의 최적화 절차
옵티마이저는 사용자가 원하는 결과를 최적의 방법으로 도출하기 위해 다양한 실행 계획을 분석하고 선택하는 역할을 한다. 가장 효율적인 처리 방식을 찾는 과정은 단순하지 않으며, 많은 경우 여러 가지 실행 계획을 비교하며 최적의 경로를 예측해야 한다. 하지만 미래의 처리 비용을 정확하게 예측하는 것은 어렵기 때문에 옵티마이저는 수집된 통계 정보와 알고리즘을 활용하여 최적의 방법을 추론한다.
SQL 문이 실행되면 옵티마이저는 데이터 딕셔너리에 저장된 테이블과 인덱스 정보를 바탕으로 가능한 실행 계획을 도출한다. 이 과정에서 아우트라인과 힌트 등의 요소도 참고하여 최적의 처리 방식을 결정하게 된다. 초기 실행 단계에서 SQL은 데이터 딕셔너리를 참조하여 분석되며, 옵티마이저는 이 분석된 결과를 기반으로 논리적으로 적용 가능한 실행 계획을 도출한다. 이러한 과정은 일차적으로 가설적인 실행 계획을 수립하는 형태로 진행된다.
옵티마이저는 데이터의 저장 구조, 인덱스 활용도, 연산 비용 등을 종합적으로 고려하여 각 실행 계획의 비용을 계산한다. 이 과정에서 통계 정보를 활용하여 데이터 분포도 및 테이블의 저장 방식이 실행 계획에 미치는 영향을 분석하며, 실행 가능한 모든 계획 중 가장 낮은 비용을 가진 실행 계획을 선택한다. 그러나 이 선택 과정에서도 항상 최적의 결정이 이루어지는 것은 아니다. 마치 최적 입찰 방식이 반드시 최상의 결과를 보장하지 않는 것과 유사하게, 옵티마이저의 결정이 항상 최적의 결과를 도출한다고 단정할 수는 없다. 이 때문에 힌트 등을 활용하여 옵티마이저의 선택을 조정하는 방법도 사용된다.
옵티마이저의 최적화 절차를 더 구체적으로 살펴보면, 먼저 SQL 문이 입력되면 질의 변환기(Query Transformer)를 통해 분석이 진행된다. 이후 비용 산정기(Estimator)가 통계 정보를 바탕으로 각 실행 계획의 비용을 평가하고, 실행 계획 생성기(Plan Generator)가 이를 바탕으로 최적의 실행 계획을 선택하는 방식으로 동작한다. 사용자가 실행한 SQL 문이 단순한 텍스트 문자열이 아니라, 테이블과 컬럼 정보를 참조하여 최적의 경로를 결정해야 하기 때문에 옵티마이저는 이를 내부적으로 구조화하여 최적화 작업을 수행한다.
이 과정에서 옵티마이저는 쿼리 블록(Query Block) 형태로 실행 계획을 수립하며, 논리적 관계를 분석하여 실행 순서를 결정한다. 테이블 간 조인 방식, 인덱스 활용 여부, 연산 비용 등이 모두 고려되며, 최종적으로 실행 계획이 도출된다. 이러한 절차를 거쳐 옵티마이저는 가장 효율적인 실행 경로를 선택하지만, 사용자의 힌트 제공 여부, 통계 정보의 정확성, 데이터의 분포 등에 따라 최적화 결과는 달라질 수 있다. 따라서 옵티마이저의 최적화 절차를 이해하고 필요에 따라 적절한 튜닝을 수행하는 것이 중요하다.
가) 질의 변환기
옵티마이저의 질의 변환 기능은 보다 효율적인 실행 계획을 수립하기 위해 SQL 문을 변형하는 역할을 한다. 이를 위해 다양한 변환 기법이 적용되는데, 대표적인 방식으로는 뷰 병합, 조건절 진입, 서브쿼리 비표조화, 실체뷰 쿼리 재작성, OR 조건의 전개, 사용자 정의 바인드 변수 엿보기 등이 있다.
뷰 병합은 사용자가 정의한 뷰를 기반으로 실행되는 SQL 문을 실제 테이블을 액세스하는 쿼리로 변환하는 기법이다. 이를 통해 불필요한 뷰의 중첩을 제거하여 실행 성능을 높일 수 있다. 다만, 특정한 제약 조건이 있는 경우 병합이 불가능할 수도 있다.
조건절 진입은 쿼리 내부의 서브쿼리에 존재하는 조건을 메인 쿼리로 이동시켜 실행 계획을 최적화하는 기법이다. 이를 통해 불필요한 연산을 줄이고, 보다 효율적인 인덱스 활용이 가능하도록 유도할 수 있다.
서브쿼리 비표조화는 서브쿼리를 포함한 SQL 문에서 불필요한 중첩 관계를 해소하여 실행 성능을 향상시키는 방식이다. 서브쿼리가 비표조화될 경우, 옵티마이저는 쿼리를 보다 단순한 형태로 변환하여 실행할 수 있으며, 불필요한 연산을 줄일 수 있다.
실체뷰 쿼리 재작성은 실체뷰를 물리적 테이블처럼 처리하는 방식으로, 실체뷰가 저장한 데이터를 직접 조회함으로써 성능을 개선하는 기법이다. 원래 실체뷰는 논리적인 집합이지만, 옵티마이저는 이를 활용하여 보다 효율적인 물리적 데이터 접근을 가능하게 한다.
OR 조건의 전개는 OR 연산자를 사용한 조건을 개별적인 쿼리로 변환한 후, UNION ALL을 통해 결합하는 방식이다. 이를 통해 병렬 처리나 특정 인덱스를 활용하는 실행 계획을 선택할 가능성이 높아진다. 그러나 단순한 OR 조건을 무분별하게 전개하면 성능이 저하될 수도 있기 때문에, 옵티마이저는 비용을 기반으로 이를 결정한다.
사용자 정의 바인드 변수 엿보기(Peeking)는 쿼리에서 바인드 변수를 사용할 때, 초기 실행 시 변수 값을 참조하여 실행 계획을 수립하는 기법이다. 이를 통해 실행 계획이 보다 최적화될 수 있지만, 바인드 변수 값이 자주 변경되는 환경에서는 계획이 일정하게 유지되지 않을 수 있다. 따라서, 특정 환경에서는 바인드 변수 대신 리터럴 값을 활용하는 것이 보다 안정적인 성능을 보장할 수도 있다.
이러한 질의 변환 기법들은 SQL 문을 보다 최적화된 형태로 변형하여 실행 성능을 향상시키는 데 중요한 역할을 하며, 옵티마이저는 이러한 변환을 비용과 성능을 고려하여 자동으로 적용한다.
나) 비용 산정기
옵티마이저는 실행 계획을 최적화하기 위해 다양한 비용 산정 기법을 활용한다. 주요 평가 요소로는 선택도(Selectivity), 카디널리티(Cardinality), 비용(Cost) 등이 있으며, 이들은 서로 밀접하게 연관되어 있다. 선택도는 특정 조건을 만족하는 데이터의 비율을 의미하며, 실행 계획 수립에 중요한 영향을 미친다. 예를 들어, 특정 컬럼이 인덱스를 갖고 있고 조건을 만족하는 데이터 비율이 낮다면 선택도가 높다고 판단할 수 있다. 반대로, 조건을 만족하는 데이터가 많다면 선택도는 낮아지게 된다.
옵티마이저는 보다 정확한 선택도를 추정하기 위해 통계 정보를 활용하지만, 개별 컬럼에 대한 정보만을 기준으로 계산하기 때문에 복합적인 조건이 적용될 경우 정확한 예측이 어려울 수 있다. 이러한 문제를 보완하기 위해 히스토그램 정보를 사용하거나 동적 표본을 이용한 통계 정보 수집 방식이 활용되기도 한다. 하지만 모든 경우에 완벽한 예측이 가능한 것은 아니며, 특정 연산자나 조건 형태에 따라 오차가 발생할 수도 있다.
카디널리티는 특정 실행 계획 내에서 처리해야 할 데이터의 양을 의미하며, 앞서 계산된 선택도와 전체 로우 수를 기반으로 결정된다. 선택도와 마찬가지로 카디널리티 역시 상대적인 개념이며, 옵티마이저는 이를 통해 처리할 데이터의 규모를 예측하여 최적의 실행 계획을 도출한다. 하지만 선택도와 카디널리티를 단순히 비율로만 계산하는 것은 한계가 있으며, 조인 순서나 데이터의 구조를 고려해야 한다.
비용(Cost)은 실행 계획에서 각 연산이 차지하는 자원 소모량을 의미하며, CPU, 메모리, 디스크 I/O 등의 요소를 고려하여 산정된다. 일반적으로 비용이 낮은 실행 계획이 선택되지만, 정확한 예측이 어렵기 때문에 최적의 실행 계획이 항상 보장되는 것은 아니다. 또한, 같은 평가 기준을 적용하더라도 환경에 따라 결과가 달라질 수 있으며, 실행 계획이 변경될 가능성도 존재한다.
결과적으로 옵티마이저는 선택도, 카디널리티, 비용을 종합적으로 분석하여 최적의 실행 계획을 수립하려 하지만, 여전히 한계를 가지고 있다. 이를 보완하기 위해 힌트나 초기화 파라미터를 활용하여 실행 계획을 조정하는 방법이 추가적으로 사용되고 있다.
다) 실행계획 생성기
실행 계획 생성기의 주요 역할은 주어진 쿼리를 처리할 수 있는 실행 계획을 생성하고, 여러 계획을 비교하여 가장 최적의 비용을 가진 계획을 선택하는 것이다. 실행 계획은 다양한 액세스 경로의 조합으로 이루어지며, 동일한 조인을 다르게 구성할 수도 있다. 하지만 실행 계획에 따라 처리 성능이 크게 차이 날 수 있으므로, 최적의 실행 계획을 선택하는 과정이 중요하다.
하위 쿼리의 실행 계획은 우선적으로 생성된다. 서브쿼리나 병합이 불가능한 부등호 조인은 상위 쿼리의 실행 계획에 직접적인 영향을 미치므로, 먼저 최적화를 수행한 후 메인 쿼리에 반영된다. 이러한 처리 방식은 SQL 문을 내부적으로 여러 개의 처리 단위(Query Block)로 나누어 처리하는 방식과 관련이 있다.
실행 계획을 최적화하기 위해 옵티마이저는 여러 실행 계획을 평가하는데, 모든 가능한 실행 계획을 고려하기보다는 일정한 제한 시간 내에 최적의 결과를 도출해야 한다. 바둑에서 제한된 시간 내에 최적의 수를 두는 것처럼, 옵티마이저도 주어진 자원 내에서 가장 효율적인 실행 계획을 찾아야 한다. 이를 위해 적응적 탐색(Adaptive Search) 및 경험적 휴리스틱(Heuristic) 기법을 활용한다.
- 적응적 탐색(Adaptive Search) - 제한 시간이 있는 경우, 가능한 실행 계획을 평가하면서 점진적으로 더 나은 계획을 찾아간다.
- 경험적 휴리스틱(Heuristic) - 과거의 실행 패턴을 분석하여 초기 실행 계획을 선택하고, 이를 기반으로 최적화를 수행한다.
이러한 전략을 통해 옵티마이저는 최적의 실행 계획을 찾을 확률을 높인다. 하지만 주어진 시간 내에 반드시 최적의 실행 계획을 찾을 수 있는 것은 아니므로, 힌트를 활용하여 옵티마이저의 결정을 조정하는 것이 중요하다.
실행 계획 생성 과정은 바둑 경기와 유사하다. 초보자와 달리 숙련된 선수들은 제한된 시간 내에서도 가장 적절한 수를 찾을 수 있다. 옵티마이저도 마찬가지로 단순히 규칙만 따르는 것이 아니라, 다양한 힌트를 활용하여 최적의 실행 계획을 선택하는 능력을 갖추고 있다. 하지만 아무리 우수한 옵티마이저라도 완벽할 수는 없으며, 이를 보완하기 위해서는 튜닝 전문가의 개입이 필요하다.
3.1.4.질의의 변환 (Query Transforming)
옵티마이저는 가능한 모든 수식의 값을 미리 구한다. 예를 들어, 상수의 연산은 실행될 때마다 이루어지는 것이 아니라 질의 변환 과정에서 한 번만 수행된다. 다음과 같은 조건절이 있다고 가정해 보자.
- sales_qty > 1200/12
- sales_qty > 100
- sales_qty*12 > 1200
첫 번째 조건식은 사전에 최대한 연산을 수행하여 두 번째 조건식으로 변환된다. 따라서 ①과 ②는 동일하게 취급된다. 하지만 ③처럼 비교 연산자가 추가된 경우에는 좌항과 우항을 단순히 변환하지 않는다.
또한, LIKE 연산자가 포함된 경우에도 변환이 가능하다. 예를 들어
- job like 'SALESMAN'
- job = 'SALESMAN'
데이터 타입이 고정길이로 지정된 경우, 이러한 변환이 다르게 처리될 수도 있다. 특히 IN 연산자의 경우, 논리 연산자를 사용하여 변환될 수 있다.
- job IN ('CLERK', 'MANAGER')
- job = 'CLERK' OR job = 'MANAGER'
ANY 또는 SOME 연산자를 포함한 일반적인 조건식도 변환될 수 있다.
- sales_qty > ANY (:in_qty1, :in_qty2)
- sales_qty > :in_qty1 OR sales_qty > :in_qty2
만약 ANY 또는 SOME 뒤에 서브쿼리가 포함된 경우, EXISTS 연산자로 변환될 수 있다.
WHERE 100000 > ANY (SELECT sal FROM emp WHERE job = 'CLERK')WHERE EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 100000 > sal)ALL을 사용하는 경우에도 변환이 가능하며, NOT ANY는 NOT EXISTS로 변환될 수 있다.
WHERE 100000 > ALL (SELECT sal FROM emp WHERE job = 'CLERK')WHERE NOT (100000 > ANY (SELECT sal FROM emp WHERE job = 'CLERK'))WHERE NOT EXISTS (SELECT sal FROM emp WHERE job = 'CLERK' AND 100000 <= sal)
BETWEEN 연산자는 비교 연산자로 변환될 수 있다.
- sales_qty BETWEEN 100 AND 200
- sales_qty >= 100 AND sales_qty <= 200
NOT 연산자가 포함된 경우, 논리 연산자를 제거하고 비교 연산자를 찾아 대체한다.
- NOT (sal < 30000 OR comm IS NULL)
- NOT sal < 30000 AND comm IS NOT NULL
- sal >= 30000 AND comm IS NOT NULL
서브쿼리에 사용된 NOT도 변환될 수 있다.
NOT deptno = (SELECT deptno FROM emp WHERE empno = 7689)deptno <> (SELECT deptno FROM emp WHERE empno = 7689)3.1.4.1. 이행성 규칙
옵티마이저는 실행 계획을 최적화하기 위해 기존 조건식을 변형하여 새로운 조건식을 생성하는데, 이를 이행성 규칙이라 한다. 이 규칙을 적용하면 원래 조건식에서는 인덱스를 활용하지 못하던 경우에도 최적화된 실행 계획이 도출될 수 있다.
예를 들어 다음과 같은 조건식이 있다고 가정해보자.
WHERE column1 comparison_operators constant
AND column1 = column2;여기서 comparison_operators는 =, !=, <, >, <=, >= 등의 연산자를 의미하며, constant는 상수 값, SQL 변수, 문자열, 바인드 변수 등을 포함할 수 있다.
우리가 알고 있는 기본 논리 A = B이고, B = C라면 A = C라는 원리가 적용되듯 옵티마이저도 이를 기반으로 다음과 같은 새로운 조건을 유추할 수 있다.
column2 comparison_operators constant이행성 규칙의 적용 효과
이행성 규칙은 비용 기반 옵티마이저(CBO, Cost-Based Optimizer)에서만 적용되며, 이는 SQL 실행 시 불필요한 연산을 줄이고, 보다 효율적인 실행 계획을 선택할 수 있도록 돕는다.
예를 들어, Nested Loops 조인을 사용할 경우 선행 테이블의 조건을 최대한 활용하여 후행 테이블의 처리량을 최소화하는 것이 중요하다.
반면 Sort Merge 조인을 사용하는 경우 각 테이블의 집합 크기를 최소화하는 것이 더 효율적인 실행 계획을 만드는 데 도움이 된다.
예를 들어, 다음과 같은 SQL을 고려해보자.
SELECT *
FROM emp e, dept d
WHERE e.deptno = 20
AND e.deptno = d.deptno;이때, 옵티마이저는 이행성 규칙을 적용하여 d.deptno = 20을 자동으로 유추할 수 있다.
즉, deptno에 인덱스가 존재하지 않는 경우라도 실행 계획을 최적화하여 먼저 인덱스를 활용할 수 있도록 조정한다.
이행성 규칙이 적용되지 않는 경우
그러나 이행성 규칙이 항상 적용되는 것은 아니다. 예를 들어, 비교 대상이 상수 수식이 아니라 컬럼일 경우 이 규칙이 적용되지 않는다.
WHERE column1 comparison_operators column3
AND column1 = column2;이 경우, 옵티마이저는 column2 comparison_operators column3을 자동으로 유추하지 않는다. 이와 같은 문제를 해결하기 위해, 경우에 따라 UNION ALL을 활용하여 변환하는 방식이 적용될 수도 있다.
예를 들어, WHERE 절에 OR 연산자가 포함된 경우 옵티마이저는 이를 UNION ALL로 변환하여 보다 최적화된 실행 계획을 수립할 수 있다.
SELECT * FROM emp WHERE job = 'CLERK'
UNION ALL
SELECT * FROM emp WHERE deptno = 10 AND job <> 'CLERK';서브쿼리 변환 적용
옵티마이저는 경우에 따라 서브쿼리를 조인 문장으로 변환할 수도 있다. 예를 들어, 다음과 같은 SQL이 있을 때
SELECT *
FROM emp
WHERE deptno IN (SELECT deptno FROM dept WHERE loc = 'NEW YORK');만약 DEPT 테이블의 DEPTNO가 기본키(Unique Index)라면 옵티마이저는 이를 다음과 같이 변환하여 실행할 수 있다.
SELECT emp.*
FROM emp, dept
WHERE emp.deptno = dept.deptno
AND dept.loc = 'NEW YORK';이러한 변환을 통해 서브쿼리를 조인 문장으로 변환하면 실행 계획을 보다 효율적으로 최적화할 수 있다.
실행 계획 선택
옵티마이저가 다음과 같은 조인 문으로 변환할 수 없다고 판단할 경우, 메인 쿼리와 서브쿼리에 대한 실행 계획을 선택하게 된다.
SELECT *
FROM emp
WHERE sal > (SELECT AVG(sal) FROM emp WHERE deptno = 20);이 경우, 실행 계획은 다음과 같이 결정된다.
- 서브쿼리의 실행 결과를 먼저 생성하여 메인 쿼리에 적용할 수 있는 경우 → 먼저 수행
- 그렇지 않은 경우, 메인 쿼리를 실행하면서 서브쿼리를 확인하는 방식 → 필터링 처리 또는 해시 조인 방식으로 실행
3.1.4.2. 뷰 병합(View Merging)
뷰 병합(View Merging)은 옵티마이저가 뷰를 포함하는 SQL 문을 최적화하기 위해 사용하는 변환 기법이다.
뷰는 크게 뷰 쿼리(View Query)와 액세스 쿼리(Access Query)로 나뉜다.
- 뷰 쿼리(View Query) - 뷰를 생성할 때 사용한 SELECT 문, 즉 데이터 딕셔너리에 저장된 SQL 문.
- 액세스 쿼리(Access Query) - 뷰를 실행할 때 실제 수행되는 SQL 문.
뷰 병합이 발생하면, 액세스 쿼리에서 뷰를 원래의 테이블로 변환하여 직접 액세스하는 형태로 실행 계획이 변경된다. 이를 통해 불필요한 뷰 연산을 제거하고 성능을 최적화할 수 있다.
뷰 병합 방식
뷰 병합 방식에는 다음과 같은 두 가지 방법이 있다.
뷰 병합(View Merging)
뷰의 SELECT 문을 액세스 쿼리로 병합하는 방식으로 예를 들어, 다음과 같은 뷰를 정의했다고 가정해볼 때
CREATE VIEW emp_10 AS
SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno
FROM emp WHERE deptno = 10;이 뷰를 사용하는 SQL이 아래와 같다면
SELECT e_no, e_name, salary, hire_date
FROM emp_10
WHERE salary > 1000000;옵티마이저는 이를 아래와 같이 변환하여 실행한다.
SELECT empno, ename, sal, hiredate
FROM emp
WHERE deptno = 10
AND sal > 1000000;
즉, 뷰를 제거하고 원본 테이블(emp)을 직접 조회하는 방식으로 변환된다.
조건절 전입(Pushing Predicate)
뷰 병합이 불가능한 경우, 뷰에 포함된 조건을 액세스 쿼리에 전입(삽입)하여 최적화하는 방식으로 예를 들어, UNION, UNION ALL, INTERSECT, MINUS 같은 집합 연산이 포함된 뷰는 병합이 어렵지만, WHERE 조건을 개별 쿼리에 삽입하여 실행 계획을 최적화할 수 있다.
뷰 병합이 어려운 경우
- 뷰 병합이 항상 가능한 것은 아니다. 다음과 같은 경우에는 뷰 병합이 어렵거나 불가능할 수 있다.
- CONNECT BY를 사용한 경우
- ROWNUM을 포함한 경우
- GROUP BY나 DISTINCT가 포함된 경우 (단, 특정 조건에서는 병합 가능)
- 집합 연산 (UNION, UNION ALL, INTERSECT, MINUS)이 포함된 경우
뷰 병합이 불가능한 경우 옵티마이저는 조건절 전입(Pushing Predicate)을 수행하여 최적화할 수 있다.
GROUP BY와 병합
GROUP BY가 포함된 뷰는 병합이 어려울 수 있다. 하지만 옵티마이저가 Complex View Merging, Optimizer Secure View Merging 옵션이 활성화된 경우 GROUP BY와 DISTINCT가 포함된 뷰도 병합이 가능하다.
예를 들어, 아래와 같은 뷰가 있다고 할 때
CREATE VIEW emp_group_by_deptno AS
SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal
FROM emp GROUP BY deptno;이를 사용하는 SQL이 아래와 같을 때
SELECT *
FROM emp_group_by_deptno
WHERE deptno = 10;옵티마이저는 이를 아래처럼 변환할 수 있다.
SELECT deptno, AVG(sal) avg_sal, MIN(sal) min_sal, MAX(sal) max_sal
FROM emp
WHERE deptno = 10
GROUP BY deptno;즉, GROUP BY를 유지하면서도 필요한 조건만 반영하여 최적화된 실행 계획을 생성한다.
옵티마이저 힌트와 병합 제어
뷰 병합이 적절하지 않을 경우, 다음과 같은 힌트를 사용하여 병합을 강제하거나 방지할 수 있다.
- MERGE 힌트 - 뷰 병합을 강제 수행.
- NO_MERGE 힌트 - 뷰 병합을 방지.
SELECT /*+ NO_MERGE(v) */ v.loc, v.avg_sal
FROM dept d, emp_group_by_deptno v
WHERE d.deptno = v.deptno
AND d.loc = 'London';위 SQL에서는 NO_MERGE 힌트를 사용하여 뷰 병합을 방지하고, 원래 뷰 형태로 실행하도록 강제하지만 반대로 MERGE 힌트를 사용하면 강제로 뷰 병합이 이루어진다.
뷰 병합의 활용
뷰 병합을 통해 불필요한 연산을 줄이고 최적화할 수 있지만 조건이 많거나 복잡한 경우 뷰 병합이 오히려 성능을 저하시킬 수 있다.
일반적으로 뷰 병합이 유리한 경우는 뷰가 단순한 SELECT문을 포함할 때, 뷰 내부의 조건이 엑세스 쿼리에 유용하게 활용 될 때이고,
반대로 뷰 병합이 유리하지 않는 경우는 GROUP BY, DISTINCT, UNION ALL 같은 연산이 많을 때나 뷰가 복잡하여 병합이 실행 계획을 오히려 비효율적으로 만들 때이다.
따라서 옵티마이저가 뷰 병합을 자동으로 수행하는지 여부를 확인하고, 필요 시 NO_MERGE 힌트를 활용하여 실행 계획을 조정하는 것이 중요하다.
3.1.4.3 사용자 정의 바인드 변수의 엿보기(Peeking)
바인드 변수를 사용한 쿼리의 최적화는 파싱 시점에서 이루어지며, 파싱 이후에 변수가 바인딩 된다. 즉, 최적화 시점에는 실제 변수값 없이 통계 정보만으로 최적화를 수행해야 한다.
데이터가 균일하지 않고 일부 값만 집중적으로 분포된 칼럼(Highly skewed column)의 경우, 균일 분포를 가정한 최적화 방식으로는 성능 문제가 발생할 가능성이 높다. 이런 경우 특정 바인드 변수를 사용하면 매우 비효율적인 실행 계획이 만들어질 수 있다.
이를 해결하기 위한 기능이 바인드 변수의 엿보기(PEEKING)이다. 이 기능은 쿼리가 최초로 실행될 때 사용자가 지정한 바인드 변수 값을 옵티마이저가 미리 확인하고, 이를 바탕으로 더 정확한 선택도(Selectivity)를 계산하여 최적화를 수행한다.
최초 실행 시 결정된 실행 계획은 이후 다른 값이 바인딩되더라도 그대로 유지된다. 이는 첫 실행의 바인딩 값이 실행 계획 결정에 매우 중요하다는 것을 의미한다.
PEEKING 기능은 칼럼의 실제 데이터 분포를 반영하여 평균적인 분포 가정을 극복하지만, 첫 번째 바인딩 값이 전체 데이터 분포를 대표하지 않을 경우 오히려 편향된 실행 계획이 발생할 가능성도 존재한다.
예를 들어, 성별 컬럼에서 'M'이 99%, 'F'가 1%라면 평균적으로는 인덱스를 사용하지 않는 것이 효율적이다. 하지만 PEEKING을 사용할 경우 첫 번째 값이 'F'일 때는 인덱스를 사용하고, 'M'일 때는 전체 테이블 스캔을 하는 방식으로 각기 다른 실행 계획을 선택할 수 있다.
이 방법은 효율성을 높이지만, 특정 값에 따라 극단적인 편향이 발생할 위험이 있고, 항상 최적의 실행 계획을 보장하지는 않는다.
PEEKING의 활성화 여부는 '_OPTIM_PEEK_USER_BINDS' 파라미터로 제어할 수 있으며, 기본값은 'TRUE'이다. 이 기능을 원치 않으면 'FALSE'로 설정할 수 있다.
최초 파싱 시점의 바인딩 값에 따라 실행 계획이 달라질 수 있으므로, 실제 쿼리의 실행 계획을 분석하고 문제점을 찾기 위해서는 SQL TRACE 등의 도구를 사용하는 것이 필요하다.
3.1.5 개발자의 역할
이미 작성된 SQL을 통해 실행계획을 개선하고 성능을 향상시키는 것은 중요하지만, 그보다 먼저 개발자들은 SQL을 잘 활용하는 방법을 익히는 것이 필요하다. SQL을 제대로 활용하면 기존의 처리 방식에 근본적인 변화를 가져오게 되며, 단순한 처리 과정이 아닌 사고의 혁신을 의미한다. 그러나 대부분의 개발자들은 아직도 절차적인 사고에 익숙해서 비절차적이고 집합적인 SQL 처리 방식에 대해 어려움을 겪고 있다.
SQL은 단순한 명령어의 나열이 아니라 그 자체로 하나의 애플리케이션이다. 따라서 SQL의 성능이 떨어진다고 하더라도 현실 업무의 복잡성을 모두 처리하지 못한다고 비판해서는 안 된다. SQL을 무작정 어렵고 복잡한 방식으로 사용하거나, 지나치게 단순한 처리로만 접근해서도 안 된다. 즉, 주어진 문제에 대해 최소한의 처리 비용과 자원을 사용하여 원하는 결과를 효율적으로 얻는 방식을 고민해야 한다.
대부분의 개발자들은 일상적으로 사용하는 절차적인 처리 방식에 따라 SQL을 사용하려는 경향이 있다. 그러나 관계형 데이터베이스를 효과적으로 이용하려면 절차적 접근 방식보다는 집합적이고 논리적인 접근 방식이 필요하다. 이 과정에서 개발자는 데이터베이스의 규정과 규칙을 준수하는 동시에, SQL의 비절차적 처리 방식을 능숙하게 다룰 수 있어야 한다.
SQL을 제대로 활용하면 마치 비행기를 타고 하늘에서 자유롭게 목적지를 향해 나아가는 것과 같지만, 절차적인 방식만을 고집한다면 마치 비행기를 육로에서 운전하는 것처럼 불필요한 낭비와 비효율을 초래할 수 있다. 결국 개발자는 데이터베이스와 SQL의 특성을 잘 이해하고 활용하는 법을 익혀, 최소한의 자원으로 최적의 성능을 얻을 수 있는 실행계획을 작성할 책임이 있다. 이를 위해서는 복잡한 문제도 간단한 SQL을 활용하여 효율적으로 처리할 수 있는 능력을 갖추는 것이 필수적이다.
하지만 단지 SQL만 잘 활용한다고 모든 문제가 해결되는 것은 아니다. 개발자는 자신이 작성한 SQL이 어느 정도의 성능을 가지는지 파악할 수 있어야 하며, 수행 과정에서 발생할 수 있는 성능 문제를 예측하고 미리 대비하는 능력을 갖추어야 한다. 또한 지나치게 복잡한 SQL을 사용하거나 옵티마이저에 무지한 상태에서 억지로 성능을 끌어내려는 태도는 바람직하지 않다. 따라서 조직 내에는 SQL과 옵티마이저를 잘 이해하고 적절히 활용할 수 있는 전문가가 반드시 필요하다.
Start : 25. 01. 22
End: 25.02.15
'프로그래밍(Web) > 공부일기' 카테고리의 다른 글
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 3장 SQL의 실행계획 - 2 (0) | 2025.04.04 |
|---|---|
| [바미] 옵티마이저(Optimizer)란 무엇인가? (0) | 2025.02.02 |
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 2장 인덱스 유형과 특징 (0) | 2024.12.30 |
| [바미] 새로 쓴, 대용량 데이터 베이스 솔루션 Vol.1 - 1장 데이터 저장구조와 특징 (0) | 2024.12.17 |
| [바미] 해시 충돌 처리 방법 (2) | 2024.10.12 |
