프로그래밍 허브

오라클 튜닝을 공부하다 보면 가장 먼저 부딪히는 벽이 바로 **"실행계획이 뭐지?"**  
그리고 그 다음엔 이런 생각이 들죠.

> 이 쿼리 진짜 느린데, 왜 느린지는 모르겠네…

그래서 오늘은 실무와 튜닝 공부에서 정말 많이 쓰이는 두 가지 기능인  
**AUTOTRACE**, **SQL TRACE + tkprof** 에 대해 정리해봤습니다.

1️⃣ AUTOTRACE란?

AUTOTRACE는 SQL을 실행하면, 그 결과와 함께 **실행계획 + 통계 정보**를 보여주는 도구입니다.  
SQL*Plus 또는 Oracle SQL Developer에서 사용 가능합니다.

• TABLE ACCESS FULL, INDEX RANGE SCAN 같은 실행계획 키워드를 볼 수 있어요.
• 특히 Rows, Cost, Time 정보를 보면서 쿼리 효율을 체크할 수 있습니다.

0. SQL*Plus 실행 및 로그인 진행
1. autotrace on 설정하기

ALTER SESSION SET SQL_TRACE = TRUE;

2. sql 실행하기(태스트용 테이블_emp을 조회하면서 발생하는 실행 계획 보기 위해서 DML 활용)

SELECT * FROM EMP;

3. autotrace off하기

ALTER SESSION SET SQL_TRACE = FALSE;

4. trc파일 위치 확인하기

SELECT value FROM v$diag_info WHERE name = 'Default Trace File';

응답값으로 폴더위치가 나옵니다. 
예시: ~~\trace\orcl_ora_123456.trc
5.CMD(명령어 프롬프트)을 열어서 4번에 나온  trc폴더 위치로 이동(trace 폴더까지만 이동)

cd C:\APP\C\사용자이름\diag\rdbms\orcl\orcl\trace

6. 이어서  trc폴더에서 명령어 하나 입력(사용자가 볼 수 있게 txt로 변환)

tkprof orcl_ora_123456.trc output.txt

tkprof orcl_ora_5번에 나온 수치.trc output.txt

7. output.txt 생성 확인 

Trace file: orcl_ora_123456.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
********************************************************************************

SELECT * 
FROM
 EMP


call     count       cpu    elapsed       disk      query    current        rows
------- ------  -------- ---------- ---------- ---------- ----------  ----------
Parse        1      0.00       0.00          0         83          0           0
Execute      1      0.00       0.00          0          0          0           0
Fetch     1335      0.00       0.02          0       1466          0       20000
------- ------  -------- ---------- ---------- ---------- ----------  ----------
total     1337      0.00       0.02          0       1549          0       20000

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

Rows (1st) Rows (avg) Rows (max)  Row Source Operation
---------- ---------- ----------  ---------------------------------------------------
     20000      20000      20000  TABLE ACCESS FULL EMP (cr=1466 pr=0 pw=0 time=25668 us starts=1 cost=39 size=2129673 card=24479)

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

SQL TRACE + tkprof란?

AUTOTRACE가 한 번의 쿼리를 분석한다면,
SQL TRACE는 전체 세션의 모든 SQL 실행을 추적해주는 기능이에요.

트레이스 결과는 .trc 파일로 저장되며, Oracle이 제공하는 tkprof로 사람이 읽기 좋게 변환합니다.

tkprof 해석 예시

• query: 논리적 블록 읽기
• disk: 실제 디스크 접근 횟수
• rows: 반환된 레코드 수
• cpu, elapsed: 처리 시간

✅ 실제로 인덱스를 탔다면 INDEX UNIQUE SCAN 같은 문구도 뜹니다!

실습 사례 정리

이번 글을 위해 직접 테스트도 진행해봤습니다.

• EMP 테이블에 2만 건 데이터 삽입
• ORA-01438, ORA-00001 오류 발생 및 해결
• PRIMARY KEY 제약 조건 수정
• AUTOTRACE 및 tkprof 분석 수행

3.1 버퍼 락의 필요성

