04.테이블 Random 액세스 부하

SQL> select * from Customer where location = 'Seoul'

Execution Plan

------------------------------------------------------------------------------------------------------------

0      SELECT STATEMENT Optimizer=ALL_ROWS

1  0      TABLE ACCESS (BY INDEX ROWID) OF 'CUSTOMER'   (TABLE)

2  1           INDEX (RANGE SCAN) OF 'CUSTOMER_LOCATION_IDX  (INDEX)

이 절에서는. 우리가 잘 보게되는, "테이블 Random 액세스"가 얼마나 부하를 주는 요인이되는지 그 원인을 살펴보자.

(1) 인덱스 ROWID에 의한 테이블 액세스

일반적으로 갖기 쉬운 선입견.

rowid검색은 부하가 거의 없이 엄청빠르다.

마치, 포인터처럼 물리적인 주소를 직접 가르킬 것이다. (아마도.)

ROWID로 검색을 해도 생각하는 것만큼 부하가 거의 없지는 않다.

ROWID는 물리적인 주소를 직접 가르키고 있지도 않다.

메인 메모리 DB (MMDBMS)

위에서 언급한 선입견은 메인 메모리 DB에서는 해당되는 이야기이다.

즉, 대량의 테이블을 인덱스를 통해 스캔하게되면 Oracle과는 비교도 되지 않을 만큼의 속도차가 나게 된다.

메인 메모리 DB는 인스턴스가 올라갈때 디스크의 데이터를 메모리(버퍼캐쉬)에 올리고. 그 후 인덱스를 실시간으로 만든다.

즉, 메모리주소를 직접 참조하는 인덱스를 구성하게 된다. (C의 포인터와 같은 개념)

오라클처럼 수시로 블록이 버퍼캐시에서 재할당되어 물리주소가 바뀌는 구조에서는 직접물리주소를 참조하는것은 불가능하다. (물리주소 참조의 의미가없다.)

엄청난 퍼포먼스가 요구되어지는 트랜잭션에서 한정적으로 쓰인다. (아마도 코스트가 디메리트이지 않을까 싶다.)

<공부 많이 하고 싶은 이들을 위한 참고링크>

ROWID는 물리적 요소들로 구성된 논리값이다.

결국, ROWID를 읽는다는것은 "주소해석"이라는 작업을 필요로 하게 된다.

인덱스 rowid에 의한 테이블 액세스 구조

rowid에 의한 테이블스캔이 얼마나 고비용인가, 아래그림을 상기해보자.

버퍼블록에서 데이터블록을 검색한다.

• rowid를 읽고, 주소해석작업(해시값을 구함)을 한다.
• 해당 해시체인을 관리하는 레치에 접근작업을 한다.
• 이떄, 레치는 여러개의 해시체인을 관리하므로, 다른 프로세스와의 경합이 있을 수 있다. (공중화장실처럼)
• 경합시에 너무 심하게 기다리면, 아에 돗자리 깔고 잠시 잔다. 주기적으로 눈뜨고 확인.
• 레치를 잡는다 해도, 해당 영역의 (타프로세스가)본작업을 진행중이면, 0순위 잡았지만 역시 기다리게 된다.
• 내가 작업끝내고(해당데이터에 액세스) 해제 할 때도 이런 경합에 의한 wait가 발생된다.

만약, 데이터블록을 찾지 못하면 디스크로 부터 읽어들여야 하므로 더 번거로운 작업들이 발생한다.

결국, ROWID검색이 생각만큼 가볍지 않다는 것을 말하고 있다.

두대의 PC를 케이블선으로 직접연결한것을 물리주소참조(포인터)라 한다면,

ROWID검색은 거대한 네트웍상에 연결된 PC들간의 IP를 이용한 참조정도라 보면 될것같다.

(라우팅작업도 발생되고, 회선에서 패킷경합도 일어나고... 등등)

(2) 인덱스 클러스터링 팩터

클러스터링 팩터(군집성계수=모여있는정도)

• 찾으려는 데이터가 물리적으로 모여있으면 CF(군집성계수)가 좋다고 본다.
• 서울바닦에 흩어진 사람들을 찾는것 보다, 내가 있는 건물안에 흩어진 사람을 찾는것이 효율이 좋은 것처럼.
• 즉, 인덱스의 순서가 실제 테이블의 데이터가 분포된 순서와 일치하는것이 가장 CF좋다고 볼 수 있다.

클러스터링 팩터 조회

• 실제 정렬시킨 컬럼으로 인덱스를 생성 (테이블 데이터 정렬과 인덱스의 정렬순서를 일치시킴)
  
• 실제 정렬과는 다른 컬럼으로 인덱스를 생성 (테이블의 데이터 정렬과 인덱스의 정렬수서를 불일치시킴)
• 두 인덱스의 클러스터링 팩터를 비교해봄으로써 수치로 내용을 확인할
  

