SQL문 분석과 공유 풀

SQL문 분석과 공유 풀

처리방법의 생성(분석)과 생성 후 캐시하는 저장소(공유 풀)

SQL문의 데이터 처리 순서

- SQL문에서는 데이터의 조건이나 관계를 기술
    - SELECT A FROM B WHERE C = 1
    - 오라클은 힌트를 활용해서 어느정도의 처리순서를 지시함.
- 옵티마이저가(파서)가 SQL문을 분석하고 플랜이라고 하는 처리 방법을 생성

서버 프로세스와 분석

- 서버 프로세스는 SQL문의 처리를 최우선으로 하는 프로세스
- 오라클에서 분석은 SQL문을 분석하고 어떤 알고리즘으로 처리할 지를 가리킴
    - 비용 기반 알고리즘 : 처리 시간이나 I/O 횟수가 가장 작다고 생각되는 처리방법이 최상의 알고리즘

[분석 그림]

비용의 수치 통계정보

- 테이블의 데이터 건 수, 데이터 양, 데이터 최댓값과 최솟값, 인덱스에 관한 기초 수치를 말함
- 오라클 10g 부터는 SQL문 정보, 파라미터 정보(사용할 수 있는 자원 정보), 시스템 통계 정보(한번 작업하는데 걸리는 시간)를 가지고 비용을 계산

**분석은 테이블 수가 많으면 많아질 수록 RDBMS가 좋지 않은 실행 계획을 선택하게 된다.

**그 이유는? 선택할 수 있는 실행 계획의 수가 많다는 점과 이런 실행 계획들이 어디까지나 예측에 지나지 않다는 점이다.

공유 풀의 동작과 구조

- 분석 처리가 많은 CPU를 사용하고 있다는 문제를 발견
- 이를 해결하기 위해, 실행 계획을 공유해서 자원 소비를 줄일 수 있는 방안을 생각
- 공유 풀은 실행 계획을 재이용하고 분석 작업을 줄이기 위한 존재
- 공유 풀을 잘 활용하게 되면 CPU 자원의 절약으로 이어짐

공유 풀의 동작원리

- 공유 풀도 프로세스 간의 공유를 하므로 공유 메모리 안에 있음.
- 공유 풀은 라이브러리 캐시와 딕셔너리 캐시 영역을 가짐
    - 라이브러리 캐시 : 실행계획 등 SQL 정보의 캐시는 여기에서 수행
    - 딕셔너리 캐시   : 통계 정보의 캐시 등 주로 SQL의 실행에 필요한 메타 정보를 보관

오라클은 어떻게 같은 SQL 문이라고 판단하나?

- 오라클은 해시 알고리즘(빠른 검색을 위한 알고리즘)을 사용해서 SQL문 마다 ID를 생성
    - SQL문을 문자열로 해시 함수에 입력하고 함수에서 출력된 해시 값을 SQL문의 ID로 활용
    - 대소문자에 따라 해시값은 달라짐
- 검색 조건이 다른 SQL문은 바인드 변수를 사용하면 같은 SQL문으로 판단
    - 바인드 변수 : 프로그램의 변수를 SQL문에서 사용하는 기능
    - Ex. where cust_no = :변수명

하드 파스와 소프트 파스

- 하드 파스 : 지금까지 해왔던 분석(SQL문 분석)
- 소프트 파스 : 해시 값을 요청한 결과, 공유 풀에 캐시되어 있는 실행 계획을 찾았기 때문에 재이용하는 경우

정리

** 결과적으로, 실행 계획을 생성하기 위해서는 분석이 필요하고 이 분석은 많은 양의 CPU를 사용하기 때문에 공유 풀에 실행계획을 캐시해서 재활용 함.

** 현장에서는, 비용 계산의 기초 정보가 되는 통계 정보가 제대로 수집되고 있는지 DBA에게 확인

** 하드 피스가 많으며 분석에 사용하는 CPU양이 많을 시에는, SQL문을 바인드 변수를 사용하도록 변경

[오라클 전체 그림]