[Oracle] Oracle Architecture(3) [SQL 처리 과정]

👇용어정리

• SGA(System Global Area) : 공유메모리 영역으로 데이터를 빠르게 처리하기 위한 공간
• PGA(Program Global Area) : 개별 메모리 공간으로 정렬 중간 데이터, 사용자 세션 정보 등 처리
• Shared Pool: 내가 방금 실행한 SQL 문장이 어떻게 실행될지 짜놓은 '계획서'를 보관합니다. 똑같은 SQL을 던지면 다시 분석 안 하고 여기서 계획서를 꺼내 씁니다
• Library Cache: SQL 문장, 실행 계획을 저장하는 곳
• Data Dictionary Cache:  메타데이터가 저장된 곳으로 테이블 정보, 권한, 통계 (설계도)를 저장하는 곳
• Database Buffer Cache: 하드디스크에서 읽어온 실제 데이터(Table 등)를 보관하는 곳입니다. 똑같은 데이터를 또 찾을 때 디스크까지 안 가고 여기서 바로 꺼내주니 속도가 엄청나게 빨라지죠.
• Redo Log Buffer: 데이터에 변화가 생겼을 때(Insert, Update 등) 그 기록을 잠시 보관합니다. 갑자기 정전이 되어도 이 기록을 보고 복구할 수 있게 해주는 보험 같은 공간입니다.
• Large Pool: 백업이나 대용량 처리처럼 메모리를 많이 잡아먹는 작업을 위해 따로 빼놓은 보조 공간입니다.
• Java Pool: 오라클 데이터베이스 내부에서 자바(Java) 코드를 실행할 때 사용하는 전용 메모리 영역입니다.
• Soft Parse: 이미 누군가 실행했던 SQL이라 실행 계획이 메모리에 있는 경우입니다. CPU를 거의 쓰지 않아 매우 빠릅니다.
• Hard Parse: 처음 들어온 SQL이라 최적화(Optimizer) 과정을 거쳐 실행 계획을 새로 짜야 합니다. CPU와 메모리를 많이 잡아먹는 '비싼' 작업입니다.

---

SQL 수행 처리 과정 

• SGA(System Global Area) : 공유메모리 영역으로 데이터를 빠르게 처리하기 위한 공간
• Shared Pool: 내가 방금 실행한 SQL 문장이 어떻게 실행될지 짜놓은 '계획서'를 보관합니다. 똑같은 SQL을 던지면 다시 분석 안 하고 여기서 계획서를 꺼내 씁니다
• Library Cache: SQL 문장, 실행 계획을 저장하는 곳
• Data Dictionary Cache:  테이블 정보, 권한, 통계 (설계도)를 저장하는 곳
• Database Buffer Cache: 하드디스크에서 읽어온 실제 데이터(Table 등)를 보관하는 곳입니다. 똑같은 데이터를 또 찾을 때 디스크까지 안 가고 여기서 바로 꺼내주니 속도가 엄청나게 빨라지죠.
• Redo Log Buffer: 데이터에 변화가 생겼을 때(Insert, Update 등) 그 기록을 잠시 보관합니다. 갑자기 정전이 되어도 이 기록을 보고 복구할 수 있게 해주는 보험 같은 공간입니다.
• Large Pool: 백업이나 대용량 처리처럼 메모리를 많이 잡아먹는 작업을 위해 따로 빼놓은 보조 공간입니다.
• Java Pool: 오라클 데이터베이스 내부에서 자바(Java) 코드를 실행할 때 사용하는 전용 메모리 영역입니다.

---

 SQL 처리 흐름 (Select 기준)

1. User Process(=Client Process) : 사용자가 SQL을 던집니다.

 

2. Server Process: 요청을 받아 실행 계획을 세우러 갑니다.

 

3. Shared Pool (Library Cache): "이 SQL 전에도 실행한 적 있나?" 확인 (Parsing)

• 👇 SQL 문장과 실행 계획의 존재 여부에 따라 아래 Parsing(파싱) 단계로 넘어갑니다.

• Soft Parse: SQL과 실행 계획 존재 시,  CPU를 거의 쓰지 않아 빠른 처리 가능
  • Soft Parse 실행 순서: 문법 체크 → 권한 체크 → 기존 실행 계획 재사용
• Hard Parse: 처음 들어온 SQL 실행 시, 최적화(Optimizer) 과정을 거쳐 실행 계획을 새로 짜야 합니다. CPU와 메모리를 많이 잡아먹는 작업입니다.
  • Hard Parse 실행 순서: 문법 체크 -> 권한 체크 -> 최적화(Optimizer) -> 실행 계획 생성
  • 바인드 변수 사용 시 Hard Parse의 부하를 획기적으로 낮출 수 있습니다.

