✍️ 1번 : 옵티마이저
옵티마이저에 대한 설명으로 가장 부적절한 것을 고르시오.
- 규칙기반 옵티마이저는 미리 정해 놓은 우선순위 규칙에 따라 액세스 경로를 평가한다. 👉 ⭕️
- 비용기반 옵티마이저는 미리 수집해 둔 통계정보를 이용해 액세스 경로를 평가한다. 👉 ⭕️
- 비용기반 옵티마이저도 내부적으로 규칙을 사용한다. 👉 ⭕️
- 규칙기반 옵티마이저도 통계정보를 일부 활용한다. 👉 ❌
🍋 기출 포인트
- 규칙기반 옵티마이저는 통계정보를 전혀 활용하지 않고 단순한 규칙에만 의존한다.
🍒 문제 해설
- 비용기반 옵티마이저도 규칙을 갖고 있다.
예를 들어, 어떤 인덱스를 사용하든 비용이 동일하다면 알파벳 순으로 인덱스를 선택한다.
/옵티마이저 모드가 first_rows인 경우, order by 컬럼에 인덱스가 있으면 인덱스를 사용한다.
✅ 비용기반 옵티마이저
비용기반(Cost-Based) 옵티마이저(줄여서 'CBO')는 사용자 쿼리를 위해 후보군이 될만한 실행계획들을 도출하고, 데이터 딕셔너리(Data Dictionary)에 미리 수집해 둔 통계정보를 이용해 각 실행계획의 예상비용을 산정하고, 그중 가장 낮은 비용의 실행계획 하나를 선택하는 옵티마이저다.
- CBO가 사용하는 통계정보로는 데이터양, 컬럼 값의 수, 컬럼 값 분포, 인덱스 높이, 클러스터링 팩터 등이 있다.
✍️ 2번 : 규칙기반 옵티마이저
규칙기반 옵티마이저가 대용량 데이터베이스 환경에 부적합한 예가 아닌 것
- 고객유형코드에 인덱스가 있으면 아래 SQL은 무조건 인덱스를 사용한다.
select * from 고객 where 고객유형코드 = 'CCO123'; 👉 ⭕️
- 고객명에 인덱스가 있으면 아래 SQL은 무조건 인덱스를 사용함으로써 소트 연산을 생략한다.
select * from 고객 order by 고객명; 👉 ⭕️
- 연령과 연봉 각각에 인덱스가 있을 때 아래 SQL은 BETWEEN 조건인 연봉 인덱스를 사용한다.
select *
from 사원
where 연령 >= 60
and 연봉 between 3000 and 6000; 👉 ⭕️
- 기본적으로 캐싱 효과를 고려하지 않는다. 즉, 모든 블록을 디스크에서 읽는다고 가정한다. 👉 ❌
🍋 기출 포인트
- 인덱스 컬럼에 대한 BETWEEN 조건(10위)이 부등호 조건(11위)보다 우선순위가 높기
때문에 연봉 인덱스를 사용한다.
- 캐싱 효과를 고려하지 않고 디스크 I/O Call 횟수로 실행계획을 선택하는 것은 "오라클 비용기반 옵티마이저의 한계"이다.
🍒 문제 해설
- 비용기반 옵티마이저의 예전 방식인 'I/O 비용 모델'에서 실행계획에 표시되는 Cost는 '예상 디스크 I/O Call 횟수'를 의미한다.
- 최근 방식인 'CPU 비용 모델'에서는 예상 I/O 발생량을 디스크에서 단일 블록을 읽을 때의 시간으로 환산한 Cost를 사용하므로 이 역시 캐싱 효과를 고려하지 않고 있다.
- 참고로, optimizer_index_caching 파라미터를 통해 일부 캐싱 효과를 고려하도록 설정할 수는 있다.
✅ 규칙기반 옵티마이저
과거에는 각 액세스 경로에 대한 우선순위 규칙에 따라 실행계획을 만드는 옵티마이저를 사
용했었다. 일명, ‘규칙기반(Rule-Based) 옵티마이저', 줄여서 RBO라고 한다.
RBO는 데이터 특성을 나타내는 통계정보를 전혀 활용하지 않고 단순한 규칙에만 의존하기 때문에 대량 데이터를 처리하는 데 부적합하다.
RBO가 사용하는 규칙
인덱스 구조, 연산자, 조건절 형태가 순위를 결정하는 주 요소이다.
✍️ 3번 : 비용기반 옵티마이저
비용기반 옵티마이저에 대한 설명으로 가장 부적절한 것
- 통계정보를 활용해 각 실행계획의 예상비용을 산정한다. 👉 ⭕️
- 비용기반 옵티마이저도 내부적으로 규칙을 사용한다. 👉 ⭕️
- 최적화에 유리한 형태로 쿼리를 변환한다. 👉 ⭕️
- 사용자가 지정한 힌트대로 실행했을 때의 비용과 옵티마이저가 선택한 실행계획의 비용을 비교해서 최종 하나를 선택한다. 👉 ❌
🍋 기출 포인트
- 힌트는 옵션이 아니라 명령어(directives)다. 몇몇 예외적인 경우가 아니면, 옵티마이저는 힌트를 그대로 따른다.
✅ 옵티마이저의 서브 엔진
옵티마이저가 최적화를 수행할 때 세부적으로 아래 3개의 서브 엔진을 사용한다.
- Query Transformer : 사용자로부터 전달받은 SQL을 그대로 최적화하지 않고 우선 최적화에 유리한 형태로 변환을 시도한다.
- Estimator : 쿼리 오퍼레이션 각 단계의 선택도(Selectivity), 카디널리티(Cardinality),비용(Cost)을 계산하고, 궁극적으로는 실행계획 전체에 대한 총 비용을 계산해 낸다.
- Plan Generator : 하나의 쿼리를 수행하는 데 있어, 후보군이 될만한 실행계획들을 생성해 낸다.
✍️ 4번 : 옵티마이저가 힌트를 무시하는 경우
옵티마이저가 힌트를 무시하는 경우
- 문법적으로 맞지 않게 힌트를 기술한 경우 👉 ⭕️
- ??옵티마이저에 의해 내부적으로 쿼리가 변환된 경우 👉 ⭕️
- 논리적으로 불가능한 액세스 경로로 유도한 경우 👉 ⭕️
- 사용자가 지정한 힌트대로 실행하면 비용이 더 높다고 판단되는 경우 👉 ❌
🍋 기출 포인트
✅ 옵티마이저가 힌트를 무시하는 경우
- 문법적으로 맞지 않게 힌트를 기술
- 잘못된 참조 사용
- 논리적으로 불가능한 액세스 경로
- 의미적으로 맞지 않게 힌트를 기술
- 옵티마이저에 의해 내부적으로 쿼리가 변환된 경우
- 버그
✍️ 5번 : '스스로 학습하는 옵티마이저(Self-Learning Optimizer)' 기능
'스스로 학습하는 옵티마이저(Self-Learning Optimizer)' 기능과 가장 거리가 먼 것
- Adaptive Cursor Sharing 👉 ⭕️
- Statistics Feedback(=Cardinality Feedback) 👉 ⭕️
- Adaptive Plans 👉 ⭕️
- Bind Variable Peeking 👉 ❌
🍋 기출 포인트
- 'Bind Variable Peeking'은 SQL이 '첫 번째 수행'될 때 함께 딸려 온 바인드 변수 값을 살
짝 훔쳐보고(peek), 그 값에 대한 컬럼 분포를 이용해 실행계획을 결정하는 기능이다.
즉, '스스로 학습'하는 옵티마이저 기능과는 거리가 멀다.
SOLServer에서는 'Parameter Sniffing'이라고 부른다.
이는 바인드 변수를 사용하면 히스토그램 정보를 활용하지 못하는 제약을 극복하기 위한 기능 중 하나이다.
🍒 문제 해설
- 'Adaptive Cursor Sharing'은 처음 실행 시 특정 실행계획으로 실행했다가 바인드 변수에
다른 값이 입력됐을 때 '예상보다 많은 I/O'가 발생하면 다른 실행계획을 추가로 생성하고,
이후로 바인드 변수 값 분포에 따라 다른 실행계획을 선택적으로 사용하는 기능이다.
- 11gR2에 도입된 'Cardinality Feedback' 기능은 12c에서 'Statistics Feedback'으로 이름이 변경되었다.
이는 최초 실행계획을 수립할 때 추정했던 카디널리티와 '실제 실행 과정에 읽은 로우 수 간에 차이'가 크다고 판단되면,
조정된 카디널리티 값을 어딘가에 저장해 두었다가 다음번 실행 시에 그것을 사용함으로써 다른 실행계획이 수립되도록 하는 기능이다.
- 12c에 도입된 'Adaptive Plans'는 런타임에 실행계획을 변경하는 기능들을 포함한다.
대표적인 기능 하나만 간단히 소개하면, 예를 들어 통계정보 상 A와 B가 둘 다 작은 집합이라고
판단해서 옵티마이저가 NL 조인을 선택했는데, 실제 실행 과정에 먼저 읽은 A 집합에서 예
상보다 많은 로우가 반환되면 해시 방식으로 조인 메소드를 변경한다.
✅ 간단 정리
- Bind Variable Peeking
- '첫 번째 수행'될 때 함께 딸려 온 바인드 변수 값을 살짝 훔쳐보고(peek), 그 값에 대한 컬럼 분포를 이용해 실행계획을 결정
- Adaptive Cursor Sharing
- 처음 실행 시 특정 실행계획으로 실행했다가 바인드 변수에 다른 값이 입력됐을 때 '예상보다 많은 I/O'가 발생하면 다른 실행계획을 추가로 생성하고, 이후로 바인드 변수 값 분포에 따라 다른 실행계획을 선택적으로 사용
- Cardinality Feedback 또는 Statistics Feedback
- 최초 실행계획을 수립할 때 추정했던 카디널리티와 '실제 실행 과정에 읽은 로우 수 간에 차이'가 크다고 판단되면,조정된 카디널리티 값을 어딘가에 저장해 두었다가 다음번 실행 시에 다른 실행계획이 수립되도록 그것을 사용
- Adaptive Plans
✍️ 6번 : 비용기반 옵티마이저가 실행계획을 수립할 때 사용하는 정보
비용기반 옵티마이저가 실행계획을 수립할 때 사용하는 정보와 가장 거리가 먼 것
- 인덱스 높이 👉 ⭕️
- 중복을 제거한 컬럼 값의 수 👉 ⭕️
- CPU 속도 및 디스크 I/O 속도 👉 ⭕️
- 버퍼 캐시 크기 👉 ❌
🍋 기출 포인트
✅ 옵티마이저가 사용하는 통계정보
- (1) 오브젝트 통계
- 테이블 통계
- 인덱스 통계
- 인덱스 높이
- 리프 블록 개수
- 클러스터링 팩터 등
- 컬럼 통계
- 중복을 제거한 컬럼 값의 수
- 최소값, 최대값
- Null 값 개수
- 히스토그램 등
- (2) 시스템 통계
- CPU 속도
- Single Block I/O 속도
- Multiblock I/O 속도
- 평균적인 Multiblock I/O 개수 등
- 오라클 11g에서 도입된 Adaptive Direct Path Read 기능이 Direct Path Read를 사용할지 여부를 결정할 때 고려하는 항목 중 하나가 버퍼캐시 크기다. 하지만, 이는 실행계획을 수립할 때가 아니라 런타임 시 고려사항이다.
✍️ 7번 : 최적화 목표(optimizer_mode)
최적화 목표(optimizer_mode)에 대한 설명으로 가장 부적절한 것
- 최초 응답속도 최적화(FIRST_ROWS, FIRST_ROWS_N)는 전체 결과집합 중 '앞쪽 일부만 읽다가
멈추는 것을 전제로' 응답 속도가 가장 빠른 실행계획을 선택한다. 👉 ⭕️
- ALL_ROWS와 비교할 때 FIRST_ROWS는 Table Full Scan 보다 인덱스를 더 많이 선택하고, 해시
조인보다 NL 조인을 더 많이 선택하는 경향을 보인다. 👉 ⭕️
- 전체 처리속도 최적화(ALL_ROWS)는 쿼리 결과집합 '전체를 읽는 것을 전제로' 시스템 리소스(1/0, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택한다. 👉 ⭕️
- 웹 브라우저를 통해 온라인으로 상품을 조회하고 주문을 처리하는 쇼핑몰이라면 최적화 목표(optimizer_mode)를 최초 응답속도 최적화(FIRST_ROWS, FIRST_ROWS_N)로 설정해야 한다. 👉 ❌
🍋 기출 포인트
- OLTP성 애플리케이션이더라도 3-Tier 구조는 클라이언트와 서버 간 연결을 지속하지
않는 환경이므로 오픈 커서를 계속 유지할 수 없어 일반적으로 페이징 기법을 사용한다.
- 이를 위해 rownum으로 결과집합을 10건 내지 20건으로 제한하는 쿼리를 사용한다. 대량
데이터 중 일부만 Fetch 하다가 멈추는 것이 아니라 집합 자체를 소량으로 정의해서 모두
Fetch 한다면, 전체 처리속도 최적화가 더 적절한 설정이다.
결론적으로, OLTP성 애플리케이션이더라도 아케텍처에 따라 최적화 목표는 다를 수 있다.
- 전체 처리속도 최적화(ALL_ROWS)는 쿼리 결과집합 '전체를 읽는 것을 전제로' 시스템 리소스(1/0, CPU, 메모리 등)를 가장 적게 사용하는 실행계획을 선택한다.
✍️ 8번 : 옵티마이저 행동에 영향을 미치는 요소
옵티마이저 행동에 영향을 미치는 요소와 가장 거리가 먼 것
- 인덱스, IOT, 클러스터링, 파티셔닝 등 오브젝트 구성 👉 ⭕️
- PK, FK, Check, Not Null 등 DBMS 제약 설정 👉 ⭕️
- 옵티마이저 모드 👉 ❌
- 네트워크 속도 👉 ❌
🍋 기출 포인트
- 네트워크 속도는 옵티마이저가 실행계획을 생성하는 데 영향을 주지 않는다.
✅ 옵티마이저 행동에 영향을 미치는 요소
- 옵티마이징 팩터 : 인덱스, IOT, 클러스터링, 파티셔닝 등 오브젝트 구성
- DBMS 제약 설정 : PK, FK, Check, Not Null 등
- 통계정보
- 옵티마이저 힌트
- 옵티마이저 관련 파라미터
✍️ 9번 : 옵티마이저의 한계
옵티마이저가 최적의 실행계획을 생성하는 데 한계를 보이는 이유와 가장 거리가 먼 것
- 비효율적인 인덱스 구성 👉 ⭕️
- 부정확한 통계 및 결합 선택도 산정의 어려움 👉 ⭕️
- 최적화 시간에 허용된 시간 제약 👉 ⭕️
- 라이브러리 캐시 공간 제약 👉 ❌
🍋 기출 포인트
- 라이브러리 캐시 공간의 크기는 옵티마이저가 생성하는 실행계획에는 영향을 주지 않는다.
다만, 공간이 부족하면 SQL 실행계획이 캐시에서 자주 밀려나므로 파싱과 최적화를 자주 수행함으로 인한 부하가 늘어날 뿐이다.
✅ 옵티마이저의 한계
옵티마이저가 항상 최적의 실행계획을 생성하지 못하는 데는 아래와 같은 이유가 있다.
● 부족한 옵티마이징 팩터 : 인덱스, IOT, 클러스터링, 파티셔닝 등 오브젝트 구성
● 부정확한 통계 : 정보 수집 및 보관 비용 측면의 한계(샘플링 비율, 수집 주기 등)
● 결합 선택도 산정의 어려움
● 바인드 변수 사용 시, 히스토그램 사용에 제약 : 균등분포 가정
● 비현실적인 가정과 규칙에 의존
● 최적화 시간에 허용된 시간 제약
✍️ 10번 : 선택도(Selectivity)와 카디널리티(Cardinality)
선택도(Selectivity)와 카디널리티(Cardinality)
아래 빈칸 ㉠과 ㉡에 들어갈 수치를 순서대로 적으시오.
[데이터 ]
● 상품 테이블 총 건수 = 100,000
■ 상품분류 = { 가전, 의류, 식음료, 생활용품 }
[SQL]
SELECT *
FROM 상품
WHERE 상품분류 = :prd_cls ;
아래
위 SQL 조건절에 의한 선택도(Selectivity)는 ㉠ 이고, 카디널리티(Cardinality)는 ㉡ 이다.
- 선택도 : 1/4 = 0.25 = 25% 👉 ⭕️
- 카디널리티 = 25,000 👉 ⭕️
🍋 기출 포인트
- 선택도(Selectivity)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 비율을 말한다.
선택도 = 1 / NDV
- 카디널리티(Cardinality)란, 전체 레코드 중에서 조건절에 의해 선택되는 레코드 개수이다.
카디널리티 = 총 로우 수 × 선택도 = 층 로우 수 / NDV
✍️ 11번 : 선택도(Selectivity)와 카디널리티(Cardinality)
선택도(Selectivity)와 카디널리티(Cardinality)
아래 빈칸 ㉠과 ㉡에 들어갈 수치를 순서대로 적으시오.
[ 데이터 ]
■ 사원 테이블 총 건수 = 10,000
● 직급으로는 부장, 과장, 대리, 사원이 있으며, 히스토그램 상 각각 25% 점유율을 가짐
● 히스토그램 상 연봉이 5000 이상인 사원은 10%
[SQL]
select 사원번호,사원명, 부서번호, 연봉
from 사원
where 직급 = '부장'
and 연봉 >= 5000;
위 SQL 조건절에 의한 선택도(Selectivity)는 ㉠ 이고,
카디널리티(Cardinality)는 ㉡이다.
- 선택도 : 1/4 x 1/10 = 0.025 👉 ⭕️
- 카디널리티 : 10,000 x 1/4 x 1/10 = 250 👉 ⭕️
✍️ 12번 : 컬럼 통계 항목
컬럼 통계 항목이 아닌 것을 고르시오.
- 중복을 제거한 컬럼 값의 수 👉 ⭕️
- 최소값과 최대값 👉 ⭕️
- NULL 값을 가진 레코드 수 👉 ⭕️
- 최소 컬럼 길이와 최대 컬럼 길이 👉 ❌
🍋 기출 포인트
- 컬럼 통계 수집 시 평균 컬럼 길이는 측정하지만, 최소 컬럼 길이와 최대 컬럼 길이는 측정하지 않는다.
✅ 컬럼 통계 항목
- 중복을 제거한 컬럼 값의 수(num_distinct)
- 최소값(low_value)
- 최대값(high_value)
- 밀도(density)
- 평균 컬럼 길이(avg_col_len)
- NULL 값을 가진 레코드 수(num_nulls)
✍️ 13번 : 시스템 통계에서 수집하는 항목
시스템 통계에서 수집하는 항목이 아닌 것을 고르시오.
- 평균적인 Single Block I/O 속도 👉 ⭕️
- 평균적인 Multiblock 1/0 속도 👉 ⭕️
- 평균적인 Multiblock I/O 개수 👉 ⭕️
- 평균적인 Single Block I/O 개수 👉 ❌
🍋 기출 포인트
- 평균적인 Single Block I/O 개수는 당연히 1이므로 수집하지 않는다.
✅ 시스템 통계에서 수집하는 항목
시스템 통계는 애플리케이션 및 하드웨어 성능 특성을 측정한 것이며, 아래 항목들을 포함한다.
- CPU 속도
- 평균적인 Single Block I/O 속도
- 평균적인 Multiblock I/O 속도
- 평균적인 Multiblock I/O 개수
- I/O 서브시스템의 최대 처리량(Throughput)
- 병렬 Slave의 평균적인 처리량(Throughput)
✍️ 14번 : I/O 비용 모델 vs. CPU 비용 모델
선택도, 카디널리티, 비용에 대한 설명으로 가장 부적절한 것을 고르시오.
- 선택도(Selectivity) : 조건절에 의해 선택될 것으로 예상되는 레코드 비중(%) 👉 ⭕️
- 카디널리티(Cardinality) : 조건절에 의해 선택될 것으로 예상되는 레코드 수 👉 ⭕️
- CPU 비용 모델의 비용(Cost) : 예상 소요시간을 Single Block I/O 횟수로 환산한 값 👉 ⭕️
- I/O 비용 모델의 비용(Cost) : 쿼리 수행 과정에 읽을 것으로 예상되는 블록 개수 👉 ❌
🍋 기출 포인트
- I/O 비용 모델의 비용은 예상되는 블록 개수가 아니라 디스크 I/O Call 횟수다.
- Single Block I/O 일 때는 I/O Call 횟수가 읽은 블록 수와 일치한다.
- Multilblock I/O 일때는 읽은 블록 수를 Multiblock I/O 단위로 나눈 만큼의 I/O Call이 발생한다.
🍒 문제 해설
- I/O 비용 모델의 비용(Cost)은 예상되는 디스크 I/O Call 횟수를 의미한다.
예를 들어, 실행계획상 Cost가 100으로 표시됐다면, “쿼리 수행 과정에 I/O Call이 100번 발생할 것으로 옵티마이저가 예상한다"는 뜻이다.
- I/O 비용 모델의 비용(Cost)은 IO요청 횟수(논리적/물리적으로 읽은 블록 개수가 아님)만을 수행 비용으로 간주하여 실행계획을 평가한다.
- CPU 비용 모델에서 비용(Cost)은 예상 I/O 시간과 예상 CPU 사용시간을 구한 후 Single Block I/O 시간으로 나눈 값이다.
- CPU 비용 모델에서 비용(Cost)은 IO요청 횟수에 시간을 더해 비용을 산정하여 실행계획을 평가한다.
- 즉, CPU 비용 모델에서 비용은 Single Block I/O에 소요되는 시간과의 상대적인 시간을 표현한 것이다.예를 들어, 실행계획 상 Cost가 100으로 표시됐다면, “우리 시스템에서 100번 Single Block I/O 하는 정도의 시간이 소요될 것으로 옵티마이저가 예상한다"는 뜻이다.
✍️ 15번 : 오라클 12c 이상 버전에서 사용하는 히스토그램 유형
오라클 12c 이상 버전에서 사용하는 히스토그램 유형이 아닌 것을 고르시오.
- 도수분포(Frequency) 👉 ⭕️
- 상위도수분포(Top-Frequency) 👉 ⭕️
- 높이균형(Height-Balanced) 👉 ⭕️
- 넓이균형 (Width-Balanced) 👉 ❌
🍋 기출 포인트
- 넓이균형(Width-Balanced) 히스토그램은 존재하지 않는다.
✅ 오라클 12c 이상 버전에서 사용하는 히스토그램 유형
■ 도수분포(Frequency) : '값별로 빈도수' 저장
■ 상위도수분포(Top-Frequency) : 많은 레코드를 가진 상위 n개 값의 '빈도수' 저장
■ 높이균형(Height-Balanced) : 각 버킷의 높이가 동일하도록 데이터 분포 관리
■ 하이브리드(Hybrid) : 도수분포와 높이균형 히스토그램의 특성을 결합
✍️ 16번 : 인덱스를 이용한 테이블 액세스 비용(Cost)을 계산할 때 사용하는 통계항목
인덱스를 이용한 테이블 액세스 비용(Cost)을 계산할 때 사용하는 통계항목이 아닌 것을 고르시오.
- 브랜치 레벨 👉 ⭕️
- 클러스터링 팩터 👉 ⭕️
- 리프 블록 수 👉 ⭕️
- 브랜치 블록 수 👉 ❌
🍋 기출 포인트
- 인덱스를 이용한 테이블 액세스 비용은 아래와 같이 구한다.
비용 = 브랜치 레벨+(클러스터링 팩터 x 유효 테이블 선택도)+(리프 블록 수 x 유효 인덱스 선택도)
- 브랜치 블록 수는 비용 공식에 포함되지 않을 뿐만 아니라 통계정보로 미리 수집해 두지도 않는다.
🍒 문제 해설
- '유효 인덱스 선택도'는 인덱스 총 레코드 중에서 조건절을 만족할 것으로 예상되는 레코드 비율을 의미한다.
- '유효 테이블 선택도'는 전체 인덱스 레코드 중에서 인덱스 스캔을 완료하고서 테이블을 방문할 것으로 예상되는 레코드 비율을 의미한다.