Simple Record

SQL은 Structed Query Language : SQL은 구조적, 집합적, 선언적 질의어

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 하능한 상태로 만드는 전 과정 = SQL 최적화

SQL 최적화

1. SQL 파싱 (SQL 파서가 수행)
   1) 파싱 트리 생성 : SQL을 개별 구성 요소로 분석 및 파싱 트리 생성
   2) Syntax 체크 : 문법적 오류 확인 : 사용할 수 없는 키워드, 순서, 누락된 키워드 확인
   3) Semantic 체크 : 의미상 오류 없는지 확인. 오브젝트 권한, 존재하지 않는 테이블 또는 컬럼 사용 여부 확인
2. SQL 최적화 (SQL 옵티마이저가 수행)
   - 미리 수집된 시스템 및 오브젝트 통계정보를 바탕으로 실행 경로 생성 및 선택(1개)
3. 로우 소스 생성 (로우 소스 생성기가 수행)
   - 실행 경로를 바탕으로 실행 가능한 코드 및 프로시저 형태로 포맷팅

SQL 옵티마이저 : 가장 효율적으로 수행할 수 있는 최적의 데이터 엑세스 경로를 선택해주는 DBMS 핵심 엔진

1. 후보군이 될 만한 실행계획들을 찾아낸다

2. 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상 비용 산정

3. 최저 비용을 나타내는 실행계획 선택

서버 프로세스는 클라이언트와 서버와의 통신을 위해 존재하는 프로세스로 커넥션 당 1개씩 생성 된다. 백그라운드 프로세스 는 DBWR, LGWR, PMON, SMON 같이 공통 기능을 위한 프로세스 이다

비용(Cost) : 쿼리를 수행하는 동안 발생할 것으로 예상하는 I/O 횟수 또는 예상 소요시간을 표현한 값

* 실측치가 아니므로 실제 수행할 때 발생하는 I/O 또는 시간과 많은 차이가 난다

옵티마이저 힌트 (지시 강제임) : 옵티마이저가 정확하지 않으므로 힌트를 통해 효율적인 액세스 경로를 선택할 수 있다
1. /*+ 힌트 */ 추천
2. —+ 힌트 비추천

주의 사항

1. 힌트 사이에 , 사용하면 , 앞에 까지만 적용 됨

2. alias 사용한 경우 alias로 힌트를 사용해야 적용 됨

힌트를 쓸꺼면 빈틈없이 사용!

System Global Area(SGA)

- DB Buffer Cache

- Redo Log Buffer

- Shared Pool
   - Library Cache
   - Data Dictionary Cache

소프트 파싱 : SQL을 캐시(Library Cache)에서 찾아 곧바로 실행단계로 넘어 가는 것

하드 파싱 : 캐시(Library Cache)에서 찾는 것을 실패하여 최적화 및 로우 소스 생성 단계 까지 모두 거치는 것

하드 파싱할 때는 많은 연산이 발생

파싱 기준 정보

테이블 컬럼 인덱스 구조

오브젝트 통계

시스템 통계

옵티마이저 관련 파라미터

사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때 이름을 갖고 컴파일 상태로 딕셔너리에 저장 되어 영구 보관 된다

SQL은 이름이 따로 없고 전체 SQL 텍스트가 이름 역할을 한다

처음 실행할 때 최적화 과정을 거쳐 생성한 내부 프로시저를 라이브러리 캐시에 적재하여 여러 사용자가 공유하며 재사용, 캐시 공간이 부족하면 버려졌다가 다음 실행 때 최적화 과정을 거쳐 캐시에 적재

SQL 텍스트가 변하면 SQL ID도 변하고 새로운 SQL로 인식하여 최적화 과정을 수행 및 캐시에 적재 한다

실행할 때마다 프로시저를 생성하면 비효율적,

파라미터를 받는 프로시저를 공유하면서 재사용 하는 파라미터 Driven 방식으로 바인드 변수 사용 ⇒ 하드 파싱 한번만 일어나고, 캐싱된 SQL을 공유 재사용

SQL이 느린 이유는 디스크 I/O 때문

데이터베이스 저장 구조(중요)

• 데이터 파일 : 디스크상 물리적인 OS파일
• 테이블 스페이스 : 세그먼트를 담는 컨테이너
  • 세그먼트(테이블, 인덱스, 파티션, LOB) : 데이터 저장공간이 필요한 오브젝트(물리적인 공간을 점유하는 오브젝트) 세그먼트는 익스텐트 맵을 세그먼트 헤더에 관리
    • 익스텐트 : 공간을 확장하는 단위, 연속된 블록 집합
      • 블록 : 데이터를 읽고 쓰는 단위
        • 로우

