SQL문 분석과 공유 풀

May 1st, 2022

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

SQL은 ‘처리 방법(절차)’를 기술하지 않는다는 점이 일반 프로그래밍 언어와 가장 큰 차이다. SQL문은 데이터의 조건이나 관계만을 기술한다. ‘SELECT A FROM B WHERE C = 1’은 테이블 B에서 C =1이라는 조건을 만족하는 A속성을 가져오라는 SQL문이다. 여기서 ‘무슨 인덱스를 사용하라’, ‘풀 스캔을 사용하라’와 같은 처리방법을 기술하지 않는다.

RDBMS에서는 이러한 처리 방법을 옵티마이저(파서)라고 불리는 기능이 처리방법을 생각해준다. 옵티마이저가 SQL문을 분석하고 실행 계획이라고 하는 처리 방법을 생성해준다.

분석과 서버 프로세스

오라클에서 분석은 SQL문을 분해해서 어떤 요소(테이블, 컬럼 등)로 구성되어 있는지 조사하는 것 뿐만 아니라 어떤 식으로 처리할지 생각하는 것을 말한다. 이러한 생각하는 것은 서버 프로세스에서 일어난다. 그리고 이 생각한다라는 것은 컴퓨터가 하는 것이므로 알고리즘 기반으로 처리하게 된다. 오라클은 규칙기반과 비용기반이라는 알고리즘이 있다. 오라클 10g부터는 규칙기반을 지원하지 않는다.

비용 기반은 처리 시간이나 i/o 횟수가 가장 작다고 생각되는 처리방법이 최상이다라는 알고리즘이다. i/o횟수를 예측하기 위해 비용이라고 불리는 수치를 이용한다. 이 비용은 처리에 필요하다고 생각되는 시간 또는 자원 사용량을 말한다.

비용을 계산하기 위한 기초 수치, “통계 정보”

시간을 예측하기 위해서는 기초 수치(데이터양 등)가 필요하다. 비용은 ‘통계 정보’라 불리는 기초 수치를 기반으로 계산된다. 이 테이블에는 데이터가 몇 row가 존재하고 있고, 데이터 양은 이정도다. 컬럼의 데이터 최댓값과 최솟값은 이런 값이다. 와 같은 정보가 기초 수치이다. 오라클에서는 통계정보를 애널라이즈(Analyze)라고 불리는 작업을 통해 얻는다. 애널라이즈는 오라클이 자동으로 수행한다.

SQL문 정보

  • 어떤 테이블의 어떤 데이터인지
  • 어떤 조건(WHERE)
  • 어떤 관계(어떤 테이블과 어떤 테이블이 조인하는지)

초기화 파라미터

  • 단일 I/O로 읽어올 수 있는 블록 수
  • 세션에서 이용할 수 있는 메모리 크기

옵티마이저 통계

  • 테이블통계(로우 및 블록정보)
  • 컬럼 통계(컬럼 값 및 데이터 분포 정보)
  • 인덱스 통계(인덱스의 깊이, 인덱스 블록의 수 및 인덱스와 데이터블록의 관계 정보)
  • 시스템 통계(I/O, CPU의 성능 및 사용률 정보)

옵티마이저는 주로 이 세 개의 정보로 비용을 계산하여 최적의 실행계획을 생성한다.

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

SELECT * FROM A, B WHERE A.ID = B.ID AND A.value = 1 AND B.value = 1;

위와 같은 쿼리가 있을 때 실행 방법은 다음과 같이 있다.

  1. 일반 실행 계획
    • A테이블에서 value 컬럼이 1인 데이터를 가져와 A의 ID가 1임을 안다
    • A.ID = B.ID라고 기술된 것을 토대로 인덱스를 사용해서 ID 컬럼의 값이 1인 데이터를 가져온다.
  2. 최적실행이 아닌경우
    • A테이블에서 인덱스를 사용해 value컬럼이 1인 데이터를 가져온다. 이때 가져온 데이터가 1000만건이다.
    • A.ID=B.ID라고 기술된 것을 토대로 인덱스를 사용해 데이터를 1000만번 꺼내오려고 한다. 얻은 데이터 중에 value가 1인 데이터를 꺼낸다.
  3. 최적실행의 경우
    • B테이블에서 인덱스를 사용해 value가 1인 데이터를 가져온다.
    • A.Id = B.Id인 것을 토대로 A테이블에서 인덱스를 사용해 ID칼럼의 값이 1인 데이터를 가져온다.

이렇게 DBMS는 실행 계획의 좋고 나쁨으로 인해 매우 큰 성능 차이가 발생한다.

