출처 : KACHISORI
본 자료는 일본
@IT(http://www.atmarkit.co.jp/fdb/index/index-db.html#tuneorasql) 에 株式会社アゲハ加藤 猛씨가 연재한 Oracle SQLチューニング講座를 번역 재구성한 것입니다.
전회 「퍼포먼스 향상의 최단 코스를 알자」에서 설명한 것처럼 SQL의 튜닝은 Oracle 인스턴스나 데이타베이스의 구성 등에도 밀접하게 관계되어 있습니다. 이미 기본적인 지식을 가지고 계신 분도 계시겠지만 그 지식을 「퍼포먼스」와 연결시키는 것이 중요한 포인트입니다.
SQL로 취득하는 데이터가 실제로는 어떠한 형식으로 파일안에 격납되고 있는지 혹은 그 데이터에 어떻게 액세스 하는지를 이해해야만이 현상 파악이나 개선 포인트를 정확히 추출해 낼수 있습니다.
이번회와 다음회 2회에 걸쳐서 Oracle 데이타베이스의 기초지식을 소개하여 SQL 튜닝을 실시할 때 어떠한 점을 고려해야할 것인가를 염두하고 확인해 보겠습니다.
■ Oracle의 I/O단위
SQL문이 테이블에서 레코드를 취득하는 경우 실제의 I/O는 데이터·블록이라 불리는 단위(2 Kbytes, 4 Kbytes, 8 Kbytes, 16 Kbytes등)로 실행되며 읽어드린 데이터·블록은 데이타베이스 버퍼 캐쉬라고 하는 메모리상에 캐쉬됩니다(그림 1).
 |
|
그림 1 Oracle의 파일 I/O
|
①②SELECT을 발행하면 ③데이터 파일에서 취득한 데이터·블록은 데이타베이스·버퍼·캐쉬에 보관 유지된다.④다음 번의 문의에서는 먼저 데이타베이스·버퍼·캐쉬를 찾아 존재하지 않는 경우에 데이터 파일에 액세스 한다
취득하려고 하는 데이터를 포함한 블록이 이미 데이타베이스·버퍼·캐쉬상에 존재할 경우를 캐쉬·히트라 하며 존재하지 않았을 경우를 캐쉬·미스라고 부릅니다.
SQL 튜닝을 실시하는 경우 "처리 시간"은 물론이고 SQL이 "액세스 할 데이터·블록수를 줄이게 해야 한다"란 것도 염두하시길.. 그렇게 함으로써 데이타베이스·버퍼·캐쉬가 보다 효율적으로 사용되고 또한 캐쉬·미스의 경우에서도 필요한 데이터·파일에의 I/O는 적어지므로 시스템 전체의 부하를 절감 할수 있습니다.
예를 들면 레코드의 삽입, 삭제, 갱신의 원인으로 데이터·블록이 fragment(단편화) 되어 버렸을 경우를 생각해 보겠습니다. 생성시에는 모든 레코드를 참조하는데 5 블록으로 가능했던 것이 fragment의 발생함으로써 9 블록을 읽어들여야 하는 경우도 있습니다. (그림 2).
 |
|
그림 2 fragment의 발생과 액세스·블록수
Oracle 데이타베이스는"테이블 스페이스"의 내부에 테이블이나 색인, 파티션등의 "데이터 세그먼트(segment)"를 가지는 구조이다. 각 데이터·세그먼트(segment)는 "extent" 라고 불리는 사이즈로 확장된다. extent내의 데이터·블록에 "레코드"가 격납되어 있다(그림의 위쪽).
|
이것은 액세스 하는 데이터·블록수가 증가한다란 일례에 지나지 않습니다만 격납되는 데이터의 특성(1 레코드·사이즈가 큰 등)에 대한 적절한 기억 영역 파라미터의 지정(PCTFREE 注1 )이나 멀티·블록·사이즈의 사용등의 SQL문 실행시에 액세스 되는 데이터·블록수를 조정하는 것이 가능합니다.
|
注1：PCTFREE |
■ 옵티마이져
대부분의 경우 어느 SQL문이 실행되면 취득하고 싶은 레코드에의 액세스 방법은 복수개 존재합니다. 예를 들면 1개의 테이블을 검색하는 경우를 생각해 봐도, "테이블 풀스캔을 할지, 색인을 사용할지 또한 색인을 사용하는 경우는, 어느 색인을 사용할지" 등 복수의 방법을 생각할 수 있습니다. 이와 같이 테이블에 어떠한 경로로 액세스해서, 어떠한 차례, 방법으로 결합하겠는가등의 계획을 "실행 계획"이라고 부릅니다. 다수의 실행 계획중에서 가장 효율적이라고 생각되는 실행 계획을 결정하는 것이 옵티마이져의 역할입니다(그림 3).
 |
| 그림 3 옵티마이져의 역할 |
옵티마이져는 일정한 룰에 따라 실행 계획을 결정하는 룰 베이스·어프로치(RBO)와 테이블과 색인의 통계 정보를 기본으로 코스트를 추측해 그 결과로 실행 계획을 결정하는 코스트 베이스·어프로치(CBO)가 있습니다.초기화 파라미터 「OPTIMIZER_MODE」가 디폴트의 「CHOOSE」인 경우에는 대상 테이블, 색인의 통계 정보가 있다면 코스트 베이스·어프로치, 통계 정보가 취득되어 있지 않은 경우는 룰 베이스·어프로치로 최적화됩니다. 이러한 동작은 SQL문 단위, 세션 단위등에서 변경하는 것도 가능합니다.
Oracle9i까지는 CBO, RBO 둘중 아무거나 사용할 수 있습니다. 반드시 CBO의 옵티마이져를 사용할 필요는 없습니다만, 퍼포먼스를 향상시키기 위해 추가된 기능의 상당수는 CBO에서만 사용 가능하므로 추천되고 있습니다.
■ 레코드에의 액세스 방법
테이블 레코드에 액세스하기 위한 액세스 경로를 액세스·패스라고 부르며 대표적으로 풀 테이블 스캔, 색인 스캔, 그리고 ROWID 스캔이 있습니다.
효율적인 색인이 존재함에도 불구하고 "풀 테이블 스캔"이 선택되어 버렸을 경우에는 액세스 해야 할 데이터 블록수가 증가하므로 퍼포먼스가 나뻐질 가능성이 있습니다.
여기에서는 튜닝이라고 하는 관점에서 풀테이블 스캔, 색인 스캔, 그리고 Oracle로 가장 효율적인 액세스 방법인 ROWID 스캔에 대해 설명합니다.
◆ 풀 테이블 스캔
풀 테이블 스캔은 반드시 HWM 注2까지의 모든 데이터·블록에 액세스 해 모든 레코드를 읽어들여, 지정된 조건의 체크 판정을 실시합니다.이 방법은 테이블에의 액세스 방법 중 가장 기본이 되는 것입니다.
|
注2：HWM |
책에 비유하면 알고 싶은 정보(행)를 찾기 위해서 반드시 마지막 페이지까지 대충 훑어보게 됩니다. 여기서 만일 1 페이지에 알고 싶은 정보가 쓰여져 있다고 해도 끝까지 대충 훑어보게 되는 것이다.
풀테이블 스캔은 복수의 데이터·블록을 한번에 읽어들이기 때문에 테이블의 대부분의 레코드를 추출하는 경우는 효율적인 액세스 방법이 됩니다. 그림4 가 풀테이블 스캔의 이미지입니다.
 |
| 그림 4 풀테이블 스캔 |
◆ ROWID스캔
ROWID 스캔은 Oracle에서 가장 고속으로 목적의 레코드에 액세스 하는 방법입니다. ROWID란 데이타베이스내의 "어느 데이터 파일의, 몇번째의 데이터 블록중의, 몇번째의 레코드"라는 레코드 위치를 나타내는 Oracle의 내부적인 표현입니다. 조건에 이 ROWID를 지정하여 액세스 하면 직접 목적의 레코드를 포함한 데이터 블록에 액세스 할 수 있습니다.
책에 비유하면 알고 싶은 정보가 어느 페이지의 어느 행에 있을까를 미리 기억하고 있어 직접 그 페이지를 열게 되는 경우??
ROWID 스캔은 매우 효율적입니다만 유저에게 있어서 의미를 가지지 않는 문자열이기 때문에 기억하는 것이 불가능하고 또 Export/Import나 테이블의 이동(alter table move 커멘드) 등에 의해 ROWID는 바뀌기 때문에 검색 조건으로서 지정하는 것은 현실적이지는 않습니다. 직접 ROWID를 사용해 액세스 하는 케이스는 어플리케이션으로 일단 취득한 레코드에 재차 액세스 하는 경우입니다. 그림 5가 ROWID 스캔의 이미지입니다
 |
| 그림 5 ROWID 스캔 |
◆ 색인 스캔
색인 스캔이란 색인을 읽어들여 ROWID를 취득한후 그 ROWID로 데이터에 액세스 하는 방법을 가리킵니다. 색인에도 여러 종류가 있습니다만, 여기에서는 가장 자주 이용되는 B*Tree 색인을 예에 설명합니다. B*Tree 색인은 소트된 색인열의 데이터와 대응하는 ROWID를 보관 유지하고 있습니다. 조건식에 색인열이 지정되어 있으면 색인을 읽어들여 ROWID 정보를 취득해 그 ROWID로 테이블의 레코드에 액세스 합니다.
책으로 비유하면 색인을 보고 페이지 번호, 행 번호를 조사해 해당의 페이지를 펴는 매우 효율좋고 현실적인 방법입니다.
그림 6은 B*Tree 색인을 사용했을 경우의 색인 스캔 동작입니다. 이 예의 같게 중복성, 혹은 선택성의 높은 컬럼에 대해서 색인 스캔을 실시했을 경우 색인을 건드려야할 블록과 실제의 레코드가 격납되고 있는 데이터·블록에의 액세스만으로 목적의 레코드를 취득할 수 있습니다.
 |
| 그림 6 B*Tree 색인 스캔 |
색인 스캔는 "색인 블럭 읽기＋데이터 블록읽기"으므로 테이블에서 어느 정도 이상의 비율을 추출하는 경우에는 풀테이블 스캔이 효율적일 가능성이 있는 것에 주의해야 합니다. 일반적으로 검색하고 싶은 레코드 건수가 레코드 전체의 5~15%정도까지의 경우는 색인 스캔이 효율적이라고 하네요.
또 색인이 작성된 열의 데이터를 갱신하면 색인도 자동적으로 관리 되므로 극단적으로 많은 색인을 작성하면 갱신 처리의 퍼포먼스에 영향을 줄 가능성이 있습니다. 별로 사용되지 않는 색인이나 퍼포먼스적으로 효과의 적은 색인을 삭제하는 것도 검토해야 할 것입니다.
색인에는 B*Tree 이외에도 비트 맵 색인이나, 함수 색인 등, 데이터의 구성이나 용도에 따라 몇 종류인가 있습니다만, 모두 목적의 레코드의 ROWID를 효율적으로 취득할 수 있는 구조가 되고 있습니다. 이와 같이 테이블에 액세스 하는 방법을 이해하여 적절한 스캔의 선택을 실시, 퍼포먼스 향상을 도모합니다.
댓글 없음:
댓글 쓰기