[SQLP]튜닝포인트 2.데이터베이스 Call 최소화 원리

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

• Parse Call : 커서를 파싱 하는 과정에 대한 통계. 실행계획을 생성하거나 찾는 과정
• Execute Call : 커서를 실행하는 단계에 대한 통계(DML문은 모든 처리 과정을 서버 내에서 완료하고 처리결과만 리턴하므로 Fetch Call이 발생하지 않음. Insert Select도 마찬가지.
• Fetch Call : select문에 실제 레코드를 읽어 사용자가 요구한 결과 집합을 반환하는 과정. Sort도 이단계에서 발생

---

• User Call : OCI를 통해 오라클 외부로부터 들어오는 Call. 부하가 더 크므로 최소화하려는 노력. 
  • LOOP 쿼리를 해소하고 집합적 사고를 통해 One-SQL로 구현 : 동일 레코드를 반복 액세스 하지 않고 얼마만큼 블록 액세스 양을 최소화할 수 있을지?
  • Array Processing : Array 단위 Fetch, Bulk Insert/Update/Delete. ONE-SQL에 버금가는 효과 구현
    부분 범위 처리 원리 활용
  • 효과적인 화면 페이지 처리
  • 사용자 정의 함수/프로시저/트리거의 적절한 활용
• Recursive Call : 오라클 내부에서 발생하는 Call. SQL 파싱과 최적화 과정에서 발생하는  Data Dictionary  조회, PL/SQL로 작성된 사용자 정의 함수/프로시저/트리거 내에서의 SQL 수행.
  • PL/SQL에서 지나친 모듈화는 지양해야 한다.

---

Fetch Call 최소화

• 부분범위 처리 원리
• OLTP 환경에서 부분 범위 처리에 의한 성능 개선 원리 : 결과 집합이 많을수록 유리
• ArraySize 조정에 의한 Fetch Call 감소 및 블록 I/O 감소 효과
• 프로그램 언어에서 Array 단위 Fetch 기능 활용 : Cursor For LOOP

---

PL/SQL 함수의 특징과 성능 부하

• 특징 : 함수 실행 시 매번 SQL 실행 엔진과 PL/SQL 가상 머신 사이에 컨텍스트 스위칭이 일어난다. SQL에서 함수를 호출할 때마다 SQL 실행 엔진이 사용하던 레지스터 정보들을 백업했다가 PL/SQL 엔진이 실행을 마치면 다시 복원하는 작업을 반복하게 되므로 느리다.
• 대용량 조회 쿼리에서 함수를 남용하면 읽는 레코드 수만큼 건건이 함수 호출이 발생해 성능이 최악(Recursive Call 난타) 그러므로 소량의 데이터 조회에 사용하거나, 대용량 조회 시에는 부분 범위 처리가 가능한 상황에서 제한적으로 사용 => 가급적 조인 또는 스칼라 서브 쿼리 형태로 변환해라
• 가장 기본적인 문장 수준 읽기 일관성이 보장되지 않으므로, 함수 내에서 수행되는 쿼리는 메인 쿼리의 시작시점과 무관하게 그 쿼리가 수행되는 시점을 기준으로 블록을 읽는다. 

PL/SQL 함수 호출 부하 해소 방안

• 페이지 처리 또는 부분범위처리 활용 : Rownum을 활용한 페이지 처리를 통해 최종 결과 집합에 대해서만 함수 호출 
• 함수를 풀어 Decode 함수 또는 Case문으로 전환
• 뷰머지뷰 머지 방지(no_merge 또는 rownum 등 뷰 머지 적용 안 되는 조건)를 통한 함수 호출 최소화
• 스칼라 서브쿼리 캐싱 효과를 이용한 함수 호출 최소화(select 함수 from dual)
• Deterministic 함수의 캐싱 효과 활용