SQLP 준비

1. SQL파싱과 최적화

SQL : Structured Query Language ⇒ 구조적 질의 언어 이다.

옵티마이저가 프로그래밍을 대신해준다.

SQL 최적화

1. SQL 파싱
   1. 파싱 트리 생성
   2. Syntax 체크
   3. Sementic 체크
2. SQL 최적화
   1. 옵티마이저가 생성한것중 최적인것을 선택한다.
3. 로우 소스 생성(로우소스 생성기)
   1. 옵티마이저가 선택한 실행 경로를 실제 실행가능한 프로시저 형태로 포맷팅 한다.

SQL 옵티마이저

사용자가 원하는 작업을 가장 효율적으로 수행할 수 있는 최적의 액세스 경로를 선택해주는 DBMS의 핵심 엔진이다.

실행 계획과 비용

옵티 마이저가 실행계획을 선택하는 근거는? cost(비용), 쿼리를 수행하는 동안 발생할것으로 예상되는 I/O횟수 또는 예상 소요시간을 표현한것이다.

cost는 예상치이기에 실제 실행시에는 달라질 수 있다.

옵티마이저 힌트

개발자가 직접 더 효율적인 액세스 경로를 찾아낼 수도 있다. 이럴때 옵티마이저 힌트를 사용해서 데이터 액세스 경로를 바꿀수 있다.

2. SQL공유 및 재사용

소프트 파싱 vs 하드파싱

소프트 파싱 : 캐시에서 찾아 곧바로 실행단계 까지 넘어가는 것

하드 파싱 : 실패해 최적화 및 로우 소스 생성 단계 까지 모두 거치는 것

왜 최적화를 하면 하드 할까? ⇒ 모든 경우의 수를 확인하느라 CPU를 많이 소비한다. ⇒ 이래서 소프트 파싱이 필요하다.

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

1. SQL이 느린이유

십중팔구 I/O 때문이다.

2. 데이터 베이스 저장구조

블록 : 데이터를 읽고 쓰는 단위

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

세그먼트 : 데이터 저장공간이 필요한 오브젝트

테이블 스페이스 : 세그먼트를 담는 콘테이너

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

3. 블록단위 I/O

블록 단위로 DBMS가 데이터를 읽고 쓴다.

4. 시퀀셜 액세스 vs 랜덤 엑세스

시퀀셜 액세스 : 논리적, 물리적으로 연결된 순서에 따라 차례대로 블록을 읽는 방식이다. (이때 시퀀셜하게 연결하기 위해 오라클은 세크먼트 헤더에 맵으로 관리한다.)

랜덤 액세스: 논리적,물리적 순서 따르지 않고 레코드 하나를 익기 위해 한블록씩 접근하는 방식

5. 논리적 I/O vs 물리적 I/O

DB버퍼 캐시 : 데이터를 캐싱한다. (I/O가 성능을 결정하기에 중요 기능이다.)

반복적인 I/O call을 줄인다.

논리적 블록 I/O : SQL을 처리하는 과정에 발생한 총 블록 I/O

물리적 블록 I/O : 디스크에서 발생한 총 블록 I/O

SQL을 수행하면서 읽은 총 블록 I/O가 논리적 I/O이다.

DB버퍼 캐시에서 블록을 찾지 못해 디스크에서 읽은 블록 I/O 가 물리적 I/O이다.

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

버퍼캐시히트율(BCHR)이 SQL성능을 좌우 하지만, 높다고 해서 효율적인 SQL을 의미하는것은 아니다.

Single Block I/O vs Multiblock I/O

모든 데이터를 캐시에 적재할 수 없다.

Single Block I/O : 한번에 한 블럭씩 요청해서 메모리에 적재하는 방식

Multiblock I/O : 한번에 여러 블록씩 요청해서 메모리에 적재하는 방식

Table Full Scan vs Index Range Scan

Table Full Scan : multiblock io + sequential access

Index Range Scan : single block io + random access

인댁스를 맹신 하지 마라 + 데이터가 일정 수준이 넘어가면 테이블 풀 스캔이 낫다.

