Msg 성장일기
SQL 처리과정과 I/O 본문
1. SQL 파싱과 최적화
-SQL은 구조적이고 집합적이고 선언적인 질의언어다. 원하는 결과 집합을 만드는 과정은 절차적일 수밖에 없다. 즉, 프로시저가 필요한데, 그런 프로시저를 생성하는 DBMS 내부 엔진이 바로 SQL 옵티마이저다.
-DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정을 SQL 최적화라고 한다.
[ SQL 최적화 ]
1) SQL 파싱
↳ 파싱트리생성, Syntax 체크, Semantic 체크
2) SQL 최적화
↳ SQL 옵티마이저가 최적의 경로를 선택한다.
3) ROW-Source 생성
↳ 최적의 경로를 실제 실행 가능한 코드 or 프로시저 형태로 포맷팅하는 단계다.
[ SQL 옵티마이저 ]
↳ 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진이다.
1) 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾아낸다
2) 데이터 딕셔너리에 미리 수집해 둔 오브젝트 통계 및 시스템 통계정보를 이용해 각 실행계획의 예상비용을 산정한다.
3) 최저 비용을 나타내는 실행계획을 선택한다.
2. SQL 공유 및 재사용
# SQL 파싱, 최적화, 로우소스 생성 과정을 거쳐 생성한 내부 프로시저를 반복 재사용할 수 있도록 캐싱해 두는 메모리 공간을 라이브러리 캐시라고 한다.
사용자가 SQL문을 전달하면 DBMS는 SQL을 파싱한 후 해당 SQL이 라이브러리 캐시에 존재하는지부터 확인한다.
[ 소프트 파싱 vs 하드 파싱 ]
소프트 파싱: SQL을 캐시에서 찾아 곧바로 실행단계로 넘어가는 것
하드 파싱: 찾는데 실패하고 최적화 및 로우 소스 생성 단계까지 모두 거치는 것
[ SQL 최적화 과정은 왜 하드(Hard)한가? ]
↳ SQL 옵티마이저는 순식간에 엄청나게 많은 연산을 한다. 그 과정에서 옵티마이저가 사용하는 정보는 다음과 같다.
* 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
* 오브젝트 통계 : 테이블 통계, 인덱스 통계, (히스토그램을 포함한) 컬럼 통계
* 시스템 통계 : CPU 속도, Single Block I/O 속도, Multiblock I/O 속도 등
* 옵티마이저 관련 파라미터
하나의 쿼리를 수행하는 데 있어 후보군이 될만한 무수히 많은 실행경로를 도출하고, 짧은 순간에 딕셔너리와 통계정보를 읽어 각각에 대한 효율성을 판단하는 과정은 결코 가벼울 수 없다.
이렇게 어려운(=hard) 작업을 거쳐 생성된 내부 프로시저를 한번만 사용하고 버린다면 이만저만한 비효율이 아니다.
↳라이브러리 캐시가 필요한 이유
[ 바인드 변수의 중요성 ]
이름이 있는 것
- 사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때 부터 이름을 가짐
- 컴파일한 상태로 딕셔너리에 저장
- 사용자가 삭제하지 않는 한 영구보관
- 실행할 때 라이브러리 캐시에 적재하여 여러 사용자가 공유하면서 재사용
이름이 없는 것
- SQL은 이름이 없음
- 전체 SQL 텍스트가 이름 역할을 함
- 딕셔너리 저장 X
- 처음 실행할 때 최적화 과정을 거쳐 동적으로 생성한 내부 프로시저를 라이브러리 캐시에 적재해 여러 사용자가 공유하면서 재사용
- 캐시 공간이 부족하면 버려졌다가 다음에 다시 실행할 때 똑같은 최적화 과정을 거쳐 캐시에 적재됨
SQL 자체가 이름이라 텍스트 중 작은 부분이라도 수정되면 그 순간 다른 객체가 새로 탄생되는 구조이므로 "바인드 변수"를 활용해 하드파싱을 줄이고 캐싱된 SQL을 사용하도록 해야한다.
즉, 공유 가능한 SQL을 만들어야 한다.
3. 데이터 저장 구조 및 I/O 메커니즘
SQL이 느린 이유는 디스크 I/O 때문이다.
↳ OS 또는 I/O 서브시스템이 I/O를 처리하는 동안 프로세스는 (waiting)잠을 자기 때문이다.
여러 프로세스가 하나의 CPU를 공유할 수 있지만, 특정 순간에는 하나의 프로세스만 CPU를 사용할 수 있기 때문에 위와 같은 메커니즘이 필요하다.
정해진 OS 함수를 호출(I/O Call)하고 CPU를 반환한 채 알람을 설정하고 대기 큐에서 잠을 잔다.
그래서 I/O가 많으면 성능이 느려진다.
[ 데이터베이스 저장 구조 ]
* 블록 :데이터를 읽고 쓰는 단위
* 익스텐트 :공간을 확장하는 단위, 연속된 블록 집합
* 세그먼트 :데이터 저장공간이 필요한 오브젝트(테이블, 인덱스, 파티션, LOB 등)
* 테이블스페이스 :세그먼트를 담는 콘테이너
* 데이터파일 :디스크 상의 물리적인 OS 파일
*모든 데이터 블록은 디스크 상에서 몇 번 데이터파일의 몇 번째 블록인지를 나타내는 자신만의 고유 주소값을 갖는다. 이 주소값을 DBA(data block address)라고 부른다. 데이터를 읽으려면 먼저 DBA부터 확인해야 한다.
인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID를 이용한다. ROWID는 DBA + 로우 번호(블록 내 순번)로 구성되므로 이를 분해하면 읽어야 할 테이블 레코드가 저장된 DBA를 알 수 있다.
[ 블록 단위 I/O ]
DBMS가 데이터를 읽고 쓰는 단위가 블록이다. 그래서 특정 레코드 하나를 읽고 싶어도 해당 블록을 통째로 읽는다.
오라클은 기본적으로 8KB 크기의 블록을 사용하므로 1Byte를 읽기 위해 8KB를 읽는 셈이다.
[ 시퀀셜 액세스 vs 랜덤 액세스 ]
1) 시퀀셜 액세스 : 논리적 or 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다. 인덱스 리프 블록은 앞뒤를 가리키는 주솺을 통해 논리적으로 서로 연결돼 있다.
2) 랜덤 액세스 : 논리적, 물리적인 순서를 따르지 않고, 레코드 하나를 읽기 위해 한 블록씩 접근(touch)하는 방식.
[ 논리적 I/O vs 물리적 I/O ]
서버 프로세스와 데이터파일 사이에 버퍼캐시가 있으므로 데이터 블록을 읽을 땐 항상 버퍼캐시부터 탐색한다.
운 좋게 캐시에서 블록을 찾는다면 프로세스가 waiting(I/O Call) 하지 않아서 효율적이고,
캐시에서 찾지 못해도 한 번은 I/O Call을 하고 waiting하지만, 같은 블록을 2번째 읽을 때부터는 waiting 하지 않아도 된다.
버퍼캐시는 공유메모리 영역이므로 같은 블록을 읽는 다른 프로세스도 득을 본다.
논리적 블록 I/O는 SQL문을 처리하는 과정에 메모리 버퍼캐시에서 발생한 총 블록 I/O를 말한다.
물리적 블록 I/O는 디스크에서 발생한 총 블록 I/O를 말한다. SQL 처리 도중 읽어야 할 블록을 버퍼캐시에서 찾지 못할 때만 디스크를 액세스하므로 논리적 블록 I/O 중 일부를 물리적으로 I/O한다.
데이터 입력이나 삭제가 없어도 물리적 I/O는 SQL실행할 때마다 다르다. 첫 번째 실행할 때보다 두 번째 실행할 때 줄어들고, 세 번째 실행할 땐 더 줄어든다. 하지만 한참 후에 다시 실행하면 반대로 물리적 I/O가 늘어난다. DB버퍼캐시가 다른 테이블 블록으로 채워지기 때문이다.
[ 버퍼캐시 히트율 ]
버퍼캐시 효율을 측정하는 데 전통적으로 가장 많이 사용해 온 지표
↳ 공식에서 알 수 있듯 BCHR은 읽은 전체 블록 중에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율을 나타낸다.
↳ 공식을 변형하면 "물리적 I/O = 논리적 I/O x (100 - BCHR)"
즉, 논리적 I/O를 줄임으로서 물리적 I/O를 줄이는 것이 곧 SQL 튜닝이다.
[ Single block I/O vs Multiblock I/O ]
Single block I/O : 한번에 한 블록씩 메모리에 적재하는 방식
Multiblock I/O : 많은 벽돌을 실어 나를 때 손수레를 이용하는 것처럼 한 번에 여러 블록씩 요청해서 메모리 적재하는 방식
인덱스를 이용할 때는 기본적으로 인덱스와 테이블 블록 모두 Sinble Block I/O 방식을 사용한다.
인덱스는 소량 데이터를 읽을 때 주로 사용하므로 이 방식이 효율적이다.
- 인덱스 루트 블록을 읽을 때
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
반대로, 많은 데이터 블록을 읽을 때는 Multiblock I/O방식이 효율적이다. 인덱스를 이용하지 않고 테이블 전체를 스캔할 때 이 방식을 사용한다.
[ Table Full Scan vs Index Range Scan ]
테이블에 저장된 데이터를 읽는 방식
1) Table Full Scan : 테이블 전체를 스캔해서 읽는 방식(테이블에 속한 블록 '전체'를 읽어서 사용자가 원하는 데이터를 찾는 방식)
2) Index Range Scan : 인덱스를 이용해서 읽는 방식(인덱스에서 '일정량'을 스캔하면서 얻은 ROWID로 테이블 레코드를 찾아가는 방식)
Table Full Scan은 시퀀셜 액세스와 multiblock I/O방식으로 디스크 블록을 읽는다. 이 방식을 사용하는 SQL은 스토리지 스캔 성능이 좋아지는 만큼 성능도 좋아진다. 하지만 시퀀셜 액세스와 Multiblock I/O가 아무리 좋아도 수십~수백 건의 소량 데이터를 찾을 때 수백만~수천만 건 데이터를 스캔하는 건 비효율적이다. 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 한다.
[ 버퍼캐시 탐색 매커니즘 ]
버퍼캐시에서 블록을 찾을 때 해시알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 액세스하는 방식을 사용한다.
# 해시 구조의 특징
1) 같은 입력 값은 항상 동일한 해시 체인에 연결됨
2) 다른 입력값이 동일한 해시 체인에 연결될 수 있음
3) 해시 체인 내에서는 정렬이 보장되지 않음
출처:개발자를 위한 SQL 튜닝 입문서 친절한 SQL 튜닝
'study_SQL > 친절한 SQL 튜닝' 카테고리의 다른 글
| 인덱스 스캔 효율화 (0) | 2023.06.14 |
|---|---|
| 부분범위 처리 활용 (0) | 2023.06.14 |
| 테이블 액세스 최소화 (0) | 2023.06.14 |
| 인덱스 확장기능 사용법 (1) | 2023.06.14 |
| 인덱스 기본 (0) | 2023.06.13 |
