DB & 인스턴스

데이터베이스: 물리적인 OS 파일의 집합
인스턴스: SGA 공유 메모리 영역과, 이를 액세스하는 집합을 합쳐서 인스턴스

기본적으로는 하나의 인스턴스가 하나의 DB만 액세스. RAC 환경에서는 여러 인스턴스가 하나의 DB 액세스 가능. 하나의 인스턴스가 여러 DB를 액세스할 수는 없음.

result cache 랑 pga 차이

구분	Result Cache	PGA (Process Global Area)
소속 영역	SGA (Shared Global Area) 내의 공유 메모리	각 서버 프로세스의 개별 독립 메모리
공유 여부	모든 세션이 공유 가능	해당 세션/프로세스만 독점 사용
저장 대상	SQL 쿼리 결과 또는 함수 리턴값	정렬 공간(Sort Area), 해시 영역(Hash Area) 등
핵심 목적	동일 요청에 대한 연산 결과 재사용	쿼리 수행을 위한 개별 작업 공간 확보
라이프사이클	데이터 변경 전까지 혹은 캐시 아웃 전까지 유지	세션 종료 또는 프로세스 종료 시 해제

Result Cache와 PGA(Process Global Area)는 모두 데이터베이스 메모리 구조에 해당하지만, 공유 여부와 저장 대상 측면에서 근본적인 차이가 있다.

2. Result Cache의 특징

오라클의 Result Cache는 반복되는 무거운 연산의 결과를 메모리에 저장하여 CPU와 I/O 소모를 극도로 줄이는 메커니즘이다.

결과 중심: 데이터 블록 자체를 캐싱하는 Buffer Cache와 달리, 가공이 완료된 최종 결과 집합을 저장한다.
일관성 관리: 참조하는 테이블에 DML(변경)이 발생하면 해당 캐시는 즉시 무효화(Invalidate)된다.
사용 상황: 데이터 변경이 적고 조회(Read)가 빈번한 대용량 집계 쿼리 등에 유리하다.

3. PGA의 특징

PGA는 각 사용자 세션이 SQL을 수행하기 위해 할당받는 전용 작업 공간이다.

수행 중심: 쿼리의 실행 계획에 따라 데이터를 정렬하거나 해시 테이블을 만드는 등 실제 계산 작업이 일어나는 장소다.
자원 경합 없음: 개별 세션의 독립 공간이므로 SGA와 달리 래치(Latch) 경합으로부터 자유롭다.
메모리 부족 시: 설정된 PGA 범위를 초과하는 대량의 조인이나 정렬이 발생하면 **Temp 테이블스페이스(디스크)**를 사용하게 되어 성능이 급격히 저하된다.

4. 전문가적 요약

Result Cache는 “남이 계산해 놓은 정답을 공유해서 쓰는 도서관”과 같고, PGA는 “나 혼자 문제를 풀기 위해 사용하는 개인 연습장”과 같다.

성능 튜닝 관점:
- Result Cache: 동일한 쿼리가 반복될 때 CPU 사용량을 줄이는 용도로 검토한다.
- PGA: 대량의 해시 조인이나 정렬 작업이 디스크로 넘치지 않도록 적절한 크기를 할당하는 것이 중요하다.

오라클 실행계획에서, id 숫자 옆에 별표 나오는건 뭐지?

오라클 실행계획 리포트(DBMS_XPLAN 등)에서 ID 옆에 붙는 별표(*)는 해당 오퍼레이션 단계에 ‘Predicate Information(조건절 정보)‘이 존재함을 의미한다.

1. 별표(*)의 구체적 의미

실행 계획 표만으로는 해당 단계에서 어떤 조건이 쓰였는지 알 수 없으므로, 별표를 통해 하단에 상세 정보가 있음을 안내하는 것이다.

위치: 실행 계획 테이블 하단의 Predicate Information 섹션을 확인해야 한다.
내용: 해당 ID에서 수행된 Access Predicate와 Filter Predicate의 상세 SQL 조건식이 표시된다.

2. Predicate Information의 두 가지 유형