• SQL문을 작성 할땐 '대소문자'를 구분하여 Library Cache에 저장하기 때문에 비효율적인 실행에 주의해야 합니다.

 

4. Shared Pool (Data Dictionary Cache): "테이블 이름 맞나? 권한은 있나?" 확인

👇 확인 과정

• 해당 테이블이 어느 데이터 파일의 어느 블록에 있는지, 유저에게 권한은 있는지 등을 ROW(행) 단위로 저장된 메모리에서 빠르게 가져옵니다.
• 만약, Data Dictionary Cache의 공간이 작으면 새로운 정보를 넣으려고 기존 정보를 밀어내고(LRU 알고리즘) 매번 디스크에 있는 시스템 테이블을 뒤져야 합니다.

 

5. ★ Database Buffer Cache (SGA): "내가 찾는 데이터가 이미 메모리에 있나?" 확인

• 있으면 (Cache Hit): 바로 9번으로 점프 (엄청 빠름) 
• 없으면 (Cache Miss): 6번으로 진행.

👇 메모리가 꽉 찼다면?

• 데이터가 메모리에 없으면 LRU알고리즘을 사용합니다. 즉, 가장 오랫동안 쓰지 않은 낡은 데이터 블록을 밀어내고, 방금 읽어온 새 데이터를 그 자리에 넣습니다.
• 자주 쓰이는 테이블인데 자꾸 메모리에서 밀려난다면 Full Table Scan이 너무 많이 일어나고 있는 건 아닌지 의심해봐야 합니다.

6. Data File (Physical I/O): 하드디스크에서 실제 데이터를 읽어옵니다. 디스크I/O 발생

 

7. Database Buffer Cache에 복사: 다음에 또 찾을지 모르니 일단 메모리에 올려둡니다.

 

8. PGA: 필요한 경우 데이터를 정렬하거나 가공합니다.

👇PGA에서 일어나는 일

• ORDER BY나 GROUP BY가 포함된 쿼리라면 PGA(전용 메모리)에서 정렬되거나 가공 됩니다.
• 할당된 PGA보다 작업 데이터가 크면 Temp Tablespace를 빌려쓰게 되며 성능이 급격히 떨어지고 direct path write/read 같은 대기 이벤트가 발생합니다 

9. User에게 전달: 최종 결과를 보여줍니다.

 

 

👇 더 자세한 과정은 아래 글을 통해 볼 수 있습니다.

[Oracle] SQL 수행 구조

 

---

💡시나리오로 감 잡기

1. "특정 시간대만 되면 로그인이 10초 이상 걸려요"

• 관련 단계: 3단계 (Library Cache) & 4단계 (Row Cache)
• 원인 분석: 서비스 오픈 직후나 이벤트 타임에 수만 명이 동시에 접속하면, 오라클은 각 유저의 권한을 확인(Data Dictionary Cache)하고 세션 접속 SQL을 실행(Library Cache)해야 합니다. 이때 Shared Pool 메모리가 부족하거나 하드 파싱이 폭주하면 Library Cache 래치 경합이 발생해 접속 자체가 밀리게 됩니다.
• 대응 : 현재 동시 접속자 폭주로 인해 Shared Pool 내에 경합이 발생하여 커넥션 풀(Connection Pool) 설정을 점검해 불필요한 재접속을 줄여야 합니다.

.👇대표적인 장애

• ORA-04031: unable to allocate X bytes of shared memory
  • 의미: Shared Pool에 새로운 SQL이나 딕셔너리 정보를 올릴 빈 공간이 아예 없는 상태입니다.
• ORA-01000: maximum open cursors exceeded
  • 의미: 한 세션이 너무 많은 SQL(커서)을 열어두고 닫지 않아 메모리를 계속 점유할 때 발생합니다.
• ORA-00018: maximum number of sessions exceeded
  • 의미: DB가 허용하는 최대 접속자 수(Sessions)를 초과하여 더 이상 로그인이 안 되는 상태입니다.

 

2. "어제까지 잘 나오던 결과가 오늘은 너무 느려요"

