Development Study/SQLP
[친절한 SQL 튜닝] 1장 SQL 처리 과정과 I/O
yooputer
2024. 10. 10. 09:01
1.1 SQL 파싱과 최적화
SQL
- SQL은 'Structured Query Language'의 줄임말
- 원하는 결과집함을 구조적, 집합적으로 선언하는 질의 언어
SQL 옵티마이저
- 원하는 결과 집합을 만들어내는 프로시저를 만들어 내는 DBMS 내부 엔진
- 가장 효율적으로 수행할 수 이쓴 최적의 데이터 액세스 경로를 선택해주는 DBMS의 핵심 엔진
SQL 최적화
- DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행 가능한 상태로 만드는 전 과정
SQL 최적화 과정
- SQL 파싱 : SQL 파서가 사용자가 입력한 SQL을 파싱
- SQL 최적화 : 옵티마이저가 다양한 실행 경로를 생성해 가장 효율적인 실행경로 선택
- 로우 소스 생성 : 로우소스생성기가 실제 실행 가능한 코드 또는 프로시저 형태로 포맷팅
SQL 파싱 과정
- 파싱 트리 생성
- Syntax 체크 : 문법적 오류가 있는지 확인. ex) 사용가능한 키워드인지, 순서가 올바른지
- Semantic 체크 : 의미상 오류가 있는지 확인. ex) 존재하지 않는 컬럼 사용, 권한 체크
옵티마이저의 최적화 단계
- 다양한 실행 계획 생성
- 데이터 딕셔너리의 오브젝트 통계 및 시스템 통계정보를 사용해 각 실행계획의 예상비용 산정
- 비용이 가장 적게 드는 실행 계획 선택
실행 계획
- SQL 옵티마이저가 생성한 처리절차를 트리구조로 표현한 것.
- 테이블을 스캔하는지, 인덱스를 스캔하는지, 어떤 인덱스를 스캔하는지 등 확인 가능
옵티마이저 힌트
- 보통 옵티마이저가 최적의 경로를 선택하지만, 옵티마이저 힌트를 사용해 임의로 경로를 지정할 수 있다.
옵티마이저 힌트 사용법
1. 아래와 같이 주석 기호에 '+'를 붙인다.
SELECT /*+ INDEX(테이블 PK명) */
2. 힌트 안에 인자를 나열할 땐 콤마를 사용하지만, 힌트와 힌트 사이에는 사용하지 않는다
/*+ INDEX(테이블 PK명) INDEX(테이블, PK명) */ -> 모두 유효
/*+ INDEX(테이블), FULL(테이블) */ -> 첫번째 힌트만 유효
3. 테이블을 지정할 때 스키마명을 명시하면 안된다
4. FROM절에서 테이블의 별칭을 지정했다면 힌트에도 반드시 테이블의 별칭을 사용한다.
자주 사용하는 옵티마이저 힌트 목록
| 분류 | 힌트 | 설명 |
| 최적화 목표 | ALL_ROWS | 전체 처리속도 최적화 |
| FIRST_ROWS(N) | 최초 N건 응답속도 최적화 | |
| 액세스 방식 | FULL | Table Full Scan으로 유도 |
| INDEX | Index Scan으로 유도 | |
| INDEX_DESC | Index를 역순으로 스캔하도록 유도 | |
| INDEX_FFS | Index Fast Full Scan으로 유도 | |
| INDEX_SS | Index Skip Scan으로 유도 | |
| 조인 순서 | ORDERED | FROM절에 나열된 순서대로 조인 |
| LEADING | LEADING 힌트 괄호에 기술한 순서대로 조인 | |
| SWAP_JOIN_INPUTS | 해시조인시, BUILD_INPUT을 명시적으로 선택 | |
| 조인 방식 | USE_NL | NL 조인으로 유도 |
| USE_MERGE | 소프트 머지 조인으로 유도 | |
| USE_HASH | 해시 조인으로 유도 | |
| NL_SJ | NL 세미조인으로 유도 | |
| MERGE_SJ | 소트 머지 세미조인으로 유도 | |
| HASH_SJ | 해시 세미조인으로 유도 | |
| 서브쿼리 팩토링 | MATERIALIZE | WITH문으로 정의한 집합을 물리적으로 생성하도록 유도 |
| INLINE | WITH문으로 정의한 집합을 물리적으로 생성하지 않고 INLINE 처리하도록 유도 | |
| 쿼리 변환 | MERGE | 뷰 머징 유도 |
| NO_MERGE | 뷰 머징 방지 | |
| UNNEST | 서브쿼리 Unnesting 유도 | |
| NO_UNNEST | 서브쿼리 Unnesting 방지 | |
| PUSH_PRED | 조인조건 Pushdown 유도 | |
| NO_PUSH_PRED | 조인조건 Pushdown 방지 | |
| USE_CONCAT | OR 또는 IN-List 조건을 OR-Expansion으로 유도 | |
| NO_EXPAND | OR 또는 IN-List 조건을 OR_Expansion 방지 | |
| 병렬처리 | PARALLEL | 테이블 스캔 또는 DML을 병렬방식으로 처리하도록 유도 |
| PARELLEL_INDEX | 인덱스 스캔을 병렬방식으로 처리하도록 유도 | |
| PQ_DISTRIBUTE | 병렬 수행시 데이터 분배방식 결정 | |
| 기타 | APPEND | Direct-Path Insert로 유도 |
| DRIVING_SITE | DB Link Remote 쿼리에 대한 최적화 및 실행 주체 지정 | |
| PUSH_SUBQ | 서브쿼리를 가급적 빨리 필터링하도록 유도 | |
| NO_PUSH_SUBQ | 서브쿼리를 가급적 늦게 필터링하도록 유도 |
1.2 SQL 공유 및 재사용
라이브러리 캐시
- 하드파싱을 통해 생성한 내부 프로시저를 반복 재 사용할 수 있도록 캐싱해두는 메모리 공간
- SGA의 구성요소
SGA
- 서버 프로세스와 백그라운드 프로세스가 공통으로 액세스하는 데이터와 제어구조를 캐싱하는 메모리 공간
소프트파싱 vs 하드파싱
- 소프트파싱 : 라이브러리 캐시에서 SQL을 찾아 바로 실행단계로 넘어가는 것
- 하드 파싱 : 라리브러리 캐시에서 SQL을 찾지 못해 SQL 최적화 과정을 모두 거치는 것
- 하드파싱은 CPU 자원을 많이 소비함
SQL 최적화 과정은 왜 힘든가?
- 테이블 조인 순서의 경우의 수 = (조인할 테이블 수)!
- 조인 방식, 테이블 스캔 방식, 인덱스 스캔 방식, 어떤 인덱스를 선택할 것인가? ...
최적화 과정에서 SQL 옵티마이저가 사용하는 정보
- 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
- 테이블 통계, 인덱스 통계, 컬럼 통계(히스토그램 포함)
- CPU 통계, Single Block I/O 속도, Multiblock I/O 속도
- 옵티마이저 관련 파라미터
이름없는 SQL의 문제
- 사용자 정의 함수/프로시저, 트리거, 패키지 등은 생성할 때부터 이름을 갖는다.
- 하지만 SQL은 이름이 없다. 전체 SQL 텍스트 자체가 이름이다.
- 한글자라도 달라지면 다른 SQL이기 때문에 다시 하드파싱하고 라이브러리 캐시에 저장해야 한다.
- IBM DB2 같은 DBMS는 SQL을 프로시저처럼 영구 저장한다. 하지만 오라클, SQL Server 등의 DBMS는 그렇지 않다.
바인드변수를 사용한 일회성 SQL 최적화
- 바인드 방식을 사용하여 파라미터 Driven 방식으로 SQL을 작성하면 변수의 값이 달라져도 라이브러리 캐시에는 하나의 SQL만 적재되기 때문에 하드파싱으로 인한 CPU 사용률을 감소시킬 수 있다.
1.3 데이터 저장 구조 및 I/O 메커니즘
SQL이 느린 이유
- SQL이 느린 이유는 대부분 디스크 I/O때문이다.
- I/O 튜닝이 곧 SQL 튜닝이다
프로세스의 생명주기
I/O Call 속도
- Single Block I/O 기준 평균 10ms. (초당 100블록정도)
- SAN 스토리지는 4~8ms, SSD를 활용하는 스토리지는 1~2ms
- Single Block I/O 방식으로 10000블록을 읽으면, 가장 최신 스토리지에서도 10초 이상 기다려야 함
데이터베이스 저장 구조
| 테이블 스페이스 | 세그먼트를 담는 콘테이너 여러개의 데이터파일(디스크상의 물리적 OS 파일)로 구성 |
| 세그먼트 | 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트 여러개의 익스텐트로 구성 파티션 구조가 아니라면 테이블도 하나의 세그먼트, 인덱스도 하나의 세그먼트 파티션 구조라면 각 파티션이 하나의 세그먼트 LOB 컬럼은 그 자체가 하나의 세그먼트, 자신이 속한 테이블과 다른 별도의 공간에 값을 저장한다. |
| 익스텐트 | 공간을 확장하는 단위 연속된 여러개의 데이터 블록으로 구성 공간이 부족해지면 테이블스페이스로부터 익스텐트를 추가로 할당받음 한 익스텐트는 하나의 테이블이 독점. 즉, 한 익스텐트에 저장된 블록은 모두 같은 테이블 블록. 참고) MS-SQL Server는 한 익스텐트를 여러 오브젝트가 같이 사용할 수 있음 파일 경합을 줄이기 위해 가능한 여러 데이터파일로 분산해서 저장 익스텐트 내 블록은 서로 인접한 연속된 공간이지만, 익스텐트끼리는 연속된 공간이 아님 |
| 블록 | 사용자가 입력한 레코드를 실제로 저장하는 공간 참고) DB2, SQL Server 등의 DBMS는 블록 대신 페이지라는 용어 사용 한 블록은 하나의 테이블이 독점. 즉, 한 블록에 저장된 레코드는 모두 같은 테이블 레코드 |
익스텐트 목록 조회
오라클에서 세그먼트에 할당된 익스텐트 목록을 조회하는 방법이다.
SELECT extent_id, file_id, block_id, blocks
FROM dba_extents
WHERE owner = USERT
AND segment_name = 'MY_SEGMENT'
ORDER BY extent_id
extent_id번 익스텐트는 file_id번 데이터파일의 block_id번 블록으로부터 연속된 blocks개 블록으로 이루어져있다는 뜻
디스크에 존재하는 데이터 접근법
- DBA(Daba Block Address)를 사용해 데이터 블록에 접근
- DBA : 디스크상에서 몇번째 데이터파일의 몇번째 데이터블록인지 나타내는 주소값
- 인덱스를 이용해 테이블 레코드를 읽을 때는 인덱스 ROWID 를 사용해야함.
- ROWID : DBA + 로우번호(블록 내 순번)
- 테이블을 스캔할 때는 테이블 세그먼트 헤더에 저장된 익스텐트 맵 이용.
- 익스텐트 맵을 통해 각 익스텐트의 첫번째 블록 DBA를 알 수 있음
- 익스텐트는 연속된 블록 집합이기 때문에 테이블을 스캔할때는 첫번째 블록 뒤에 연속해서 저장된 블록을 읽으면 됨.
데이터베이스에서 데이터를 읽고 쓰는 단위
- 테이블과 인덱스에 블록 단위로 데이터를 읽고 쓴다
- 오라클은 기본적으로 8KB 크기의 블록 사용
시퀀셜 액세스 vs 랜덤 액세스
| 시퀀셜 액세스 | 논리적 또는 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식 인덱스의 리프 블록은 앞뒤를 가리키는 주소값으로 서로 연결되어 있기 때문에 이 주소값으로 인덱스 시퀀셜 액세스 가능 테이블의 경우 세그먼트 헤더에 각 익스텐트의 첫번째 블록 주소값이 저장되어 있는데, 이 주소가 가리키는 블록과 연속된 블록을 읽으면 테이블 시퀀셜 액세스 가능 |
| 랜덤 액세스 | 레코드 하나를 읽기 위해 한 블록씩 접근하는 방식 |
DB 버퍼 캐시
- 자주 사용하는 데이터(블록)은 DB 버퍼 캐시에 저장해놓는다.
- 반복적인 I/O call을 줄일 수 있다
논리적 I/O vs 물리적 I/O
| 논리적 I/O | SQL을 처리하는 과정에서 발생한 총 블록 I/O 메모리 I/O + 디스크 I/O 논리적 I/O는 항상 동일하다. 논리적 I/O 횟수는 일반적으로 DB 버퍼 캐시에서 블록을 읽은 횟수와 일치한다. ???? 이해안됨 |
| 물리적 I/O | 디스크에서 발생한 총 블록 I/O 메모리 I/O는 전기신호인데 디스크 I/O는 액세스 암을 통해 일어나므로 상대적으로 느리다. 물리적 I/O는 버퍼캐시에서 읽은 블록의 수에 따라 달라지기 때문에 실행할때마다 다르다. |
버퍼캐시 히트율(BCHR, Buffer Cache Hit Ratio)
- 읽은 전체 블록 중에서 물리적인 디스크 I/O를 수반하지 않고 곧바로 메모리에서 찾은 비율
- (캐시에서 곧바로 찾은 블록 수 / 총 읽은 블록 수) * 100
((논리적 I/O - 물리적 I/O) / 논리적 I/O) * 100
(1 - 물리적 I/O / 논리적 I/O) * 100 - 온라인 트랜잭션을 주로 처리하는 애플리케이션이라면 시스템 레벨에서 평균 99% 히트율을 달성해야함
- 물리적 I/O가 성능을 결정하지만 실제 SQL 성능을 향상하려면 물리적 I/O가 아닌 논리적 I/O를 줄여야 함
- BCHR이 SQL 성능을 좌우하지만, BCHR이 높다고 효율적인 SQL은 아님
Single Block I/O vs Multiblock I/O
| Single Block I/O | 한번에 한 블록씩 메모리에 적재하는 방식 인덱스를 이용할 때는 기본적으로 인덱스와 테이블 모두 Single Block I/O 사용 |
| Multiblock I/O | 한번에 여러 블록씩 메모리에 적재하는 방식 테이블 전체 스캔할 때 Multiblock I/O 사용 테이블이 클수록 Multiblock I/O 단위도 크면 좋다 보통 OS의 I/O단위는 1MB이므로 최대 1MB만큼 Multiblock I/O할 수 있다. |
Table Full Scan vs Index Range Scan
| Table Full Scan | 테이블 전체를 스캔해서 읽는 방식 |
| Index Range Scan | 인덱스를 이용해서 읽는 방식 |
- Index Range Scan이 항상 좋을까?
- 큰 테이블에서 소량 데이터를 검색할 때는 반드시 인덱스를 이용해야 한다.
- 하지만 많은 데이터를 읽을 때는 Multiblock I/O를 사용하는 Table Full Scan을 사용하는 것이 더 효율적일 때가 많다
- 인덱스가 항상 옳은 것은 아니며, Table Full Scan이 항상 나쁜 것도 아니다.
- 인덱스는 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾기 위한 도구일 뿐, 모든 성능 문제를 인덱스로 해결하려 해선 안된다.
- 인덱스를 안타서 느린 경우보다 불필요하게 인덱스를 타서 느린 경우가 더 많다.
캐시 탐색 메커니즘
- Direct Path I/O를 제외한 모든 블록 I/O는 메모리 버퍼캐시를 경유한다.
- 구체적으로 아래 오퍼레이션은 모두 버퍼캐시 탐색 과정을 거친다.
- 인덱스 루트 블록을 읽을 때
- 인덱스 루트 블록에서 얻은 주소 정보로 브랜치 블록을 읽을 때
- 인덱스 브랜치 블록에서 얻은 주소 정보로 리프 블록을 읽을 때
- 인덱스 리프 블록에서 얻은 주소 정보로 테이블 블록을 읽을 때
- 테이블 블록을 Full Scan할 때
- 버퍼캐시는 해시 구조로 구성된다.
- 해시 알고리즘으로 버퍼 헤더를 찾고, 거기서 얻은 포인터로 버퍼 블록을 액세스한다.
메모리 공유 자원에 대한 액세스 직렬화
- 버퍼 캐시에 캐싱된 버퍼 블록은 모두 공유자원이다.
공유자원에 동시에 접근하려하면 정합성 문제가 생길 수 있기 때문에 한 프로세스씩 순차적으로 접근하도록 구현하기 위해 직렬화 메커니즘이 필요하다.
캐시버퍼 체인 래치
- 해시 체인을 스캔하는 동안 다른 프로세스가 체인 구조를 바꾸지 못하도록 현재 작업중인 체인에는 Key를 획득한 프로세스만 진입할 수 있다.