[SQLD/SQLP] 전문가가이드 3과목 1장 아키텍쳐 기반 튜닝원리 암기 요약 정리 (+오라클성능고도화)

3과목 1장 아키텍쳐 기반 튜닝원리 요약정리

3과목부터는 오라클성능고도화도 참조하며

공부하면서 헷갈리거나 이해가 잘 되지 않던 부분 위주로 요약했습니다.

---

User Call 최소화 방안

1. Loop 쿼리를 해소하고 집합적 사고를 통해 One SQL로 구현

2. Array Processing (Array 단위 fetch)

3. 부분범위처리 원리 활용

4. 효과적인 화면 페이지처리

5. 사용자 정의 함수/프로시저/트리거의 적절한 활용

Response Time 

 = Service Time + Wait Time

 = CPU Time + Queue TIme

I/O효율화 원리

- 필요한 최소 블록만 읽도록 쿼리작성

- 최적의 옵티마이징팩터 제공

  1. 전략적인 인덱스구성

  2. dbms가 제공하는 다양한 기능활용  

  3. 옵티마이저 모드 설정

  4. 통계정보의 중요성

- 필요하다면 옵티마이저 힌트를 사용해 최적의 액세스 경로로 유도

Fast commit 

- 사용자의 갱신내용이 메모리상 버퍼블록에만 기록된 채 아직 디스크에

기록되지 않았더라도 Redo로그를 믿고 빠르게 커밋을 완료한다는 의미

Write ahead Logging

- 버퍼캐시블록을 갱신하기전에 변경사항을 먼저

로그버퍼에 기록해야하며 Dirty버퍼를 디스크에 기록하기전에 해당 로그엔트리를

먼저 로그파일에 기록하는것

Log force at commit

- 로그버퍼를 늦어도 커밋시점에는 로그파일에 기록

Online Redo로그

- 캐시에 저장된 변경사항이 아직 데이터파일에 기록되지 않은상태에서

인스턴스가 비정상 종료되면, 이때 데이터유실에 대비하기 위해 Online Redo로그 사용(캐시복구)

Archived(Offline) Redo 로그

- Online Redo로그가 재사용 되기전에 다른 위치로 백업해둔 파일

버퍼 Pinning

- Random Access에 의한 논리적 블록 요청횟수를 감소시키고 prefetch는

디스크 I/O에 의한 대기 횟수를 감소(스칼라 서브쿼리일 때 버퍼피닝X)

Result캐시

- 한번 수행한 쿼리 또는 PL/SQL결과값을 Result캐시에 저장하는 기능추가

- 버퍼캐시에 위치하지 않고 Shared Pool에 위치

- Result캐시에서 결과값을 찾았을 때는 I/O발생 X

적응적 커서 공유

- 입력된 바인드 변수 값의 분포에 따라 다른 실행계획이 사용되도록 하는것

사용자 정의 함수

1. 소량 Data 조회시만

2. 대용량 조회 시 부분범위 처리가 가능한 상황에서 제한적으로

3. 조인, 스칼라 서브쿼리로 변환노력

Deterministic(CGA, 스칼라는 UGA)

- 함수 생성시 Deterministic키워드를 쓰면 스칼라 서브쿼리를 안써도 캐싱한다

Delayed Block Cleanout 

- 변경된 블록을 커밋 시점에 Cleanout하지 않고 그대로 두었다가

나중에 해당 블록을 처음 읽는 세션에 의해 정리되도록 하는 것

PGA

- 각오라클 서버 프로세스는 자신만의 PGA메모리 영역 할당받음

- PGA는 다른 프로스세와 공유되지 않는 독립적인 메모리공간으로서, 래치가 필요없어

똑같은 개수의 블록을 읽더라도 SGA버퍼캐시에서 읽는 것보다 훨씬 빠르다

UGA(User Call Area)

- 전용서버 1:1 방식 - PGA할당(이게 대부분)

- 공유서버 1:M 방식 - SGA할당

- 하나의 프로세스가 여러개 세션을 위해 일한다. 따라서 각 세션을 위한 독립적인 메모리 공간이

필요한대 이것이 UGA

- 하나의 프로세스는 하나의 PGA를 갖는다

- 하나의 세션은 하나의  UGA를 갖는다

- PGA에는 세션과 독립적인 프로세스만의 정보를 관리

- UGA에는 프로세스와 독립적인 세션만의 정보를 관리

CGA(Call global Area)

- PGA에 할당되는 메모리공간

- Oracle은 하나의 Database call을 넘어서 다음 Call까지 계속 되어야하는건

UGA, Call이 진행되는 동안에만 필요한건 CGA

- DML문장 수행 시 발생하는 소트는 CGA에서 수행

I/O효율화 원리

- 물리적으로 디스크에서 읽어야 할 블록 수를 줄이는것 뿐만아니라

논리적인 블록 요청 횟수를 줄여야함

함수의 내장된 쿼리를 수행할 때마다 Execute Call, Fetch Call이 재귀적으로 일어남

Parse Call은 처음 수행할 때 한번만 일어난다.

애플리케이션 커서 캐싱

PL/SQL에서는 위와 같은 옵션을 별도로 적용하지 않더라도

자동적으로 커서를 캐싱한다. 단, static sql을 사용할 때만 그렇다

Dynamic SQL을 사용하거나 Cursor Variable을 사용할 때는 커서를 자동으로

캐싱하는 효과가 사라진다는 사실을 명심

(추가로 자동으로 100개씩 Array Fetch가 일어나지만, Cursor For Loop구문을 이용할때만 작동)

Checkpoint(CHPT)

DBMS는 Redo 로그에 기록해 둔 버퍼 블록에 대한 변경사항 중 현재 어디까지를 데이터 파일에

기록했는지 체크포인트 정보관리. 이는 버퍼캐시와 데이터 파일이 동기화된 시점

블록크기

오라클은 2,4,8,16,32,64KB사용

SQL Server은 8kb 단일 크기사용

익스텐트

SqlServer의 경우 2가지 타입의 익스텐트를 사용한다.

균일 익스텐트 : 64kb이상의 공간을 필요로 하는 테이블이나 인덱스를 위해 사용되며

8개 페이지 단위로 할당된 익스텐트를 단일 오브젝트가 모두 사용

혼합 익스텐트 : 한 익스텐트에 할당된 8개 페이지를 여러 오브젝트가 나누어 사용하는 형태

모든 테이블이 처음에는 혼합 익스텐트로 시작하지만 64kb를 넘으면서 2번째부터 균일 익스텐트를 사용

대기이벤트 관련 부하

1. 라이브러리 캐시 부하 : 라이브러리 캐시에서 sql 커서를 찾고 최적화 하는 과정에서 경합이 발생, 라이브러리 캐시 관련 경합이 급증하면 심각한 동시성 저하 초래 

2. 데이터베이스 Call과 네트워크 부하

3. 디스크 I/O 부하

4. 버퍼캐시 경합

5. Lock관련 대기 이벤트

User Call을 최소화하려는 노력

1. Loop 쿼리를 해소하고 집합적 사고를통해 One SQL로 구현

2. Array Processing

3. 부분범위처리 원리 활용

4. 효과적인 화면 페이지 처리

5. 사용자 정의 함수/프로시저/트리거의 적절한 활용

분산쿼리

driving_site(b) 원격 서버가 쿼리를 처리하도록 하는 힌트

Single / Multi Block IO

Index Range Scan또는 Index Full Scan일때도 Multi Block IO로 

읽는 경우가 있는데, 테이블 액세스 없이 인덱스만 읽고 처리할 때가 그렇다.