💡SQL TUNING
DB time이라고도 불리는 처리 시간은 SQL 실행 속도 + 대기 시간으로 구성된다. 이때, SQL 실행 속도를 줄여 전체적인 처리 시간을 줄이는 과정을 SQL TUNING이라고 한다.

SQL 처리 과정

ORACLE DATABASE

ORACLE DATABASE

오라클 데이터베이스 서버는 인스턴스와 데이터베이스로 구성되어 있다.

DATABASE

오라클이 사용하는 3가지 타입의 물리적 저장 파일이 있다.

• Data: 유저 데이터 저장, 여기에 있는 로우데이터를 사용, 하나로 해결되지 않아 여러 개로 구축할 수 있음
• Redo: 복구에 사용하기 위한 로그
• Control: Physical Structure 정보 (Data File List, 동기화 정보, DB Structure), 찾고자 하는 데이터가 어떤 Data 파일에 있는지 등등의 위치 정보 저장, 동기화 정보
  • 정보 자체의 양은 작으나 손실될 경우 데이터를 찾지 못하는 경우가 많음
  • 따라서 장애 대응을 위해 미러링의 개념으로 여러 개를 만들어서 안정적 운영

Instance

오라클 인스턴스는 System Global Aread라는 메모리 구조와 인스턴스 실행과 관련된 대ㅔ부분의 작업을 처리하는 백그라운드 프로세스로 구성된다. 백그라운드 프로세스의 예시로는 Shared Pool, Streams Pool, Large Pool, Java Pool, Database Buffer Cache, Redo Log Buffer 등이 있다.

오라클이 뜰 때 OS에게 메모리 할당을 요구해 SGA에 해당하는 메모리를 확보하게 된다. 이후 띄워야 하는 백그라운드 프로세스까지 띄우는 것이 STARTUP 명령어의 역할이다.

이후에 SELECT, UPDATE 등이 가능한데, 그 말은 백그라운드 프로세스가 명령어 처리를 위해 반드시 필요하다는 뜻이다.

여러 백그라운드 프로세스 중에서 Shared Pool, Database Buffer Cache, Redo Log Buffer는 필수적으로 실행된다. 즉, 이 세 개의 프로세스는 무조건 있어야 명령어 실행할 준비가 가능해진다는 것이다.

SELECT 명령어 처리

DEDICATED SERVER

• 개발자 툴에서 User Process 만들기
• 오라클 서버에 Username, Password 전달하면 오라클 서버에서 서버 프로세스 생성
• 인증 절차 실행 후 세션 시작 → 명령어 실행 준비 완료
• 유저와 서버 프로세스가 1:1로 매칭되어 있음
• 유저 프로세스가 Connection 끊으면 서버 프로세스도 함께 사라짐
• 서버 프로세스는 STARTUP 할 때 뜨는 것이 아니라, 유저가 로그인할 경우에 생성
• 서버 프로세스 = 옵티마이저

PARSE

• 옵티마이저가 알아들을 수 있는 P-Code로 변환하는 과정
• Syntax Check: 문법적 오류 확인
• Semantic Check: 의미상 오류 확인 (테이블이 실제로 있는지, 권한이 있는지)
• Shared Pool에 작성된 Shared SQL Area가 있는지 검색
  • 있으면 실행 (Soft Parse)
• 수행 계획, Row Source를 생성하고 Shared SQL Area구조로 만들어 (Hard Parse) Shared Pool에 저장
  • 해당 명령어랑 똑같은 명령을 다시 수행하려고 할 때 이미 파싱해 둔 정보 그대로 재사용하기 위해 저장
  • 다른 세션이 와도 같은 Shared Pool 영역 메모리를 사용하기 때문에, 여러 세션이 함께 사용할 수 있음
• TUNING POINT: Soft Parse를 자주 활용할 수 있도록 하기
  • 동일한 명령어 수행 (대소문자 일치, 공백문자 일치, 바인드 변수 활용)
  • cursor_sharing=force 등의 옵션을 둬서 바인드변수를 사용하지 않아도 동일한 효과를 볼 수 있도록 설정 가능 (전체 시스템 레벨에서는 오히려 성능 저하가 일어날 수 있어서 권하지 않고 해당 애플리케이션 세션 정도에서만 사용 추천)

EXECUTE

• Row Source를 수행하여 Data가 위치한 Block의 주소를 알아냄
• DB Buffer Cache에서 검색하려는 Block이 있는지를 검색해서 있으면 필요한 행을 읽고 (Logical Read) Fetch 단계를 수행
• 알아낸 주소로 Datafile의 Block을 읽어 DB Buffer Cache에 저장 (Physical Read) 후 DB Buffer Cache에 저장된 Block 내의 필요한 행을 읽어옴 (Logical Read)
• DB Buffer Cache 역시 SGA 영역에 저장되기 때문에 여러 세션이 함께 공유 가능
• TUNING POINT: Logical Read를 자주 활용할 수 있도록 하기
  • 랜덤으로 실행되기 때문에 Logical Read만 줄일 수 있는 튜닝은 없음
  • 따라서 그냥 Total I/O를 줄여서 Logical / Physical을 모두 줄이는 방식으로 수행

FETCH

• SELECT 명령에만 있는 처리 단계 (SELECT만 결과를 보여 주기 때문)
• 서버 프로세스 (세션 하나) 혼자 쓰는 메모리인 PGA를 확보하고 있는데, 그중 Cursor Stack이라는 전용 메모리에 가져다 놓는 것까지가 Excure 단계이고, 이를 유저 프로세스 메모리에 전달해 줌
  • 만약 유저가 50개의 Fetch Array Size를 가졌을 경우 100개의 결과를 가져야 할 때 두 번 전달해 줘야 할 경우가 있음
  • 50개를 읽고 던지고, 50개를 읽고 던지고 식으로 진행 (한 번에 읽어온 뒤 잘라서 보내 주지 않음), 일단 50개 먼저 읽어서 던지는 방식 → 부분 범위 처리
