SQL Tuning 1

SQL 튜닝

---

SQL 처리 과정과 I/O

목차

• sql 파싱과 최적화

• sql 공유 및 재사용

• 데이터 저장 구조 및 I/O 매커니즘

---

SQL 파싱과 최적화

sql : 구조적, 집합적, 선언적 질의 언어 그 결과 집합을 만드는 과정은 절차적.

절차적 => 프로시저가 필요. 그런 프로시저는 누가 만들지? => DBMS 내부 엔진의 옵티마이저

SQL 최적화: DBMS 내부에서 프로시저를 작성하고 컴파일해서 실행가능한 상태로 만드는 전 과정 (아래는 전 과정 순서)

1. SQL파싱

2. SQL최적화 (최적화 라고 같은 이름이지만 명확하게 구분할 필요는 없다.)

3. 로우 소스 생성

옵티마이저

1. 사용자로부터 전달받은 쿼리를 수행하는 데 후보군이 될만한 실행계획들을 찾음.

2. 데이터 딕셔너리에 미리 수집해둔 오브젝트 통계 및 시스템 통계를 이요해 예상비용 산정

3. 최저비용 실행계획 선택

실행 계획을 선택하는 근거

=> 예상 비용 실측치가 아니므로 실제 수행과 차이가 날 수도 있다.

힌트시 주의사항

/*+ INDEX( A, A_X01) INDEX(B, B_X03) */ -> 모두 유효
/*+ INDEX( A, A_X01), INDEX(B, B_X03)*/ -> 첫 번째 힌트만 유효
=> 콤마(,)를 힌트 사이에 기술해서는 안됨.

SELECT /*+FULL(SCOTT.EMP) */ -> 무효
=> 스키마명 작성금지

SELECT /*+FULL(EMP) */
FROM EMP E     => 무효
=> ALIAS 설정시 반드시 ALIAS 설정

자주 사용하는 힌트 목록

분류	힌트	설명
최적화목표	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을 병렬 처리 유도
	PARALLEL_INDEX	인덱스 스캔을 병렬방식으로 처리 유도
	PQ_DISTRIBUTE	병렬 수행시 데이터 분배 방식 결정
기타	APPEND	DIRECT-PATH-INSERT 유도
	DRIVING-SITE	원격 쿼리에 대한 최적화 및 실행 주체 지정
	PUSH_SUBQ	서브 쿼리를 가급적 빨리 필터링하도록 유도
	NO_PUSH_SUBQ	서브쿼리를 가급적 늦게 필터링하도록 유도

---

SQL 공유 및 재사용

라이브러리 캐시: SQL 파싱, 최적화, 로우 소스 생성 과정을 거쳐 생성한 내부 프로지서를 반복 재사용할 수 있도록 캐싱해두는 메모리 공간

• SGA
  • DB BUFFER CACHE
  • REDO LOG BUFFER
  • SHARED POOL
    • LIBRARY CACHE
    • DATA DICTIONARY CACHE

SQL 파싱후 프로시저가 라이브러리 캐시에 존재하는가? => 예, 소프트 파싱 ==>아니오, 하드 파싱 => 최적화 => 로우 소스 생성

옵티마이저가 사용하는 정보

• 테이블, 컬럼, 인덱스 구조에 관한 기본 정보
• 오브젝트 통계: 테이블, 인덱스,(히스토그램을 포함한) 컬럼 통계
• 시스템 통계: CPU속도, SINGLE BLOCK I/O 속도, MULTIBLOCK I/O 속도 등
• 옵티마이저 파라미터

이름 없는 SQL : SQL은 이름이 따로 없고, 전체 텍스트가 이름이다. 한 글자만 다르더라도 서로 다른 SQL이다. 그러나 문맥적으로는 같은 값을 반환할 수도 있다.

이에 주의하지 않으면 자주 사용하는 SQL이 라이브러리 캐시에서 밀려날 수도 있다.

