1장. SQL 처리 과정과 I/O

1.1 SQL파싱과 최적화

 

SQL을 실행 전, 아래와 같은 최적화 과정을 수행한다.

1) SQL 파싱

• SQL Parser가 파싱을 진행하는 단계
• SQL문을 이루는 개별 구성요소를 분석해서 파싱 트리를 생성하고,
• 문법적 오류가 없는지 확인한다.
• 의미상 오류가 없는지 확인한다.

2) SQL 최적화

• 옵티마이저가 활약하는 단계
• 미리 수집한 통계정보를 바탕으로 다양한 실행경로를 생성 및 비교한 후 가장 효율적인 경로를 선택한다.

 

3) 로우 소스 생성

• 옵티마이저가 선택한 실행경로를 실행 가능한 코드 형태로 포맷팅 하는 단계
• 로우 소스 생성기가 그 역할을 맡는다.

 

---

옵티마이저가 활약하는 단계에 대해서 요약한다.

 

1) 사용자로부터 전달 받은 쿼리를 실제로 실행할 실행계획들을 찾아낸다.

2) Data Dictionary 에 미리 수집해 둔 오브젝트 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.

3) 최저 비용을 나타내는 실행 계획을 선택한다.

 

 

 

실행계획을 읽기 위해서는 아래 세 가지의 규칙만 기억하면 된다.

 

1. 위에서 아래

2. 내려가는 과정에서 같은 들여쓰기가 존재하면, 무조건 위에서 아래 순으로 읽기

3. 읽고자 하는 스텝보다 들여쓰기가 된 하위 스텝이 존재한다면,

    가장 안쪽으로 들여쓰기 된 스텝을 시작으로 하여 한 단계씩 상위 스텝으로 읽어 나오기

 

Id	Operation	Name	Rows	Bytes	Cost(%CPU)	Time
0	select cstmer_no		14	1638	17(0)	00:00:01
1	nested loops		1
2	nested loops		14	1638	17(0)	00:00:01
3	table access full	tbwcstmr	14	1218	3(0)	00:00:01
* 4	indes unique scan	pk_cstmr	1		0(0)	00:00:01
5	table accescc by index rowid	tbwcard	1	30	1(0)	00:00:01

* cost는 쿼리 수행하는동안 발생할 것으로 예상되는 I/O횟수 예상 소요시간을 표현한 값 

 

실행계획을 읽는 순서는 다음과 같다.

ID 3 → 4 → 2 → 5 → 1 → 0

---

옵티마이저 힌트(Hint) : /*+ hint */ 

 

SELECT문에 실행하고 싶은 계획을 전달할 때 사용하는 문법입니다.

SELECT /*+ INDEX_DESC(테이블명 PK명)*/ 컬럼명1, 컬럼명2, ... FROM 테이블명; //내림차순
SELECT /*+ INDEX(테이블명 PK명)*/ 컬럼명1, 컬럼명2, ... FROM 테이블명; //오름차순

 

• 테이블을 지정할 때, 스키마명까지 명시하면 안된다.
• 테이블 Alias 를 지정했다면, 힌트에서도 반드시 Alias를 사용해야 한다.

자주 사용하는 힌트 목록

1) INDEX Access Operation 관련 HINT

HINT	내용	사용법
INDEX	Index Scan으로 유도	INDEX(TABLE_name, INDEX_name)
INDEX_ASC	INDEX를 내림차순으로 스캔.
INDEX_DESC	INDEX를 오름차순으로 스캔.	INDEX_DESC(TABLE_name, INDEX_name)
INDEX_FFS	INDEX FAST FULL SCAN	INDEX_FFS(TABLE_name, INDEX_name)
PARALLEL_INDEX	INDEX PARALLEL SCAN	PARALLEL_INDEX(TABLE_name, INDEX_name)
NOPARALLEL_INDEX	INDEX PARALLEL SCAN 제한	NOPARALLEL_INDEX(TABLE_name, INDEX_name)
AND_EQUALS	여러개의 INDEX MARGE 수행	AND_EQUALS(INDEX_name, INDEX_name)
FULL	Table full scan으로 유도	FULL(TABLE_name)

 

2) JOIN Access Operator 관련 HINT

