(ORACLE) 테이블 Random 엑세스 부하

인덱스의 두 얼굴

1. 인덱스를 효과적으로 활용했을 때 성능dms up

2. 대량의 데이터를 인덱스를 통해 엑세스할때 성능은 down

 

 

메인 메모리 DB  vs  오라클 DB

 

메인 메모리 DB : 모든 데이터를 메모리에 로드해 놓고 메모리를 통해서만 I/O 수행

 

디스크를 경유한 데이터 입출력은 디스크의 액세스 암(Arm)이 움직이면서 헤드를 통해 데이터를 읽고 쓰기 때문에 느린 반면, 메모리를 통한 입출력은 전기적 신호에 불과하기 때문에 디스크를 통한 I/O에 비해 비교할 수 없을 정도로 빠르다. 

 

오라클 또한 버퍼 캐시 히트율(물리적인 디스크 읽기 가 아닌 곧바로 메모리에서 블록을 찾는 비율) 을 99% 사용한다.

 

그럼 오라클이 왜 느린 걸까?

 

메인 메모리 DB는 오라클 처럼 디스크 상의 주소 정보를 담는게 아닌 포인터를 사용하기 때문이다.

 

ex) 메인 메모리 DB : 집 전화 번호      

      오라클 : 우편번호

 

---

오라클 또한 일부 포인터를 이용해 테이블에 빠르게 접근하는 tunning 기법을 가지고 있다.

-> 반복적으로 읽힐 큰 블록 에서만 일부 적용

 

 

인덱스 rowid를 이용한 테이블 엑세스

• rowid에 의한 테이블 엑세스
  1. 인덱스에서 하나의 rowid를 읽고 DBA를 해시 함수에 적용해 해시 값 확인.
  2. 각 해시 체인은 래치(Latch)에 의해 보호되므로 해시 값을 가리키는 해시 체인에 대한 레치(cache buffers chains 래치)
  를 얻으려고 시도. (하나의 cache buffers chains 래치가 여러 개 해시 체인을 동시에 관리)
  3. 다른 프로세스가 래치를 잡고 있으면 래치가 풀렸는지 확인하는 작업을 일정 횟수 반복(기본 2,000)
  4. 그러고도 실패하면 CPU를 OS에 반환하고 잠시 대기 상태로 빠지는데, 이때 latch free(10g부터는 latch: chae buffers chains) 
  대기 이벤트가 나타난다.
  5. 정해진 시간 동안 잠을 자다가 깨어나서 다시 래치 상태를 확인하고, 계속해서 래치가 풀리지 않으면 또 다시 대기 상태로 빠진다.
  6. 래치가 해제되었다면 래치를 재빨리 획득하고 원하던 해시 체인으로 진입.
  7. 거기서 데이터 블록이 찾아지면 래치를 해제하고 바로 읽으면 되는데, 앞서 해당 블록을 액세스한 프로세스가 아직 일을 마치지 
  못해 버퍼 Lock을 쥔 상태라면 또다시 대기.(buffer busy waits)
  8. 블록 읽기를 마치고 나면 버퍼 Lock을 해제해야 하므로 다시 해시 체인 래치를 얻으려고 시도. 이때 또다시 경합이 발생할 수 있다.

• 해시 체인을 스캔 했는데 데이터 블록을 찾지 못했을 경우
  1. 디스크로부터 블록을 퍼 올리려면 우선 Free 버퍼를 할당 받아야 하므로 LRU 리스트를 스캔. 이를 위해 cache buffers lru chain 
  래치를 얻어야 하는데, 래치 경합이 심할 때 latch free(10g부터 latch: cache buffer lru chain) 이벤트 대기.
  2. LRU 리스트를 정해진 임계치만큼 스캔했는데도 Free 상태의 버퍼를 찾지 못하면 DBWR에게 Dirty 버퍼를 디스크에 기록해 
  Free 버퍼를 확보해 달라는 신호를 보낸다. 그런 후 해당 작업이 끝날 때까지 잠시 대기 상태에 빠지는데, 이때 나타나는 
  대기 이벤트가 free buffer waits.
  3. Free 버퍼를 할당 받은 후에는 I/O 서브시스템에 I/O 요청을 하고 다시 대기 상태에 빠진다. 
  db file sequential read 대기 이벤트 발생.
  4. 읽은 블록을 LRU 리스트 상에서 위치를 옮겨야 하기 때문에 다시 cache buffers lru chain 래치를 얻어야 하는데,
  이 또한 원활하지 못할 때는 latch free 이벤트 발생.