• User Process에 결과 행을 전달

Database Buffer Cache

Database Buffer Cache

사용자가 요청한 SQL 문장을 처리할 때, EXECUTE 단계에서 사용하는 메모리이다. 오라클 서버가 가장 최근에 읽거나 변경하기 위해 액세스했던 데이터 블록의 복사본을 저장하는 메모리이다. 블록의 복사본을 메모리에 저장해 두면 다음 번에 해당 블록 요청 시에 디스크 I/O를 하지 않고 메모리에서 바로 읽게 되어 응답 속도가 빨라진다. DB_CACHE_SIZE 파라미터를 통해 크기를 조정한다.

논리적 I/O, 물리적 I/O

• 논리적 I/O
  • SQL을 처리하는 과정에서 발생한 총 블록 I/O
  • Memotu I/O + Direct Path I/O
  • SQL 튜닝시 목표는 논리적 I/O를 줄이는 것
• 물리적 I/O
  • 디스크에서 발생한 총 블록 I/O
  • 논리적 I/O 중 일부
  • 서버 시스템 상황에 의해 결정되며 논리적 I/O가 줄어들면 같이 줄어듦

ORACLE OPTIMIZER

OPTIMIZER

OPTIMIZER

옵티마이저는 SQL 문에 대한 실행계획을 생성하는 오라클 데이터베이스의 일부이다. 실행계획의 결정은 SQL문 처리에서 중요한 단계이며 실행 시간에 큰 영향을 줄 수 있다. 실행계획이란 명령문을 실행하기 위해 순서대로 수행되는 일련의 작업을 말한다. 옵티마이저는 쿼리에서 지정된 조건과 참조되늰 객체와 관련된 다음과 같은 많은 요소들을 고려한다.

Cost based Optimizer

오라클에서 참조하고 있는 옵티마이저이다. SQL 문장이 참조하고 있는 객체들에 대한 수집된 통계 정보의 값과 데이터베이스 파라미터 설정값을 기초로 가장 적은 비용이 발생하는 실행계획을 결정하는 옵티마이저이다. 즉 통계 정보를 활용해 SQL 수행시 소요될 비용을 예측하고 그 값을 기준으로 실행계획을 결정하는 것이다.

Cost Based Optimizer Mode

• ALL_ROWS: 일괄 처리 작업 등에 대한 전체 처리 속도에 대해 최적화하는 방식이며 기본 방식
• FIRST_ROWS_N: OLTP 환경에서 첫 n개의 결과가 반환될 때까지의 시간을 단축하기 위해 최적화하는 것이며 빠른 응답 시간을 원할 때 사용

Optimizer가 필요한 이유

옵티마이저는 사용 가능한 액세스 경로를 기반으로 SQL문의 잠재적 계획 집합을 생성한다. 데이터 분포 및 명령문에서 액세스한 테이블과 인덱스 저장 영역 특성에 대한 통계를 기반으로 계획별 비용을 예측한다. 여러 계획의 비용을 비교하고 최저 비용의 계획을 선택하기 위해 옵티마이저는 필요하다.

Optimizer의 구문 분석 작업 중 최적화

1. 명령어 구분 분석 (PARSE): 문법적 체크, 문맥적 체크, Shared Pool 체크
2. Optimizer
   1. 명령어 변형기 (Query Transformer): SQL을 최적화하기 쉬운 형태로 변환
   2. 예측기 (Estimator)
      1. Selectivity: 전체 대상 row 중에 특정한 조건에 의해 선택될 것으로 예상되는 row 비율
      2. Cardinality: 특정 액세스 단계를 거치고 나서 출력될 것으로 예상되는 결과 건수
      3. Cost: 특정 명령문을 실행하는 데 필요한 표준화된 I/O에 대한 최적 예측 비용
   3. 계획 생성기 (Plan Generator): 최적화를 위한 여러 기법을 시도하고 예측기를 사용하여 각 최적화 제안의 비용 계산한 뒤 비용을 기반으로 최상의 최적화 제안 선택한 뒤 실행 계획 생성
3. 실행계획생성 (Row Source Generator)

Optimizer에 영향을 미치는 요소

• 작성된 명령어
• 테이블, 인덱스, 파티션 등 제공되는 데이터 구조
• 통계 정보
• 힌트
• 옵티마이저 관련 파라미터

Optimizer의 한계

• 부족한 Factor
• 부정확한 통계
• Histogram의 한계
• 조건절 Column들의 상관관계
• Bind 변수 사용시 균등분포 가정
• 동일 비용의 경우 최신, 알파벳 수 인덱스 선택 규칙
• H/W 특징 반영하지 않음
• 조인 순서 결정시 가능한 모든 경우를 고려하지는 않음
• 최적화에 사용되는 시간 제한

QUERY 변형기

QUERY 변형기

• Heuristic Transformation 오라클의 경험치에 의해서 튜닝하는 방식으로 거의 대부분 이 실행 계획을 선택
  
• Cost Based Transformation 오히려 튜닝된 실행계획이 비용이 더 나갈 수 있기 때문에 예측치로 계산해 보고 비용이 덜 드는 방법을 선택
  

CSE(Common Subexpression Elimination)

WHERE 절에서 OR 연산시 사용된 중첩된 조건절은 제거

SELECT DEPARTMENT_ID, SALARY
FROM EMPLOYEE
WHERE (DEPARTMENT_ID = 10 AND SALARY = 4200)
**OR DEPARTMENT_ID = 10;**

JE(Join Elimination)

직접 사용하지 않은 테이블은 SQL에서 삭제

SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.SALARY
FROM EMPLOYEES E, DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
AND E.JOB_ID = 'SH_CLERK';

SELECT E.EMPLOYEE_ID, E.LAST_NAME, E.SALARY
FROM EMPLOYEES E, DEPARTMENTS D
AND E.JOB_ID = 'SH_CLERK';