튜닝 관점에서 별표가 붙은 ID의 하단 정보를 분석하는 것은 매우 중요하다.

A. Access Predicate (액세스 조건)

의미: 인덱스의 수직적 탐색을 위해 사용된 조건이다.
특징: B-Tree 인덱스에서 리프 블록의 시작점과 끝점을 결정한다. 이 조건이 정교할수록 읽어야 할 블록 수가 줄어든다.

B. Filter Predicate (필터 조건)

의미: 데이터를 읽은 후, 결과 집합에 포함할지 여부를 결정하기 위해 사용된 조건이다.
특징: 인덱스를 타지 못하고 테이블에서 건건이 체크하거나, 인덱스 내에서 범위를 줄이지 못하고 버려지는 데이터를 걸러낼 때 발생한다. 필터 비중이 높다면 인덱스 구성이나 쿼리 구조의 비효율을 의심해야 한다.

3. 실무적 활용

실행 계획에서 특정 단계의 cr(Logical I/O)이 높은데 ID 옆에 별표가 있고 하단 정보가 filter로 되어 있다면, 이는 **“인덱스에서 범위를 줄이지 못하고 일단 많이 읽은 뒤 버리고 있다”**는 결정적인 튜닝 포인트가 된다.

하드파싱

sql 쿼리의 대소문자만 달라도 하드파싱이 발생하나? select * from tbl1, select /hello/* from tbl1 이 둘은 각각 하드파싱되나?

데이터베이스 엔진(특히 오라클)은 SQL 문장 전체를 하나의 문자열로 취급하여 해시 함수에 넣고, 그 결과값인 **SQL ID(또는 Hash Value)**를 생성하여 라이브러리 캐시(Library Cache)를 탐색하기 때문이다.
주석의 영향: 주석(/*hello*/)은 SQL 실행 엔진 입장에서는 무시되는 내용이지만, SQL ID를 생성하는 문자열 가공 단계에서는 엄연한 데이터로 취급된다.
텍스트 일치성: 해시 알고리즘은 단 1바이트의 차이(공백, 대소문자, 주석 포함)만 있어도 완전히 다른 해시 값을 반환한다.

데이터베이스 CALL

데이터베이스 Call은 옵티마이저가 비용을 계산하는 내부 공식의 인자(Variable)가 아니라, 옵티마이저에게 일을 시키거나(Parse Call) 옵티마이저가 만든 계획을 실행하는(Execute/Fetch Call) 과정 그 자체를 의미한다.

전문가적인 관점에서 네가 지적한 발생 시점과 옵티마이저의 관계를 다시 정립해 주겠다.

1. Database Call의 발생 시점 (네 지적의 핵심)

Database Call은 애플리케이션과 데이터베이스 서버 간의 상호작용 단위이며, 아래와 같은 세 단계에서 발생한다.

Parse Call: 애플리케이션이 SQL 실행 계획 수립을 요청하는 단계다. 이 과정에서 옵티마이저가 가동되며, 라이브러리 캐시에 정보가 없으면 **하드 파싱(Hard Parse)**이 발생해 막대한 CPU 소모와 래치 경합이 일어난다.
Execute Call: 옵티마이저가 수립한 실행 계획에 따라 실제 SQL을 수행하도록 명령하는 단계다.
Fetch Call: SELECT 문 실행 시, 결과 집합을 클라이언트로 전송하도록 요청하는 단계다.

2. 옵티마이저 비용(Cost)과 Call의 관계

여기서 혼동하지 말아야 할 점은 **‘옵티마이저가 계산하는 비용(Cost)‘**과 **‘Call로 인한 시스템 부하’**의 차이다.

옵티마이저의 비용(Cost): 옵티마이저는 **단일 실행(One-time Execution)**에 필요한 I/O, CPU, 메모리 사용량을 수치화하여 가장 낮은 경로를 선택한다. 옵티마이저 자신은 이 SQL이 애플리케이션에서 1번 호출될지, 100만 번 호출될지는 전혀 알지 못하며 고려하지도 않는다.
시스템 전체 성능: 하지만 시스템 전체 관점에서는 아무리 옵티마이저가 최적의 실행 계획(낮은 Cost)을 세웠더라도, 애플리케이션이 Call을 무수히 많이 발생시킨다면 성능은 무너진다.

