[SQLP]튜닝포인트 1.라이브러리 캐시 최적화 원리

SQLP 공부할 때 에버노트에 정리했던 내용입니다.

• 파싱 단계에서 SQL 커서를 메모리에서 찾아 곧바로 실행 단계로 넘어가는 것을 소프트 파싱.
• 실패해서 최적화 및 Row-Source 생선 단계를 거치는 것을 하드 파싱.
• 문법적으로 완전하고 의미상 오류가 없다면 해싱 알고니즘을 이용해서 해당 SQL 커서가 Shared POOL에 캐싱돼 있는지를 확인한다. 
• 라이브러리 캐시도 해시 구조로 관리. 해시 버킷에 LCO 핸들이 체인으로 연결돼 있고, 핸들을 통해 LCO 힙을 찾아가는 구조.

---

• 커서 별 공유할 수 있는 형태로 SQL을 작성한다. 특히 바인드 변수를 사용해 같은 형태의 SQL에 대한 반복적인 하드 파싱이 일어나지 않도록 해야 한다.
  • 공유 커서 : 라이브러리 캐시에 공유돼 있는 Shared SQL Area. 여기에 공유되어 있지 않으면 하드 파싱
  • 공유 커서 사용하면 최적화 및 Row-Source Generation 단계를 생략하고 곧바로 실행 단계로 넘어감
  • 커서 공유 시에 키 값은 sql 문장 그 자체(PL/SQL은 주석 제거와 소문자를 대문자로 바꿔서 파싱).
  • 바인드 변수를 사용해라. 바인드 변수의 부작용 해법 : 실행 시점에서 바인드 변수를 바인딩하므로 최적화 단계에서 히스토그램 사용X. 파티션 테이블 쿼리 시에 파티션 레벨 통계 정보 이용하지 못함
    1. 적응적 커서 공유 => 비추
    2. 입력 값에 따라 SQL 분리(OLTP 시스템이면 UNION ALL 이용보다 이득. UNION ALL은 값이 없어서 I/O를 일으키지 않더라도 실제 실행하는 것)
    3. 예외적으로 상수값 이용
• 세션 커서 캐싱 기능을 이용해 라이브러리 캐시에서 SQL 찾는 비용을 줄인다. 즉 Parse call을 더 가볍게 한다(session_cached_cursors)
  • Private SQL Area(공유 커서를 실행 시에 우선 PGA 영역에 메모리를 할당)에 저장된 커서
  • Users_opening : 공유 커서를 참조하고 있는 세션 커서의 수. 수행을 마쳐 커서를 닫았더라도 참조를 버리지 않은 채 세션 커서 캐시로 옮겨진다면 여기 집계
  • Users_executing : 해당 SQL을 현재 실행 중인, 즉 커서가 열려 있는 세션 커서의 수
  • Parse Call을 줄이지는 않음. 부하만 감소
• 애플리케이션 커서 캐싱을 이용해 Parse Call을 줄인다(아예 발생하지 않게)
  • 세션 커서를 가리키는 핸들
  • 일반적으로 EXECUTE call만큼 parse call은 반복되나, 해당 기능을 이용하면 공유 커서를 pin한채 반복 수행하므로 parse call이 최초 한 번만 발생하고 이후로는 발생하지 않는다
  • PL/SQL은 자동 커서 캐싱하나, Dynamic SQL과 커서 변수는 예외
  • Static SQL을 작성하는 것을 원칙으로 할 것. 방법이 없거나 SQL이 너무 복잡해질 때 Dynamic SQL
  • 선택적 검색 조건에 사용할 수 있는 기법
    • NOT NULL 컬럼일 때는 NVL, DECODE 사용하는 것이 편함.
    • NULL 값을 허용하고 인덱스 액세스 조건으로 의미 있는 컬럼이라면 UNION ALL을 사용할 것
    • 인덱스 액세스 조건으로 참여하지 않는 경우, 즉 인덱스 필터 또는 테이블 필터 조건으로만 사용되는 컬럼이라면 (:C is null or col = :C) 또는 c like :||'%' 어떤 방식을 사용해도 무방 

---

• Static SQL 구현을 위한 기법
  • IN-LIST 항목이 가변적이지만 최대 경우수가 적은 경우 : 컬럼 IN (:A, :B, :C)
  • IN-LIST 항목이 가변적이지만 최대 경우수가 많은 경우 : 인덱스 활용으로 파라미터 받는 것.
  • 체크 조건이 가변적인 경우 : 전종목일 때와 아닐 때로 UNION ALL로 나눠서 EXISTS 조건 적용
  • SELECT-LIST가 동적으로 바뀌는 경우 : DECODE(:gubun , '1', avg(계약수) , sum(계약수))