조인까지 할 필요가 없는 명령어일 경우 (EMPLOYEE 테이블의 FK 보고 확인) Single Table Access의 실행 계획 보여 줌

SJ(Semi Join)

서브쿼리를 semi join으로 변환

SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID
FROM DEPARTMENTS D
WHERE EXIST (SELECT NULL FROM EMPLOYEE E
							WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)
AND D.LOCATION_ID = 1700;

SELECT D.DEPARTMENT_ID, D.DEPARTMENT_NAME, D.LOCATION_ID
FROM DEPARTMENTS D, EMPLOYEE E
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID)
AND D.LOCATION_ID = 1700;

예측기

Selectivity

• 선택성 = 조건을 충족하는 행 수 / 총 행 수

선택성은 0.0에서 1.0 사이의 값으로 표현하며 통계가 없을 경우 동적 샘플링을 사용하며, 히스토그램이 없을 경우 행 분산을 균등한 것으로 가정하고 계산한다.

기본값의 가정을 사용할 경우 최적 수준에 못 미치는 계획을 사용하게 될 수도 있다. 따라서 옵티마이저의 예측을 더욱 정확하게 하기 위해 히스토그램 통계값을 제공하여 정확도를 높일 수 있다.

-- 컬럼 통계
SELECT NUM_DISTINCT, NUM_NULLS, NUM_BUCKETS, HISTOGRAM
FROM DBA_TABLE_COLUMNS
WHERE TABLE_NAME = 'EMPLOYEES'
AND COLUMN_NAME = 'JOB_ID'
AND OWNER = 'HR';

통계 정보 제공은 Autotask에 의해 통계가 수집된다. 매일 한 번씩 오브젝트 통계 수집이 수행된다. 통계 정보가 없거나 수집 이후 10% 이상 데이터가 변동된 오브젝트를 대상으로 진행된다. 혹은 아래 명령어를 통해 DBMS_STATS 패키지를 통해 수동으로 실행할 수 있다.

begin
dbms_stats.gather_tables_stats('test', 'cp_emp'); -- test 스키마의 cp_emp 테이블 
end;

테이블은 생성된다고 통계 수집이 바로 되지 않지만, 인덱스는 생성되는 즉시 통계 수집이 바로 이루어진다.

Cardinality

• 기수 = 선택도 * 총 행 수

실행계획의 각 단계의 검색 행의 예측 수이다. 조인, 필터 및 정렬 비용을 결정할 중요한 수치이다.

Cost

• 비용 = (단일 블록 IO 비용 + 멀티 블록 IO 비용 + CPU 비용) / 단일 블록 읽기 시간

옵티마이저가 예측한 명령어를 실행하는 데 필요한 표준화된 I/O 횟수이다. 비용 공식은 세 가지의 다른 비용 단위가 포함된다. 만약 비용이 10이라면 10번의 Single Block I/O를 한 시간이라고 생각하면 된다.

실행 계획 확인

EXPLAIN PLAN

EXPLAIN PLAN

옵티마이저가 실행 계획을 생성하고 예상 실행 계획을 PLAN_TABLE에 저장한다. 명령문을 실제로 실행하지는 않는다.

EXPLAIN PLAN FOR select * from employees where employee_id = 100;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
Plan hash value: 3541774394

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    67 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     1 |    67 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMPLOYEES_IX01 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

항목	설명
ID	각 Operation ID
Operation	실행되는 job
Name	Operation이 액세스하는 테이블, 인덱스
Rows	각 Operation이 끝났을 때 리턴되는 건수
Cost	각 Operation의 Cost
Time	수행시간

PLAN_TABLE

실행 계획이 저장되는 테이블이며 오라클을 설치하면 자동으로 생성된다. 이름만 똑같고 세션마다 다른 플랜 테이블을 사용한다. 즉 공유되는 테이블이 아니기 때문에 세션마다 독립적으로 사용한다.

SELECT OWNER, SYNONYM_NAME, TABLE_OWNER, TABLE_NAME
FROM ALL_SYNONYMS
WHERE SYNONYM_NAME = 'PLAN_TABLE';

OWNER                          SYNONYM_NAME                   TABLE_OWNER                    TABLE_NAME                    
------------------------------ ------------------------------ ------------------------------ ------------------------------
PUBLIC                         PLAN_TABLE                     SYS                            PLAN_TABLE$    

실행 계획 읽기

EXPLAIN PLAN FOR
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

Plan hash value: 3541774394
 
----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    67 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     1 |    67 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMPLOYEES_IX01 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
   2 - access("EMPLOYEE_ID"=100)

1. 하위 단계는 들여쓰기 되어 있음
2. 하위 단계 수행 후 상위 단계 처리
3. 동일 LEVEL끼리는 먼저 나온 단계부터 수행

이런 실행 계획이 있다면, 4 → 3 → 6 → 5 → 2 → 8 → 7 → 1 → 0 순서이다.

AUTOTRACE

AUTOTRACE

SQL 명령의 예상 실행 계획 및 실행 통계를 자동으로 얻을 수 있다. AUTOTRACE ON이 되어 있으면 모든 명령어가 명렁 결과, 실행 플랜, 통계 정보가 세트로 보여진다. 수행 통계는 V$ 테이블에 있는 데이터들을 읽어와서 보여 준다. 이럴 경우 DBA의 권한이 필요하기 때문에 이를 주의하자. AUTOTRACE 기능에는 여러 가지 명령 옵션들이 있다.

• OFF: AUTOTRACE 비활성화
• ON: AUTOTRACE 활성화
• TRACEONLY: AUTOTRACE 활성화하고 명령문의 결과를 출력하지 않음
• EXPLAIN: 실행 계획은 표시하지만 통계를 표시하지 않음
• STATISZTICS: 통계는 표시하지만 실행 계획은 표시하지 않음

SET AUTOTRACE ON
SELECT *
FROM EMPLOYEES
WHERE EMPLOYEE_ID = 100;

