SQLP 오답노트 2장

✔ Oracle AutoTrace 항목과 SQL 트레이스 항목

1. Oracle AutoTrace와 SQL Trace 비교

구분	Oracle AutoTrace	SQL Trace
목적	SQL의 실행 계획과 통계 정보 빠르게 확인	SQL 실행 시 내부 처리 흐름과 성능 병목 분석
사용 대상	주로 개발자/튜너가 특정 SQL 분석 시 사용	상세한 성능 분석, 트러블슈팅 시 사용
접근 방식	SQL*Plus(오라클에서 제공하는 기본 CLI 도구), TOAD 등의 GUI 툴에서 SET AUTOTRACE ON	오라클 데이터베이스에 접속 가능한 SQL 클라이언트, DB 내부 세션 또는 프로시저 단에서 ALTER SESSION SET sql_trace = TRUE 등으로 설정
결과 위치	화면에 직접 출력	Trace 파일로 저장 (서버 측)

* SET AUTOTRACE ON은 실행 결과, 실행 계획, 통계 정보를 화면에 보여 주는 명령어.

2. Oracle AutoTrace와 SQL Trace 항목 비교

AutoTrace 항목	SQL Trace 항목	내용
consistent gets	query	일관된 읽기 (논리적 블록 읽기)
physical reads	disk	디스크에서 실제로 읽은 블록 수
buffer gets	gets	전체 블록 접근 수 (논리 + 물리 포함)
rows processed	fetch rows	SQL 실행 시 반환된 총 행(row) 수
SQL*Net roundtrips to/from client	fetch call count	클라이언트와 서버 간 round-trip 횟수
recursive calls	recursive calls	내부적으로 수행된 SQL 호출 수
parse calls	parse count	SQL을 파싱한 횟수
CPU time	cpu	SQL 실행 시 사용된 CPU 시간 (초)
elapsed time	elapsed	SQL 실행에 걸린 총 시간 (초)
executions	execute count	SQL이 실행된 횟수

✔ 10046 Event

1. 10046 Event란?

• Oracle 7.3 이상 버전에서 사용할 수 있는 SQL Trace 생성 이벤트
• 실행한 SQL에 대해서 실행 계획 및 SQL 실행에 따른 여러 리소스의 사용 정보 및 실행에 사용한 Bind 정보, 실행 과정에서 발생되는 Wait Event 정보를 기록
• 우리가 흔히 아는 기본 SQL Trace는 Level 1

2. 10046 Trace Level 별 정보

• Level 0: SQL_TRACE = FALSE와 동일
• Level 1: SQL_TRACE = TRUE와 동일 (일반적인 Trace 정보 제공)
• Level 4: Level 1 + Bind 변수 정보
• Level 8: Level 1 + Wait Event 정보
• Level 12: Level 1 + Bind 변수 정보 + Wait Event 정보

3. Event 활성/비활성화

해당 이벤트의 활성화/비활성화는 세션 레벨, 시스템 레벨 등으로 나눌 수 있다.

활성화

1) 세션 레벨

