SQLP 요점정리 #11 : 3과목 Tuning - 1장 2,3,4절 SQL Parsing, DB Call, Block I/O

3과목 SQL 고급 활용 및 Tuning

1장 Architecture 기반 Tuning

1.2 SQL Parsing

SQL Parsing 과정

1. 사용자 입력 SQL
2. Semantic 검사 : 문법적 오류, 객체 존재 여부
3. Library Cache에서 검색 : Hash구조로 관리. SQL의 Hash값의 Bucket을 찾아봄
   • Soft Parsing : Cache에서 찾아서 바로 실행단계로 넘어감
   • Hard Parsing : Cache에서 찾지 못해서 최적화 과정을 거치고 실행단계로 넘어감
4. 찾지 못했을 경우 최적화 수행
5. 최적화 결과를 Library Cache의 Hash Bucket chain에 연결
6. 해당 Execution Plan을 실행

• Optimizer : 사용자 SQL을 가장 빠르고 효율적인 처리경로를 선택해 주는 DBMS의 핵심엔진

Caching 된 SQL 공유

• 공유조건 : 그냥 무조건 같은 SQL문이어야 한다.
  1. 공백, 줄바꿈이 다른 경우 : X
  2. 대소문자가 다른 경우 : X
  3. 주석이 다른 경우 : X
  4. OWNER명시 여부 (EMP 와 SCOTT.EMP) : X
  5. Optimizer Hint 여부 : X
  6. WHERE 절의 literal값이 다른 경우 : X

솔직히 1 ~ 5는 각 부서별 SQL표준을 정하면 되는데 6의 경우에는 Bind Variable을 사용할 수 밖에 없다.
Bind Variable을 사용하면 SQL 재사용율이 좋아진다.
하지만 Bind Variable을 사용하면 실행계획 생성시 통계정보를 활용하지 못한다.
(당연한 얘기다. 어떤 값이 들어올지 모르기 때문에 그냥 균등하다고 생각하고 실행계획을 작성한다.)

• Bind Variable을 사용하지 않는게 좋은 경우
  1. DW, OLAP 환경에서의 Long Running Query : Parsing 소유시간에 비해 Execution 시간이 훨씬 길며, Parsing도 자주 일어나지 않는다.
  2. WHERE절의 칼럼 Distinct 값이 적을 경우 : 그만큼 분포가 균일하지 않기 때문에 Histogram을 활용하는게 유리하다.
• Bind Variable Peeking (Sniffing)
  • SQL이 처음 실행될때 Bind Variable 값을 살짝 훔쳐보고 Execution Plan을 작성
  • 뒤에 어떻게 분포가 바뀔지 모르므로 상당히 위험한 기능
  • 왠만해서는 해당 기능을 비활성화 하는게 좋음

ALTER SYSTEM SET "_optim_peek_user_binds" = FALSE;

Static SQL, Dynamic SQL

• Static SQL : code 사이에 SQL문을 직접 기술 (Pro*C, Power Builder, SQLJ 등... ). Compile 단계에서 SQL 구문체크가 가능
• Dynamic SQL : String 타입 변수에 SQL문을 저장 (현재 대부분의 경우)
• Application Cursor Caching
  • SQL문을 한번 Parsing한 후 Bind Variable 값만 바꿔가면서 반복적으로 수행
  • Java에서 .setImplicitCachingEnabled(true)로 한다던지 Statement를 닫지 않고 재사용하는 방법
  • Parse Call 한번에 Execute Call이 여러번 일어남 (일반적인 경우는 Parse, Execute Call의 수가 같음)

1.3 Database Call과 네트워크 부하

Call의 종류

• SQL Cursor 작업 요청에 따른 구분
  • Parse Call : SQL Parsing을 요청
  • Execute Call : SQL 실행을 요청
  • Fetch Call : SELECT문의 결과 데이터 전송을 요청
• Call 발생 위치에 따른 구분
  • User Call : DBMS외부로부터 요청
  • Recursive Call : DBMS내부에서 발생하는 Call
    • SQL Parsing 과정에서 발생하는 데이터 딕셔너리 조회
    • 사용자 정의 함수/프로시저 내에서의 SQL수행

User Call과 성능

User Call은 시스템 확장성을 떨어뜨리는 가장 큰 요인 중 하나이므로 최소화 하려는 노력이 중요하다.

1. One SQL 구현 : Loop Query를 해소

• 1번의 Call로 해결되는 Query는 5번의 Call로 해결되는 Query에 비해 5배의 확장성을 갖는 것이다.

2. Array Processing : Array 단위 Fetch, Bulk Insert/Update/Delete

• INSERT 할때 Array에 계속 담다가 1,000건이 쌓일 때마다 executeBatch() 실행 (Java)
• SELECT 할때 .setFetchSize(1000)으로 1,000건 단위로 Fetch (Java)

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