EMPLOYEE_ID FIRST_NAME                               LAST_NAME
----------- ---------------------------------------- --------------------------------------------------
EMAIL                                              PHONE_NUMBER                             HIRE_DATE  JOB_ID          SALARY COMMISSION_PCT
-------------------------------------------------- ---------------------------------------- ---------- -------------------- ---------- --------------
MANAGER_ID DEPARTMENT_ID
---------- -------------
        100 Steven                                   King
SKING                                              515.123.4567                             2003/06/17 AD_PRES          24000
                      90

1 row selected.

Elapsed: 00:00:00.01

Execution Plan
----------------------------------------------------------
Plan hash value: 3541774394

----------------------------------------------------------------------------------------------
| Id  | Operation                   | Name           | Rows  | Bytes | Cost (%CPU)| Time     |
----------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                |     1 |    67 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES      |     1 |    67 |     1   (0)| 00:00:01 |
|*  2 |   INDEX UNIQUE SCAN         | EMPLOYEES_IX01 |     1 |       |     0   (0)| 00:00:01 |
----------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("EMPLOYEE_ID"=100)

Statistics
----------------------------------------------------------
          1  recursive calls
          0  db block gets
          2  consistent gets
          0  physical reads
          0  redo size
       1167  bytes sent via SQL*Net to client
        512  bytes received via SQL*Net from client
          1  SQL*Net roundtrips to/from client
          0  sorts (memory)
          0  sorts (disk)
          1  rows processed

통계에 대한 항목 설명은 다음과 같다.

항목	설명
recursive calls	유저 레벨과 시스템 레벨에서 생성되는 recursive call 수
db block gets	Current 모드로 읽은 블록 수
consistent gets	Consistents 모드로 읽은 블록 수
physical reads	디스크에서 읽은 블록 수
redo size	총 리두 양
bytes sent via SQL*Net to client	서버 프로세스에서 클라이언트로 보낸 총 바이트 수
bytes received via SQL*Net from client	오라클넷을 통해 클라이언트에서 받은 총 바이트 수
SQL*Net roundtrips to/from client	클라이언트가 발행한 fetch all 횟수
sorts (memory)	메모리에서 완전히 수행되어 디스크 쓰기가 필요하지 않은 정렬 작업의 수
sorts (disk)	최소한 하나의 디스크 쓰기가 필요한 정렬 작업 수
rows processed	조회 건수

V$SQL_PLAN

V$SQL_PLAN

V$SQL_PLAN은 라이브러리 캐시에 있는 SQL 커서의 실제로 사용된 실행 계획을 제공한다. 따라서 DBMS_XPLAN.DISPLAY_CURSOR(’sql_id’, child_number, ‘format’)을 이용해 실행 계획을 출력한다.

v$sql 테이블에 실행한 SQL이 모두 저장되어 있다. 따라서 여기에서 내가 조사하고 싶은 SQL을 검색한 뒤에 sql_id를 알아내야 한다.

현재 세션에서 직전에 수행한 SQL의 경우는 sql_id, child_number를 NULL로 지정하거나 생략 가능하다.

-- SQL ID 찾기
SELECT SQL_ID, SQL_TEXT
FROM v$sql
WHERE SQL_TEXT LIKE '%SELECT E.LAST_NAME, %';

-- 실행 계획 보여 주기
SELECT * 
FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('43tystsdw8hmp'));

실행 계획은 실제 사용한 것이 맞지만 함께 보여 주는 통계값은 옵티마이저의 예측치다.

V$SQL_PLAN_STATISTICS_ALL

V$SQL_PLAN_STATISTICS_ALL은 단계별 실험 통계를 제공한다. STATISTIC_LEVEL을 ALL로 설정하고 SQL을 실행한 뒤에 실제 사용한 실행 통계를 알 수 있다. 따라서 이를 통해 옵티마이저의 예측치와 실제 통계가 일치한지 확인 및 검증할 수 있다.

ALTER SESSION SET STATISTICS_LEVEL=ALL;

SELECT E.LAST_NAME, D.DEPARTMENT_NAME
FROM HR.EMPLOYEES E, HR.DEPARTMENTS D
WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID;

ALTER SESSION SET STATISTICS_LEVEL=TYPICAL;

SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(null, null, 'ALLSTATS LAST'));

SQL_ID  5bd1y8jpdkm7g, child number 0
-------------------------------------
SELECT E.LAST_NAME, D.DEPARTMENT_NAME FROM HR.EMPLOYEES E, 
HR.DEPARTMENTS D WHERE E.DEPARTMENT_ID = D.DEPARTMENT_ID
 
Plan hash value: 1473400139
 
------------------------------------------------------------------------------------------------------------------------------------------------
| Id  | Operation                    | Name              | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |  OMem |  1Mem | Used-Mem |
------------------------------------------------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT             |                   |      1 |        |     50 |00:00:00.01 |       8 |      6 |       |       |          |
|   1 |  MERGE JOIN                  |                   |      1 |    106 |     50 |00:00:00.01 |       8 |      6 |       |       |          |
|   2 |   TABLE ACCESS BY INDEX ROWID| DEPARTMENTS       |      1 |     27 |      5 |00:00:00.01 |       2 |      2 |       |       |          |
|   3 |    INDEX FULL SCAN           | DEPT_ID_PK        |      1 |     27 |      5 |00:00:00.01 |       1 |      1 |       |       |          |
|*  4 |   SORT JOIN                  |                   |      5 |    107 |     50 |00:00:00.01 |       6 |      4 | 73728 | 73728 |          |
|   5 |    VIEW                      | index$_join$_001  |      1 |    107 |    106 |00:00:00.01 |       6 |      4 |       |       |          |
|*  6 |     HASH JOIN                |                   |      1 |        |    106 |00:00:00.01 |       6 |      4 |  1096K|  1096K| 1541K (0)|
|   7 |      INDEX FAST FULL SCAN    | EMP_DEPARTMENT_IX |      1 |    107 |    106 |00:00:00.01 |       3 |      2 |       |       |          |
|   8 |      INDEX FAST FULL SCAN    | EMP_NAME_IX       |      1 |    107 |    107 |00:00:00.01 |       3 |      2 |       |       |          |
------------------------------------------------------------------------------------------------------------------------------------------------

