[DB] 친절한 SQL 튜닝 - SQL 처리 과정, 실행 계획

0. 정의

SQL : Structured Query Language

의미 : 구조적 질의 언어

 

우리가 흔히 사용하는 SQL문인 

SELECT 컬럼명 FROM 테이블명 WHERE 조건문 방식의 질의 언어이다.

우리가 적은 이 문장은 절차적으로 순서에 맞게 해석이 될 수 밖에 없다.

해석을 해주는 방법은 프로시저(함수)를 통해서 진행이 되어야하는데 그 프로시저를 만들어주는 DBMS 내부 엔진이 SQL 옵티마이저다.

DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태로 만드는 전 과정을 SQL 최적화라고 한다.

 

1. SQL 실행 과정

SQL을 실행하기 전 최적화 과정

 

1. SQL 파싱

사용자로부터 전달받은 질의문을 파서가 파싱진행

Syntax(문법) 오류 체크

파싱 트리 생성(각 SQL문의 구성요소를 파악해서 트리 생성)

Semantic 체크 (의미상 오류나 존재하지 않는 테이블, 컬럼 여부, 권한 여부 체크)

 

2. SQL 최적화

옵티마이저를 통해서 미리 수집한 시스템 및 오브젝트 통계정보를 바탕으로 다양한 실행경로를 생성하여 비교 후 효율적인 하나를 선택한다.

 

3. 로우 소스 생성

SQL 옵티마이저가 선택한 실행경로를 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅하는 단계

 

1.1 옵티마이저

네비게이션의 역할과 흡사하다.

옵티마이저는 사용자의 질의문을 바탕으로 후보군이 될만한 실행계획을 찾아내는데 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 정보를 이용해서 계획의 예상비용을 산정한다.

 

옵티마이저의 실행계획과 비용

우리가 네비게이션을 사용할때 미리 다양한 경로를 추천 받고 선택 할 수 있듯이 옵티마이저도 실행 계획을 미리 보고 각 실행 순서르 처리 절차를 확인 할 수 있다. 또한 원하는 경로로 변경시킬 수 있다.

 

1.2 실행 계획 예시 코드

데이터가 적기 때문에 정확한 테스트가 어려웠다.

 

// 테이블 생성
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    name VARCHAR2(100),
    department_id NUMBER,
    salary NUMBER
);

// 임시 데이터 넣기
insert into employees values (1, 'tory', 1, 10000);
insert into employees values (2, 'mimi', 2, 20000);
insert into employees values (3, 'baba', 3, 30000);
insert into employees values (4, 'mama', 4, 40000);

// 인덱스 생성 1 (부서 아이디로만 인덱스 생성)
CREATE INDEX idx_employee_department ON employees(department_id);

// 인덱스 생성 2 (부서 아이디로 + 이름 + 급여로 인덱스 생성)
CREATE INDEX idx_employee_department2 ON employees(department_id, name, salary);

 

인덱스는 쉽게 생각하면 사전의 앞쪽에 자음 + 모음으로 사용자가 빠르게 원하는 단어를 찾을 수 있도록 분류해놓은 것이라고 할 수있다.

예를 들어서 우리가 찾고자하는 단어가 마우스라면 가나다라마바사 순서로 읽다가 마에서 멈춰서 쭉 원하는 단어를 찾을 것이다. 인덱스는 이런 방법 처럼 테이블의 정보를 우리가 원하는 그룹으로 묶어서 미리 선별해놓는 작업이라고 볼 수 있다.

따라서 특정 작업에서는 매우 빠르게 동작한다.

(소량의 데이터를 가져올때 빠르다 -> 대량의 데이터에서는 인덱스가 single bolck형태로 동작하기 때문에 테이블 풀 스캔보다 느리다)

 

옵티마이저(네비게이션)가 자동으로 동작하도록 맡기고 실행계획 확인 하는 예제

// 쿼리 실행
EXPLAIN PLAN FOR
select * from employees e where department_id = 2;

// 실행 계획 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

 

1번째 인덱스를 적용한 예제

// 1번째 인덱스를 적용한 쿼리 (힌트를 적용해서 인덱스 강제)
// 1번째 인덱스는 부서 아이디만 적용하여 분류한 작업

EXPLAIN PLAN FOR
select /*+ INDEX(e idx_employee_department) */ * from employees e where department_id = 2 and name = 'mimi' and salary = 20000;

// 실행 계획 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

// 실행 결과
// 아래 결과에서 filter를 통해서 한번 조건을 걸고 그 다음에 액세스를 진행 했다.
// 2단계로 진행
Plan hash value: 3502024757
 
-------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                    | Rows  | Bytes | Cost (%CPU)| Time     |
-------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                         |     1 |    91 |     1   (0)| 00:00:01 |
|*  1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES               |     1 |    91 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMPLOYEE_DEPARTMENT |     1 |       |     1   (0)| 00:00:01 |
-------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   1 - filter(""NAME""='mimi' AND ""SALARY""=20000)"
"   2 - access(""DEPARTMENT_ID""=2)"
 
Note
-----
   - dynamic sampling used for this statement (level=2)

 

2번째 인덱스를 적용한 예제

// 2번째 인덱스를 적용한 쿼리 (힌트를 적용해서 인덱스 강제)
// 2번째 인덱스는 부서 아이디 + 이름 + 급여로 적용하여 분류한 작업
// 따라서 아래의 쿼리문에 조건절과 정확히 일치

EXPLAIN PLAN FOR
select /*+ INDEX(e idx_employee_department2) */ * from employees e where department_id = 2 and name = 'mimi' and salary = 20000;