HINT	내용	사용법
USE_NL	NESTED LOOP JOIN  옵티마이저가 NESTED LOOP JOIN을 사용하도록 한다.	USE_NL(TABLE1, TABLE2)
USE_NL_WITH_INDEX	INDEX를 사용해서 NESTED LOOP JOIN을 사용하도록 한다.	USE_NL_WITH_INDEX(TABLE  INDEX)
USE_MERGE	SORT MERGE JOIN  옵티마이저가 SORT MERGE JOIN을 사용하도록 한다.  먼저 각각의 TABLE의 처리 범위를 스캔하여 SORT한 후, 서로 MERGE하면서 JOIN하는 방식.	USE_MERGE(TABLE1, TABLE2)
USE_HASH	HASH JOIN  옵티마이저가 HASH JOIN을 사용하도록 한다.	USE_HASH(TABLE1, TABLE2)
HASH_AJ	HASH ANTIJOIN	HASH_AJ(TABLE1, TABLE2)
HASH_SJ	HASH SEMIJOIN	HASH_SJ(TABLE1, TABLE2)
NL_AJ	NESTED LOOP ANTIJOIN	NL_AJ(TABLE1, TABLE2)
NL_SJ	NESTED LOOP SEMIJOIN	NL_SJ(TABLE1, TABLE2)
MERGE_AJ	SORT MERGE ANTIJOIN	MERGE_AJ(TABLE1, TABLE2)
MERGE_SJ	SORT MERGE SEMIJOIN	MERGE_SJ(TABLE1, TABLE2)

 

3) JOIN 순서

HINT	내용	사용법
ORDERED	FROM절에 명시된 테이블의 순서대로 DRIVING
LEADING	파라미터에 명시된 테이블의 순서대로 JOIN	LEAING(TABLE_name1, TABLE_name2, ...)
DRIVING	해당 테이블을 먼저 DRIVING	DRIVING(TABLE)

 

4) 기타 HINT

HINT	내용	사용법
APPEND	INSERT시 DIRECT LOADING
PARALLEL	SELECT, INSERT시 여러개의 프로세스로 수행	PARALLEL(TABLE, 개수)
CACHE	데이터를 메모리에 CACHING
NOCACHE	데이터를 메모리에 CACHING하지 않음
PUSH_SUBQ	SUBQUERY를 먼저 수행
REWRITE	QUERY REWRITE 수행
NOREWIRTE	QUERY REWRITE를 수행 못함
USE_CONCAT	IN절을 CONCATENATION ACCESS OPERATION으로 수행
USE_EXPAND	IN절을 CONCATENATION ACCESS OPERATION으로 수행못하게 함
MERGE	VIEW MERGING 수행
NO_MERGE	VIEW MERGING 수행못하게 함

 

---

1.2 SQL 공유 및 재사용

Library Cache(라이브러리 캐시) : PL/SQL, SQL에 대한 분석 정보(Parse Tree) 및 실행 계획을 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간으로서, Shared Pool의 영역 안에 있다.

 

소프트 파싱이란 SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것을 말한다.

• sql 구문을 수행 시 SGA - shared pool - Library Cache 영역에서 해당 구문이 존재여부를 조회하여 존재하는 경우          해당 구문의 파싱 트리, 실행계획 등을 재사용함.
• 재사용하기 때문에 실행 속도가 빠름.
• 처음 쿼리를 조회하고 동일한 쿼리를 다시 조회할 때 첫 번째 쿼리 속도보다 빠른 이유.
• 단 공백, 라인, 대소문자의 차이가 있다면 다른 구문으로 인식하여 hard parse 수행.

하드 파싱이란 라이브러리 캐시에  SQL 파싱 결과가 없을 때, 최적화 부터 로우 생성 단계까지 모두 거치는 것을 말한다.

• sql 구문을 수행 시 SGA - shared pool - Library Cache 영역에서 해당 구문이 존재 여부를 조회하여 존재하지 않는 경우 해당 구문의 문법 및 권한 검사,  테이블 및 컬럼 존재 여부 등을 수행 후 Library Cache 영역에 해당 구문을 적재함.
• 수행 시 CPU 사용량이 증가할 수 있음.

 

옵티마이저가 SQL을 최적화할 때, 아래와 같은 많은 정보를 이용하여 최적화 작업을 수행한다.

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 오브젝트 통계 : 테이블 통계, 인덱스 통계, 컬럼 통계
• 시스템 통계 : CPU속도, Single Block I/O 속도, Multiblock I/O 속도 등
• 옵티마이저 관련 파라미터

 

SQL 변수 종류

리터럴 변수 란?

sql 구문 중 where절에 column과 비교되는 값이 상수값으로 직접 선언된 경우의 변수 

