1. 테이블 액세스 최소화

• 튜닝은 랜덤I/O와의 전쟁, SQL튜닝에 있어 랜덤 I/O가 그만큼 중요.
• 본장에서는 랜덤 액세스 최소화하는 구체적인 방법들을 소개

1.1 테이블 랜덤 액세스

• 아무리 데이터가 많아도 인덱스를 사용하면 데이터가 금방 조회됨(소량의 데이터 조회 시)
• 대량의 데이터를 조회할 때 인덱스를 사용하면 테이블 전체 스캔할때 보다 훨씬 느리다.
• 인덱스 ROWID는 물리적 주소? 논리적 주소?
  • 물리적주소 : 데이터파일 번호, 오브젝트번호, 블록번호등의 조합으로 구성
  • 논리적 주소 : 테이블 레코드를 찾아가기 위한 논리적 주소정보를 담고있음
  • ROWID는 논리적 주소이며, 디스크상의 테이블 레코드를 찾아가기위한 위치정보를 담는다(프로그래밍의) 포인터가 아니며, 물리적으로 직접 연결된 구조는 더더욱 아니다.
• 메인 메모리 DB(MMDB)와 비교
  • 데이터를 모두 메모리에 로드해 놓고 메모리를 통해서만 I/O를 수행하는 DB
  • 잘 튜닝된 OLTP성 DBMS는 버퍼 캐시 히트율이 99%이상. 그런데도 메인메모리 DB만큼 빠르지 않다
  • 특히 대량데이터를 인덱스로 액세스할 때는 엄청난 차이가 난다.
  • MMDB의 인덱스는 디스크상 주소정보가 아닌 메모리상 주소정보(포인터)이기에 액세스 비용이 매우낮다
  • 오라클은 테이블 블록이 수시로 버퍼 캐시에서 밀려났다가 캐싱되며, 그때마다 다른 공간에 캐싱되기때문에 인덱스에서 포인터로 직접 연결할 수 없는 구조.
  • 메모리 주소정보(포인터)가 아닌 디스크 주소정보를 이용해 해시 알고리즘으로 버퍼 블록을 찾아간다.
• I/O메커니즘 복습
  • DBA(데이터파일번호+블록번호)는 디스크상에서 블록을 찾기위한 주소정보.
  • 매번 디스크에서 블록을 읽을 수 없기에 I/O성능 향상을 위해 버퍼 캐시를 활용 해야한다.
  • 데이터를 찾을 때는 항상 버퍼 캐시부터 찾아본다.
  • 해싱 알고리즘으로 버퍼 헤더 찾고 - 거기서 얻은 포인터로 버퍼블록을 찾아간다.
  • FULL SCAN할 때는 익스텐트 맵을 통해 읽을 블록들의 DBA정보를 얻는다.
  • 인덱스 ROWID는 포인터가 아니다, 디스크상 테이블레코드 찾기 위한 논리적 주소 정보 .ROWID가 가리키는 테이블 블록을 버퍼 캐시에서 먼저 찾아보고, 못찾을 때만 디스크블록에서 읽는다.(버퍼 캐시에 적재한 후 읽는다.)
  • 모든 데이터가 캐싱 되 있어도 테이블 레코드를 찾기 위해 매번 DBA 해싱과 래치 획득 과정을 반복 해야 한다.
  • 동시 액세스가 심할 때는 캐시 버퍼 체인 래치와 버퍼 Lock에 대한 경합까지 발생.
  • ROWID를 이용한 테이블 액세스는 생각보다 고비용구조.
• 인덱스 ROWID는 우편주소
  • 디스크 DB(오라클 등의 일반 DBMS)가 사용하는 ROWID를 우편주소 메인 메모리 DB가 사용하는 포인터를 전화번호에 비유할 수 있다.
  • 전화통신은 물리적으로 연결된 통신망을 이용하므로 전화번호를 누르면 곧바로 통화할 수 있다. 우편 통신은 봉투에 적힌대로 우체부 아저씨가 일일이 찾아다니는 구조이므로 전화와 비교할 수 없이 느리다.

1.2 인덱스 클러스터링 팩터

• 특정 컬럼(인덱스)를 기준으로 같은 값을 갖는 데이터가 테이블상에 서로 모여 있는 정도를 의미
• CF가 좋은 컬럼에 생성한 인덱스는 검색효율이 매우 좋다. EX) 거주지역 = '제주' 에 해당하는 데이터가 물리적으로 근접해 있으면 흩어져 있을 때보다 서칭 속도가 빠르다. 분가한 자녀가 모두 한 동네 모여 살면 CF가 좋기 때문에 부모가 자녀들 집을 모두 방문하는데 하루면 족하다. 전국 각지에 흩어져 살면 며칠이 걸린다.
• CF가 좋은 컬럼에 생성한 인덱스 검색효율이 좋다는 것은 테이블 액세스 량에 비해 블록I/O가 적게 발생함을 의미
• 버퍼 피닝 때문, ROWID로 테이블을 액세스할 때 오라클은 래치 획득과 해시체인스캔 과정을 거쳐 어렵게 찾아간 테이블 블록에 대한 포인터(메모리 주소값)를 바로 해제하지 않고 일단 유지한다 > 버퍼 Pinning
• CF가 안 좋은 인덱스 사용시 테이블 액세스하는 횟수만큼 블록I/O가 발생

1.3 인덱스 손익분기점

• 인덱스 ROWID를 이용한 테이블 액세스는 생각보다 고비용구조