SQL> create table t as select * from all_objects order by object_id

SQL> create index t_object_id_idx on t (object_id)                              ----------------- 1.

SQL> create index t_object_name_idx on t (object_name)                   ----------------- 2.

SQL> exec dbms_stats.gather_table_stats (user, 'T');

SQL> select i.index_name, t.blocks table_blocks, i.num_rows, i.clustering_factor

     2   from    user_tables t, user_indexes i

     3   where  t.table_name = 'T'

     4   and     i.table_name = t.table_name;

INDEX_NAME                 TABLE_BLOCKS    NUM_ROWS    CLUSTERING_FACTOR

------------------------------------------------------------------------------------------------------------------

T_OBJECT_ID_IDX                            709             50093                         689

T_OBJECT_NAME_IDX                       709             50093                     24936  

※CLUSTERING_FACTOR 수치의 계산로직은, 스캔하면서 헛스윙 할 때마다 카운터를 해 가는 방식으로 구현되었다.

인덱스를 이용한 테이블 액세스 비용

blevel + ? 인덱스 수직적 탐색 비용
(리프 블록 수 x 유효 인덱스 선택도) + ? 인덱스 수평적 탐색 비용
(클러스터링 팩터 x 유효 테이블 선택도) ? 테이블 Random 액세스 비용

• blevel : 리프 블록에 도달하기 전 읽게 될 브랜치 블록 개수
• 유효 인덱스 선택도 : 전체 인덱스 레코드 중에서 조건절을 만족하는 레코드를 찾기 위해 스캔할 것으로 예상되는 비율(%)
• 유효 테이블 선택도 : 전체 레코드 중에서 인덱스 스캔을 완료하고서 최종적으로 테이블을 방문할 것으로 예상되는 비율(%)

클로스터링 팩터와 물리적 I/O

위의, 클러스터링 팩터조회 에서 느꼈겠지만

• 인덱스의 정렬과 테이블의 정렬상태가 똑같을수록(인덱스 CF가 좋을수록) → 데이터들이 군집해있음을 의미.
• 블록단위로 데이터를 읽어들이는 오라클에서는 군집해 있을수록 → 적은 블록의 적은 범위에서 히트. 즉, 캐쉬히트율좋다.
• 캐쉬히트율이 좋다 → 물리적 I/O의 횟수가 줄게 됨을 의미.

클러스터링 팩터와 논리적 I/O

인덱스 CF는, 인덱스를 경유해 테이블 전체 로우를 액세스할 때 읽을 것으로 예상되는 논리적인 블록 개수를 의미한다.

즉, 다음과 같은 특징을 보인다.

• CF가 가장 좋을 때 → clustering_factor가 전체 테이블 블록 개수와 일치
• CF가 가장 나쁠 때 → clustering_factor가 전체 레코드 개수와 일치
  

버퍼 Pining에 의한 논리적 I/O감소 원리

같은 레코드를 읽더라도 인덱스 CF가 좋으면, 나쁜 경우에 비해 논리적 I/O가 심하게 줄어든다.

그 이유는 버퍼 Pinning이 일어나기 때문이다.

• 방금 액세스한 블록의 Pin을 바로 해제하지 않는다.
• 바로, 같은 블록을 액세스하게 되면, 레치획득 작업을 생략하고 Pin한 상태의 블록을 읽는다.
• 그래서, 래치획득 작업등에서 발생될 논리적 I/O 가 감소.
  
• SQL의 캐쉬처리(분석등을 생략하는)에 관해 생각해보면 같은 맥락이라 쉽게 이해 될것이다.
  

(3) 인덱스 손익분기점

손익 분기점은, Index Rang Scan에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점을 말하며, 그 요인은 다음과 같다.

• 인덱스 rowid에 의한 테이블 액세스는 Random 액세스인 반면 Full Table Scan은 Sequential 액세스 방식을 사용
  
• 디스크 I/O시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식을 사용하는 반면, Full Table Scan은 Multiblock Read 방식을 사용

손익 분기점은 보통 5~20%의 수준에서 결정되는데. CF가 나쁘면 1~5%까지도 떨어지고, 반면에 CF가 좋으면 90%까지도 올라가게 된다.

※CF가 나쁘면 논리적 I/O, 물리적 I/O가 증가하게 되어 상당히 않좋다.

손익분기점을 극복하기 위한 기능들

1. IOT : 테이블을 인덱스 구조로 생성하는것
2. 클러스터 테이블 : 키값이 같은 레코드는 같은 블록에 모여 있도록 저장하는것
3. 파티셔닝 : 대량 범위 조건으로 자주 사용되는 컬럼 기준으로 테이블을 파티셔닝 하는것

• 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
• 작성자: 노종현 (cotosiry)
• 최초작성일: 2011년 2월 19일
• 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
• 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^