캐시 탐색 메커니즘

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

2장 : 인덱스 기본

1. 인덱스 구조 및 탐색

대게 데이터를 찾는 경우 기법에는 두가지가 있다.

1. 테이블 전체 스캔
2. 인덱스 이용

특히 OLTP(Oline Traction Processing) 시스템은 특히 소량 검색이 중요하여 튜닝이 중요하다.

인덱스 튜닝방법

1. 인덱스 스캔 효율화 튜닝 (특정 기준으로 정렬 하는 것과 같은 방법)
2. 테이블 액세스 횟수를 줄인다. ( 랜덤 액세스 최소화 튜닝) ⇒ 이게 더 중요하다.

SQL 튜닝은 랜덤 I/O와의 전쟁이다.

인덱스를 이용하면 일부만 읽을 수 있다. (범위 스캔 가능)

DBMS는 일반적으로 B*Tree를 사용한다.

인덱스 탐색 과정 : 수직적 탐색 + 수평적 탐색

1. 수직적 탐색 : 인덱스 스캔 시작 지점을 찾는다
2. 수평적 탐색 : 데이터를 찾는다
   1. 조건절을 모두 만족하는 데이터를 찾는다
   2. 나중에 테이블 접근했을때를 대비한 RowId를 얻기 위해

인덱스는 엑셀과 같은 평면적인 구조가 아니다.

2. 인덱스 기본 사용법

인덱스 컬럼을 가공하면 인덱스를 정상적으로 사용할 수 없다.

⇒ 인덱스 스캔의 시작점을 찾을 수 없어서, 이렇게 하면 인덱스를 Range Scan 할수가 없다.

⇒ like연산도 앞에서 부터 시작하는것에서는 가능하지만 중간에 있는것을 찾는경우에는 인덱스가 의미가 없다.

인덱스를 Range Scan 하기 위한 가장 첫 번째 조건은 인덱스 선두 컬럼이 조건절에 있어야 한다는 사실이다.

⇒ 인덱스를 선두 컬럼이 가공되지 않은 상태로 조건절에 있으면 인덱스 Range Scan은 가능하다.

단순히 인덱스 잘 탄다고 튜닝이 끝나는것이 아니라 실제 스캔하는 데이터의 양을 확인해야 한다.

인덱스를 RangeScan할 수 있는 이유는 sort가 되어 있기 때문이다 .

인덱스로 sort 연산을 생략 함으로써 성능을 높일 수 있다. ⇒ 이때도 가공되어있으면 사용이 불가하다.

옵티마이저는 별도의 정렬 연산을 따로 수행하지 않는다. 수직적 탐색을 하여 조건을 만족하는 가장 왼쪽 지점으로 내려가서 찾는다. 최솟값은 내려가서 가장 왼쪽 최댓값을 가장 오른쪽으로 가서 탐색한다.

인덱스 컬럼을 가공하면 SQL성능이 더 안 좋아질 수 있다.

자동 형변환에 의존하지 않고 인덱스 컬럼 기준으로 반대편 컬럼 또는 값을 정확히 형변화 해주서야 한다.

SQL성능 원리를 잘 모르는 경우 함수를 의도적으로 생략하곤 하는데 이 함수를 생략하는것 보다는 I/O를 줄이는것이 중요하다.

3. 인덱스 확장기능 사용법

Index Range Scan

수직적으로 탐색하고 필요한 범위만 스캔한다.

성능은 인덱스 스캔범위, 테이블 액세스 횟수를 얼마나 줄일 수 있느냐로 결정된다.

Index Full Scan

수직적 탐색 없이 인덱스 리프 블록을 처음부터 수평적으로 탐색하는것

옵티마이저는 인덱스 풀스캔을 하기에 데이터에 부담이 없으면 인덱스 활용을 고려하지 않고 인덱스 풀 스캔을 선택한다.

경우에 따라 range scan보다 full scan이 효율적일 수 있다.

Index Unique Scan

= 조건으로 탐색하는 경우에 작동한다.