세그먼트 공간이 부족해지면 테이블스페이스로부터 익스텐트를 추가로 할당받는다. 단, 세그먼트에 할당된 모든 익스텐트가 같은 데이터파일에 위치하지 않을 수 있다

• 익스텐트 내 블록은 서로 인접한 연속된 공간
• 익스텐트 끼리는 연속된 공간이 아닐 수 있다

DBA (Data Block Address)

인덱스를 이용해 테이블 레코드를 읽을 때 인덱스 ROWID를 이용하는데, ROWID는 DBA + 로우 번호(블록 내 순번) 으로 구성되어있다

테이블을 스캔할 때는 테이블 세그먼트 헤더에 저장된 익스텐트 맵을 이용한다. 익스텐트 맵을 통해 각 익스텐트의 첫 번째 블록 DBA를 알 수 있다

블록 : 데이터를 읽고 쓰는 단위

테이블 뿐 아니라 인덱스도 블록 단위로 데이터를 읽고 쓴다

테이블 또는 인덱스 블록을 액세스(읽는) 방식

• 시퀀셜 액세스 : 논리적 또는 물리적으로 연결된 순서에 따라 차례로 블록을 읽는 방식 리프 블록은 앞뒤를 가리키는 주소값을 통해 논리적으로 서로 연결되어 있다. 이 주소 값에 따라 앞 또는 뒤로 순차적으로 스캔하는 방식
• 랜던 액세스 : 논리적, 물리적 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근 하는 방식

논리적 I/O : SQL 문을 처리하는 과정에서 메모리 버퍼캐시에서 발생한 총 블록 I/O Direct I/O가 발생할 수 있어 논리적 I/O가 메모리 I/O가 정확히 같은 의미는 아니지만, 일반적으로 같다

물리적 I/O : 디스크에서 발생한 총 블록 I/O

물리적 I/O = 논리적 I/O * (100 * BCHR)

SQL 트레이스에서

Query + Current = DB 버퍼 캐시에서 읽은 총 블록 개수 (Disk 포함됨)

Disk = 물리적으로 읽은 블록 수

블록을 읽을 때는 해당 블록을 먼저 버퍼 캐시에서 찾아보고 없을 때만 디스크에서 읽는다. 이때 디스크에서 곧 바로 읽는 것이 아닌 버퍼 캐시에 먼저 적재 후 읽는다

BCHR = (1 - (Disk / (Query + Current))) * 100

Single Block I/O

- 한 번에 한 블록씩 요청해서 메모리에 적재하는 방식

- 인덱스를 이용할 때는 인덱스와 테이블 블록 모두 Single Block I/O로 동작

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 읽은 주소로 테이블 블록을 읽을 때

Multiblock I/O

- 한 번에 여러 블록씩 요청해서 메모리에 적재하는 방식

- 디스크 상에 그 블록과 인접한 블록들을 한꺼번에 읽어 캐시에 미리 적재 (인접한 블록은 익스텐트에 속한 블록) : 익스텐트의 결계를 넘지 못한다

index ffs, full

multiblock readcount에 의해 몇 개를 읽어올지 결정 된다

캐시 탐색 메커니즘

Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼 캐시를 경유한다

• 인덱스 루트 블록을 읽을 때
• 인덱스 루트 블록에서 얻은 주소로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 읽은 주소로 테이블 블록을 읽을 때
• 테이블 블록을 Full Scan 할 때

메모리 공유자원에 대한 액세스 직렬화

SGA는 공유 자원으로 여러 프로세스가 접근 가능하므로 두 개 이상 프로세스가 동시에 접근하려고 하면 블록 경합이 발생할 수 있다

따라서 직렬화 메커니즘을 통해 이를 해결한다(래치)

SGA를 구성하는 서브 캐시마다 별도이 래치가 존재 버퍼캐시에는 캐시버퍼 체인 래치, 캐시버퍼 LRU 체인 래치 등이 작동

버퍼 Lock : 오라클은 캐시버퍼 체인 래치를 해제하기 전에 먼저 버퍼 헤더에 버퍼 Lock을 설정함으로 써 버퍼 블록 자체에 대한 직렬화 문제 해결