SQL> ALTER SESSION SET SQL_TRACE=TRUE;`

-- SYS나 SYSTEM 유저가 아닌 일반 유저로 실행 시 권한 불충분 오류가 발생함으로 alter session 권한을 부여해야 한다.
SQL> grant alter session to 유저명;

2) 시스템 레벨

SQL> ALTER SYSTEM SET SQL_TRACE=TRUE;

-- system 단위로 event 설정 시 모든 SQL이 Trace가 생성됨으로 시스템 부하가 심각하게 발생할 수 있음
-- 그렇기 때문에 system 단위는 특별한 이유가 있을 경우 매우 한시적으로 설정한 뒤 바로 비활성화하여야 한다.

3) 트레이스 레벨 레벨

SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT FOREVER, LEVEL 12';

--레벨을 지정하면 SQL_TRACE = TRUE를 해 주지 않아도 활성화됨

4) 트레이스 파일 네이밍 지정

SQL> ALTER SESSION SET TRACEFILE_IDENTIFIER='MYTRACE'; 

--트레이스 파일의 네이밍은 필수 지정은 아니지만 USER TRACE 파일을 찾기 쉽게 하려면 네이밍을 하는 게 좋다.
-- 다수 유저가 반복적으로 10046을 생성하는 환경이면 내가 수행한 Trace를 찾기 힘들기 때문에 별도의 네이밍을 지정해 Trace 파일을 손쉽게 찾을 수 있게 도와주는 것

비활성화

--SQL_TRACE=TRUE 로 실행했을 경우
SQL> ALTER SESSION SET SQL_TRACE=FALSE;


--LEVEL 지정 하여 활성화 하였을 경우
SQL> ALTER SESSION SET EVENTS '10046 TRACE NAME CONTEXT OFF';

✔ SQL 하드 파싱

1. 하드 파싱이란?

• SQL 문장을 처음 보거나 기존 캐시에 없을 때 수행되는 전체 파싱 작업
• 오라클은 SQL 문장 수행 전 라이브러리 캐시에서 해당 SQL이 있는지 없는지 조회하고 만약 없으면 하드 파싱 처리한다.

2. 하드 파싱 과정

1) SQL 문장 문법 분석 (Syntax Check)
2) SQL 문장 의미 분석 (Semantic Check)
3) 객체 이름 확인 (Object resolution)
4) 권한 확인 (Privilege Check)
5) 실행 계획 생성 (Optimizer - CBO or RBO)
6) 실행 계획을 공유 풀에 저장
7) 커서(Cursor) 생성
해당 단계는 모두 CPU와 메모리를 많이 쓰며, 동시 사용자 수가 많으면 병목 현상이 발생한다.

3. 하드 파싱이 문제가 되는 이유

• 높은 CPU 사용량
• 라이브러리 캐시 경합 (Latch Contention) 발생
• Parse-to-Execute 비율 증가 -> 처리 속도 저하
• 커서 캐시 누수 -> 메모리 낭비

* 경합이란 여러 세션이 동시에 동일한 리소스에 접근하고자 할 때 서로 기다리는 현상을 말한다. 이때 라이브러리 캐시 경합이란 여러 세션이 동일한 SQL문을 동시에 실행하거나 하드 파싱으로 공유 풀에 새 커서를 넣으려는 순간 발생한다. 또한 라이브러리 캐시는 데이터를 동기화해 줘야 하므로 여러 세션이 동시에 접근하면 경합이 생긴다.

4. 관련 있는 주요 이벤트

이벤트 명	설명
library cache lock	커서 생성 중 다른 세션이 해당 SQL을 점유하고 있어 대기
library cache pin	커서가 고정된 상태로 다른 세션에서 수정을 못하게 대기
cursor: pin S wait on X	공유 커서를 얻으려다 대기 (경합 발생 시 자주 보임)
latch: shared pool	공유 풀에 접근하려는 경합 발생 (메모리 부족 시)

17 번 문제에서 1 번과 2 번 모두 답인 것처럼 보였지만 1 번의 단계가 직접적인 단계이고, 2 번의 단계는 후속 단계.
latch: shared pool 하드 코딩 과정에서 가장 먼저 발생하는 경합이 래치이다. SQL이 처음 실행되려 할 때 공유 풀에서 커서를 저장할 공간을 찾거나 기존 커서와 비교하려고 할 때 shared pool 접근 권한이 필요하기 때문. 또한 래치는 메모리 보호용 잠금으로, 파싱 중 반드시 거쳐야 하는 단계
library cache lock은 하드 파싱보다는 객체 수준의 공유 문제라고 봐야 한다.

✔ AWR (Automatic Workload Repository)

• Oracle이 제공하는 성능 관련 통계 정보를 자동으로 수집하고 저장하는 저장소
• 데이터베이스의 성능 이력을 주기적으로 자동 저장하는 기능

1. 수집하는 정보

AWR은 주기적으로(기본: 1시간 간격) 다음과 같은 성능 정보를 수집한다.

• Wait Events: 세션이 대기한 이벤트들 (예: latch, I/O 등)
• System statistics: CPU, 메모리 사용량 등 시스템 레벨 통계
• Active Session History (ASH): 세션의 활동 이력 (활동 중인 세션 추적)
• SQL Statistics: Top SQL, 실행 횟수, 경과 시간 등
• Segment Statistics: 많이 사용된 테이블/인덱스 정보
• Instance Activity Stats: 전체 인스턴스 단위 통계 (parse 수, redo 크기 등)
• Undo, Latch, Buffer Stats: Undo 사용, 래치, 버퍼 읽기 등 관련 정보

2. AWR Report

AWR Report란 두 시점 사이의 성능 스냅샷을 비교 분석한 보고서이다.

-- SQL*Plus 접속 후 실행
@?/rdbms/admin/awrrpt.sql

• DB Time, DB CPU: 총 응답 시간, CPU 사용 시간
• Top 5 Timed Events: 가장 많은 시간을 소비한 대기 이벤트
• SQL ordered by Elapsed Time: 실행 시간이 긴 SQL 목록
• Instance Efficiency %: 캐시 히트율, 파싱 효율성 등
• Buffer Pool Advisory: 버퍼 캐시 사이즈 대비 효과
• Segments by Logical Reads: 가장 많이 읽힌 세그먼트(테이블/인덱스)
• Wait Class Breakdown: 대기 유형별 비중 (I/O, Concurrency 등)
• ADDM Recommendations: (옵션) Oracle 자동 성능 진단 결과