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

옵티마이저 – SQL 최적화 기능, 효율적인 액세스 경로 선택, 옵티마이저 힌트, 실행계획

 

SGA(시스템 글로벌 애리아) – 서버 프로세스와 백그라운드 프로세스가 공통으로 사용하는 메모리

(DB 버퍼캐시, 리두 로그 버퍼, 라이브러리 캐시 등이 포함되어 있음)

Ø  라이브러리 캐시 (코드 캐시; sql 함수 프로시저 실행계획 등)

소프트 파싱 – 캐시에서 해결되는 파싱; 라이브러리 캐시의 특장점

하드 파싱 - 하드디스크까지 가서 최적화와 로우소스까지 생성해야 하는 파싱

Ø  DB 버퍼캐시 (데이터 캐시; 디스크에서 읽은 데이터 블록)

I/O call 줄임

 

I/O 튜닝

프로세스는 준비-실행-대기 상태를 반복하기 때문에 디스크 I/O call이 발생하면 그 동안 프로세스는 대기 상태가 됨.

즉, 디스크 I/O가 SQL 성능을 좌지우지함

블록 : DBMS가 데이터를 읽고 쓰는 단위 (레코드 하나가 아닌 레코드 속한 블록을 통째로 읽음)

시퀀셜 액세스 : 순차적 액세스, full table scan, 다중 블록 I/O

랜덤 액세스 : 랜덤으로 한 블록씩 접근; 인덱스는 single block I/O

 

Sql 튜닝을 통해 읽어들이는 총 블록 개수를 감소시켜야 함

실제 성능을 향상시키려면 건들일 수 없는 물리적 I/O가 아닌 통제 가능한 논리적 I/O를 감소시켜야 함

 

Table Full Scan (대량 데이터 호출) VS Index Range Scan (소량 데이터 호출; ROWID 제공)

• 2. 인덱스

1. 인덱스 스캔 효율화 튜닝

2. 랜덤 액세스 최소화 튜닝 ☆

 

선두 컬럼이 가공되지 않은 상태로 조건절에 있어야한다!!

시작점을 찾아야 일부만을 스캔하기 때문이다.

 

인덱스는 데이터가 “정렬”되어 있기 때문에 range scan 할 수 있다. 즉, 소트 연산 생략 효과가 있다.

-       인덱스 구성대로 order by col1, col2하면 소트 연산 생략되지만

order by col1 || col2하면 생략 안됨

-       테이블 alias 사용 시에는 order by alias.col로 해야함

-       (자동)형변환도 가공으로 취급됨 - null값도 처리해주기

 

인덱스 full scan도 인덱스 컬럼 순으로 정렬됨 >> 이것은 소트 연산 생략 의도로 사용하기도 함

힌트 first_rows를 사용하면 거의 모든 범위의 테이블 액세스가 일어나 성능 면으로 좋지 않지만
부분범위 처리 시에는 소트 연산을 생략함으로써 일부를 빠르게 출력할 수 있음

(table full scan, index range scan 이외에도 index unique scan, index skip scan, index fast full scan, index range scan descending 있음)

 

• 3. 인덱스 튜닝

Rowid는 논리적 주소이며 포인터가 아니다.
버퍼 캐시에서 DBA(데이터파일번호+블록번호)를 통해 테이블 블록을 찾는 구조이다. (고비용 구조)

인덱스 클러스터링 팩터 

특정 컬럼을 기준으로 같은 값을 갖는 데이터가 서로 모여있는 정도
이것이 안 좋은 인덱스를 사용하면 그만큼 블록 I/O가 발생

인덱스 손익분기점

index range scan에 의한 테이블 액세스가 table full scan보다 느려지는 지점
이것이 CF클러스터링 팩터와 관련이 있음

테이블 파티셔닝(ex) date 기준) -> full scan을 빠르게 처리할 수 있음

인덱스 컬럼 추가

인덱스에 컬럼을 추가하는 것만으로도 성능 향상.
하지만 추가해야할 컬럼은 많아지고, 인덱스가 많을수록 정렬 때문에 성능에 안 좋음

Include 인덱스