예) SELECT * FROM TABLE01 WHERE COL01 = "TEST";

 

바인드 변수 란?

sql 구문 중 where절에 column과 비교되는 값이 바인드 변수 형태로 사용하는 경우의 변수

바인드 변수의 자리에는 parameter로 넘겨지는 값들이 대체됨.

예) SELECT * FROM TABLE01 WHERE COL01 :=1;

바인드 변수를 사용하면 최초 HARD 파싱 한번 일어난 후 SQL 재사용이 가능하다.

 

---

1.3 데이터저장 구조 및 I/O 메커니즘

데이터베이스 저장구조

1. 데이터 파일

 물리적으로는 데이터파일에 데이터를 저장하고 관리한다.   

1-1. 블록(Block)

오라클에서의 I/O는 블록단위로 이루어 진다. 데이터를 읽고 쓸때의 논리적인 단위가 블록인 것이다.

블록단위로 I/O를  한다는 것은 하나의 레코드에서 하나의 컬럼만을 읽으려 할때도 레코드가 속한 블록전체를 읽게 됨을 뜻한다.
1-2. 익스텐트(Extent)

데이터를 읽고쓰는 단위는 블록이지만서도 테이블 스페이스로 부터 공간을 할당하는 단위는 익스텐트다.

테이블이나  인덱스에 데이터를 입력하다가 공간이 부족해지면 해당 오브젝트가 속한 테이블스페이스로 부터 추가적인 공간을 할당 받는데, 이때 정해진 익스텐트 크기의 블록을 할당받는다.

1-3. 세그먼트(Segment)

세그먼트는 테이블, 인덱스, Undo 처럼 저장공간을 필요로 하는 데이터베이스 오브젝트다.

저장공간을 필요로한다는  것은 한개 이상의 익스텐트를 사용함을 뜻한다.
테이블을 생성할 때, 내부적으로는 테이블 세그먼트가 생성되고, 인덱스를 생생할때는 내부적으로 인덱스 세그먼트가  생성된다.

한 세그먼트는 자신이 속한 테이블 스페이스 내 여러데이터 파일에 걸쳐 저장될 수 있다.

즉, 세그먼트에 할당된 익스텐트가 여러 여러 데이터 파일에 흩어져 저장되는 것이며, 그래야 디스크 경합을 줄이고 i/o 분산 효과를  얻는다. 

1-4. 테이블 스페이스(Table Space)

테이블 스페이스는 세그먼트를 담는 콘테이너로서, 여러 데이터 파일로 구성된다.

사용자는 세그먼트를 위한 테이블 스페이스를 저장하고 DBMS는 실제 값을 저장할 데이터 파일을 선택하고 익스텐트를 할당한다.

출처: https://neocan.tistory.com/26?category=649827 [미스터 역마살:티스토리]

 

 

 

Database 읽기 단위 

PC에서 한글 파일이나 Word 파일을 저장할 때, 파일 단위로 읽고 저장한다. 물론 내부 메카니즘은 좀 더 섬세한 면이 있겠지만, 대용량 파일을 저장/수정할 때 시간이 걸리는데는 이러한 이유일 것이다.

Database는 익히 알려진대로, 블록(Block) 단위로 읽고 저장한다. 오라클은 기본적으로 블록 사이즈가 8kb이다. 즉, database가 아주 작은 데이터를 가져온다고 하더라도, 최소한 8kb의 블록을 읽는 셈이다.

Database 튜닝에 있어서,가장 중요한 것은 바로 이 블록 단위 I/O를 줄이는 것이다.

참고로 오라클은 아래와 같은 방법으로 블록 사이즈를 확인할 수 있다.

 

 

 

 

Database 액세스 방법(Sequential VS Random Access)

데이터 블록을 액세스 하는 방법은 시퀀셜 액세스와 랜덤 액세스가 있다.  

​

먼저 시퀀셜 액세스는 연결된 순서에 따라 차레대로 블록을 읽는 방식을 말한다. 인덱스 리프 블록은 앞뒤를 가리키는 주소값, 즉 DBA(Data Block Address)를 통해 논리적으로 서로 연결돼 있다. 이 주소 값을 이용해서 순차적으로 액세스 하는 방식이 시퀀셜 액세스다. 

테이블 블록 간에는 서로 논리적인 연결고리를 갖고 있지 않는데, 테이블은 어떻게 시퀀셜 방식으로 액세스할까?

 