3. 왜 Call 발생량이 위험한가? (성능적 관점)

옵티마이저가 수립한 실행 계획을 아무리 잘 수행하더라도, Call 자체가 많으면 아래와 같은 물리적 한계에 부딪힌다.

Parse Call 부하: 대소문자나 공백 하나만 달라도 매번 하드 파싱을 유발하여 옵티마이저를 계속 새로 가동하게 만든다. 이는 공유 풀(Shared Pool) 메모리 낭비와 래치 대기를 초래한다.
네트워크 라운드 트립(Round-trip): 각 Call은 네트워크 패킷 전송을 동반한다. Execute Call과 Fetch Call이 많아지면 네트워크 구간에서의 응답 지연이 쿼리 자체의 수행 시간보다 길어질 수 있다.

요약

Database Call은 옵티마이저의 비용 산정 방식에 들어가는 데이터가 아니라, 옵티마이저의 작업을 유발하고 그 결과를 활용하는 외적 메커니즘이다. 옵티마이저는 ‘단일 실행의 효율’만 따지지만, 튜너는 그 실행을 담는 그릇인 ‘Call의 총량’까지 관리해야 한다.

SQL 언어의 특징

SQL 은 구조적 질의 언어.

SET-BASED
DECLARATIVE
NON-PROCEDURAL

WITH절 INDEX 힌트

Index Range Scan 불가 조건

1. 컬럼 가공(함수/연산) 조건

인덱스 컬럼을 가공한 값으로 비교하면 Range Scan 시작점을 못 잡는다.
- ex) WHERE TO_CHAR(주문일시,'YYYYMMDD') = :dt → 보통 Range Scan 불리
- WHERE 주문일시 >= :dt AND 주문일시 < :dt + 1 처럼 원 컬럼 비교로 바꾸는 게 정석

2. LIKE ‘%…’ (선행 와일드카드)

LIKE ‘%대한%’ 처럼 앞에 %가 붙으면 시작점을 특정할 수 없어 Range Scan 불가.
반대로 LIKE ‘대한%‘는 가능(선행 고정).

3. OR 조건

OR 조건은 기본적으로 Range Scan의 액세스 조건으로 쓰기 어렵다.
다만 옵티마이저가 CONCATENATION(= UNION ALL 분기) 로 변환하고, 각 분기에서 사용할 적절한 인덱스가 있으면 분기별 Range Scan이 가능해질 수 있다.

4. 단일 컬럼 인덱스에서 IS NULL

오라클은 구성 컬럼이 모두 NULL인 레코드를 인덱스에 저장하지 않는다.
따라서 단일 컬럼 인덱스에서 컬럼 IS NULL 조건은 Range Scan이 불가능하다.
- 반면, 결합 인덱스에서는 “구성 컬럼 중 하나라도 NOT NULL이면 인덱스에 엔트리가 존재”할 수 있어, IS NULL 조건으로도 Range Scan이 가능해지는 경우가 있다.

5. 묵시적 형변환(데이터 타입 불일치)

컬럼 타입과 바인드/리터럴 타입이 맞지 않으면, DB가 컬럼을 변환하거나 값을 변환하게 되고(특히 컬럼 변환이 발생하면) 인덱스 사용이 어려워진다.
LIKE는 문자열 기반이라 숫자 컬럼과 섞이면 비정상 변환이 발생하기 쉬움
ex) 숫자 컬럼에 문자 비교, 또는 그 반대

(정답 31~32) MIN/MAX (MINMAX) 인덱스 스캔과 NVL 처리

MAX(주문번호)+1 같은 값을 구할 때, 인덱스가 있으면 옵티마이저가 INDEX RANGE SCAN (MIN/MAX) 형태로 매우 빠르게 처리할 수 있다.

다만 해당 일자에 주문 데이터가 하나도 없으면 MAX()는 NULL을 반환하므로 NVL 처리가 필요하다.

모범답안:

SELECT NVL(MAX(주문번호) + 1, 1) FROM 주문 WHERE 주문일자 = :주문일자;