항목	설명
Id	각 단계 ID
Operation	실행되는 Job
Name	단계에서 액세스하는 테이블, 인덱스
Starts	각 단계를 반복 실행한 횟수
E-Rows	각 단계에서 리턴하는 건수 (예상)
A-Rows	각 단계에서 리턴하는 건수 (실제)
A-Time	각 단계별 소요 시간 (누적)
Buffers	각 단계별 읽은 총 블록 (누적)
Reads	각 단계별 디스크에서 읽은 블록 수
OMem	Opotimal일 때 예상되는 PGA (모두 메모리에서 처리)
1Mem	One-PASS일 때 예상되는 PGA (디스크 한 번 읽어옴)
Used-Mem	실제 사용된 PGA

SQL Trace

SQL Trace

일반적으로 세션 레벨에서 활성화된다. 세션별로 그룹화된 SQL 문에 대한 통계를 수집하고, tkprof 유틸리티를 이용해 읽기 쉬운 출력을 생성한다. 마치 통계에 대한 로그 파일을 OS에 저장한다고 생각하면 된다. OS가 FTP가 가능한 계정이어야 한다는 제약 조건이 있다.

1. 초기화 파라미터 설정
2. TRACE 활성화
3. 응용 프로그램 실행
4. TRACE 비활성화
5. TKPROF로 TRACE FILE 변환
6. 출력 결과 해석

SELECT name, value FROM v$diag_info;
ALTER SESSION SET tracefile_identifier='case1';
exec DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>true);
SELECT * FROM HR.EMPLOYEES WHERE EMPLOYEE_ID = 102;
exec DBMS_SESSION.SESSION_TRACE_DISABLE;

tkprof xe_ora_19216_case1.trc report_1.txt sys=no
type report_1.txt

TKPROF: Release 11.2.0.2.0 - Development on 월 8월 18 16:54:27 2025

Copyright (c) 1982, 2009, Oracle and/or its affiliates.  All rights reserved.

Trace file: xe_ora_19216_case1.trc
Sort options: default

********************************************************************************
count    = number of times OCI procedure was executed
cpu      = cpu time in seconds executing
elapsed  = elapsed time in seconds executing
disk     = number of physical reads of buffers from disk
query    = number of buffers gotten for consistent read
current  = number of buffers gotten in current mode (usually for update)
rows     = number of rows processed by the fetch or execute call
********************************************************************************

SQL ID: 06m0pgb53b824 Plan Hash: 0

BEGIN DBMS_SESSION.SESSION_TRACE_ENABLE(waits=>true); END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        0      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          1        100          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        1      0.00       0.00          1        100          0           1

Misses in library cache during parse: 0
Misses in library cache during execute: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       1        0.00          0.00
  SQL*Net message from client                     1       24.21         24.21
********************************************************************************

SQL ID: gmp19j6mx7njy Plan Hash: 1833546154

SELECT *
FROM
 HR.EMPLOYEES WHERE EMPLOYEE_ID = 102

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        4      0.00       0.00          0          2          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48
Number of plan statistics captured: 1

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
         1          1          1  TABLE ACCESS BY INDEX ROWID EMPLOYEES (cr=2 pr=0 pw=0 time=6 us cost=1 size=69 card=1)
         1          1          1   INDEX UNIQUE SCAN EMP_EMP_ID_PK (cr=1 pr=0 pw=0 time=2 us cost=0 size=0 card=1)(object id 16413)

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       2        0.00          0.00
  SQL*Net message from client                     2       23.20         23.20
********************************************************************************

SQL ID: 9wuhwhad81d36 Plan Hash: 0

BEGIN DBMS_SESSION.SESSION_TRACE_DISABLE; END;

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0          0          0           0
Execute      1      0.00       0.00          0          0          0           1
Fetch        0      0.00       0.00          0          0          0           0
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        2      0.00       0.00          0          0          0           1

Misses in library cache during parse: 1
Optimizer mode: ALL_ROWS
Parsing user id: 48

********************************************************************************

OVERALL TOTALS FOR ALL NON-RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        2      0.00       0.00          0          0          0           0
Execute      3      0.00       0.00          1        100          0           2
Fetch        2      0.00       0.00          0          2          0           1
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total        7      0.00       0.01          1        102          0           3

Misses in library cache during parse: 2
Misses in library cache during execute: 1

Elapsed times include waiting on following events:
  Event waited on                             Times   Max. Wait  Total Waited
  ----------------------------------------   Waited  ----------  ------------
  SQL*Net message to client                       3        0.00          0.00
  SQL*Net message from client                     3       24.21         47.42

OVERALL TOTALS FOR ALL RECURSIVE STATEMENTS

call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse       12      0.00       0.00          0          0          0           0
Execute    199      0.01       0.01          0          0          0           0
Fetch      246      0.00       0.00          0        652          0        1735
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total      457      0.01       0.01          0        652          0        1735

Misses in library cache during parse: 10
Misses in library cache during execute: 10

    3  user  SQL statements in session.
   12  internal SQL statements in session.
   15  SQL statements in session.
********************************************************************************
Trace file: xe_ora_19216_case1.trc
Trace file compatibility: 11.1.0.7
Sort options: default

       1  session in tracefile.
       3  user  SQL statements in trace file.
      12  internal SQL statements in trace file.
      15  SQL statements in trace file.
      15  unique SQL statements in trace file.
     792  lines in trace file.
      47  elapsed seconds in trace file.