---

클리스터링 팩터

: 특정 컬럼을 기준으로 같은 값을 갖는 데이터가 모여있는 정도

 

분포도가 좋을때

 

분포도가 나쁠때

 

-- 테이블 레코드가 object_id 순으로 입력되도록 함
create table t
as
select * from all_objects
order by object_id;  

create index t_object_id_idx on t(object_id);

create index t_object_name_idx on t(object_name);



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

select i.index_name, t.blocks table_blocks, i.num_rows, i.clustering_factor
from   user_tables t, user_indexes i
where  t.table_name = 'T'
and    i.table_name = t.table_name;

INDEX_NAME                     TABLE_BLOCKS   NUM_ROWS CLUSTERING_FACTOR
------------------------------ ------------ ---------- -----------------
T_OBJECT_ID_IDX                         705      49831               685
T_OBJECT_NAME_IDX                       705      49831             25072

 

• clustering_factor 수치가 테이블 블록(table_blocks)에 가까울수록 데이터가 잘 정렬돼 있음을 의미
• 레코드 개수(num_rows)에 가까울수록 흩어져 있음을 의미

---

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

"인덱스 CF가 좋다..?"

= 인덱스 정렬 순서와 테이블 정렬 순서가 서로 비슷하다.

 

인덱스는 항상 정렬되어 있기에 테이블 데이터 또한 정렬되어있다면 물리적 I/O 횟수는 자연스럽게 줄어든다.

 

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

인덱스를 경유해 테이블 전체 로우를 인덱스할 때 읽을 것으로 예상되는 논리적인 블록갯수

 

-> 옵테마이저는 CF를 통해 계산된 논리적 I/O 횟수를 그대로 물리적 I/O 횟수라 생각하고 인덱스 비용을 측정한다.

 

 

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

 

buffer pinning 이란 버퍼에서 읽은 블럭이 다음에도 같이 읽힐것을 대비 flush되는 걸 방지하는 기능이다. 

경과시간의 측면에서 보면 버퍼에서 읽을때 latch를 걸고 해제한다...따라서 pinning이 되어있으면 latch를 걸고 해제하는 시간이

필요없게 된다. 그리고 블럭 액세스수도 같은 블럭(버퍼에 존재하는)에서 다수의 데이터를 fetch하므로 줄어드는걸

확인할수 있겠다.. 클러스터링 되어있다고 가정하면 buffer pinning수는 급격히 증가할것이고 블럭 액세스수는 급격히

감소할것이다(클러스터링 되어있지않은 테이블과 비교할때). 

출처:https://rcho.tistory.com/entry/latch-fetch-buffer-pinning등의-개념정리[읽는세상]

 

 인덱스 손익분기점

• Index Rang Scan에 의한 테이블 액세스가 Table Full Scan 보다 느려지는 지점
• 인덱스에 의한 액세스가 Full Table Scan 보다 더 느려지게 만드는 가장 핵심적이 두가지 요인
  1. 인덱스 rowid에 의한 테이블 액세스는 Random 액세스, Full Table Scan은 Sequential 액세스
  2. 디스크 I/O시, 인덱스 rowid에 의한 테이블 액세스는 Single Block Read 방식, Full Table Scan은 Multiblock Read 방식.

 

---

오라클 성능 고도화 원리와 해법 2 국내도서 저자 : 조시형 출판 : (주)비투엔컨설팅 2010.01.09 상세보기