06 주차 정리 내용 - 이정수

[붸릴]

04 라이브러리 캐시 최적화 원리

01 SQL과 옵티마이저

- SQL (Structured Query Language) : 4세대 언어 ( VIsual Basic / Delphi / PowerBuilder... ) 라고 부른다.

=> GUI 환경의 비쥬얼 개발 툴

=> SQL은 원하는 결과 집합을 요구할 뿐 결과집합을 얻기 위한 처리절차를 개발자가 직접 기술하지는 않기 때문에

- DBMS에 내장된 옵티마이저가 결과집합을 얻기 위해 필요한 프로시저를 내부에서 자동으로 생성한다.

- 옵티마이저에 의해 생성된 처리절차를 사용자가 확인할 수 있도록 트리 구조로 표현한 것이 실행계획 이다.

- DBMS의 핵심 기능 

!! 옵티마이저 :  최소비용 / 최적의 경로를 선택해서 작업을 가장 효율적으로 수행할 수 있는 프로시저를 자동으로 생성 

- 옵티마이저의 최적화 수행단계

1. 사용자의 쿼리를 수행 -> 후보군이 될만한 실행계획을 찾아낸다.

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

3. 각 실행계획 중 최소비용을 갖는 하나를 선택

- 서버프로세스 : SQL 파싱 -> (필요시 최적화 수행) -> 커서를 열어 SQL을 실행하면서 블록을 읽음 -> 읽은 데이터 정렬

      -> 클라이언트가 요청한 결과집합을 네트워크 통해 전송

02 SQL 처리과정

- 사용자의 SQL -> SQL 파싱 -> SQL의 메모리 캐싱 여부 확인 -> (메모리에서 찾지 못하면 최적화 단계)

-> 소프트 파싱 : 파싱 단계에서 SQL커서를 메모리에서 찾아 곧바로 실행단계로 넘어가는 것

-> 하드 파싱 : 파싱 단계에서 SQL커서를 메모리에서 찾지 못해 최적화 및 Row-Source생성 단계를 거치는 것   

(1) SQL 파싱

- 사용자가 던진 SQL을 가장 먼저 처리하는 엔진 : SQL 파서 (Parser)

-> SQL Parser : SQL 문장을 이루는 개별 구성요소 분석 -> 파싱 -> 파싱트리를 만든다.

=> 이 과정에서 Syntax 체크 수행 : 키워드의 오류 / 순서 / 누락 등등..

=> 파싱트리가 만들어 지면 Semantic 체크 수행 :

-> 존재하지 않거나 권한 없는 오브젝트 사용 / 존재하지 않는 컬럼 참조 등등..

-> Semantic 체크까지 지나면 해싱 알고리즘 이용하여 SQl 커서가 Shared Pool에 캐싱돼 있는지 확인

-> SQL ASCII 텍스트에 대한 숫자 값을 계산하고 이를 다시 해시 값으로 변환

=> Shared Pool에서 SQL 문장을 찾았더라도 요청한 사용자가 다르거나 / 옵티마이저 관련 파라미터 설정이 다르면

새로운 SQL 커서를 생성해야 한다.

Ex) EMP 테이블이 SCOTT과 HR 스키마에 각각 생성돼 있다면 SCOTT / HR 계정으로 로그인할때 각각 다른 테이블을 액세스 해야하고 실행계획도 달라져야만 한다.

(2) SQL 최적화

- 옵티마이저가 최적화를 수행할 때 세부적으로 사용하는 3개의 서브 엔진

1. Query Transformer : 사용자가 던진 SQL을 그대로 둔 채 최적화하는 게 아니라 우선 최적화 하기 쉬운 형태로 변환을

시도한다. 물론 쿼리 변환 전후 결과가 동일함이 보장될 때만 그렇게 한다.

2. Plan Generator : 하나의 쿼리를 수행할 때, 후보군이 될만한 실행계획들을 생성해 낸다.