// 실행 계획 확인
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY);

// 실행 결과
// filter는 없고 access만 정확히 한번에 조회하여 가져오기때문에 1번 인덱스보다 효율적이라고 볼 수 있다.
Plan hash value: 4207677018
 
--------------------------------------------------------------------------------------------------------
| Id  | Operation                   | Name                     | Rows  | Bytes | Cost (%CPU)| Time     |
--------------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT            |                          |     1 |    91 |     1   (0)| 00:00:01 |
|   1 |  TABLE ACCESS BY INDEX ROWID| EMPLOYEES                |     1 |    91 |     1   (0)| 00:00:01 |
|*  2 |   INDEX RANGE SCAN          | IDX_EMPLOYEE_DEPARTMENT2 |     1 |       |     1   (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------------
 
Predicate Information (identified by operation id):
---------------------------------------------------
 
"   2 - access(""DEPARTMENT_ID""=2 AND ""NAME""='mimi' AND ""SALARY""=20000)"
 
Note
-----
   - dynamic sampling used for this statement (level=2)

 

2. SQL의 공유 및 재사용

앞서본 2.SQL의 실행과정에서 SQL을 파싱, 최적화, 로우 소스 생성 과정 속에서 생성한 프로시저를 반복 재사용 할 수 있도록 캐싱해 두는 메모리 공간을 라이브러리 캐시라고 한다.

여기서 두가지로 나눠지는데

 

2.1 파싱 방법

1번째 방법 - 하드 파싱(라이브러리 캐시에 SQL이 캐시되지 않은 경우)

처음 SQL을 실행하면 SQL을 파싱 하고 라이브러리 캐시에 존재하는지 체크한 후에 없으면 최적화 후에 로우소스를 생성하고 실행한다.

 

2번째 방법 - 소프트 파싱 (라이브러리 캐시에 SQL이 캐시 된 경우)

SQL을 실행하면 라이브러리 캐시에 존재하기 때문에 찾은 즉시 바로 실행한다.

 

왜 라이브러리 캐시에 저장하는지 이유

우리가 네비게이션으로 서울에서 부산으로 갈때 가는 길의 경우의 수는 무수히 많이 존재하는데 거기서 최적화 된 하나의 길을 도출 해내는 과정은 얼추봐도 꽤 무거운 연산으로 보인다.

실행계획도 마찬가지로 테이블 조인하는 방법, 스캔 방법등 여러 경우의 수가 존재한다. 매번 이 경우의 수를 고려해 SQL을 생성한다면 큰 영향을 줄 수 있다. 따라서 매번 하드 파싱하지 않도록 하는 것이 라이브러리 캐시가 존재하는 이유이다.

 

SQL이 라이브러리 캐시에 영구 저장되는 방식

IBM DB2같은 경우 실제로 SQL도 영구 저장이 되지만 오라클과 SQL server같은 DBMS는 그렇지 않다.

 

영구적인것과 반?영구적인 차이에서 트레이드 오프는 존재한다.

예를 들어서 하드파싱한 SQL을 매번 저장한다고 했을때 일정한 양에서는 저장하고 찾는데 속도가 반영구적인 방법보다는 빠를 수 있다.

 

다만 그 양이 너무 많아진다면 찾는 속도가 느려질 것 이다.

반대로 어느정도 양이 차면 자주 사용되지않는 SQL은 삭제되도록 하는 방법의 경우에는 매번 자주사용되는 부분만 저장이되어서 빠르지만 간혹 사용되는 쿼리가 들어오는 경우에 있어서는 하드파싱이 일어나서 더 느릴 수 있다.

 

2.2 바인드변수 사용하여 SQL 재사용

SQL은 이름이 없기 때문에 조금만 달라도 새로운 SQL로 파싱되어 라이브러리 캐시에 저장된다.

 

예시 상황 )

사내 어드민에 이벤트가 진행되어 00시에 선착순 5명 상품을 준다고 했을때 쿼리를 두가지를 비교해서 재사용되는지 체크할 수 있다.

 

1. SQL이 하드파싱 되는 경우

// 아래와 같이 사용자들이 각 이름에 fit하게 조회가 들어가는 경우
select * from employees e where name = 'tory';
select * from employees e where name = 'mimi';
select * from employees e where name = 'pepe';

// 라이브러리 캐시 확인 쿼리
select sql_id, executions, loads, sql_text from v$sql where sql_text like '%employee%';

// 라이브러리 캐시 결과
select * from employees e where name = 'tory';
select * from employees e where name = 'mimi';
select * from employees e where name = 'pepe';

같은 테이블에 같은 조건으로 걸었지만 이름마다 새로운 SQL을 하드파싱한다.

 

2. SQL이 소프트파싱 되는 경우

// 아래와 같이 사용자들이 이름이 바인드 변수로 할당되는 경우
select * from employees where name = :test;

// 라이브러리 캐시 확인 쿼리
select sql_id, executions, loads, sql_text from v$sql where sql_text like '%employee%';

// 라이브러리 캐시 결과 (하나의 SQL만 생성됨)
select * from employees where name = :"SYS_B_0"

// 만일 DB자체에서 쿼리를 날렸는데 계속 하드파싱 되는경우
// Oracle은 SQL 문에서 상수 값을 자동으로 바인드 변수로 대체하여 커서 공유를 강제한다.
ALTER SYSTEM SET CURSOR_SHARING = 'FORCE' SCOPE=BOTH;

바인드변수로 SQL을 파싱한 결과 소프트 하나의 SQL을 공유하여 사용하게된다.