항목	설명
call	명령어 처리 단계에 따른 통계 정보
count	각 단계가 수행된 횟수
cpu	처리에 소요되는 시간
elapsed	실행에 소요되는 총 경과 시간
disk	물리적 블록 읽기 수
query	일관성 있는 모드에서 논리적 버퍼 읽기 수
current	현재 모드에서 논리적 버퍼 읽기 수
rows	fetch, execute에 의해 처리된 행 수

인덱스 사용 튜닝

TABLE ACCESS

Full Table Scan

다중 블록 읽기 수행으로 High-Water Mark 아래의 모든 블록을 읽는다. 행 필터링이 가능하고 많은 양의 데이터를 처리하는 경우 Index Scan보다 빠르다. 사용 사례는 다음과 같다.

• 적절한 인덱스가 없음
• 낮은 선택적 필터 (필터가 거의 없음)
• 작은 테이블
• 높은 병렬도
• 전체 테이블 스캔 힌트: FULL

Rowid Scan

ROWID를 사용하여 하나의 행만 바로 읽어오는 방식이다. 사용되는 경우는 다음과 같다.

• User Rowid, Index Rowid를 이용하여 소량의 데이터 검색이 유용할 때
• Single Block I/O 수행
• ROWID: Obeject ID (4bytes) + Datafile Number (1.5bytes) + Block Number (2.5bytes) + Row Number (2bytes)

B Tree Index가 필요한 이유

RowID를 이용해서 값을 찾으러 갈 때, 모든 Row의 RowID를 외우고 있을 수는 없다. 그렇다면 찾고자 하는 데이터에 대한 RowID를 알아야 하는데, 이 RowID를 알기 위해 인덱스 테이블을 앞에서부터 쭉 훑는 것 역시 비효율적이다. 따라서 B+Tree Index를 사용해 이진 탐색을 통해 인덱스를 찾는 속도를 높인다.

루트 노드에는 LMC(Left Most Child) 값을 기록하여 리프 블록을 찾아가는 구조이다.

B Tree Index 구조

• 인덱스: 인덱스 항목 헤더 + 키 열 길이 + 키 열 값 + ROWID

각 B+ Tree 인덱스의 검색 시작점은 루트 블록이다. 항목 수에 따라 여러 개의 분기 블록으로 갈라지고 여기서 다시 여러 개의 최하위 블록들이 있다. 인덱스는 항상 균형을 이루며 위에서 아래로 확장한다. 균형 알고리즘에 의해 B+Tree 높이가 불필요하게 커질 수도 있는데, 그런 경우 인덱스를 재구성한다.

B+ Tree 인덱스의 내부 구조는 인덱스화된 컬럼값에 대한 신속한 액세스를 허용한다.

• 컬럼의 값, ROWID가 항상 정렬된 값 유지
• 컬럼의 값은 null을 제외한 모든 값 보유
• Root, Branch, Leaf Block을 이용하여 Tree 구조로 오라클이 자동 관리
• 생성
  • PK, Unique 제약 조건이 선언된 컬럼으로 자동 생성
  • 유저가 성능 향상을 위해 수동 생성

INDEX SEARCH

Index Range Scan

인덱스의 root, branch, leaf block까지 수직적으로 탐색한 후에 leaf block에서 필요한 범위만 수평적으로 탐색하는 방식이다. 따라서 root, branch는 첫 번째의 leaf block을 탐색할 때만 한 번씩 거치고, 이후에는 leaf block을 수평적으로 쭉 훑는다.

Single Block Read를 수행하며, 우리가 인덱스를 설정하는 가장 기본적인 이유는 이 Index Range Scan을 진행하기 위해서이다. 기본적으로 ASC으로 접근한다. 순서를 정리한다면 다음과 같다.

1. 수직 스캔: 조건에 맞는 첫 키 값을 찾기 (Root → Branch → Leaf)
2. 수평 스캔: 조건에 맞는 키 값이 더 이상 나오지 않을 때까지 Leaf 블록을 스캔하는 ROWID를 알아냄
3. 테이블 액세스: 인덱스 스캔으로 알아낸 ROWID를 통해 테이블을 찾아감 (Random I/O)

인덱스는 여러 개의 칼럼을 결합해서 만들 수 있다. 최대 32개의 컬럼까지 허용한다. 이 조합을 현명하게 해서 테이블의 액세스, 인덱스 스캔 최소화에 활용할 수 있다. 우리가 주목해야 할 것은 첫 번째로 Random I/O를 줄이고, 충분히 줄였다면 수평 탐색을 줄이는 방향으로의 튜닝이 필요하다.

예를 들어 아래와 같은 SQL을 수행한다고 가정해 보자.

SELECT 사번
FROM 사원
WHERE 사원명 = '조희수'
AND 직급 = '부장';

인덱스가 이름으로만 되어 있는 상태일 때, 조희수 과장과 조희수 부장이 존재한다면, 인덱스로 두 개의 조희수를 찾고 과장과 부장으로 두 번의 Random I/O가 일어난다. 하지만 애초에 인덱스에 (사원명, 직급)으로 결합 인덱스를 생성해 둔다면, 한 번에 조희수 부장을 찾아서 Random I/O는 한 번만 일어난다.

이때 결합 인덱스에서 인덱스의 선행 컬럼이 조건절에 없거나 ‘=’ 조건이 아니면 인덱스 스캔의 효율성이 낮아집니다. 첫 선분 조건 컬럼까지만 연속적 스캔이 가능하므로 이후 컬럼에 대한 조건은 스캔의 범위를 줄여 주지 못하고 FILTER 조건으로만 참여하게 된다. 따라서 결합 인덱스 생성시 WHERE 절에서 항상 사용하는 컬럼이나 ‘=’ 조건을 많이 사용하는 컬럼을 앞에 둘수록 좋다.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  b5p87f5czvjh7, child number 0
-------------------------------------
SELECT /*+ index(emp2 emp2_x01) */ * FROM EMP2 WHERE DEPTNO=30 AND
SAL>=2000

Plan hash value: 3451918632