3. Estimator : 쿼리 오퍼레이션 각 단계의 선택도 / 카디날리티 / 비용을 계산하고, 궁극적으로 실행계획 전체에 대한

총 비용 (예상치) 을 계산한다. 데이터베이스 오브젝트 정보 / 시스템 성능 통계정보를 이용한다.

이들 통계 정보는 오라클이 자동으로 수집하거나 DB 관리자의 정책에 따라 주기적으로 수집된다.

- Adaptive search startegy : 쿼리 수행시 예상되는 총 수행시간에 비해 쿼리 최적화에 걸리는 시간이 일정비율을 넘지 않도록 사용하는 적응적 탐색전략

- Multiple Initial orderings heuristic : 조인 순서를 무순위로 평가하는 게 아니라 최적의 실행계획을 발견할 가능성이 높은 순서대로 비용을 평가 => 실행계획을 평가하는 도중에 멈추더라도 그중에서 선택된 실행계획이 최적에 가까운 좋은 실행계획에 속할 가능성을 높인다.

(3) Row-Source Generation

- Row-Source : 레코드 집합을 반복처리하면서 사용자가 요구한 최종 결과집합을 실제적으로 생성하는데 사용되는 제어 구조

- DB에서 이루어지는 처리과정은 대부분 I/O 작업

- CPU를 많이 소비하게 만드는 요인 : 하드 파싱 / Shared Pool, 라이브러리 캐시에 대해 발생하는 래치 경합

03 라이브러리 캐시 구조

- 라이브러리 캐시 : Shared Pool내에 위치 - 오브젝트( 테이블 , 인덱스 등 )에 대한 정보를 관리

=> 저장되는 정보의 단위 : 라이브러리 캐시 오브젝트 (LCO)

- 커서 : 하드파싱 과정을 거쳐 메모리에 적재된 SQL , Parse tree, 실행계획, 이것을 실행하는데 필요한 정보를 담은 SQL Area

- 라이브러리 캐시에 스키마 오브젝트 정보를 캐싱하는 것은 LCO간 의존성을 관리하려는데 목적이 있다.

 <실습>

- 라이브러리 캐시에 어떤 유형의 오브젝트들이 적재되는지를 보여준다.

          1. 생성 후 Drop 하기 전까지 데이터베이스에 영구적으로 보관되는 오브젝트 정보

              => 테이블 / 인덱스 / 클러스터 / 뷰 / 트리거 / 패키지 / 사용자 정의 함수 / 프로시저 등.. : 생성될때 부터 이름을 갖음

          2. 실행시점에 생성돼서 인스턴스가 떠있는 동안에만 존재하는 일시적인 오브젝트 정보

              => 커서 / Anonymous PL/SQL문 : 문장을 구성하는 전체 문자열 그대로가 이름 역활을 함

    

- Shared Pool도 DB 버퍼 캐시처럼 LRU 알고리즘에 의해 관리 된다.

=> 재사용 빈도가 낮은 SQL은 캐시에서 밀어냄으로써 새로운 SQL을 캐싱할 수 있도록 공간을 확보한다.

- 9i부터 Shared Pool을 여러 개 Sub Pool로 나누어 관리할 수 있게 되면서 래치도 7개 까지 사용할 수 있데 되었다.

- 라이브러리 캐시도 DB 버퍼 캐시처럼 해시구조로 관리 된다.

=> 패시 버킷에 LCO 핸들이 체인으로 연결돼 있고, 핸들을 통해 LCO 힙을 찾아가는 구조

=> DB 버퍼 캐시와 마찬가지로 해시 함수를 통해 리턴된 해시 값을 가지고 해시 버킷을 할당한다.

- Parent 커서 밑에 Child 커서가 연결되는 구조를 갖는다.

=> 커서를 공휴하지 못하고 별도로 생성해야 할 때 : 다중 Child 커서를 사용한다.

- 라이브러리 캐시 체인을 탐색하고 변경하려면 먼저 library cache래치를 획득해야 한다.