• SQL*Plus에서 set arraysize 100으로 할 경우 301번의 Fetch Call로 30,000 rows를 읽는 것을 Trace에서 확인이 가능하다.
• 만약 10개의 record를 담는 block이 3개 있는 경우 ArraySize를 3으로 할 경우 총 10번의 Fetch Call이 발생하지만, Block I/O는 12번이 된다. (왜냐면 4번째 Call의 경우 Block 1에서 1 record만 읽고, Block 2에서 2 record만 읽는다. 즉 2번의 Block I/O가 발생한 것이다.)
• 즉, ArraySize를 무작정 키운다고 좋은게 아니다.

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

• Web에서 Page 단위로 게시물을 보여줄 경우 처음부터 다 읽어서 Fetch하면서 필요한 것만 보여주게 된다면 성능에 치명적이다.
• 필요한 Page부분만 읽도록 수정해야 한다. (뒤에서 자세히 다루겠다.)

5. 분산 Query

원격에 있는 DB 간의 Table을 JOIN할 경우 성능을 높일 수 있는 방안은 ?

1. 실행계획을 보고 다음의 Rows수를 확인한다.
   • 실제 결과 데이터
   • 각 Table 별로 필터 조건으로 걸러지는 데이터
   • REMOTE로 원격으로 전송된 데이터
2. /*+ driving_site(테이블명) */ Hint를 활용하여 어느 Server에서 JOIN을 수행하고 나머지 Server에서 원격으로 데이터를 전송하도록 조절한다.
3. 결론적으로 네트워크를 통해 데이터 전송량을 줄이는게 핵심이다.

6. User-defined Function/Procedure

• Built-In Function/Procedure : Native code로 완전 compile된 형태이므로 엄청 빠르다.
• User-defined Function/Procedure : 실행할때마다 Virtual Machine에서 별도 실행 엔진을 통해 호출되므로 느리다.
• 특히나 내부적으로 또다른 Query가 있을 경우에는 Execute Call, Fetch Call이 재귀적으로 일어난다.
• 그러므로 다음의 경우에만 제한으로 사용할 것을 권장한다.
  • 소량의 데이터를 조회할 경우
  • 부분범위처리가 가능한 상황
• 가급적으로 One SQL로 구현하고자 노력하는게 좋다. (JOIN, Scalar-Subquery)
  • 그 과정에서 별도의 Logic이 필요한 것을 따로 Table로 구현하는 방법도 있다.
    • ex. 휴무일을 검사하는 Function 대신 업무일 Table을 생성해두고 EXISTS로 비교

1.4 DB I/O 원리

Block I/O

• 모든 DBMS는 Block단위 I/O를 한다
• 1개의 record를 읽더라도 해당 Block 전체를 읽는다.
• Buffer Cache, Disk상의 Datafile 모두 Block 단위 I/O로 읽는다.
  • Datafile -> DB Buffer Cache로 Block을 적재할 때
  • Datafile에서 Block을 직접 읽고 쓸때
  • DB Buffer Cache에서 Block을 읽고 쓸때
  • DB Buffer Cache -> Datafile로 다시 데이터를 쓸때

Buffer Cache Hit Ratio

BCHR = (버퍼 캐시에서 바로 찾은 Block수 / 총 읽은 Block수) X 100

Fetch Call 에서 disk : 18 , query : 822 일 경우 총 읽은 Block수는 822, 버퍼 캐시에서 읽은 수는 822 - 18 = 804 이므로 BCHR = 97.8%가 된다.

Sequential I/O vs. Random I/O

• Sequential I/O : Index에서 Leaf Node를 따라 Scan하는 것과 Full Table Scan
• Random I/O : Index에서 한 건을 읽기 위해 한 Block씩 접근하는 방식
• Sequential 액세스의 비중을 높이고 Random 액세스의 발생량을 줄이는게 I/O 튜닝의 핵심이다.
• Sequential 액세스의 효율을 높일려면 총 읽은 건수 중 결과집합으로 선택되는 비중을 높여야 한다. 즉 같은 결과를 얻기 위해 얼마나 적은 record를 읽느냐로 판단한다.
• Index의 컬럼 및 그 순서를 조정하는게 가장 좋은 방법이다.

Single Block I/O vs. MultiBlock I/O

• Index : Single Block I/O (10g이후 Index Range Scan, Index Full Scan에서는 MultiBlock I/O로도 가능)
• Table : MultiBlock I/O
• 대량의 데이터를 읽을 땐 MultiBlock I/O가 I/O Call을 줄일 수 있기 때문에 유리하다.

I/O 효율화 원리

• 필요한 최소 Block만 읽도록 SQL 작성
• 최적의 Optimizer 팩터 제공
  • 전략적 Index 구성
  • DBMS가 제공하는 기능 활용 : Index, Partition, Cluster, Window 함수
• 필요하다면 Optimizer Hint를 활용하여 최적의 액세스 경로로 유도
  • 전체처리 최적화, 최초 응답속도 최적화 및 그밖에 파라메터들
• 통계정보 제공