4장 SQL문 분석과 공유 풀

SQL 문의 분석과 공유 풀을 배우는 이유?

• [SQL문 분석 / 플랜 생성]에 CPU의 처리시간이 오래 걸려 생성하는 횟수를 줄이기 위해 SQL문의 분석을 배웁니다.
• 처리 방법의 생성 결과를 공유풀에 캐시하기 때문에 공유 풀(shared pool)을 배웁니다.

 

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

• 프로그래밍 언어는 처리 방법을 기술합니다.
• SQL은 처리 방법을 기술하지 않아 작업을 수행하기 위해 옵티마이저(파서)가 SQL문을 분석하여 플랜을 생성합니다.

 

서버 프로세스와 분석

오라클은 파서가 플랜을 생성할 때 알고리즘을 기반으로 생성합니다. 알고리즘에는 규칙 기반과 비용기반이 있습니다(오라클 10g부터 규칙 기반이 제외되었습니다). 서버 프로세스는 SQL문의 처리를 최우선으로 합니다. Oracle에서의 분석은 [SQL문 분석(형식, 내용) -> 구성 요소 조사 -> 비용기반 알고리즘을 통한 선택]를 일컫습니다.

 

비용 기반 알고리즘

처리 시간 또는 I/O 횟수가 가장 작다고 생각되는 처리 방법을 선택하는 알고리즘입니다. 처리에 필요한 시간이 작다거나 자원 사용량이 작은 처리방법을 선택합니다. 오라클은 통계수집 작업을 통해 기초 수치(통계 정보)를 쌓아두고 [SQL문 정보 + 기초 수치 + 자원 정보 + I/O 시간] 을 종합적으로 고려하여 비용을 계산합니다.

 

RDBMS가 좋지 않은 실행 계획을 선택하는 원인

• 선택할 수 있는 실행 계획 수가 많다는 점
• 실행 계획들이 예측에 지나지 않는다는 점

 

공유 풀의 동작과 구조

공유 풀의 역할

플랜을 재사용하고 분석 작업을 줄이기 위한 역할을 합니다. 공유 풀에는 최근 실행한 실행 계획이 캐시되어 있습니다.

 

공유 풀의 위치와 구성

공유풀은 공유 메모리에 할당됩니다. 버퍼 캐시가 많은 부분을 차지하는 것에 반해 남은 일부를 사용합니다. 공유 풀은 라이브러리 캐시 딕셔너리 캐시 그리고 그 외로 나뉩니다. 

 

라이브러리 캐시

SQL 정보 + 실행계획을 캐시하고 있습니다.

 

딕셔너리 캐시

통계 정보 등 실행에 필요한 메타정보를 캐시하고 있습니다.

 

해시 알고리즘

오라클은 SQL문자열을 해시 알고리즘의 인풋으로 하여 출력값을 ID로 하여 SQL문 마다 ID를 생성합니다. 해당 ID를 통해 전에 요청된 실행 계획인지 아닌지 판단합니다. 해시 함수이므로 SQL문의 대소문자가 다르면 SQL 별로 다른 ID를 생성합니다.

 

바인드 변수

SQL 구문이 같고 검색 조건의 값이 서로 다른 경우 해당 값을 바인드 변수를 치환합니다. 그래서 파서 입장에서 공유 풀 내 라이브러리 캐시에 입력 받은 SQL 문의 플랜 유/무를 판단할 때 해당 SQL이 전에 실행된 적이 있는 플랜으로 판단하여 파싱에 소요되는 CPU의 처리시간을 줄이는 역할을 합니다.

 

Parse 유형

하드 파싱

공유 풀에 플랜이 없어 실행 계획을 생성하는 경우

 

소프트 파싱

공유 풀에 플랜이 있어 실행 계획을 재사용하는 경우

 

생각하기

• 실행 계획이 나쁘고 SQL문 성능이 좋지 않을 때
  • 통계 수집 작업을 통한 기초수치가 잘 수집되고 있는지 확인합니다.
  • 수집되고 있지 않다면 dbms_stats 패키지를 실행해서 최신 통계 정보를 수집합니다.
  • 그럼에도 실행계획이 좋지 않다면 힌트나 플랜 스태빌리티 기능을 오라클에게 요구합니다. 장기적 관점에서 앞선 기능을 사용하기에 앞서 최신 통계 정보 + 적절한 인덱싱 + 정기적 모니터링 및 튜닝을 통해 사용을 최소화합니다.
    
    • 힌트 : SQL 구문에 주석을 달아 옵티마이저로 하여금 실행계획을 가이드합니다.
    • 플랜 스태빌리티 : SQL 문에 대해 일관성 있는 실행계획을 유지하기 위해 사용하는 테크닉입니다.
• 하드 파스가 많아 CPU 사용량이 많을 때
  • SQL문을 바인드 변수로 변경할 것 ---> 소프트 파스로 비율 상승
  • 오라클에 패치(Patch Set Release) 파일 적용 후 CURSOR_SHARING의 초기화 파라미터 설정
• 공유풀 크기 튜닝
  • 캐시의 크기가 작아 하드파스로 라이브러리 캐시 내 플랜이 밀릴 경우 공유풀의 크기를 늘려 튜닝합니다

정리

공유풀은 파싱 작업을 줄이고 I/O 성능을 올리는 데 그 목적이 있습니다.