오라클은 다수의 프로세스가 동시에 같은 데이터 블록을 읽고/쓰는 상황에서도 데이터 무결성과 동기화를 보장해야 한다. 만약 여러 프로세스가 동일한 버퍼를 변경한다면, 데이터 손상이나 충돌이 발생할 수 있다. 이를 방지하기 위해, 오라클은 Buffer Lock이라는 내부 락 메커니즘을 제공한다.

🔐 동시 접근 방지

• 프로세스가 캐시된 버퍼 블록을 읽거나 변경하려면 먼저 해당 블록의 Buffer Lock을 획득해야 한다.
• 버퍼 내용을 읽을 때는 Shared 모드, 변경할 때는 Exclusive 모드로 락이 설정된다.

🧠 이는 파일 시스템에서 읽기/쓰기 락을 거는 것과 유사하다.

3.2 버퍼 락 획득 과정

1. 해시 체인 래치 획득: 버퍼를 찾기 위해 먼저 해시 버킷을 따라가는 체인 구조 접근 시 래치를 사용.
2. 버퍼 블록 탐색: 요청 블록을 체인에서 탐색.
3. 버퍼 락 확인:
   • 이미 다른 프로세스가 Buffer Lock을 Exclusive 모드로 점유 중이라면,
   • 현재 프로세스는 버퍼 락 대기자 목록에 등록되어 대기한다.
4. 락 해제 감지 후 재시도: 기존 프로세스가 락을 해제하면, 대기 중이던 프로세스가 락을 획득한다.

3.3 블록 클린아웃(Block Cleanout)

오라클은 다중 트랜잭션 환경에서 **일관된 읽기(Read Consistency)**를 유지하기 위해 Undo를 사용한다. 하지만 한 번 트랜잭션이 커밋되면, 나중에 그 블록을 읽는 사용자가 해당 정보를 Undo에서 찾지 않도록, 해당 블록에 커밋 정보를 반영하는 작업을 수행한다. 이 작업을 블록 클린아웃이라 부른다.

• 클린아웃은 SELECT 시점이나 백그라운드에서 수행될 수 있다.
• 클린아웃 중에는 블록 수정이 발생하므로 Buffer Lock이 필요하다.

🔄 이 과정은 읽기 작업임에도 일시적으로 블록이 "쓰기 락" 상태로 바뀔 수 있다.

3.4 버퍼 핸들과 Pin 설정

📦 버퍼 핸들(Buffer Handle)이란?

• 버퍼 헤더의 포인터를 통해 참조되는 구조체로, 특정 블록의 접근 제어 정보를 담고 있다.
• 각 프로세스는 버퍼 핸들을 통해 블록을 Pin하여 사용.

🔗 Pinning의 과정:

• 프로세스가 버퍼 핸들을 획득하면 헤더에 있는 소유자 리스트에 연결된다.
• 이 리스트에 등록됨으로써, 해당 프로세스가 블록을 사용하는 동안 다른 프로세스가 블록을 교체하지 못하게 한다.

3.5 Buffer Pinning의 장점

Buffer Pinning은 논리적 블록 읽기(Logical Block Read) 시 발생하는 불필요한 작업을 제거하여 성능을 획기적으로 향상시킨다.

🌟 효과:

• 해시 체인 래치 재획득 없이 블록을 연속해서 참조 가능
• 재검색 없이 캐시에 유지된 블록을 직접 재사용 가능

🔁 CPU와 메모리 자원을 절약하며, 특히 반복적인 액세스가 많은 인덱스 스캔에서 효과적이다.

3.6 인덱스 리프 블록과 Range Scan

📌 인덱스 리프 블록이란?

• B-Tree 인덱스 구조에서, 실제 인덱스 항목들이 저장되는 최하위 블록.
• 데이터베이스에서 인덱스를 통해 WHERE 조건을 만족하는 값을 찾을 때 참조된다.

🔍 Index Range Scan의 I/O 증가 원인

• 인덱스 리프 블록은 여러 개로 나뉘어 있어 여러 블록을 순차적으로 탐색해야 함.
• 매번 새로운 블록을 읽어야 하므로 디스크 I/O가 늘어날 수 있다.

3.7 Buffer Pinning으로 I/O 감소하는 원리

Buffer Pinning은 인덱스 리프 블록 같이 빈번하게 반복 접근되는 블록을 메모리에 고정시켜, 불필요한 디스크 접근을 줄여준다.

