2023년 12월 8일 5교시 오라클 구조 1. UP, SP, ShP

2023년 12월 8일 5교시

 

(칠판 그림)

 

★ 데이터를 저장하는 논리적인 관점 / 물리적인 관점

 

     DB

      ↑

tablespace            ←           Data file

      ↑

segment

      ↑                                          ↑

   extent

      ↑

   block                  ←             OS file

 

 (논리적)                               (물리적)

 

 

(논리적 관점)

- block 이 모여 extent 가 만들어지고

- extent 가 모여 segment 가 만들어지고

- segment 가 모여 tablespace 가 만들어지고

- tablespace 가 모여 DB 가 만들어진다.

 

- 오라클의 I/O 단위는 블록 block 단위인데

- 블록의 크기는 2k, 4k, 8k, 16k, 32k (기본값은 8k) 가 있다.

 

(물리적 관점)

실제 데이터는 디스크 안에 OS file로 저장이 되는데

하나의 테이블스페이스는 여러 개의 데이터 파일로 구성이 되고 ( tablespace            ←           Data file)

데이터 파일은 OS 블록으로 만들어진다. ( Data file                  ←             OS file)

(OS 블록의 크기는 512byte 인데, 오라클은 그 512byte 짜리 블록을 여러 개 가져다 8k 짜리 블록을 만든다.)

 

★ extent : 관련성이 있는 block 들을 모아 놓은 것

 

emp 테이블이 쓰는 block 은 걔네들끼리, dept 테이블이 쓰는 block 들은 또 걔네들끼리

이렇게 끼리끼리 모아서 관리하면 > full table scan 할 때도, table 을 drop 혹은 truncate 할 때도 성능이 훨씬 좋아진다.

 

Segment 는 책, extent 는 장(chapter), block 은 page 라고 생각하면 된다.

그리고 책을 아무렇게 꽂아 놓지 않고 비슷한 분야의 책들끼리 분류해서 꽂아 놓듯이

segment 도 아무렇게나 어질러 놓지 않고 비슷한 것들끼리 분류해서 모아 놓는다.

 

 

1. user process 와 server process

 

user process = SQL 문장을 던지는 SQL+ 혹은 SQL developer
server process = 유저의 비서 = SQL 문 처리기

server process 안에는 PGA (Program Global Area) 라는 메모리가 있고
그 안에는 커서라는 SQL 문 실행 메모리 영역이 있다.
서버 프로세스는 유저가 전달한 SQL 문장을 커서에 받아서
parse, bind, execute 등의 여러 가지 작업을 한다.

 

예를 들어, 유저가
select * from hr.employees
where employee_id = 100;
이라는 SQL 문장을 던졌다고 해보자.

그러면 서버 프로세스에서는 저 문장을 받아서 PGA 안의 커서로 전달하고
커서는 그 문장을 가지고 여러 가지 작업을 한다.

 

★ 1단계 parse 작업
- syntax (문법) 체크: select, from, where 등 철자나 문법 등이 맞는지 먼저 확인
- semantic : 의미 분석, 권한 체크
  (semantic check 는 Data Dictionary Cache 를 참고해서 한다)
    - hr 이 db 내에 생성되어 있는 유저인지, hr 이 가진 object 안에 employees 가 있는지 등을 체크하고
    - object type 을 체크 (저게 실제 테이블인지 아니면 view 인지 등) 하고
    - 만약 테이블이라면 - 저 테이블이 디스크의 어느 스토리지에 들어 있는지 체크
    - 우리는 테이블에 있는 모든 컬럼들을 다 보고 싶을 때 * 기호를 쓰지만
      사실 * 이라는 컬럼 이름은 없다.
      * 기호가 들어오면 유저 프로세스는 그걸
      저 테이블에 있는 컬럼 이름으로 다 바꾸는 작업을 해야 한다.
    - 만약 scott 이라는 유저가 저 select 문장을 던진 경우
      scott이 테이블에 대한 권한이 있는지도 체크한다.

 

 

그런데, 이렇게 semantic 작업을 하려면

유저에 대한 정보, 유저가 가지고 있는 object 정보, 권한 정보 등을 누군가는 가지고 있어야 하는데

그 정보를 가지고 있는 것이 바로 Data Dictionary Cache

 

Data Dictionary 는 디스크 안에 있는데
그 디스크에 있는 내용에 우리가 access 는 할 수 있지만
유저들이 SQL 문장을 던질 때마다 디스크에서 Data Dictionary 를 찾아보려니 너무 I/O가 많이 발생한다.
그래서 Data Dictionary 를 메모리에 올려놓은 것이 Data Dictionary Cache

 

 

Data Dictionary Cache:

디스크 안의 system tablespace에 있는 딕셔너리 정보들을 메모리에 올려놓은 것

 

Data Dictionary Cache 는 Shared pool 이라는 메모리 구조 안에 들어 있으므로

Shared pool 을 먼저 살펴보자.

 

 

★ Shared Pool

 

Shared pool 이라는 메모리 구조는 900개가 넘는 sub-component 메모리 구조로 이루어져 있는데

우리는 그 중 두 가지만 기억하면 된다.

 

1. Library Cache

2. Data Dictionary Cache (row cache)

 

유저가 SQL 문장을 던졌을 경우 Server process 는 parse 작업을 하면서

저 Data Dictionary Cache 를 가장 먼저 읽어들이게 된다.

(Semantic 과 권한 체크를 하기 위해서는 Dictionary 정보들이 필요하므로)

 

1. Library Cache

 

유저가 SQL 문장을 던졌을 경우 Server process 는

동일한 SQL 문이 Shared pool 메모리 안 Library Cache 에 있는지 조회해 본다.

