SQL 성능 튜닝과 옵티마이저

SQL 성능 튜닝은 같은 결과를 내는 SQL이 더 적게 읽고 끝나도록 고치는 일입니다. 어느 길로 돌지는 DBMS 안의 옵티마이저가 고르고, 고른 길은 실행 계획으로 볼 수 있습니다.

같은 결과를 내는 길은 여러 가지입니다. 표를 처음부터 끝까지 읽어도 되고, 인덱스를 거쳐 몇 칸만 읽어도 됩니다. SQL을 적는 사람은 '무엇을 원하는지'만 적고 '어떻게 찾을지'는 적지 않습니다. 그 '어떻게'를 정하는 것이 옵티마이저(optimizer)입니다. 그래서 튜닝은 SQL 글자를 다듬는 일이기보다, 옵티마이저가 고른 길을 보고 더 싼 길이 보이게 해 주는 일입니다. 아래 그림은 한 글자도 바뀌지 않은 SQL의 실행 계획이 인덱스와 힌트에 따라 어떻게 갈리는지, 비용 막대로 견주어 보입니다.

SELECT /*+ FULL(학생) */ 이름FROM 학생WHERE 이름 = '이서준';

SELECT 이름FROM 학생WHERE 이름 = '이서준';

1옵티마이저 — 규칙 기반과 비용 기반

옵티마이저가 길을 고르는 방법은 두 가지로 갈립니다.

규칙 기반은 '인덱스가 있으면 인덱스를 쓴다'처럼 정해진 순위를 따라, 표가 작아 전체를 읽는 편이 싼 경우에도 인덱스를 씁니다. 비용 기반은 행 수와 값의 종류 수 같은 통계 정보를 보고 길마다 비용을 어림해 가장 작은 쪽을 고릅니다. 지금 쓰이는 DBMS는 비용 기반이고, 통계 정보가 실제와 어긋나 있으면 비용 기반도 엉뚱한 길을 고릅니다.

2실행 계획 읽기

실행 계획(execution plan)은 옵티마이저가 고른 길을 단계별로 적어 둔 것입니다. 그림의 오른쪽 표가 실행 계획이고, 읽을 때 보는 것은 세 가지입니다.

• 연산: 표를 처음부터 끝까지 읽는 TABLE ACCESS FULL인지, 인덱스를 거치는 INDEX RANGE SCAN인지.
• 대상: 그 연산이 읽는 표나 인덱스의 이름.
• 비용: 옵티마이저가 어림한 일의 양. 작은 쪽이 적게 읽는 길입니다.

비용은 상대적인 값이라 다른 SQL의 비용과 견주는 숫자가 아닙니다. 같은 SQL의 고치기 전과 뒤를 견줄 때 뜻이 있습니다.

전체를 읽는 길과 인덱스를 거치는 길 가운데 어느 쪽이 싼지는 표마다 다릅니다. 가늠하는 기준이 셋입니다.

세 기준이 서로 다른 쪽을 가리키면 어림한 비용으로 갈립니다. 걸리는 행이 똑같이 셋인데 어느 표에서는 전체 스캔이, 어느 표에서는 인덱스가 싸게 나오는 일이 그래서 생깁니다. 표를 다시 찾아가는 값을 옵티마이저가 얼마로 어림하는지는 그 인덱스에 모여 있는 통계 정보에 달려 있고, 통계 정보가 없으면 기본값으로 크게 어림합니다. 그래서 '걸리는 행이 몇 개부터 인덱스를 쓴다'는 경곗값은 외울 수 없습니다. 가늠은 위 세 기준으로 하고, 확인은 실행 계획으로 합니다.

3힌트와 튜닝 절차

힌트(hint)는 옵티마이저에게 길을 지정하는 주석입니다. 그림의 마지막 장면처럼 SELECT 뒤에 /*+ FULL(학생) */을 붙이면 인덱스가 있어도 전체 스캔을 합니다. 옵티마이저보다 사람이 데이터를 더 잘 아는 경우에 쓰고, 통계 정보가 맞는데 힌트로 누르면 더 비싼 길로 갑니다.

튜닝은 다음 순서로 합니다.

1. 느린 SQL을 찾습니다. 재지 않으면 고칠 곳을 고를 수 없습니다.
2. 실행 계획을 보고 어느 연산에서 많이 읽는지 짚습니다.
3. 인덱스를 만들거나, 조건이 인덱스를 쓸 수 있는 꼴로 SQL을 고치거나, 통계 정보를 다시 모읍니다. 힌트는 마지막 수단입니다.
4. 다시 재 비용과 시간이 줄었는지 봅니다.

예제

결과는 Oracle에서 돌린 것입니다. EXPLAIN PLAN FOR 뒤에 SQL을 적으면 그 SQL을 돌리지 않고 계획만 만들어 PLAN_TABLE에 넣습니다. 블록마다 처음 데이터에서 시작합니다 — 앞 블록이 만들거나 바꾼 것은 남아 있지 않습니다.

인덱스가 없을 때

EXPLAIN PLAN FOR
SELECT 이름
FROM 학생
WHERE 이름 = '이서준';
SELECT ID, OPERATION,
  OPTIONS, OBJECT_NAME, COST
FROM PLAN_TABLE
ORDER BY ID;

ID가 0인 줄은 문장 전체이고 1인 줄이 실제로 하는 일입니다. 학생 표를 처음부터 끝까지 읽고 비용은 3입니다.