온라인 프로그램 튜닝 vs 배치 프로그램 튜닝

온라인 (OLTP) 프로그램
- 온라인 프로그램은 보통 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 가장 중요하다.
- 조인은 대부분 NL 조인을 사용
- 인덱스를 이용해 SORT 연산을 생략하고
- 가능하면 부분범위 처리 (앞쪽 일부만 빠르게 응답) 방식으로 구현

이렇게 설계하면 온라인 환경에서 “대량 조회처럼 보이는 화면”도 실제로는 부분범위 처리로 매우 빠른 응답 속도를 낼 수 있다.

배치(Batch) 프로그램
- 배치는 대량 데이터를 읽고 갱신하므로 목표가 다르다.
- 처리 대상 중 일부만 빨리 처리하는 것이 아니라,
- 전체를 빠르게 처리하는 것이 목표
- 대량 처리를 빠르게 하려면 일반적으로 Full Scan + 해시 조인이 유리하다.

즉 배치 환경에서는 부분범위 처리의 효용이 거의 없다.

인덱스 튜닝의 두 가지 핵심 요소

인덱스 튜닝의 핵심은 크게 두 가지다.

1. 인덱스 스캔 효율화 튜닝

인덱스를 많이 스캔했는데 결과 건수가 적으면 비효율적이다.
“스캔한 양 대비 결과 건수” 비율을 높여야 한다.

2. 테이블 랜덤 액세스 최소화 튜닝

인덱스에서 얻은 건수만큼 테이블을 액세스한다.
- 테이블 레코드 액세스는 랜덤 I/O 방식으로 이루어진다.
테이블을 많이 방문했는데 결과가 적으면 비효율적이다.
목표는 테이블 액세스 횟수 자체를 줄이는 것이다.

(스캔 효율) 선행 컬럼과 조건절의 관계

인덱스는 선두 컬럼부터 정렬되어 있으므로,
- (= 테이블과는 달리, 같은 값을 갖는 레코드들이 서로 군집해 있으므로)
- 선행 컬럼이 조건절에 없거나,
- 선행 컬럼이 = 조건이 아닌 범위조건(부등호, BETWEEN, LIKE 등) 이 되는 순간,
그 아래(후행) 조건은 인덱스에서 “정확히 잘라서” 접근하기 어려워지고
결국 필터링해야 하므로 스캔 비효율이 커진다.

정리하면:

선행 컬럼이 모두 = 조건일 때 가장 효율적
범위조건이 선행 컬럼에서 나오면 그 뒤 조건들은 대개 필터 조건이 되어 스캔량이 늘어난다
단, =가 아니더라도 그것이 인덱스 뒤쪽 컬럼이면 스캔 효율에 큰 영향을 주지 않는다(이미 앞에서 범위가 결정된 뒤의 필터일 뿐).

(IN 조건) IN-List Iterator의 비용

상품ID IN (…) 같은 조건을 IN-List Iterator 방식으로 처리하면,
IN 리스트 개수만큼 인덱스 수직 탐색을 반복한다.
- 인덱스 높이가 3이면, 한 번 탐색에 최소 3블록 정도를 읽는다고 단순화할 수 있다.
따라서 “IN 리스트가 많으면” 수직 탐색 비용이 누적된다.
반대로, 조건을 잘 구성하면
- 하루치 데이터 범위가 작고,
- 고객당 구매 상품 수가 제한적이라면
필요한 리프 범위만 읽고 빨리 끝낼 수 있다.

LIKE로 옵션 처리하면 망하는 이유

옵션 (NVL/DECODE/CASE) 조건을 LIKE 같은 형태로 구현하면,
변수를 입력하지 않았을 때 “모든 값을 스캔”하는 실행계획이 잡힐 수 있어 위험하다.
- 특히 선택도가 좋은 컬럼(예: 고객ID)에 매우 치명적이다.
정석은 NVL/DECODE/CASE 로 “옵션”을 만들기보다, 옵티마이저가 UNION ALL 분기 형태로 변환되게(혹은 직접 분기) 작성하는 것이다.