(왜? 실행 계획을 sharing 하기 위해서.)

(실행 계획이란? 필요한 데이터를 찾는 방법을 결정하는 계획)

 

select * from hr.employees

where employee_id = 100;

이 문장을 수행해야 한다고 할 때

필요한 데이터를 디스크에서 어떻게 찾을지를 결정해야 하는데

(row id scan / full table scan / index scan 등)

그걸 Library Cache 를 참고해서 결정한다.

 

찾는 문장이 Library Cache 에 있으면 실행 계획을 Sharing 하고 (soft parsing)

없으면 hard parsing 이 발생한다. (일이 많아진다)

 

hard parsing 이 발생하면

① 일단 실행 계획을 만들어야 하고

② 다음 번에 그 실행 계획을 또 쓸 수 있으므로 메모리에 담아 놓아야 하는데

③ 그러려면 메모리 확보를 해야 한다.

(성능이 매우 떨어진다)

 

그런데 꼭 hard parsing 에서만 문제가 발생하는 것은 아니고

soft parsing 이 발생할 때도 문제가 발생할 수 있는데 (Latch 라는, 메모리의 lock 개념)

이것도 나중에 가서 더 자세히 배우게 된다.

 

(select * from hr.employees

where employee_id = 100;

위의 SQL 문장을 수행하는데

숫자 100 부분이 상수로 처리되어 있으면 무조건 hard parsing 이 발생한다.

그래서 저 숫자 부분을 변수로 처리해서 soft parsing 을 유도시키기 위해 배운 것이 PL/SQL)

만약 저 숫자 부분이 변수로 처리되어 있다면

parse 단계는 끝나고 bind 단계로 넘어간다.

 

★ 2. bind 작업

- 변수에 실제 값이 입력되는 단계

- 실행 계획을 만들 때 실제 값을 가지고 만드는 것이 아니라 예측을 해서 만든다.

  그래서 값의 분포도가 중요한데

 

select * from hr.employees

where employee_id = 100;

 

- employee_id 라는 컬럼을 보니까 유일한 값들만 들어 있다면 (primary key / unique index)

- 각각의 값들이 오직 하나

- 변수 처리가 되어 있더라도 테이블 전체 중에서 하나만 찾는 계획을 세우면 되므로

  실행 계획을 명확하게 잡을 수 있다.

 

- 하지만 만약 부서 코드를 변수 처리할 경우

  어떤 부서에는 2명, 어떤 부서에는 70명... 하는 식으로

  분포도가 균일하지 않다.

  이런 경우에는실행 계획이 명확하게 만들어질 수 없다.

- 그래서 변수 처리는 값의 분포도에 따라 할지 말지를 결정해야 한다. (무조건 하는 것이 아니다)

- 분포도가 너무 들쭉날쭉하면 실행 계획 공유는 차라리 안 하느니만 못하다.

 

- 실제 값은 bind 단계에서 들어간다. (이미 실행 계획이 만들어져 있는 상태에서 수행)

 

 

★ 3. execute 작업

- 실행 단계

- 이 단계에서 Data Buffer Cache 를 읽어들인다.

- 찾고자 하는 row 가 들어 있는 block 이 DBC 에 있는지 조회해 보는데

  8K 짜리 block 들이 DBC에 어마어마하게 많이 들어 있다.

  그래서 DBC 에서 내가 원하는 row 를 찾는 것 자체도 쉽지 않기 때문에 

  DBC를 Shared pool 이 관리한다. (퍼포먼스 튜닝 과정에 더 자세히 나온다)

 

- 찾고자 하는 데이터가 메모리에 있는지 먼저 조회해보는 것을 논리적인 I/O 라고 하는데
  논리적인 I/O 를 통해 원하는 데이터를 찾지 못하면 그제서야 물리적인 I/O 가 발생한다.

 

select * from hr.employees

where employee_id = 100;

100 번 사원의 정보를 메모리에서 찾아봤는데 만약 없으면

디스크로 찾아가게 되는데

찾아가는 정보는 실행 계획이 가지고 있다.

그래서 실행 계획은 해당 데이터를 찾아서 메모리 block 에 올려놓게 되는데

올리려면 DBC 에 free 메모리 공간이 있어야 한다.

(free 메모리 공간이 없으면 I/O 를 유발할 수가 없다)

 

★ 패치

 

- 100번 사원의 정보가 들어있는 block을 찾았다고 하자.
  그런데 해당 블록 안에는 100번 사원의 row뿐만 아니라 다른 row 들도 잔뜩 들어 있다.
- Segment 는 책, extent 는 장(chapter), block 은 page 이라고 했던 것처럼 row 는 문장이라고 생각하면 되는데
  페이지 안에 하나의 문장만 써 있지 않듯이 블록 안에도 내가 원하는 row 하나만 들어 있지 않다.
- 그래서 내가 원하는 정보가 들어 있는 블록 안에서, 내가 원하는 row 하나만 뽑아내는 작업을 해야 되는데
  그 뽑아내는 작업이 바로 패치
- 패치란 : DBC 안에 올라와 있는 블록 안에서 내가 필요한 row 만 커서로 가져오고 > User Process 에게 전달하는 것

 

참고로, 필요한 데이터를 디스크에서 찾는 방식 (실행 계획)
을 결정하는 것이 Optimizer 인데

Optimizer 는
1) 데이터 처리 방법을 결정하고
2) join 절이 들어올 경우 join 의 순서를 결정하고
3) join 의 방법을 결정한다.

달랑 3가지 일밖에 안 하지만
이게 곧 문장 튜닝의 핵심이라고 보면 된다.