Chapter 4 SQL문 분석과 공유 풀

• 옵티마이저와 공유 풀에 대한 설명
• 옵티마이저: SQL문 분석 최적의 처리방법 -> 실행계획
• 공유 풀(Shared Pool): 실행계획이 캐시되는 곳
• 아무리 성능이 좋더라도 처리방법자체가 좋지 않다면 무용지물
• SQL을 처리하는 방법을 생성하는 데에 CPU를 오래 사용함
• SQL을 처리하는 방법을 생성하는 횟수를 줄이면 또한 성능을 높일 수 있음 -> 공유 풀의 이용

SQL문과 일반 프로그래밍 언어의 차이

• SQL 문은 "데이터의 조건이나 관계"만을 기술
  • SELECT A FROM B WHERE C = 1
    • 여기에서 처리 방법을 제공하진 않는다
• 프로그래밍 언어에 직접 처리하도록 기술하지만, 반면에 SQL 문은 옵티마이저(파서)가 분ㅂ석하고 실행계획이라는 처리방법을 생성해준다

서버 프로세스와 분석

• 분석이란?
  • SQL문을 분해해서 어떤 요소로 구성되어있는지 조사하고 어떤식으로 처리할지 생각하는 것
  • 여기서 말하는 생각이란?
    • 알고리즘을 말함, 오라클은 두개의 기반이 되는 알고리즘이 있음
      • 규칙 기반(rule base)
      • 비용 기반(cost base): 처리 시간이나 I/O 횟수가 가장 작은 것이 최고!
        • 오라클은 이를 예측하기 위해 "비용(cost)" 이라 불리는 수치를 이용
      • 참고: 오라클 10g 부터는 규칙 기반을 지원하지 않음

비용(cost)를 계산하기 위한 기초 수치, "통계 정보"

• 서버 프로세스는 비용을 계산하기 위해 통계정보라 불리는 기초수치를 사용
  • 테이블이나 인덱스의 관한 기초 수치
• 이 통계정보는 Analyze 작업을 통해 얻는다
  • 관리자가 수행하지 않더라도 자동으로 수행

실행 계획 최적이라는 판단하기 위해서는?

예제 조건
1. SELECT * FROM A, B WHERE A.ID = B.ID AND A.value = 1 AND B.value = 1;
2. A 테이블 1000만 건, B테이블 100건, A의 Value 는 대부분 1.

• 어떤 식으로 가져와야 최적으로 데이터를 가져오는 것일까? 생각해보자 67p
• 어떤 방식으로 접근하냐에 따라 성능 차이가 매우 크게 날 수 있다.

그럼 어떤 처리 방법이 가장 좋은지를 어떻게 판단?

• 모든 처리 방법의 비용을 계싼하는 것 외에는 방법이 없음
• 그러나 실제로 모든 처리방법의 비용을 계산하면 큰일
• 테이블 수가 늘어날수록 비용을 계산해야하는 경우의 수가 막대해지기 때문임
• 간혹 DBMS 가 좋지 않은 실행 계획을 선택하는 이유이기도 함
  • 모든 실행계획의 경우의 수를 계산할 수 없기때문.
  • 또한, 이런 실행계획들도 "예측"에 지나지 않기 때문.

공유 풀(Shared Pool)의 동작과 구조

• 이런 CPU 소모가 큰 SQL문 분석결과(실행계획)를 공유해서 사용할 수 있도록 캐시할 수 있는 곳이 공유 풀
• Shared Pool 또한 서버 프로세스 간에 공유되어야하기 때문에 버퍼캐시처럼 공유메모리에 존재
  • 참고로 공유메모리에서 버퍼캐시가 가장 많은 부분을 차지

오라클은 어떻게 SQL문이 같다고 판단하고 실행계획을 캐시할까?

• 해시 알고리즘을 사용해 SQL 문마다 ID 생성
  • 참고로 SQL문의 대소문자구별해서 해시생성한다 -> 같은 SQL문이라도 대소문자 다르면 다른 해시값
• WHERE 조건에 들어가는 변수 등은 바인드 변수로 해석하고 같은 SQL로 인식
  • SELECT * FROM customer WHERE id = 1;
  • SELECT * FROM customer WHERE id = 2;
  • 위 두 쿼리는 SELECT * FROM customer WHERE id = :바인드_변수 같은 쿼리로 취급하여 해시값이 같음

하드 파스(hard parse)와 소프트 파스(soft parse)

• 실행 계획을 생성해서 공유 풀에 생성하는 것을 hard parse
• 해시값을 id로 하여 공유풀에 캐시되어있는 실행계획을 찾아 재사용하는 것을 soft parse

기타

• Statspack: 오라클 성능 진단 도구
  • 보고서를 보면서 soft와 hard 분석(parse)을 얼마나하는지 체크할 수 있고
  • CPU 사용량중에서 얼마만큼이 parse가 사용되는지 확인하여 성능 분석 가능
• AWR(Automatic Workload Repository): 오라클 10g 부터 사용가능한 Statspack 업그레이드, 라이센스 필요
• 통계정보 언제수집? 77p -> 언제수집하는지도 중요하다

todo) 해시 알고리즘 공부