• 관련 단계: 5단계 (Buffer Cache) & 6단계 (Physical I/O)
• 원인 분석: 어제는 데이터가 메모리(Buffer Cache)에 올라와 있어 빨랐는데(Cache Hit), 오늘 다른 대량 작업 때문에 해당 데이터가 메모리에서 밀려난 경우(Cache Miss)입니다. 이제는 느린 디스크(Physical I/O)에서 다시 읽어와야 하므로 속도가 뚝 떨어집니다.
• 대응: 데이터가 메모리에서 밀려나면서 물리적 I/O가 발생하고 있어 자주 쓰이는 데이터가 Buffer Cache에 오래 머물 수 있도록 튜닝하거나 인덱스 효율을 점검해야 합니다.

👇 대표적인 장애

• I/O TimeoutOS
  • 의미: 레벨에서 디스크 응답이 너무 늦어 연결을 끊음"디스크가 비명을 지르고 있어!" I/O 요청이 너무 몰려 대기 큐가 꽉 찬 상태입니다.
• ORA-01555: Snapshot too old
  • 의미: 쿼리가 디스크에서 데이터를 읽어오는 동안, 다른 작업이 데이터를 바꿔버리고 옛날 정보(Undo)까지 덮어써 버린 경우입니다.
• Checkpoint Not Complete
  • 의미: 로그 파일에 기록은 끝났는데 디스크에 반영이 안 됨, Buffer Cache의 변경 사항을 디스크에 쓰는 속도가 너무 느려 DB 전체가 일시 정지됩니다.

 

3. "단순 조회인데 Temp 테이블스페이스가 꽉 찼다고 에러가 나요"

• 관련 단계: 8단계 (PGA)
• 원인 분석: SELECT 문에 큰 정렬(ORDER BY)이나 해시 조인이 포함된 경우입니다. 서버 프로세스가 할당받은 개인 메모리(PGA)에서 처리가 안 될 만큼 데이터가 크면, 디스크의 Temp 공간을 빌려 쓰다 결국 한계에 다다른 것입니다.
• 대응: SQL 가공 작업이 PGA 범위를 초과하여 Temp 세그먼트를 과도하게 사용하고 있습니다. 쿼리 정렬 조건을 최적화하거나 작업용 메모리 할당량을 검토해야 합니다.

👇대표적인 장애

• ORA-01652: unable to extend temp segment by X in tablespace TEMP
  • 의미: 정렬이나 조인을 하다가 Temp 공간을 다 써버려서 쿼리가 중단된 상태입니다.
• ORA-04030: out of process memory when trying to allocate X bytes
  • 의미: OS 레벨에서 서버 프로세스에 줄 메모리가 더 이상 없을 때 발생합니다.
• ORA-12801: error signaled in parallel query server
  • 의미: 여러 명이 나눠서 정렬하다가 한쪽에서 Temp를 다 써버려 전체 병렬 쿼리가 죽는 경우입니다.
• direct path write (temp) - V$SESSION_WAIT 성능 뷰
  • 의미: PGA가 부족해서 정렬 중인 데이터를 Temp 공간(디스크)에 열심히 쓰고 있는 상태입니다. 이게 보이면 "아, 메모리에서 처리가 안 되고 있구나"라고 생각하시면 됩니다.
• direct path read (temp) - V$SESSION_WAIT 성능 뷰
  • 의미: Temp에 써놓은 데이터를 정렬을 마치고 다시 읽어오는 상태입니다.
• enq: TS - contention - V$SESSION_WAIT 성능 뷰
  • 의미: 여러 사용자가 동시에 Temp 공간을 할당받으려고 경쟁할 때 발생합니다.

 

4. "분명 데이터를 수정했는데 조회가 안 돼요 (Snapshot too old)"

• 관련 단계: 5단계 (Buffer Cache) & Undo 영역
• 원인 분석: 대량 조회를 수행(Buffer Cache)하는 중에 다른 세션이 데이터를 계속 수정하고 커밋합니다. 오라클은 '조회를 시작한 시점'의 데이터를 보여주기 위해 Undo 영역을 뒤지는데, 조회가 너무 길어져서 그 Undo 데이터가 덮어씌워진 경우입니다.
• 대응: 장시간 수행되는 쿼리가 읽기 일관성(Read Consistency)을 유지하지 못해 ORA-01555가 발생하여 Undo Retention을 늘리거나 쿼리 수행 시간을 단축해야 합니다.

👇대표적인 장애

• ORA-01555: snapshot too old
  • 의미: 쿼리 시작 시점의 데이터를 보러 Undo(롤백) 세그먼트로 갔는데, 이미 다른 데이터로 덮어씌워져서 과거 데이터를 찾을 수 없는 상태입니다.