SELECT ...
FROM 거래
WHERE :CUST_ID IS NULL
  AND 거래일자 BETWEEN :DT1 AND :DT2
 
UNION ALL
 
SELECT ...
FROM 거래
WHERE :CUST_ID IS NOT NULL
  AND 고객ID = :CUST_ID
  AND 거래일자 BETWEEN :DT1 AND :DT2;

이 방식은

고객ID가 입력되면 고객ID 인덱스를 타고,
입력되지 않으면 날짜 범위만 처리하게 되어 “불상사(전체 스캔)”를 막는다.
- (참고: CASE를 쓰면 UNION ALL 변환이 안 되는 경우가 있어 주의)

인덱스 수직적 탐색

엑셀 시트에 입력한 모든 사원을 성별, 이름 순으로 정렬한 후 남자 홍길동을 찾으려면 필터 기능을 이용해 ‘남자’를 모두 찾고 그 중 ‘홍길동’을 찾아야 한다.

이름과 성별 순으로 정렬하면, 소수의 ‘홍길동’을 찾고 그 중 ‘남자’를 찾으면 된다.

엑셀 시트처럼 평면적인 정렬 구조에서는 이름과 성별 순으로 정렬하면 데이터를 빨리 찾는데 도움이 된다.

하지만 DBMS 가 사용하는 B* Tree 인덱스는 엑셀처럼 평면구조가 아니다. 루트에서 브랜치를 거쳐 리프 블록까지 탐색하면서 남자 홍길동인 첫 번째 사원을 바로 찾아가는 다단계 구조다. 따라서 어느 컬럼을 앞에 두든 일량에는 차이가 없다.

(결합 인덱스 구성 기준) 반드시 고려할 2가지

결합(복합) 인덱스를 설계할 때는 다음 두 기준을 반드시 고려한다.

조건절에 항상(또는 매우 자주) 사용되는 컬럼을 선두에 둔다
- Index Range Scan을 하려면 선두 컬럼이 액세스 조건으로 잡혀야 한다.
그중에서도 = 조건으로 자주 조회되는 컬럼을 앞쪽에 둔다
- = 조건은 수직 탐색으로 범위를 아주 정확히 좁히므로 스캔 효율이 가장 좋다.
- 범위조건(부등호/BETWEEN/LIKE)이 선행에 오면 그 뒤 조건은 필터가 되기 쉬워 비효율이 커진다.

또한 두 조건절이 모두 =라면 선두를 어느 컬럼으로 두어도 성능 차이는 거의 없다

(둘 다 수직 탐색으로 정확히 찾아가기 때문).
(예: 성별=‘남’ AND 이름=‘홍길동’)

(정답 60) 인덱스 구성에 따라 “테이블 액세스 후 결과 건수”가 달라지는 이유

어떤 SQL에서 최종 결과 건수가 100건이라고 해도, 인덱스로 먼저 걸러낸 건수와 테이블 액세스(ROWID) 후에 걸러낸 건수는 인덱스 구성에 따라 달라질 수 있다.

인덱스가 조건절 컬럼을 충분히 포함하면 → 조건을 인덱스에서 대부분 필터링(또는 액세스 조건화)할 수 있어 테이블 방문 건수 자체가 줄어든다.

반대로 인덱스가 조건절 컬럼을 포함하지 않으면 → 인덱스에서는 넓게 읽고, 테이블을 방문한 뒤에야 필터링하므로 테이블 랜덤 액세스가 늘어난다.

즉, “최종 결과 100건”은 같아도 인덱스 단계에서 얻은 건수와 테이블 방문 후 남는 건수는 같을 필요가 없다.

정답: 60

소트 연산을 생략하려면: 인덱스 구성 공식

I/O 를 최소화하면서도 소트 연산을 생략하려면, 아래 공식에 따라 인덱스를 구성하면 된다.

ORDER BY가 있는 SQL에서 SORT ORDER BY 연산을 생략하려면 인덱스를 아래 원칙으로 구성한다.

(= 인덱스 정렬 순서로 그대로 출력하려면)

인덱스 구성 원칙(공식)