🔧 원리 요약:

1. 첫 번째 액세스 시 블록을 버퍼 캐시에 로드하고 핀 설정
2. 이후 동일 세션에서 동일 블록 재접근 시
   • 해시 체인 탐색, 래치 획득 없이 직접 재사용 가능
   • 디스크 I/O 생략 가능

📈 인덱스 기반 검색이 많은 OLTP 환경에서는 Buffer Pinning이 성능 최적화의 핵심이 될 수 있다.

📎 관련 태그

#Oracle #BufferLock #BufferPinning #Latch #BlockCleanout #IndexLeafBlock #RangeScan #LogicalRead #DatabasePerformance #ConcurrencyControl #SGA

1.1 오라클을 배우기 전에

오라클 데이터베이스는 기업에서 가장 널리 사용되는 RDBMS 중 하나로, 안정성과 성능, 확장성을 기반으로 다양한 산업군에서 활용되고 있다. 오라클을 제대로 배우기 위해서는 단순히 SQL을 실행하는 수준을 넘어, 내부 구조와 아키텍처를 이해하는 것이 중요하다. 이 장에서는 오라클의 핵심 구성 요소들과 이들이 어떤 원리로 작동하는지에 대해 직관적으로 설명한다.

1.2 오라클 아키텍처 개요

오라클 데이터베이스는 크게 두 가지 구성 요소로 이루어진다:

• 오라클 인스턴스: 메모리 구조(SGA 등)와 백그라운드 프로세스
• 오라클 데이터베이스: 실제 데이터가 저장되는 파일들의 집합

🔹 인스턴스 = SGA + 백그라운드 프로세스

1.3 SGA(System Global Area)

SGA는 오라클 인스턴스가 작동하기 위해 사용하는 공유 메모리 공간으로, 여러 세션이 동시에 접근하며 SQL 실행, 데이터 캐시, 트랜잭션 처리를 돕는다.

주요 구성 요소:

• Database Buffer Cache: 디스크에서 읽어온 데이터 블록을 임시로 저장. 반복 조회 시 성능 향상.
• Redo Log Buffer: 트랜잭션의 변경 내역을 일시 저장. 복구에 사용됨.
• Shared Pool: SQL 실행 계획, PL/SQL 코드 등을 캐싱하여 파싱 부담 줄임.
• Large/Java Pool: 병렬 처리, Java 실행을 위한 메모리.

💡 Dirty Buffer란? Buffer Cache 내에서 변경되었지만 아직 디스크에 반영되지 않은 블록을 의미. DBWR 프로세스가 디스크로 플러시할 때까지 메모리에 존재.

1.4 Lock 메커니즘

여러 사용자가 동시에 같은 데이터를 사용할 수 있기 때문에, 오라클은 Lock을 통해 데이터 일관성과 무결성을 보장한다.

주요 Lock 종류:

• TX Lock: 행 단위 락. DML 트랜잭션 시 발생.
• TM Lock: 테이블 락. DDL 또는 제약 조건 관련.
• UL Lock: 사용자 정의 락 (DBMS_LOCK 사용).

💥 Deadlock: 서로 상대방의 락을 기다리는 교착 상태. 오라클은 이를 감지하고 하나의 세션을 종료함으로써 해결한다.

1.5 RAC(Real Application Clusters)

RAC는 여러 대의 서버가 하나의 데이터베이스를 동시에 접근할 수 있도록 구성된 클러스터 구조이다. 고가용성과 확장성이 필요한 엔터프라이즈 환경에서 자주 사용된다.

RAC의 장점:

• 고가용성: 한 노드 장애 시 다른 노드로 자동 전환 가능
• 수평 확장성: 서버를 추가하여 성능 향상 가능
• 로드 밸런싱: 요청을 여러 인스턴스로 분산 처리

핵심 기술 - Cache Fusion:

• 각 노드의 SGA를 네트워크로 연결해 메모리 블록을 공유. 데이터 일관성을 유지하면서도 빠른 처리 가능.

1.6 커넥션 풀의 필요성

애플리케이션이 DB와 통신할 때, 매번 새로 커넥션을 만드는 것은 성능적으로 비효율적이다. 커넥션 풀은 일정 수의 DB 커넥션을 미리 만들어 두고 재사용함으로써 다음과 같은 장점을 제공한다:

• 커넥션 생성/소멸 오버헤드 제거
• DB 자원 사용 제한 및 보호
• 다수 사용자 처리 능력 향상

💡 실무에서는 HikariCP(Spring Boot), QueuePool(SQLAlchemy) 등을 통해 커넥션 풀을 관리한다.

1.7 요약

구성 요소설명

이제 오라클의 기본 구조를 이해했으니, 다음 장에서는 SQL 실행 흐름과 옵티마이저에 대해 배워보자.

1. 데이터베이스의 데이터 유형

데이터 유형은 테이블에 저장할 데이터의 형식과 속성을 정의합니다. 잘못된 유형의 데이터를 삽입하려고 하면 데이터베이스는 오류를 발생시킵니다.

데이터 유형의 특징

1. 숫자형:
   • 정수와 실수 데이터를 저장.
   • 예: NUMERIC, DECIMAL, SMALLINT, BIGINT, FLOAT, NUMBER.
2. 문자형:
   • 텍스트 데이터를 저장.
   • 예: CHAR, VARCHAR, VARCHAR2.
3. 날짜/시간형:
   • 날짜와 시간 데이터를 저장.
   • 예: DATE, DATETIME, TIMESTAMP.
4. 기타:
   • 이진 데이터나 특수 데이터를 저장.
   • 예: BLOB, CLOB, JSON.

2. 주요 데이터 유형 설명

데이터 유형설명

3. CHAR와 VARCHAR (VARCHAR2)의 비교

1) 저장 방식

• CHAR:
  • 고정 길이 문자열.
  • 선언된 길이에 미달하는 데이터는 공백으로 채워짐.
  • 예: CHAR(5)에 'A'를 저장하면 실제 데이터는 'A '(공백 포함).
  • 적합한 경우: 고정 길이 데이터(예: 사번, 우편번호).
• VARCHAR (또는 VARCHAR2):
  • 가변 길이 문자열.
  • 선언된 길이에 미달해도 공백을 추가하지 않음.
  • 예: VARCHAR(5)에 'A'를 저장하면 실제 데이터는 'A'.
  • 적합한 경우: 길이가 변할 수 있는 데이터(예: 이름, 주소).

2) 비교 방식

• CHAR:
  • 공백을 포함하여 비교.
  • 예: 'AA'와 'AA '는 동일('AA ' == 'AA ').
• VARCHAR:
  • 공백을 다른 문자로 간주하여 비교.
  • 예: 'AA'와 'AA '는 다름.

3) 저장 공간 효율성

• CHAR:
  • 선언된 길이만큼 공간을 차지.
  • 길이가 고정된 데이터에 적합.
• VARCHAR:
  • 데이터의 실제 길이만큼 공간을 사용.
  • 저장 공간 효율성이 높음.

4. 사용 사례

데이터 유형적합한 데이터

5. 예제 코드

1) CHAR

CREATE TABLE fixed_data (
    id CHAR(10),  -- 고정 길이 10
    code CHAR(5)  -- 고정 길이 5
);

2) VARCHAR

CREATE TABLE variable_data (
    name VARCHAR(50),  -- 최대 50자
    address VARCHAR(100)  -- 최대 100자
);

3) 데이터 비교

-- CHAR 비교
SELECT * FROM fixed_data WHERE id = '12345    '; -- 공백 포함해 동일하게 판단

-- VARCHAR 비교
SELECT * FROM variable_data WHERE name = 'John '; -- 공백 포함 시 다른 값으로 판단

6. 데이터 유형 선택 시 고려사항

1. 데이터 길이:
   • 고정 길이 → CHAR.
   • 가변 길이 → VARCHAR.
2. 저장 공간:
   • 저장 공간 최적화가 필요하면 VARCHAR 추천.
3. 데이터 비교:
   • 공백 무시 → CHAR.
   • 공백 고려 → VARCHAR.

요약

• CHAR: 고정 길이 문자열, 저장 공간 낭비 가능, 공백 포함 비교.
• VARCHAR: 가변 길이 문자열, 저장 공간 효율적, 공백은 다른 문자로 취급.
• 데이터의 특성에 따라 적합한 유형을 선택하는 것이 중요합니다.