오라클은 세그먼트에 할당된 익스텐트 목록을 세그먼트 헤더에 맵으로 관리한다. 익스텐트 맵은 각 익스텐트의 첫 번째 블록 주소 값을 갖는다. 읽어야 할 익스텐트 목록을 익스텐트 맵에서 얻고, 각 익스텐트의 첫 번째 블록 뒤에 연속해서 저장된 블록을 순서대로 읽으면,

그것이 곧 Full Table Scan이다.

시퀀셜 액세스

 

 

 

랜덤 액세스는 레코드간 논리적, 물리적인 순서를 따르지 않고, 레코드 한 건을 읽기 위해 한 블록씩 접근하는 방식이다.

 

---

DB 버퍼캐시

DB 버퍼 캐시는 '데이터 캐시'라고 할 수 있다. 디스크에서 어렵게 읽은 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄이는 데 목적이 있다. 

 

 

라이브러리 캐시가 Server Request로 들어오는 여러 SQL을 저장하고 있다면, DB Buffer Cache는 이러한 SQL을 실행해서 얻은 데이터를 저장해놓은 공간이다. 따라서 Buffer Cache에서는 데이터 블록을 캐싱해 둠으로써 같은 블록에 대한 반복적인 I/O Call을 줄일 수 있다.

참고로 아래와 같이 SGA 뷰를 통해 SGA의 각 영역별 사이즈를 확인할 수 있다.

 

 

 

 

논리적 I/O, 물리적 I/O

논리적 I/O

• SQL을 처리하는 과정에서 Buffer Cache에서 발생한 총 블록 I/O를 말한다.

물리적 I/O

• 디스크에서 발생한 I/O를 말한다. SQL을 처리하면서, 찾고자 하는 데이터가 없을 경우, 디스크를 액세스하는 경우가 물리적 I/O이다. 

 

버퍼캐시 히트율(Buffer Cache Hit Ratio)

버퍼캐시 히트율이란 버퍼 캐시 효율을 측정하는 지표로써 전체 읽은 블록 중, 메모리 버퍼 캐시에서 찾은 비율을 말한다. 

 

BCHR = (버퍼 캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) * 100 또는 ((논리적/IO - 물리적I/O) / 논리적 I/O) * 100 또는 (1 - (물리적 I/O) / (논리적 I/O) * 100

버퍼캐시 히트 비율이 낮은 것일 수록 SQL 성능을 떨어뜨리는 주범이다.

BCHR을 계산해보자.

call   count  cpu  elapsed disk query current rows 
------ ----- ----- ------- ---- ----- ------ ---- 
Parse     15  0.00    0.08    0    0      0   0 
Execute   44  0.03    0.03    0    0      0   0 
Fetch     44  0.01    0.13   18  822      0  44 
------ ----- ----- ------- ---- ----- ------ ---- 
total    103  0.04    0.25   18  822      0  44 

총 읽은 블록 수 = 822

버퍼 캐시에서 찾은 블록수 = 822 - 18 = 804

BCHR = (822-18)/822 = 97.8%

 

 

Single Block I/O, MultiBlock I/O

Single Block I/O

• 한번에 I/O Call에 하나의 데이터 블록만 읽어서 메모리에 적재하는 방식으로, 인덱스와 테이블을 액세스할 때, 이 방식을 사용한다.
• 인덱스 루트 블록 읽을때
• 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
• 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
• 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때

Multi Block I/O

• I/O Call이 필요한 시점에, 인접한 블록들을 같이 읽어 메모리에 적재하는 방식으로 Full Table Scan할 때, 이 방식을 사용한다. 
• 일반적으로 OS 단에서 1MB

 

 

Table Full Scan vs Index Range Scan

 

Table Full Scan

- 테이블에 속한 블록 전체를 읽어 조건이 맞으면 결과로 추출 조건에 맞지 않으면 버려서 사용자가 원하는 데이터를 찾음

- 시퀀셜 액세스 + Multi Block I/O

- 캐시에서 못 찾으면 한번의 I/O Call로 인접한 수십-수백개의 블록을 한꺼번에 불러오는것이 좋음 

 

 

 

 

Index Range Scan

- 인덱스 선두 컬럼이 가공되지 않은 상태로 조건절에 있어야 Index Range Scan이 가능

- 인덱스에서 일정량을 스캔하면서 얻은 rowID로 테이블 레코드를 찾음

- 랜덤 액세스 + Single Block I/O

- 루트 블록에서 리프 블록까지 수직적으로 탐색 한 후 리프 블록을 필요한 부분만 스캔하는 방식