컬럼에 추가하는 것이 아닌 index 생성 시 끝에 include(column)하면 됨
그러면 기존 방식으로 추가한 컬럼은 수직적 탐색에 사용하고
include로 추가한 컬럼은 리프 블록에만 저장하여 수평적 탐색에 필터 조건으로 사용 가능

클러스터형 인덱스

테이블을 인덱스 구조로 형성
인덱스 리프 블록이 곧 데이터 블록
Create table index_ (col1 number, col2 varchar(10),
Constraint index_pk primary key(a) )
Organization index;

클러스터 테이블

같은 공간에 저장해 둘 뿐, 위와 같이 정렬하지 않음
랜덤 액세스가 적고, 클러스터에 도달하면 시퀀셜 방식으로 스캔

-       인덱스 클러스터

Create cluster c_dept ( eptno number(2) ) index;
Create index c_dept on cluster c_dept;
Create table dept ( deptno number(2) not null
, dname varchar2(14) not null
, loc varchar2(13) )
Cluster c_dept( deptno );

-       해시 클러스터

Create cluster c_dept ( eptno number(2) ) hashkeys 4;
Create table dept ( ... );

 

부분범위 처리 활용

아무리 데이터가 많아도 빠른 응답속도

           큰 사이즈로 데이터 호출하여 fatch call 최소화

 

– 인덱스 스캔 효율화

액세스 조건(스캔 범위 결정)과 필터 조건(테이블 액세스 결정)

   선행 컬럼들이 모두 = 조건이면 조건이 모두 모여있어 인덱스 스캔 범위가 됨

   In-List 유용 > In list iterator : in 개수만큼 index range scan 반복함 good

but In-list는 원래 '필터' 조건. 본질적으로는 = 조건과 다름

                              데이터가 대용량으로 굉장히 많아야 = 조건으로 유용하다

  힌트 NUM_INDEX_KEYS(a 고객별가입상품_X1 1) 사용 : 인덱스의 첫 번째 컬럼(세 번째 인자에    명시)까지 인덱스 액세스 조건으로 사용

  소트 연산을 생략하기 위해 In-list가 액세스 조건이 아닌 필터 조건으로 풀려야할 때가 있음 ->
  이 경우에는 인덱스의 맨 끝으로 위치시키면 됨!

Between 조건을 in-list로 전환할 때는 데이터가 멀리 떨어져 있거나 데이터가 적어야 유용

Index skip scan을 사용하면(힌트 INDEX_SS) 선두컬럼을 in이 아닌 between에 사용해도  블록만을 스캔하고 효율적으로 스캔

Like보다 Between이 좋음. 하지만 범위검색 조건도 남용하면 안 좋음

OR 조건은 인덱스가 아닌 컬럼에 사용하면 됨

인덱스 선두 컬럼에 between, like 사용 금물

Union all 사용 – 옵션 조건 컬럼도 인덱스 액세스 조건으로 사용함 good

Null값 해결을 위해 NVL(IFNULL), DECODE 사용 (but OR expansion 한 번만 가능)

조건절의 컬럼이 모두 인덱스에 있으면 함수도 모두 액세스 조건으로 한 번만 수행됨 good

 

- 인덱스 구성 조건 : 데이터량이 많으면 적절한 인덱스 구성이 꼭 필요

           1. 선두 컬럼을 조건절에 반드시 사용하고,

2. 조건절에 많이 사용하고

3. = 조건으로 자주 조회하는 컬럼

4. 수행 빈도도 큰 영향

ex) 일자/일시 조건을 선두에 두고, 자주 사용하는 필터 조건을 뒤쪽에 추가

 

소트연산 생략하기 위한 인덱스 구성

= 연산자로 사용한 조건절 컬럼

ORDER BY 절에 기술한 컬럼이어야 함

            + 다른 조건절일 경우 데이터 분포 고려해서 인덱스에 추가하기

 

인덱스 생성 시 컬럼의 선택도가 충분히 낮은지도 살펴봐야함 <> 변별력 높은 컬럼인지 확인!

           카디널리티가 높은 컬럼(변별력이 낮음)은 테이블 액세스가 많이 발생한다

           인덱스 생성 시에는 선택도가 중요하고

           컬럼 간 순서를 결정할 때는 필수 조건 여부, 연산자 형태가 더 중요하다