옵티마이져(Optimizer/성능최적화기)는 실행계획(Execution Plan)을 만드는 DBMS의 핵심 모듈이다. 옵티마이져가 어떤 판단으로 어떤 실행계획을 만드는지에 따라 SQL의 성능이 달라질 수 있다.
Optimizer
옵티마이저(Optimizer)는 인간의 뇌와 같다. 그리고 서버의 디스크, CPU, 메모리와 같은 하드웨어는 인간의 손이나 발과 같다.
물건을 A 지점에서 B 지점으로 옮기려면, 두 손은 물건을 힘 있게 밀고, 두 발은 왼발과 오른발을 교대로 땅을 힘있게 딛어 앞으로 나가야 한다. 이러한 움직임은 뇌의 명령에 의해서 이루어진다. 데이터베이스 역시 마찬가지다. SQL을 처리하기 위한 움직임들을 옵티마이져가 결정한다. 옵티마이져의 결정은 실행계획으로 만들어지고, 그 실행계획에 따라 디스크와 메모리, CPU가 작업을 진행한다. 인간의 뇌는 얼만큼의 힘을 줄까를 결정할 수 있다. 하지만 그 힘의 강도가 원래 가진 근력의 최대치를 벗어날 수는 없다. 옵티마이져도 마찬가지다. 옵티마이져가 디스크나 메모리, CPU의 물리적인 속도를 올릴 수는 없다. 어떤 순서로 작업을 진행하는 것이 효율적인지를 판단해 줄 뿐이다.
Optimizer와 SQL 튜닝
SQL 튜닝은 옵티마이져가 현명한 판단을 내리도록 돕는 일이다. 하드웨어 성능은 추가적인 비용 이슈로 튜너가 접근하기 어려운 영역이다. 또한, 하드웨어 성능을 아무리 높여도 옵티마이져가 이상한 결정을 내린다면 절대 SQL의 성능이 좋아지지 않는다. 우리가 SQL 튜닝을 열심히 한다면, 다시 말해 옵티마이져가 현명한 판단을 내릴 수 있도록 열심히 도와준다면, 오히려 하드웨어 성능을 낮추어도 데이터베이스가 잘 작동할 것이다.
PostgreSQL에서는 옵티마이져 대신, 쿼리플래너란 용어를 사용하기도 한다. 필자 개인적으로 쿼리 플래너는 조금 더 넓은 개념으로 보여진다.
비용 기반 옵티마이져(Cost-Based Optimizer)
옵티마이져에는 비용 기반(Cost-Based)과 규칙 기반(Rule-Based) 옵티마이져가 존재한다. 비용 기반 옵티마이져는 테이블의 통계를 참고해 작업 방법에 따른 비용을 산출하고, 가장 적은 비용이 드는 실행계획을 만들어낸다. 예를 들어, 데이터를 검색하는데 FULL SCAN 비용이 100이고, INDEX SCAN 비용이 10이라면 비용이 적인 INDEX SCAN을 선택해 실행계획을 만든다. 반대로, FULL SCAN의 비용이 100, INDEX SCAN 1,000이라면 당연히 FULL SCAN을 선택할 것이다. 이러한 비용 계산의 핵심은 통계 정보다. 통계는 테이블에 저장된 데이터의 분포를 수집한 정보다. 비용 기반 옵티마이져는 통계를 기반으로 실행계획을 만들기 때문에, 통계가 제대로 수집되어 있지 않다면 이상한 실행계획을 만들 가능성이 높아진다. 실제로, 말이 전혀 안되는 실행계획이 나왔을때, 통계를 확인해 보면, 통계가 제대로 수집되지 않은 경우가 종종 있다. 규칙 기반 옵티마이져는 통계가 아닌 정해진 규칙(Rule)에 위해서 실행계획을 만든다. 2,000년대 초반만 해도 비용 기반보다는 규칙 기반이 좋다라고 말하는 사람들이 제법 있었다. 하지만 지금은 거의 모든 DBMS가 비용 기반 옵티마이져를 사용한다.
Optimizer Said, 나도 힘들다.
옵티마이져는 말 그대로 ‘최적화기’다. SQL을 좋은 성능으로 처리할 수 있도록 최적화된 실행계획을 만드는 것이 본연의 임무다. 그러나, 무조건적으로 좋은 실행계획을 만들 수 있는 것은 아니다. 옵티마이져가 좋은 실행계획을 만들게 하기 위해서는 아래의 내용들이 준비되어 있어야 한다.
•
잘 작성된 SQL
•
적절한 인덱스 구성
•
적절한 통계
위의 항목들이 잘 준비되어 있어도 옵티마이져는 ‘최적’이란 이름이 무색하게 엉뚱한 실행계획을 만들기도 한다. 실행계획을 만드는 작업은 비교적 큰 비용(CPU, 메모리 등)이 든다. SQL의 FROM 절에 사용한 테이블이 많을수록, 좋은 실행계획을 만드는데 더 많은 시간이 필요해진다. SQL에 A와 B 두 개의 테이블만 있다면 테이블을 연결(조인)하는 순서는 ‘A에서 B로’, 또는 ‘B에서 A로’ 이처럼 두 가지 순서밖에 없다. 하지만 테이블이 세개라면 다음과 같은 다양한 순서를 고려해야 한다.
•
A→B→C
•
A→C→B
•
B→A→C
•
B→C→A
•
C→A→B
•
C→B→A
만약에 테이블이 네 개라면, 24가지의 다양한 조인 순서를 고려해야 한다. 여기에, 테이블별로 인덱스와 조인 알로리즘까지 고려한다면 더 많은 후보 계획을 고려해야 한다. 그러므로 테이블이 많아질수록, 옵티마이져는 더 많은 시간이 있어야만 좋은 실행계획을 만들 수 있다. 하지만 무한정으로 실행계획만 만들 수는 없다. 주어진 시간내에 실행계획을 만드는 작업을 마무리해야 한다. 결국, SQL이 복잡하고 사용되는 테이블이 많아질수록 이상한 실행계획이 만들어질 가능성이 생긴다.
SQL과 인덱스를 완벽하게 준비했음에도 복잡한 SQL에서는 기대에 못 미치는 실행계획이 나올 때가 있다. 이럴 때면 ‘옵티마이저’라는 이름이 무색하다고 느끼지만, 현실적으로 최적의 경로를 찾기 위해 가장 많이 고민해 주는 존재도 결국 옵티마이저다.
실행계획의 재사용
옵티마이져가 실행계획을 만드는 과정은 제법 비용이 많이 드는 작업이다. 그러므로 한 번 만들어진 실행계획은 재사용하는 것이 데이터베이스 성능에 도움을 준다. ORACLE은 만들어진 실행계획을 서버 레벨(정확히는 Shared Pool의 Library Cache)에 저장해 모든 세션이 저장된 실행계획을 재사용할 수 있도록 되어 있다. 반면에 PostgreSQL과 MySQL은 만들어진 실행계획은 세션별로만 저장해 같은 세션내에서만 공유가 이루어진다.
예를 들어, 천 줄 이상의 SQL이 실행되었다고 가정해보자. 해당 SQL은 실행계획을 만드는데 0.1초, 실행계획대로 데이터를 처리하는데 0.5초가 걸린다고 가정한다. ORACLE은 해당 SQL이 처음 실행되는 순간 실행계획을 만들어 서버 영역에 저장한다. 이후에 동일한 SQL이 실행되면, 이미 만들어진 실행계획을 재사용한다. 그러므로 실행계획을 다시 만들 필요가 없다. 결과적으로 SQL을 처음 실행할 땐, 총 0.6초(실행계획 생성 0.1초, 데이터 처리 0.5초)가 걸렸지만, 그 다음부터는 0.5초의 시간만 걸린다.
거렸지만 그 다음 사용자들은
ORACLE은 해당 SQL이 처음 실행되는 순간 실행계획을 만들어 서버 영역에 저장한다. 이후에 동일한 SQL을 실행하는 사용자는 이미 만들어진 실행계획을 사용해 바로 데이터 처리 작업을 시작한다.
아래 SQL은 천
SELECT *
FROM tr_ord t1
INNER JOin tr_ord_det t2
ON (t1.ord_no = t2.ord_no)
...생략...
SQL
복사
필자의 개인적인 경험에, ORACLE의 옵티마이저가 일반적으로 좋은 실행계획을 만든다. 물론 간단한 SQL이나 특정 SQL 패턴에서는 PostgreSQL이나 MySQL이 더 좋은 실행계획을 만들기도 한다. 하지만 SQL이 길고 복잡할 수록 ORACLE이 좀 더 좋은 실행계획을 만드는 편이다. 물론, 필자의 개인적인 경험이기 때문에 각자가 느껴온 바가 다를 수 있다.
ORACLE은 만들어진 실행계획을 서버 레벨(정확히는 Shared Pool의 Library Cache)에 저장해 모든 세션이 저장된 실행계획을 재사용할 수 있다. 반면에, PostgreSQL과 MySQL은 일반적으로 실행계획을 동일 세션에서만 공유할 수 있다. (이러한 사항은 버젼에 따라 달라 질 수 있을 것이다. 지금 이야기하는 기준은 PostgreSQL 16.4와 MySQL 8.0.35 기준이다.)
아마도, ORACLE은 한 번 만든 실행계획을 모두가 공유할 수 있기 때문에, 실행계획을 만들 때 약간의 시간을 더 투자할 수 있는 여지가 있으리라 생각한다. 물론, 오랜 역사를 가진 ORACLE의 옵티마이져가 더 좋은 실행계획을 만드는 노하우가 쌓여있는 것이 가장 큰 이유라 생각한다.
ORACLE이 더 좋은 실행계획을 만들 수 있는 가장 주된 이유
한 번 만들어진 실행계획은 모두가 공유할 수 있기 때문에, ORACLE은 처음 실행계획을 만들때 약간의 시간을 더 투자할 여유가 있다.
하지만 결국, ORACLE은 서버 레벨에서 실행계획을 공유하므로, 처음 실행계획을 만들때 공을 들여 만들어 놓은 실행계획을 모두가 재사용할 수 있기 때문이다. 반면에 PostgreSQL과 MySQL은 실행계획을 비교적 빠른 시간에 만들어 SQL을 실행해야 한다.