[Oracle] DBMS와 실행 계획

1. 데이터에 접근하는 방법의 결정

RDB를 조작할 때는 SQL이라는 전용 언어를 사용합니다. 사용자 또는 개발자가 데이터베이스에서 의식적으로 사용하는 것은 SQL 레밸까지입니다. 이후의 모든 것은 SQL 구문을 읽어들인 DBMS가 알아서 처리하고 결과를 제공해주기 때문에 사용자는 데이터가 있는 곳을 알 필요도 없고, 데이터에 접근하는 방법도 따로 생각하지 않습니다. 

 

RDB에서 데이터 접근 절차를 결정하는 모듈은 쿼리 평가 엔진입니다. 쿼리 평가 엔진은 사용자로부터 입력받은 SQL 구문을 처음 읽어들이는 모듈입니다. 쿼리 평가 모듈은 추가로 파서 또는 옵티마이저와 같은 여러 개의 서브 모듈로 구성됩니다. 

 

● 파서

파서의 역할은 구문을 분석하는 것 입니다. 사용자로부터 입력받은 SQL구문이 올바른지 검사를 해줍니다. 

 

● 옵티마이저

파서를 통해 서류 심사를 통과한 쿼리는 옵티마이저로 전송됩니다. 옵티마이저의 한국어 번역은 '최적화'입니다. 이때 최적화의 대상은 데이터 접근법(실행 계획)입니다. 옵티마이저가 바로 DBMS 두뇌의 핵심입니다. 옵티마이저는 인덱스 유무, 데이터 분산 또는 편향 정도, DBMS 내부 매개변수 등의 조건을 고려해서 선택 가능한 많은 실행 계획을 작성하고 이들의 비용을 연산하며, 가장 낮은 비용을 가진 실행 계획을 선택합니다. RDB가 데이터 접근의 절차 지향 결정을 자동화하는 이유는 이런 귀찮은 일을 기계적으로 처리하기 위해서입니다.

 

● 카탈로그 매니저

옵티마이저가 실행 계획을 세울 때 옵티마이저에게 중요한 정보를 제공하는 것이 카탈로그 매니저입니다. 카탈로그란 DBMS의 내부 정보를 모아놓은 테이블들로, 테이블 또는 인덱스의 통계 정보가 저장되어 있습니다. 따라서 간단하게 '통계 정보'라고 부르기도합니다. 

 

● 플랜 평가

옵티마이저가 SQL 구문에서 여러 개의 실행 계획을 세우고 최적의 실행 결과를 선택하는 것이 플랜 평가입니다. 실행계획이라는 것은 곧바로 DBMS가 실행할 수 있는 형태의 코드가 아닙니다. 인간이 읽기 쉽게 만들어진 계획서입니다. 따라서 성능이 좋지 않은 SQL 구문이 있을 때 실행 계획을 읽고, 수정 방안을 고려할 수 있습니다. 

 

이렇게 하나의 실행 계획을 선택하면, DBMS는 실행 계획을 절차적인 코드로 변환하고 데이터 접근을 수행합니다.

 

2. 옵티마이저와 통계 정보

옵티마이저가 명령하는 대로 다 잘 처리해주는 만능은 아닙니다. 특히 카탈로그 매니저가 관리하는 통계 정보에 대해서는 데이터베이스 엔지니어가 항상 신경 써줘야합니다. 옵티마이저가 실패하는 패턴이 몇 가지 있는데, 통계 정보가 부족한 경우가 대표적인 원인으로 꼽힙니다. 카탈로그에 포함되어 있는 통계 정보는 다음과 같은 것들입니다.

 

 - 각 테이블의 레코드 수

 - 각 테이블의 필드 수와 필드의 크기

 - 필드의 카디널리티(값의 개수)

 - 필드값의 히스토그램(어떤 값이 얼마나 분포되어 있는지)

 - 필드 내부에 있는 NULL 수

 - 인덱스 정보

 

이러한 정보를 활용하여 옵티마이저는 실행 계획을 만듭니다. 문제가 생기는 경우는 카탈로그 정보가 테이블 또는 인덱스의 실제와 일치하지 않을 때 입니다. 테이블에 데이터 삽입/갱신/제거가 수행될 때 카탈로그 정보가 갱신되지 않는다면, 옵티마이저는 오래된 정보를 바탕으로 실행 계획을 세웁니다. 과거 정보를 통해 계획을 세우므로 잘못된 계획을 세울 수 밖에 없습니다.

 

3. 최적의 실행 계획 작성

올바른 통계 정보가 모이는 것은 SQL 성능에 굉장히 중요한 문제입니다. 따라서 테이블의 데이터가 많이 바뀌게 되면 카탈로그의 통계 정보도 함께 갱신해야 하는 것은 데이터베이스 엔지니어의 상식입니다. 수동으로 갱신하는 것 뿐만 아니라, 데이터를 크게 갱신하는 배치 처리가 있을 때는, Job Net을 조합하는 경우도 많고, Oracle 처럼 기본 설정에서 정기적으로 통계 정보 갱신 작업이 수행되는 경우도 있습니다. 통계 정보 갱신은 실행 비용이 굉장히 높은 작업입니다. 하지만 DBMS가 최적의 플랜을 선택하려면 필요 조건이므로 갱신 시점을 확실하게 검토해야합니다. 다음은 Oracle의 통계 정보 갱신 명령어 입니다. 기본적인 구문이르몰 옵션 지정에 따라 테이블 단위가 아니라 스키마 전체를 기준으로 구하거나, 샘플링 레이트를 지정하거나, 테이블에 부여된 인덱스의 통계 정보도 함께 구할 수 있는 등의 다양한 제어가 가능합니다.

 

exec DBMS_STATS.GATHER_TABLE_STATS(OWNNAME
=> [스키마이름], TABNAME => [테이블 이름]);

* 출처: DB 성능 최적화를 위한 SQL 실전 가이드(미크 지음, 윤인성 옮김)