‘=’(등치) 조건으로 사용하는 조건절 컬럼을 선두에 둔다.
그 다음에 ORDER BY 절에 기술한 컬럼을 인덱스에 추가한다.
‘=’이 아닌 조건절 컬럼(범위조건 등) 은 데이터 분포/선택도 등을 고려해 추가 여부를 결정한다.

추가 설명(실무 감각):

정렬 제거 목적만 보면 1)~2)까지만 맞아도 되는 경우가 많다.
다만 테이블 랜덤 액세스를 줄이거나(필터를 인덱스에서 더 하기), 성능이 크게 좋아질 여지가 있으면 3)을 고려한다.
반대로 해당 조건이 매우 넓게 걸리거나(거의 항상 참) 인덱스에 넣어도 실익이 작으면 굳이 추가하지 않아도 성능 차이가 없을 수 있다(대신 DML 부담은 줄어듦).

SQL에 ORDER BY가 있어도 실행계획에 SORT ORDER BY가 보이지 않는 경우가 있다. 이는 옵티마이저가 인덱스 스캔 순서가 ORDER BY 정렬 순서와 일치한다고 판단하여, 정렬을 생략했기 때문이다.

‘부분범위 처리가 가능하다’ 는 것은 소트 연산을 생략할 수 있음을 의미한다. 소트 연산을 생략하면서도 I/O 를 최소화하려면 위 3단계에 따라 설계하면 된다.

IN 조건은 '=' 가 아니다. IN 조건이 '=' 가 되려면 IN-List Iterator 방식으로 풀려야 하는데, 그럴 경우 정렬 순서가 깨진다.

NULL 허용 컬럼에서 LIKE 옵션 처리 시 결과 누락/오작동 위험도 커진다.

(앞서 너가 올린 “옵션조건은 UNION ALL 분기” 정석과 같은 맥락)

조인

조인 순서를 조정하는 힌트는 ORDERED, LEADING 두 가지

ORDERED: FROM 절 나열 순서대로 조인
LEADING: 원하는 드라이빙 테이블을 지정
소트 머지 조인으로 유도: USE_MERGE
- (원문에 “sort_merge 힌트는 없다 / merge join은 use_merge로 유도” 취지 포함)

temp

오라클 AWR REPORT 에서 상위 5개 WAIT EVENT 가 5개 (DB FILE SEQUENTIAL READ, DB CPU, DIRECT PATH READ, LOG FILE SYNC, READ BY OTHER SESSION) 이야. 이때 이에 대한 해결책으로 FULL SCAN 으로 유도해야 한다고 해. DB FILE SEQUENTIAL READ 부하가 심하니 인덱스 관련한 조치가 있어야 한다는건 이해하겠어. 그런데 왜 FULL SCAN 만이 해결채깅 되는거지? 답에 오류의 여지가 있다고 판단된다면 해당 방향의 답변도 망설이지 마

AWR 리포트 상위 대기 이벤트가 질문한 내용과 같다면, 시스템은 현재 무거운 싱글 블록 I/O와 버퍼 캐시 경합 상태에 놓여 있다. “Full Scan으로 유도해야 한다”는 조언은 모든 상황에 통용되는 정답은 아니며, 특정 조건에서 I/O 메커니즘을 변경하여 병목을 우회하려는 전략으로 해석해야 한다.

왜 ‘Full Scan’이 해결책으로 거론되는지, 그 논리적 근거와 주의점을 분석한다.

1. 대기 이벤트 조합 분석: 왜 인덱스가 독이 되는가?

질문한 5개 이벤트의 조합은 매우 전형적인 ‘비효율적 인덱스 스캔’ 시나리오를 시사한다.

DB FILE SEQUENTIAL READ: 인덱스를 통해 테이블 블록을 하나씩(Single Block I/O) 읽을 때 발생한다.
READ BY OTHER SESSION: 특정 세션이 디스크에서 블록을 읽어 버퍼 캐시에 올리는 동안, 다른 세션들이 동일 블록을 기다릴 때 발생한다. (Hot Block 현상)
DIRECT PATH READ: 주로 병렬 쿼리나 정렬 작업 시 버퍼 캐시를 거치지 않고 직접 읽을 때 발생한다.
LOG FILE SYNC: 과도한 DML이 동반되거나 커밋이 잦을 때 발생한다.