이러한 처리 방법을 고르는 방법은 기본적으로 모든 처리 방법의 비용을 계산해서 비교하는 것이외에는 방법이 없다. 가령 테이블이 한개일때는 풀스캔을 할것인지 인덱스를 사용할것인지만을 고려하면 되지만 테이블이 늘어나면 모든 조합가능한 방법을 고려해야한다.

간혹 DBMS가 좋지 않은 실행 계획을 선택하는 이유는 선택할 수 있는 실행 계획의 수가 많다는 점과 어디까지나 예측에 지나지 않는다는점 때문이다.

오라클 12c부터는 어댑티브 쿼리 최적화(Adaptive Query Optimization)이라는 옵티마이즈 통계를 보정하는 기능이 추가되었다. 사람이 수고하지 않더라도 좀 더 성능이 좋은 실행계획을 선택할 수 있게 되었다.

실행계획은 사전에 수집해둔 통계 정보를 참고해서 처리 대상 테이블의 추세를 파악한다. 주목해야할건 통계 정보는 사전에 수집된다라는 점이다. SQL문이 실행할때 통계 정보가 실데이터와의 괴리가 있다면 최적이 아닐수가 있다. 이때문에 DBA가 적절한 수집 시점이나 빈도 수준으로 통계 정보를 수집하는 작업을 수행한다.

오라클 12c에서 추가된 어댑티브 쿼리 최적화 기능으로 SQL문을 실행할때 추가로 통계 정보를 동적으로 수집해 실행계획을 선택할 수 있게되었다. 그러나 동적으로 수집하는 만큼 분석시간도 길어진다.

공유 풀의 동작과 구조

분석 처리가 CPU 자원을 얼마나 소비하는지 알아보자

SQL문을 실행했을 때 실제 데이터를 처리하는데 필요한 CPU자원보다 SQL문 분석단계에서 더 많은 CPU를 소모할 수도 있다. 실행 계획을 공유(돌려쓰기)해서 자원 소비를 절약할 수 있다.

공유 풀은 실행 계획을 재사용하여 분석 작업을 줄이기 위한 존재라고 봐도 좋다. 공유 풀을 잘 활용하면 cpu자원의 절약으로도 이어진다. 공유 풀도 프로세스 간에 공유되어야 하므로 버퍼 캐시와 마찬가지로 공유 메모리에 있다. 공유 메모리의 많은 부분이 버퍼 캐시로 사용되고 일부를 공유 풀로 사용한다.

277784335517EFA72F

오라클은 해시 알고리즘을 사용해 SQL문마다 ID를 생성한다. SQL문을 문자열로 해시 함수에 입력하고 함수에서 출력된 해시 값을 SQL문의 ID로 사용한다. 쿼리의 대문자와 소문자가 다르면 받는 해시 값도 달라진다. 그리고 검색 조건등은 바인드 변수로 사용하면 같은 SQL문으로 판단한다.

select id from a; -- (1)
Select id from a; -- (2)
-- 대문자 차이로 둘은 다른 쿼리
select id from a where id = 1; -- (1)
select id from a where id = 100; -- (2)
-- 조건의 값은 다르지만 바인드 변수를 사용해 같은 실행계획으로 본다.

분석에는 하드 파스(hard parse)와 소프트 파스(soft parse)가 있다. 하드 파스는 지금까지 설명해왔던 분석을 말한다. 소프트 파스는 해시값을 요청한 결과 공유 풀에 캐시가 되어있다면 재사용하는 것을 말한다.

결론

  • SQL문에는 처리방법이 없기 때문에 오라클이 처리 방법을 생성한다.
  • 실행 계획의 좋고 나쁨에 따라 성능이 크게 변한다.
  • 실행 계획을 생성하기 위해서는 많은 양의 CPU를 사용하기 때문에 공유 풀에 실행 계획을 캐시해두고 재활용한다.

통계 정보는 언제 수집하는가?

적절한 실행 계획이 있어야 성능 문제를 일으키지 않을 수 있다. 통계 정보를 수집하기 위해서는 시점을 고려해야한다.

만일 통계 수집은 22:00~02:00에 실행되고 매일 밤 21시에 수행되는 배치가 있고 배치 처리로 인해 테이블의 데이터양이 낮시간대에 비해 1/1000정도로 준다고 가정한다.

데이터양이 줄어든 시점에서 수집된 통계 정보를 기반으로 만들어진 실행 계획을 데이터 양이 많은 낮시간에 사용한다면 성능이 다를것이다.

이처럼 데이터양의 증감이 반복되는 상황에서 데이터양이 많은 낮시간대의 처리를 대비하기위해서는 일반적으로 데이터양이 많은 시점에 통계 정보를 수집하면 적절한 실행계획이 만들어질것이라 예측할 수 있다.