Index Skip scan

인덱스 선두 컬럼을 조건절에 사용하지 않으면 기본적으로 풀스캔을 선택한다. 테이블 풀 스캔보다 I/O를 줄일 수 있거나 정렬된 결과를 얻을 수 있는경우 Index Full Scan을 사용하기도 한다.

그 중 Index Skip Scan은 루트 또는 브랜치 블록에서 읽은 컬럼 값 정보를 이용해 조건절에 부합하는 레코드를 포함할 가능성이 있는 리프 블록만 골라서 액세스 스캔하는 방식이다.

해당 스캔 방식이 작동하기 위해서는 선두 컬럼의 분산도가 낮고 후행컬럼에 대한 분산도가 큰경우 or 선두 컬럼의 조건절은 있지만 중간절에 대한 조건이 없는 경우에 사용된다.

인덱스 레이지 스캔이 불가능 하거나 효율적이지 못한 상황에서 빛을 발한다.

Index Fast Full Scan

Index full scan보다 빠르다. 논리적인 인덱스 트리구조를 무시하고 인덱스 세그먼트 전체를 Multiblock I/O방식으로 스캔하기 때문이다. 이렇게 함으로써 디스크로부터 대량의 인덱스 블록을 읽어야 할때 큰 효과를 발휘한다. 속도는 빠르지만 인덱스 리프노드가 갖는 연결 리스트 구조를 무시한 채 데이터를 읽어서 결과 집합이 인덱스 키 순서로 정렬되지 않는다. 쿼리에 사용한 컬럼이 모두 인덱스에 포함돼있을때만 사용할 수 있다. 인덱스가 파티션 되어있지 않더라도 병렬 쿼리가 가능하다.

Index Range Scan Descending

Index Range Scan과 기본적으로 동일한 스캔방식이지만 인덱스를 뒤에서 앞으로 스캔하는 방식이다.

인덱스가 있는 컬럼이라면 옵티마이저가 알아서 인덱스를 거꾸로 읽는 실행 계획을 수립한다. 만약 거꾸로 읽지 않으면 인덱스 힌트를 이용해 유도할 수 있다. index_desc

3.1 테이블 액세스 최소화

대량 데이터의 경우 인덱스가 한없이 느리다 (테이블 전체를 스캔하는 경우보다 느리다)

RowId 는 논리적 주소에 더 가깝다. 테이블 레코드를 찾아가기 위한 위치 정보를 담는다

메인 메모리 DB : 데이터를 모두 메모리로 로드해놓고 메모리를 통해서만 I/O를 수행하는 DB이다.

잘 튜닝된 OLTP성 데이터 베이스 시스템이라면 버퍼 캐시 히트율이 99%이상이다. ⇒ 디스크를 경유하지 않고 메모리에서 읽어오는 것이다. ⇒ 어떤 메인메모리의 경우 인스턴스를 기동하면 디스크에 저장된 데이터를 버퍼캐시로 로딩하고 이어서 인덱스를 생성한다. 이때 인덱스 주소가 아닌 메모링 상의 주소정보(포인터)를 갖는다.

이에 오라클은 테이블 블록이 수시로 버퍼캐시에 밀려났다 캐싱되어 그때마다 다른 공간에 캐싱되어 인덱스에서 포인터로 연결할 수 없다. 이 때문에 일반 DBMS에서 인덱스 ROWID를 이용한 테이블 액세스가 생각 만큼 빠르지 않은것이다.

DBA(= 데이터파일번호 + 블록번호)는 디 스크 상에서 블록을 찾기 위한 주소 정보다. 이때문에 버퍼캐시를 잘 활용해야한다.

인덱스 ROWID는 포인터가 아니다. 디스크 상에서 테이블 레 코드를 찾아가기 위한 논리적인 주소 정보다. ROWID가 가리키는 테이블 블록을 버퍼캐시 에서 먼저 찾아보고 못 찾을 때만 디스크에서 블록을 읽는다. 물론 버퍼캐시 에 적재한 후에 읽는다.