=> 이 때 발생하는 경합 - latch : library cache -> 소프트 파싱이 많이 발생해도 래치에 대한 경합이 증가한다.

- LCO를 보호하기 위해 라이브러리 캐시 Lock / 캐시 Pin을 사용한다.

- LCO에 접근할 때는 먼저 핸들에 대한 Lock을 획득 해야 한다.

- LCO의 실제 내용이 담긴 힙에서 정보를 읽거나 변경할 때는 Pin을 걸어두어야 한다.

- Shared Pool 래치 / library cache 래치 경합 :  소프트 / 하드 파싱을 동시에 심하게 일으킬 때 발생

- library cache lock / library cache pin 대기 이벤트 : DDL을 날릴 때 발생한다.

- 라이브러리 캐시 최적화를 위한 노력 3가지

1. 커서를 공유할 수 있는 형태로 SQL을 작성한다 -> 바인드 변수의 사용

2. 세션 커서 캐싱 기능을 이용 -> 라이브러리 캐시에서 SQL 찾는 비용을 줄인다.

3. 애플리케이션 커서 캐싱을 이용 -> Parse Call 발생량 줄임

04 커서 공유

(1) 커서란 ?

1. 공유 커서 : 라이브러리 캐시에 공유돼 있는 Shared SQL Area

2. 세션 커서 : Private SQL Area에 저장된 커서

3. 애플리케이션 커서 : 세션 커서를 가리키는 핸들

- 공유 커서 : SQL을 수행하면서 서버 프로세스는 해당 SQL이 라이브러리 캐시에 공유돼 있는지를 먼저 확인한다.

=> 라이브러리 캐시에 공유돼 있는 Shared SQL Area를 커서 라고 부른다.

- 세션 커서 : 라이브러리 캐시에 공유돼 있는 커서를 실행할 때는 우선 PGA영역에 메모리를 할당한다. 이를 Private SQL Area라 부른다.

=> Persistent Area / Runtime Area 로 나뉜다, 커서를 실행하는 데 필요한 정보들을 Private SQL Area에 담고, 공유 커서를 가리키는 포인터를 유지한다.  / 커서의 상태 정보도 관리한다.

- 커서를 실행하기 위한 준비과정을 커서를 오픈한다 고 표현하고, PGA에 저장된 커서 정보를 또한 커서라고 부른다.

- 클래스를 인스턴스화 한것이 객체다

- 클래스는 디자인이고, 그것을 실체화한 것이 객체다.

- 클래스는 붕어빵을 만드는 틀이고, 객체는 그 틀로써 만들어낸 붕어빵이다.

- 커서를 오픈하면 라이브러리 캐시에 공유돼 있는 커서를 인스턴스화 함으로써 PGA에 커서를 위한 메모리 공간을 할당하고, 실제 데이터 추출을 시작할 수 있또록 준비작업을 한다.

- 애플리케이션 커서 : PGA에 있는 커서를 핸들링하려면 JAVA, VB, Pro *C, PL/SQL같은 클라이언트 애플리케이션에도 리소스를 할당해야 하는데 이 또한 커서라 한다.

<공유커서 / 세션커서 / 애플리케이션 커서 개념>

(2) 커서 공유

- 커서를 공유한다는 표현을 할 때 커서는 라이브러리 캐시의 공유 커서를 일컫는다.

=> 세션 커서 / 애플리케이션 커서는 공유가 불가능 하다.

- 라이브러리 캐시에 공유돼 있는 커서의 수행 통계를 v$sql을 통해 조회해 볼 수 있다.

 <실습>

- parse_calls : 라이브러리 캐시에서 SQL 커서를 찾으려는 요청 횟수

- loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수

- executions : SQL을 수행한 횟수

- invalidations : 커서가 무효화된 횟수, 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함 

05 바인드 변수의 중요성

06 바인드 변수의 부작용과 해법