SAG(System Global Area) shared pool(공유 메모리), SELECT문 처리과정

■ SGA(System Global Area) 

정의: Oracle 데이터베이스에서 사용되는 메모리 영역

- 데이터베이스 인스턴스를 실행하는 동안 메모리에 할당되며, 여러 중요한 구성 요소를 포함하고 있다.

- 모든 user process 가 공유하는 공유 메모리 영역으로,  데이터베이스의 성능과 안정성에 영향을 미친다.

■ shared pool(공유메모리)
- ★libarary cache : SQL, PL/SQL문이 저장되어 있는 메모리 -> why? 실행계획
- ★data dictionary cache : 딕셔러리 정보들이 저장되어 있는 메모리
- session data 정보 : 사용자 세션에 대한 SQL 문장, 파싱된 쿼리 계획, 사용자의 테이블과 인덱스에 대한 엑세스 경로 정보 등 
예시) v$session data 정보 → kill 했던 부분
- shared server 환경을 사용할 경우 UGA(User Global Area)가 포함된다.
- ☆ shared_pool_size

 

---

 

◎ 실행계획

- SQL문이 어떻게 실행될지 계획을 세운 것.
- 데이터 처리 방법, 조인 방법 및 순서 등을 결정하여 최적의 성능을 위해 사용됨.

1. SQL문 수행 전, 실행계획 없이는 SQL문이 실행되지 않음.
2. 데이터 처리 방법 결정
   ▶ Rowid Scan
     - 사용자 rowid를 이용한 접근.
     - 인덱스에 의한 rowid (Primary key, Unique key 제약 조건 설정 시 생성) 접근.
   ▶ Full Table Scan
     - 전체 테이블을 스캔하여 처리.
3. 조인 방법
    1) Hash
    2) Nested Loop
    3) Sort Merge
4. 조인 순서 결정:
    1) 테이블 나열 순서에 따라 결정.
    2) 조인을 수행할 테이블이 3개면 3! (3x2x1) 경우의 수만큼 결정.
       ex) FROM a, b, c -> a,b,c / a,c,b / b,a,c / b,c,a / c,a,b / c,b,a

---

 

 

 

■ SELECT 처리과정 

1. Parse (파싱) 단계

1) 문법, 의미분석, 권한 체크 

▶ 문법 체크 ⓐ server process에서 수행 ⓑ SQL 문법을 체크하여 올바른 문장인 확인
▶ 의미분석 (Semantic Analysis) ⓐ 데이터 사전 캐시 (Data Dictionary Cache)에서 수행 ⓑ 사용자, 테이블 타입, 뷰, 컬럼의 존재 여부 등을 확인하여 의미를 분석
▶ 권한 체크 ⓐ 데이터 사전 캐시 (Data Dictionary Cache)에서 수행 ⓑ 사용자가 해당 작업을 수행할 권한이 있는지 확인
▷ 시퀀스 번호 (Data Dictionary Cache)에서 오류 발생 가능성 ⓐ 캐시 기능이 미리 만들어져 있어야 함 ⓑ 오류 발생이 빈번한데, 캐시를 미리 만들어두면 성능 향상에 도움이 됨

 

2-1) Soft Parsing과 Library Cache 검색

▶ Soft Parsing 발생
  ⓐ 동일한 SQL 문장이 실행되면서 새로운 정렬 작업이 필요한 경우 Soft Parsing이 발생.
  ⓑ Soft Parsing은 새로운 정렬 작업에 대한 실행계획을 수립하는 과정.

▶ Library Cache 조회 ('검색용')
  ⓐ 동일한 SQL 문장이 Shared Pool 메모리에 Library Cache에 있는지 조회.
  ⓑ 왜냐하면 동일한 SQL 문장이 있다면 실행계획을 공유하기 위함.