--------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers |
--------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |   8192 |00:00:00.02 |    2339 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMP2     |      1 |  30492 |   8192 |00:00:00.02 |    2339 |
|*  2 |   INDEX RANGE SCAN          | EMP2_X01 |      1 |  38229 |  49152 |00:00:00.01 |     238 |
--------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   1 - filter("SAL">=2000)
   2 - access("DEPTNO"=30)

21 rows selected.

emp2_x01 인덱스를 사용한 스캔은 인덱스 스캔에서 238번의 I/O, 그리고 Random I/O를 2000번 한 것을 확인할 수 있다.

PLAN_TABLE_OUTPUT
------------------------------------------------------------------------------------------------------------------------------------------------------
SQL_ID  67cz5fd95du3u, child number 0
-------------------------------------
SELECT /*+ index(emp2 emp2_new) */* FROM EMP2 WHERE DEPTNO=30 AND
SAL>=2000

Plan hash value: 2378551592

-----------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name     | Starts | E-Rows | A-Rows |   A-Time   | Buffers | Reads  |
-----------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |          |      1 |        |   8192 |00:00:00.01 |     854 |     22 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMP2     |      1 |  30492 |   8192 |00:00:00.01 |     854 |     22 |
|*  2 |   INDEX RANGE SCAN          | EMP2_NEW |      1 |  30492 |   8192 |00:00:00.01 |     104 |     22 |
-----------------------------------------------------------------------------------------------------------

Predicate Information (identified by operation id):
---------------------------------------------------

   2 - access("DEPTNO"=30 AND "SAL">=2000 AND "SAL" IS NOT NULL)

20 rows selected.

이때 INDEX(DEPTNO, SAL)을 새로 설정해 튜닝해 주면, 인덱스 스캔에서 104번으로 거를 수 있으며, Random I/O가 850번으로 확 줄어든 것을 확인할 수 있다. 또한 이미 인덱스에 SAL 칼럼이 반영되어 있기 때문에 인덱스 스캔 이후 따로 FILTERING이 불필요하다.

Index Inlist Iterator

QUERY에 값을 가진 IN절이나 동일한 열에 등식 술어가 OR로 연결되어 있을 떄 사용된다. INLIST ITERATOR는 열거된 값들을 별도로 실행한다. 실행 계획은 등식 절을 가진 명령문의 결과와 동일하며 단계가 하나 추가된다.

옵티마이저는 IN절에 값이 지정될 때 INLIST ITERATOR를 사용하고, 해당 열에 대한 선택적 인덱스를 찾는다. 동일한 인덱스를 사용하는 OR 절이 있는 경우 옵티마이저는 이 연산을 선택한다. IN 연산자로 작성한 값이 매우 많고 이미 가까이 있는 근접값이라면 인덱스를 각각 타는 것보다 범위 선택 후 필터링하는 것이 더 나을 수도 있다. IN 연산자는 값들이 여기저기 흩어져 있을 경우 범위 스캔의 비용을 줄이기 위해 선택한다고 보면 된다.

Index Full Scan

모든 leaf block을 logical 순서대로 읽는 것이다. 인덱스 컬럼 이외의 컬럼 조회 시에도 사용될 수 있으며, 사용되는 컬럼의 NOT NULL 제약 조건 또는 조건식이 필요하다. Left Most Child를 찾기 위해 Root와 Branch를 한 번씩 방문한다. Multi Block이 아닌 차례차례로 읽어 오는 방식이다. 인덱스에 따라 저장된 데이터들이 물리적으로 옆에 있지 않기 때문에, 논리적인 B+ Tree 구조에 따라 똑같이 ROWID를 찾아 불러오는 형식이다. 따라서 Single Block I/O를 실행한다. 보통 Full Table Scan 대신 혹은 SORT 대신으로 사용한다.

Index Fast Full Scan

모든 Leaf Block을 읽는다. 인덱스에 포함된 컬럼으로만 조회할 때 사용한다. 이것 역시 사용되는 칼럼에 NOT NULL 제약 조건 또는 조건식이 필요하고, Multi Block Read를 수행하기 때문에 빠른 읽기가 가능해진다. 병렬 처리가 가능하고, 넓은 범위의 검색에서 사용된다.

Index Join Scan

인덱스끼리 조인되는 형태이며 SELECT 절과 WHERE 절 칼럼이 모두 인덱스에 존재해야 사용할 수 있다. 테이블의 접근 없이 인덱스만 이용 가능할 경우 사용된다. HASH JOIN의 형태로 동작한다. ROWID가 같은 데이터들끼리 JOIN한다고 생각하면 된다.

SORT ORDER BY

SORT ORDER BY 실행 단계는 Sort Area를 할당하여 정렬 작업을 수행한다. 정렬 상태를 이미 가지고 있는 인덱스가 있다면, 별도의 정렬 작업 수행 없이 그냥 검색할 수 있다. 따라서 인덱스를 적절하게 활용하면 정렬하는 작업에 대한 큰 이득을 볼 수 있다. 하지만 정렬을 수행하는 것이 더 나은 경우도 있기 때문에 주의 깊게 사용해야 한다.

이때 조건절을 이용하여 NOT NULL인 값들만 액세스하도록 해야 인덱스를 이용한 정렬 작업을 수행할 수 있다. 그냥 정렬을 할 때는 NULL 값은 가장 큰 값이라고 가정하고 정렬한다. 하지만 B+ Tree Index에는 NULL 값이 안 들어가 있기 때문에 인덱스를 이용한 정렬을 할 때는 주의해야 한다. 또한 기존의 정렬 작업은 모든 데이터를 정렬해야 해서 부분 범위 처리가 불가능하지만, 인덱스를 사용하면 정렬 작업 자체가 생략되기 때문에 부분 범위 처리가 가능하다. 따라서 유저는 더욱 빠르게 응답을 받아 볼 수 있다.

인덱스 스캔을 못 하는 경우