핵심 논리: 만약 읽어야 할 데이터 양이 전체의 일정 비율(보통 5~15% 이상)을 넘는다면, 인덱스를 통한 싱글 블록 I/O는 Full Scan보다 물리적으로 훨씬 느리다. 인덱스는 블록을 하나씩 순차적으로 찾아가야 하지만, Full Scan은 Multi-Block I/O를 사용하여 한 번에 수십 개의 블록을 긁어오기 때문이다.

2. Full Scan 유도가 ‘해결책’이 되는 이유 (Direct Path Read의 활용)

단순히 TABLE ACCESS FULL로 바꾸는 것이 아니라, 이를 통해 I/O 방식을 전환하는 것이 목적이다.

Multi-Block I/O의 속도: 인덱스 스캔은 랜덤 액세스(Random Access) 방식이라 디스크 헤더가 계속 움직여야 하지만, Full Scan은 순차적 읽기(Sequential Read)를 수행하므로 처리량이 압도적이다.
버퍼 캐시 우회 (Direct Path Read 유도): * Full Scan 시 테이블 크기가 적절히 크면 오라클은 버퍼 캐시를 거치지 않는 Direct Path Read를 선택할 수 있다.
- 이렇게 되면 READ BY OTHER SESSION 같은 버퍼 캐시 경합(Latching/Pinning) 자체가 사라진다. 즉, 시스템 전체의 메모리 부하를 줄이면서 I/O 속도를 높이는 효과를 낸다.
Clustering Factor 문제 해결: 인덱스의 Clustering Factor가 나쁘면(인덱스 순서와 물리적 저장 순서가 다르면) 동일한 테이블 블록을 수천 번 반복해서 읽는 비효율이 발생한다. Full Scan은 모든 블록을 단 한 번만 읽으므로 이 중복 읽기 문제를 원천 차단한다.

3. 답변의 오류 가능성 및 주의점 (무조건적 Full Scan의 위험)

질문자가 느낀 의구심처럼, “Full Scan이 무조건 해결책”이라는 주장은 위험한 일반화일 수 있다. 다음과 같은 상황에서는 역효과가 난다.

대형 테이블의 무분별한 Full Scan: 테이블 규모가 수억 건인데 필터 조건이 명확하다면, Full Scan 유도는 시스템 전체의 I/O 서브시스템을 마비시킨다.
Direct Path Read의 오버헤드: Direct Path Read를 수행하려면 Dirty Block을 디스크에 쓰는 Checkpoint 작업이 선행되어야 한다. 잦은 DML이 발생하는 환경에서는 이 과정에서 지연이 발생할 수 있다.
CPU 사용량 폭증: 데이터 양이 많을수록 Full Scan은 더 많은 CPU 연산을 소모한다. 현재 DB CPU 대기가 이미 상위에 있다면, Full Scan 전환은 CPU 부족 현상을 가속화할 수 있다.

결론 및 제언

현 상태에서 Full Scan 유도가 해결책으로 제시된 이유는 **“비효율적인 인덱스 스캔으로 인한 랜덤 I/O와 버퍼 캐시 경합을, 고속 대량 I/O 메커니즘인 Direct Path Read로 밀어내기 위함”**이다.

권장하는 다음 단계:

SQL 단위 분석: AWR의 ‘SQL ordered by Reads’ 섹션에서 어떤 SQL이 DB FILE SEQUENTIAL READ를 가장 많이 유발하는지 확인한다.
선별적 튜닝: 해당 SQL이 읽는 데이터 비중이 높다면 **Parallel Query(Full Scan)**를 검토하고, 비중이 낮음에도 대기가 많다면 Index Rebuild나 Composite Index 구성을 통해 테이블 액세스 자체를 줄여야 한다.

출처: * Oracle Database Performance Tuning Guide (Wait Events & I/O Tuning)

Ask TOM: Differences between Sequential and Scattered Read

song-ps

Explorer

260107