▶ Library Cache 'Latch' 잡고 검색
  ⓐ Library Cache에서 검색을 위해 'Latch'를 잡아야 함.
  ⓑ 라치는 CPU에 종속되어 있어서 무조건 'latch'를 잡고 검색해야 함.
  ⓒ 예시로 전화번호부(두꺼운 책)를 Library Cache로 비유하면, 전화번호 목록은 hash value, 원하는 전화번호 조회는 latch에 해당.
  ⓓ Latch를 잡고 검색하는 동안 다른 세션이 접근하지 못하게 됨.

▶ Latch와 'Wait Event'
  ⓐ Latch를 잡으면 'wait event'가 발생. 이를 'latch wait event'라 함.
  ⓑ 이는 메모리 공간을 관리하는 것이 아니라 대기 이벤트 경합이 발생하는 상황.
  ⓓ 빈번한 'latch wait event'가 발생하면 성능 문제 발생 가능성.
  ⓓ 예시로, 누군가가 latch를 잡고 있고, 다른 세션이 대기 중인 상황.

▶ 다수의 세션이 동일한 SQL 문장을 실행할 때, 실행계획을 공유하기 위해 Library Cache를 검색함.
▶ Library Cache 검색 시에 Latch를 잡아야 하는데, 이는 CPU에 종속된 작업으로 다른 세션이 대기해야 하는 상황이 발생함.
▶ 이러한 'latch wait event'는 성능 이슈의 원인이 될 수 있으며, 빈번하게 발생할 경우에는 문제가 됨.
▶ 상황에 따라 변수 처리, 테이블의 유저명 존재 여부 등의 문제를 해결하여 'latch wait event'를 최소화해야 함.

2-2) Soft Parsing 실패 및 Hard Parsing 발생

▶ Soft Parsing 실패
  - 동일한 SQL 문장이 Shared Pool 메모리에 Library Cache에 존재하지 않으면 Soft Parsing 실패.
  - 이는 이전에 실행한 동일한 SQL의 실행계획이 공유되지 않았음을 의미.

▶ Hard Parsing 발생
  - Soft Parsing 실패 시, 동일한 SQL 문장에 대해 처음부터 실행계획을 새로 수립하는 과정이 필요한데, 이를 Hard Parsing이라 함.
  - 새로운 SQL 문장에 대한 문법 체크, 의미 분석, 권한 체크 등을 다시 수행함.

▶ Soft Parsing은 이전에 수행한 SQL의 실행계획을 공유하여 재사용하는 것.
▶ Soft Parsing이 실패하면, 동일한 SQL 문장에 대해 처음부터 실행계획을 새로 수립하는 Hard Parsing이 발생함.
▶ Hard Parsing은 CPU와 자원 소모가 크기 때문에 Soft Parsing이 가능하면서 더 효율적. Soft Parsing이 실패할 때마다 Hard Parsing이 반복되면 성능에 영향을 미칠 수 있음. 

 

3) Hard Parsing과 메모리 확보

▶ Hard Parsing 이란?
   - 동일한 SQL 문장에 대해 이전 실행계획을 공유하지 못하고 새로운 실행계획을 생성하는 과정
   - 실행계획을 새롭게 만들어야 하는데, 이때 메모리 공간을 화복해야 함.

▶ 메모리 공간 확보와 'Latch'
   - Hard Parsing 시에는 메모리 공간을 확보하기 위해 'latch'를 잡아야 함.
   - 'latch'는 CUP에 종속되어 무조건 잡아야 함.

   - 'latch wait event'가 발생할 수 있음. (메모리 공간을 확보하기 위한 경합)

▶ 프리 공간 부족 시 ORA-04031 오류 발생 가능
   - 프리 공간이 부족하면 Hard Parsing 시 ORA-04031 오류 발생 가능.
   - 단편화로 인해 프리 공간이 부족할 경우 문제 발생.

▶ 예시 상황)
  - 프리 조각이 7K, 10K, 1K, 100 byte로 있음.
  - 필요한 프리 공간은 12K인데 사용 가능한 조각이 없음.   