String sqlsmt = "select * from customer where login_id= '"+login_id+"'";

=> 미친 하드파싱이 일어난다.

---

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

SQL이 느린 이유 => I/O가 가장 대표적이고 절대 비중을 차지

SINGLE BLOCK I/O기준 I/O CALL 속도 평균 10ms 초당 100블록쯤 읽음 큰 캐시를 가진 SAN 스토리지 4~8ms 초당 125~250블록쯤 읽음 SSD 활용 1~2ms 초당 500~1000블록쯤 읽음

어떤 SQL이 10000블록을 읽는다면, 가장 최신 스토리지에서도 10초이상 기달려야함.

디스크 I/O가 성능을 좌우한다고 해도 과언이 아님.

• 테이블 스페이스
  • 세그먼트
    • 익스텐트
      • 블록
        • 로우
  • 세그먼트

테이블 스페이스: 세그먼트를 담는 콘테이너. 여러개의 데이터파일

세그먼트 : 테이블, 인덱스처럼 데이터 저장공간이 필요한 오브젝트

익스텐트 : 공간 확장 단위, 연속된 블록 집합

블록(or 페이지): 데이터를 읽고 쓰는 단위.

데이터파일: 디스크 상의 물리적인 OS파일

같은 데이터 파일내의 익스텐트라도 연속된 공간이 아니다.

DBA(Data block Address):데이터 블록 주소 ROWID: DBA + 로우 번호(블록 내 순번)

DB 버퍼 캐시 = 데이터 캐시 버퍼 캐시는 공유메모리 영역. 같은 블록을 읽는 다른 프로세스도 득을 본다.

SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O. (Direct Path Read 제외) 디스크에서 읽은 블록I/O 는 물리적I/O

물리적 I/O는 결국 시스템 상황에 의해 결정되는 통제 불가능한 외생변수.

SQL 성능을 높이기 위해 할 수 있는 일은 논리적I/O를 줄이는 일뿐이다.

논리적 I/O를 줄임으로써 물리적 I/O를 줄이는 것이 곧 SQL튜닝.

참고로 BCHR이 SQL 성능을 좌우하지만, BCHR이 높다고 해서 효율적인 SQL을 의미하지 않는다.

테이블에 저장된 데이터를 읽는 방식은 두 가지.

1. 테이블 풀 스캔
2. 인덱스 범위 스캔

테이블 풀스캔은 시퀀셜 액세스와 멀티블록I/O 방식으로 디스크 블록을 읽음 => 한번의 수면(I/O CALL)을 통해 인접한 수십~수백개 블록을 한꺼번에 I/O하는 매커니즘

인덱스 범위 스캔은 랜덤 액세스와 싱글블록I/O방식으로 디스크 블록을 읽음 => 레코드 하나를 읽기 위해 매번 잠을 자는 I/O매커니즘

인덱스가 항상 옳은 것은 아니며, 바꿔 말해 테이블 풀 스캔이 항상 나쁜 것도 아니다.

메모리 버퍼 캐시를 탐색할 때는 해시 매커니즘으로 탐색하는데 이런 탐색과 점유 할때 두 개 이상의 프로세스가 동시에 접근할 수는 없다. 공유가 아니라 교대로 사용하는 것이다.

이러한 직렬화 개념으로 래치(줄서기)가 등장한다.

빠른 데이터베이스를 구현하려면 버퍼캐리 히트율을 높여야 하지만, 캐시I/O도 생각만큼 빠르지 않을 수 있다. 래치에 의한 경합이 생길 수 있기 때문이다.

캐시버퍼 체인 뿐만 아니라 버퍼블록 자체에도 직렬화 매커니즘 이 존재한다. 바로 버퍼LOCK이다.

결국, 이런 직렬화 매커니즘에 의한 캐시 경합을 줄이려면, SQL튜닝을 통해 쿼리 일량(논리적 I/O) 자체를 줄여야 한다.