인덱스를 만든 뒤

SQL은 한 글자도 고치지 않고 이름 열에 인덱스만 만듭니다.

CREATE INDEX 학생_이름_IX
  ON 학생 (이름);
EXPLAIN PLAN FOR
SELECT 이름
FROM 학생
WHERE 이름 = '이서준';
SELECT ID, OPERATION,
  OPTIONS, OBJECT_NAME, COST
FROM PLAN_TABLE
ORDER BY ID;

연산이 인덱스 범위 스캔으로 바뀌고 비용이 3에서 1로 줄었습니다. 찾는 열이 인덱스에 들어 있어 표는 읽지 않았습니다. SQL은 그대로입니다.

힌트로 길을 지정하면

같은 인덱스를 둔 채로 전체 스캔을 지정합니다.

CREATE INDEX 학생_이름_IX
  ON 학생 (이름);
EXPLAIN PLAN FOR
SELECT /*+ FULL(학생) */ 이름
FROM 학생
WHERE 이름 = '이서준';
SELECT ID, OPERATION,
  OPTIONS, OBJECT_NAME, COST
FROM PLAN_TABLE
ORDER BY ID;

계획이 전체 스캔으로 돌아가고 비용도 3으로 올랐습니다. 힌트는 옵티마이저의 선택을 누릅니다.

시험 포인트

• 필기: 옵티마이저는 같은 SQL이 돌 여러 길 가운데 하나를 고릅니다. 규칙 기반은 정해진 규칙의 순위로, 비용 기반은 통계 정보로 어림한 비용이 가장 작은 길로 고릅니다. 실행 계획은 고른 길을 적어 둔 것이고 연산·대상·비용을 봅니다. 힌트는 주석으로 길을 지정해 옵티마이저의 선택보다 앞섭니다. 튜닝 순서는 느린 SQL 찾기, 실행 계획 보기, 고치기, 다시 재기입니다.
• 실기: 실행 계획(Execution Plan)·옵티마이저(Optimizer)·규칙 기반 옵티마이저(RBO, Rule Based Optimizer)·비용 기반 옵티마이저(CBO, Cost Based Optimizer)·힌트(Hint)로 씁니다.

확인 문제

1. 수강 표는 학번과 과목코드를 묶은 기본키가 있고, 그 기본키 인덱스의 앞 열이 학번입니다. 행은 12개입니다. 다음 실행 계획의 ID가 1인 줄로 알맞은 것을 고르세요.

EXPLAIN PLAN FOR
SELECT 점수
FROM 수강
WHERE 학번 = 101;
SELECT ID, OPERATION,
  OPTIONS, OBJECT_NAME
FROM PLAN_TABLE
ORDER BY ID;

• ① INDEX · RANGE SCAN · 수강_PK
• ② INDEX · UNIQUE SCAN · 수강_PK
• ③ TABLE ACCESS · FULL · 수강
• ④ TABLE ACCESS · BY INDEX ROWID · 수강

2. 다음 (가)~(라)에 맞는 이름을 한글 이름과 영문으로 함께 쓰세요. 약어가 있으면 약어와 원어를 함께 씁니다. 답을 끝까지 쓴 뒤 정답을 펼치세요.

• (가) 옵티마이저가 고른 길을 단계별로 적어 둔 것입니다. 연산 이름과 읽는 대상, 어림한 일의 양이 줄마다 적혀 있습니다.
• (나) 표의 행 수와 값의 분포를 모아 둔 자료를 보고, 길마다 들 일의 양을 어림해 가장 적은 쪽을 고르는 방식입니다.
• (다) 데이터를 보지 않고 미리 정해 둔 순위를 따라 길을 고르는 방식입니다. 데이터가 늘어도 같은 길을 고릅니다.
• (라) SQL 안에 주석으로 적어 옵티마이저에게 길을 지정하는 것입니다. 지정한 길이 더 비쌀 수도 있습니다.

3. 1번의 SQL에 수강 표의 인덱스를 쓰라고 힌트를 붙였습니다. 수강 표에 있는 인덱스는 기본키 인덱스뿐입니다. 비용은 어떻게 될지 고르세요. 힌트를 붙이기 전 비용은 3이었습니다.

EXPLAIN PLAN FOR
SELECT /*+ INDEX(수강) */ 점수
FROM 수강
WHERE 학번 = 101;
SELECT ID, OPERATION,
  OPTIONS, OBJECT_NAME, COST
FROM PLAN_TABLE
ORDER BY ID;

• ① 계획이 인덱스 쪽으로 바뀌고 비용은 3보다 작아진다
• ② 계획이 인덱스 쪽으로 바뀌고 비용은 3보다 커진다
• ③ 힌트는 비용 기반 옵티마이저에서 무시되어 계획과 비용이 그대로다
• ④ 계획은 바뀌지만 비용은 어림하지 않아 비어 나온다

4. 같은 SELECT 문을 통계 정보를 다시 모으기 전과 뒤에 각각 돌렸더니, 글자를 한 자도 고치지 않았는데 읽는 길이 달라졌습니다. 두 번 모두 어느 길로 돌지 정한 DBMS 안의 부품 이름을 한글 이름과 영문으로 함께 쓰세요. 답을 끝까지 쓴 뒤 정답을 펼치세요.