해결 방법)
  - 8K의 프리 공간이 필요하다면 10K 프리 공간을 8K로 확보하고 2K 프리 공간으로 남김.
  - 그 후, 7K, 2K, 1K, 100 byte와 같은 상황에서 8K 프리 공간을 확보하기 위해 latch를 잡고 프리 검색을 시도. 
  - 하지만 없어서 ORA-04031 오류가 발생하는 경우, 문장 튜닝(변수 처리 하거나 hard parsing 빈번히 발생, 무조건 X)이나 단편화 해결, 프로그램(PL/SQL, JAVA 등)으로 코드 작성 등을 고려하여 해결 방법 모색.

▶ LCO 생성과 Wait Event
- LCO 생성을 위해 'library cache lock'을 획득해야 하는데, 경합이 발생하면 'wait event'가 발생함.
- 'library cache lock'을 획득하면 LCO가 생성되고, 이후 'library cache pin'을 사용하여 실행계획을 생성함.

http://gurubee.net/lecture/3061

2. Bind (바인드) - 옵션 단계

- 변수 처리된 SQL문의 변수에 실제값이 입력되는 단계.
- 데이터의 건수에 따라 실행계획의 공유 여부가 달라짐.
- PL/SQL로 로직을 구현하여 실행계획을 효율적으로 관리해야 함.

 

-- Primary key의 실행계획
SELECT *
FROM hr.employees
WHERE employee_id = :id; => Primary Key를 이용하여 검색하므로 실행계획이 공유될 가능성이 높음.

-- 불균등한 실행계획 생성(data의 건수 다름)
SELECT *
FROM hr.employees
WHERE department_id = :id; => department_id를 이용하여 검색하므로 데이터의 건수에 따라 실행계획이 불균등하게 생성될 가능성이 있음.

▶ PL/SQL 로직 구현의 중요성
- 데이터의 특성에 따라 바인드 변수를 적절히 활용하여 실행계획의 공유 여부를 관리해야 함.
- 효율적인 실행계획을 위해 필요에 따라 Hard Parsing을 유도하는 등의 로직을 PL/SQL에서 구현해야 함.

 

3. Execute (실행) 단계

▶ 'library cache lock'과 'library cache pin'이 'shared mode'로 변환되고 SQL문을 실행함.
  - 예시) LCO를 shared mode에서 exclusive로 변환할 때는 테이블이 drop될 때나 PL/SQL문을 컴파일할 경우 등 exclusive 모드가 필요한 상황에서 'library cache lock'이 발생할 수 있음.

Tip) Index 생성, 컬럼 추가, 테이블의 통계 수집(dba_tables, user_tables), 제약조건 추가(primary key, unique key) 등의 작업은 실행계획을 무효화하고 재생성할 필요가 있음.

▶ 블록 I/O 발생
  - Data Buffer Cache 영역을 확인하며 필요한 데이터를 로딩함.
  - Cursor에 사용자에게 결과물을 생성하기 위한 데이터가 담겨 있음.
  - 위에서 언급한 작업들은 데이터 구조를 변경하므로 실행계획을 다시 생성해야 함.
  - 이러한 작업이 수행되면 블록 I/O가 발생하여 필요한 데이터를 Data Buffer Cache로 로딩함.
  - Cursor는 이 데이터를 활용하여 사용자에게 결과를 생성하게 됨.

4. Fetch (검색 결과 가져오기) 단계

▶ 'library cache lock'을 'null 모드'로 변환하고 'library cache pin' 해제한다.

     (해석: 어떠한 작업도 자유롭게 수행할 수 있는 상태로 변환.)

▶ Library Cache Lock이 null 모드로 변환되면 다른 세션이나 트랜잭션이 필요에 따라 실행 계획을 변경하거나 추가 작업을 수행할 수 있게 됨.
▶ Artive set 결과를 user process 에 전달한다.

    (해석: select문 의 결과물을 사용자 프로세스에게 전송.)

▶ Fetch 단계에서는 이미 실행된 SQL 쿼리에 대한 결과를 사용자에게 반환함.
▶  Active Set은 현재 실행 중인 SQL 문장에 의해 생성된 결과 집합을 나타냄.
▶  이 결과 집합은 User Process로 전달되어 최종적으로 사용자에게 보여지게 됨.