• <> , !=, ^= 등 부정형 비교 부정형 비교는 특정값을 제외한 나머지 모든 값을 비교해야 하는 상황이기 때문에 인덱스의 검색 범위가 넓어질 수 있어서 인덱스 사용이 불가능한 경우가 많다.
  
• 인덱스 컬럼의 변형이 발생한 경우 인덱스는 컬럼에 저장되어 있는 값으로 정렬되어 있기 때문에 계산, 변형하는 경우 인덱스를 사용할 수 없다. (ex TO_CHAR(sal))
  
• LIKE ‘%문자열’ 시작되는 문자열을 모르면 인덱스의 스캔 시작 부분을 찾을 수 없기 때문에 Table Full Scan이 사용되거나 Index Full Scan을 사용할 수밖에 없다.
  
• IS NULL (B+ Tree에 없음) B+ Tree 인덱스는 NULL 값을 저장하지 않기 때문에 IS NULL 연산자는 인덱스를 항상 사용할 수 없다. NULL 값이 대량으로 들어 있어서 인덱스를 사용하지 않아도 되는 경우가 아니라 소량의 NULL을 검색해야 한다면 DEFAULT 값으로 대체하거나 Function Based Index를 하는 등의 우회적인 방법이 필요하다.
  
• 가공하지 않은 선행 컬럼이 조건절에 없을 때

조인 튜닝

Nested Loops Join

Nested Loops Join

Outer Table 행의 소스를 스캔하는 것이다. Outer Table의 각 행에 대해 조인 조건을 만족하는 Inner Table의 모든 행을 검색하는 것이다. Outer Table 검색 조건의 행이 적을 경우에 적합하고 조인에 참여하는 테이블의 순서가 중요하다.

Nested Loops Join은 선행 테이블 (Outer Table - driving) 결정 후 후행 테이블 (Inner Table - driven)에 반복적으로 접근한다. 후행 테이블의 조인 컬럼에 인덱스가 필요하고, 소량의 데이터를 조인하는 OLTP 업무 환경에 적합하다. 한 레코드씩 순차적으로 처리하기 때문에 부분 범위 처리에 최적화되어 있다. 많은 양의 데이터를 조인할 때 Random Access 횟수가 높아진다. 따라서 Nested Loops Join의 튜닝 포인트는 Index 튜닝 포인트랑 동일하다.

선행, 후행 테이블을 결정하는 조건은 다음과 같다.

1. 1건 걸러내는 조건이 있는 테이블이 선행 테이블
2. 조인 조건 컬럼에 인덱스가 있는 것이 후행 테이블 (Random Access 최소화)
3. 양쪽에 인덱스가 있고 추가 조건이 있다면 추가 조건이 있는 쪽이 선행 테이블

실행 계획에서는 Outer Table - Inner Table 순서로 나온다.

NLJ Batching

만약 조인하려는 데이터가 버퍼 캐시에 없으면 계속해서 물리적 읽기를 시도해야 하기 때문에 서버가 I/O 응답을 얻을 때까지 Sleep해야 하는 시간이 많다. 따라서 일단 버퍼 캐시에 있는 데이터끼리만 일단 JOIN을 하고, 버퍼 캐시에 없어서 조인을 보류한 데이터들을 모아다가 한 번에 물리적 읽기를 시도하는 것이 NLJ Batching이다.

Sort Merge Join

Sort Merge Join

첫 번째와 두 번째 행 소스가 동일한 정렬 키로 정렬되고, 양 쪽에서 정렬된 행이 병합되는 것이다. 작업 순서는 다음과 같다.

1. 첫 번째 데이터 집합을 가져와서 조인 컬럼에 대해 정렬
2. 두 번째 데이터 집합을 가져와서 조인 컬럼에 대해 정렬
3. 첫 번째 데이터 집합의 각 행에 대해서 두 번째 데이터 집합에서 시작점을 찾고 조인하지 않는 행을 찾을 때까지 수행

합병해야 하기 때문에 조인 작업이 완료되기까지 부분 범위 처리가 불가능하다. 양쪽 모두 정렬이 끝나야지만 조인이 만들어지기 때문이다. 전용의 메모리 공간인 PGA에서 수행되기 때문에 속도가 빠르나, 충분한 메모리 확보가 이루어지지 않으면 임시 테이블 스페이스를 활용하기 때문에 성능 저하의 원인이 될 수도 있다. 그럼에도 불구하고 이 조인이 많이 활용될 때는, 이미 인덱스가 있어서 SORT 없이 MERGE만 해도 될 때 선택될 수 있다. 둘 다 정렬하고 조인하는 경우는 대용량 DB에서는 거의 볼 수 없다.

Hash Join

Hash Join

작은 행 소스 (Build Table)를 사용하여 해시 맵을 생성한다. 두 번쨰 행 소스 (Probe Table)이 해싱 처리되어 해시 맵에 대해 검사한다. 작업 순서는 다음과 같다.

1. 크기가 작은 테이블을 Build Table로 결정 후 해시맵을 PGA의 Hash Area에 생성한다.
2. 크기가 큰 테이블을 Probe Table로 액세스하여 조인한다.

이는 해시데이터를 활용한 조인이기 때문에 = 조인에서만 사용이 가능하고, 전체 범위 처리에 최적화되어 있다. 소량의 데이터를 사용할 때 쓰인다면 불필요한 I/O가 증가하여 오히려 비효율적일 수 있다. Hash Join은 대량의 조인을 수행할 때 사용되기 때문에 가급적으로 Multi Block I/O를 지원하는 액세스 방법을 사용한다. 조인 순서에 따라 I/O의 변화는 없으나 사용된 메모리의 크기가 눈에 띄게 증가된 것을 확인할 수 있다. 데이터가 더 많은 집합을 먼저 액세스하여 Hash Map을 생성하려면 그에 따른 부담이 더 커질 수도 있기 때문에 행이 작은 테이블을 Build Table로 삼는다.