그림으로 공부하는 오라클 구조 - Ch4. SQL문 분석과 공유 풀

 

SQL문 분석 with 옵티마이저

SQL과 실행 계획

- SQL은 처리 방법(절차)를 기술하지 않는다.(이렇게 저렇게 해라라는 방법에 대한 기술이 없다)

- 대신 옵티마이저(파서)라고 불리는 기능이 실행 계획(plan)이라는 처리 방법을 생성한다. 이 작업은 서버 프로세스의 SQL문 분석에 해당하는 작업이다.

- 실행 계획은 규칙 기반(rule base)과 비용 기반(cost base)라는 알고리즘을 가지고 생성한다. 하지만 규칙 기반은 더 이상 쓰이지 않아 비용 기반만 고려한다.

- 비용 기반이란 '처리 시간이나 I/O 횟수가 가장 적을 것으로 예상되는 처리 방법이 최상'이라는 알고리즘이다. 

- 이 비용을 계산하기 위해서 여러 통계 정보를 사용한다.

옵티마이저 동작 원리

- 비용 계산을 위해 데이터 딕셔너리 뷰의 USER_TAB_STATISTICS와 같은 통계 정보를 이용한다.

비용 계산에 필요한 정보들

실행 계획 수립의 한계와 공유 풀(Shared Pool)

 어떤 처리 방법이 가장 좋은지(비용이 적은지)를 판단하기 위해서는 모든 처리 방법의 비용을 비교해야 한다. 모든 처리 방법을 비교한다는 것은 수 많은 경우의 수에 대한 예상치를 계산해야하기 때문에 그 자체로 비용(자원)이 많이 든다. 즉, 분석에 드는 CPU 자원이 아까워지는 현상이 발생할 것이다. 그럼 이 실행 계획을 공유해서 자원 소비를 줄이는 방법을 자연스럽게 생각하게 된다. Ch3에서 이미 캐시와 공유 메모리에 대해 알아보았다. 이 실행계획도 서버 프로세스들이 서로 공유한다면 실행 계획을 수립하는 데 사용되는 자원을 줄일 수 있다. (*또한, 선정된 실행 계획이 무조건 가장 좋은 계획이 아닐 수 있다. 어디까지나 예상이기 때문이다. SQL 튜닝(인덱스 등을 활용한)을 통해 더 나은 실행 계획을 세우도록 유도할 수 있다.)

 공유 풀(Shared Pool)이라는 공간이 공유 메모리 영역(SGA)에 존재한다. 공유 메모리는 대부분 버퍼 캐시로 사용되고 남은 일부가 공유 풀로 사용되어 그 안에 통계 정보나 실행 계획 등의 캐시 데이터가 저장된다.

 실행 계획 등은 라이브러리 캐시(Libary Cache) 공간에 캐싱된다.

공유 풀의 구조

 

같은 SQL은 같은 실행 계획을 사용한다. 그러면 오라클은 어떻게 같은 SQL을 판단할까?

바인드 변수의 사용

SELECT ID, CUST_NAME, TEL_NO
  FROM CUST_INFO
 WHERE ID = '001'

SELECT ID, CUST_NAME, TEL_NO
  FROM CUST_INFO
 WHERE ID = '002'

-- 위 두 가지 SQL은 오라클이 다른 SQL로 취급한다. 오라클은 SQL문을 하나의 문자열로 간주하기 떄문이다.

SELECT ID, CUST_NAME, TEL_NO
  FROM CUST_INFO
 WHERE ID = :P1

SELECT ID, CUST_NAME, TEL_NO
  FROM CUST_INFO
 WHERE ID = :P1

-- 바인드 변수를 사용하여 SQL을 실행하면 오라클은 'P1'에 어떤 값이 담기든 같은 SQL로 간주한다.
-- 같은 SQL이 실행된 것으로 판단하여 이전에 캐시에 저장해둔 실행 계획을 가져와 SQL을 처리한다.

- Hard Parse : 공유 풀에 실행 계획이 없어 실행 계획을 새로 생성. 위 경우에 해당한다. 사용자(Client)는 같은 SQL이라고 생각해도 오라클은 그렇게 판단하지 않는다.

- Soft Parse : 공유 풀에 있는 실행 계획을 재사용. 아래 경우에 해당한다.

 

 이처럼 공유 풀과 바인드 변수를 사용하여 소프트 파스를 유도하여 실행 계획 수립에 대한 비용을 낮추는 방법이 사용된다.

 

공유 풀 정보 with statspack report

- Statspack은 오라클의 분석용 도구이다.

예시 정보

실제 예시

- 위 통계를 보고 parse를 위한 CPU사용량 등이 적절한 지에 대한 판단을 할 수 있을 것

정리

- SQL문은 처리 방법에 대한 기술이 없어 분석(parse)을 통해 처리 방법(실행 계획)을 수립한다.

- 실행 계획에도 좋고 나쁨이 있다. 

- 실행 계획을 생성하는 데 사용되는 비용을 줄이기 위해 공유 풀(라이브러리 캐시)에 실행 계획을 캐시해서 재활용.