설령 모든 데이터가 캐싱돼 있더라도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 회 득 과정을 반복해야 한다. 동시 액세스가 심할 때는 캐시버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생한다. 이처럼 인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용 구조다.

오라클에서 하나의 레코드를 찾아가는 데 있어 가장 빠르다고 알려진 ROWID에 의한 테이 블 액세스는 고비용 연산이다.

인덱스 ROWID를 이용한 테이블 엑세스는 고비용 구조다

인덱스를 이용한 테이블 액세스가 Table Full Scan보다 더 느려지게 만드는 가장 핵심적인 두 가지 요인은 다음과 같다.

• Table Full Scan은 시퀀셜 액세스인 반면, 인덱스 ROWID를 이용한 테이블 액세스 는 랜덤 액세스 방식이다.
• Table Full Scan은 Multiblock I/0인 반면, 인덱스 ROWID를 이용한 테이블 액세 스는 Single Block I/O 방식이다.

즉, 테이블 스캔이 항상 나쁜 것은 아니며, 바꿔 말해 인덱스 스캔이 항상 좋은 것도 아니라는 사실을 설명하는 데 목적이 있다.

온라인 프로그램 튜닝 : 소량 데이터를 읽고 갱신하므로 인덱스를 효과적으로 활용하는 것이 무엇보다 중요하다.조인도 대부분 NL방식을 사용한다(인덱스 활용방식) ⇒ 소트 연산을 생략하고 온라인 환경에서 대량 데이터를 조회 할 떄도 아주 빠른 응답속드를 낼 수 있다.

배치 프로그램 : 대량 데이터 처리 , 항상 전체 범위의 처리 기준으로 튜닝해야 한다. 처리 대상 집합중 일부를 빠르게 처리하는 것이 아니라 전체를 빠르게 처리하는 것을 목표로 삼아야 한다. 대량 데이터를 빠르게 처리하려면 인덱스,NL 조인보다 FUllScan 과 HashJoin이 유리하다.

다시 강조하지만, 모든 성능 문제를 인덱스로 해결하려 해선 안 된다. 인덱스는 다양한 튜닝 도구 중 하나일 뿐이며, 큰 테이블에서 아주 적은 일부 데이터를 빨리 찾고자 할 때 주로 사용한다.

테이블 액세스 최소화를 위해 가장 일반적으로 사용하는 튜닝 기법은 인덱스에 컬럼을 추가 하는 것이다.

인덱스 자체를 추가하다보면 수십개의 인덱스가 달려 배보다 배꼽이 커진다.

이럴 때 기존 인덱스에 SQL 컬럼을 추가하여 효과를 얻을 수 있다. 인덱스 스캔량이 줄진 않지만 데이블 랜덤 엑세스 횟수는 줄여준다.

반드시 성능을 개선해야 한다면, 쿼리에 사용된 컬럼을 모두 인덱스에 추가해서 테이블 액세 스가 아에 발생하지 않게 하는 방법을 고려해 볼 수 있다. 참고로, 인덱스만 읽어서 처리하는 쿼리를 Cvered 쿼리 라고 부르며, 그 쿼리에 사용한 인덱스를 Covered 인덱스라고 부른다. ⇒ 추가해야할 컬럼이 많아진다는 단점이 있다.

랜덤 엑세스가 아예 발생하지 않도록 테이블을 인덱스 구조로 생성하는 방법 → IOT (인위적으로 클러스터링 팩터를 좋게 한다)

summary : table index가 성능에 미치는 영향, 그리고 그것을 최소화하기 위해 인덱스에 컬럼을 추가하고 테이블 저장 구조를 개선하는 방법

3.2 부분범위 처리 활용

부분 범위 처리 : 데이터를 전송할 때 일정량 씩 나누어 전송한다.

1. 정렬 조건이 있을때 부분범위 처리

2. ArraySize 조정을 통한 Fetch Call 최소화

3. 쿼리 툴에서 부분 범위 처리

   → 중간에 멈췄다가 사용자의 추가 요청이 있을 때마다 데이터를 가져오도록 구현