출처 : KACHISORI
전회의 「퍼포먼스를 향상시키는SQL의 기술법」에서는 SQL의 기술방법을 통일하는 것의 메리트에 대해 설명했습니다. SQL의 처리 스텝을 이해해 기술 방법을 통일하는 것이 퍼포먼스에도 크게 영향을 주는 것을 알수 있었습니다. 이번회에서는 색인을 사용해 데이터 액세스 범위를 좁히고, 색인을 사용하기 위한SQL의 기술 방법이나, 색인을 사용한 검색시의 주의점에 대해 설명합니다.
■ 색인을 사용할 수 없는 케이스
「색인을 작성했건만 처리가 늦다」「튜닝을 위해서 색인을 추가했지만 처리 시간이 변하지 않다」라는 케이스는 빈번히 일어납니다. 원인은 얼마든지 생각할 수 있습니다만, 가장 많은 것은 「모처럼 작성한 있는 색인을 사용 되어 있지 않다」란 케이스입니다. 이것들은 SQL의 기술 방법이나 색인에 대한 이해 부족에서 일어나고 있습니다. 그러면 구체적인 예를 이용해 색인이 사용되지 않는 SQL에 대해 설명해 보겠습니다.
NULL 값의 검색
「supplier 」테이블의 「s_suppkey 」열에 색인을 작성했다고 가정하고 그림1과 같은 SQL 문장을 실행해 SQL 트레이스 TKPROF 유틸리티를 사용해 실행 계획을 취득합니다.
 |
|
그림1 is null 검색의 실행 계획 |
그림1의 실행 계획의(1 )을 보면 풀테이블 스캔이 실행되고 있으며 「s_suppkey 」열에 작성되고 있는 색인이 사용되고 있지 않은 것이 확인할 수 있습니다. Oracle에서는 B*Tree 색인에 NULL 값의 데이터를 포함하지 않기 때문에 IS NULL 검색에서는 색인을 사용할 수 없습니다. 따라서 검색 조건열에 색인이 작성되고 있어도 풀테이블 스캔이 되어 버립니다.
이러한 데이터를 빈번히 검색할 필요가 있으며 퍼포먼스가 요구되는 경우에는,
-
NULL 을 특정의 값으로 교체한다.
-
비트 맵 색인을 사용한다
과 같은 방법을 생각할 수 있습니다 주1 .
|
주1 |
예를 들면 NULL 값 대신에 시스템상 있을 수 없는 값으로 해서 「-1 」을 세트 했을 경우, 그림2 와 같이 색인 스캔을 합니다. 또 비트 맵 인덱스는 NULL 값의 데이터를 포함하기 때문에 검색 조건에 IS NULL로 지정되더라도 그림3 과 같이 색인 스캔을 실행합니다. 그림 3 은 비트 맵 색인을 사용했을 때의SQL 트레이스,TKPROF 유틸리티의 결과입니다. (1)과(2)에서 보듯 비트 맵 색인을 사용해서 ROWID에의 변환후 검색되고 있는 것을 알수 있습니다.
 |
|
그림2 NULL값을 특정의 값으로 교체했을 경우의 실행 계획 |
 |
|
그림3 비트 맵 색인을 사용했을 경우의 실행 계획 |
그 외에 색인이 사용되지 않는 케이스
대표적인 예로 NULL 값의 검색을 예로 들었습니다만 조건열에 색인이 있더라도 옵티마이져가 색인 스캔을 선택할 수 없는 케이스에는 이하와 같은 것이 있습니다. 색인 사용의 유무는 퍼포먼스에 크게 영향을 주기 때문에 SQL을 기술할 때는 이러한 기본을 제대로 기억해 둘 필요가 있습니다.
|
케이스 |
SQL 의 예 |
대처 방법 |
|
NULL 값의 검색 |
・열명 IS NULL |
NULL 값을 다른 값으로 교체한다. |
|
암묵의 형태 변환 |
・CHAR 열 = 1 |
비교하는 데이터형을 열의 데이터형으로 맞춘다 |
|
색인열에 대해, 함수나 산술을 실시 |
・VARCHAR2 열||' 양' = ' 김양' |
함수, 연산을 우변(색인열이 아닌 쪽)으로 이동한다 함수 색인을 사용한다(Oracle 9i 이상으로 사용 가능) |
|
LIKE 의 중간 일치, 후방 일치 |
・열명 LIKE'%TEST%' |
INDEX 힌트를 사용한다(색인열에NOT NULL 제약이 필요) |
|
!= ,<> 의 사용 |
・열명 != '1' |
in 으로 교에한다.(가능한 경우) 리스트수는1000 개 |
|
표1 색인을 사용할 수 없는SQL과 대처 방법 | ||
■사용하고 있는 색인의 문제
대부분의 경우 색인 스캔으로 퍼포먼스를 개선할 수 있습니다만, 색인 스캔도 만능이 아닙니다.「실행 계획을 확인하면, 색인 스캔을 실시하고 있음에도 불구하고 처리 속도가 늦다」 「풀테이블 스캔보다 처리가 늦다」라고 하는 경우도 있을 수 있습니다. 여기에서는 어떠한 경우에 그러한 사태가 일어나는 지 예를 들어 설명하겠습니다.
색인열의 데이터 편향
색인 스캔이 풀테이블 스캔보다 퍼포먼스가 나쁜다란 의미가 어떠한 경우인지를 생각해 봅시다.우선, 제2 회 「SQL 튜닝의 필수 지식을 총 자리등 있어(전편) 」에서 설명한 레코드에의 액세스 방법을 생각해 보겠습니다.
HWM (High Water Mark : 최고 수위표)까지의 모든 레코드를 읽어들이는 풀테이블 스캔과 ROWID 정보를 취득하여 그 ROWID 정보를 기본으로 테이블의 레코드를 읽어들이는 색인 스캔은 레코드에의 액세스 방법이 크게 다릅니다. 그 때문에 각각의 메리트를 살릴 수 있는 상황을 이해하고, 적절히 구분하여 사용할 필요가 있습니다.
그림4는 검색하는 레코드의 비율에 의해 색인 스캔, 풀테이블 스캔의 처리 속도가 어떻게 변해가는지를 나타낸 일례입니다. 색인 스캔과 전표 스캔의 처리 속도는 검색 레코드의 비율이 증가에 따라 차이가 줄어들고 어떤 시점을 경계로 역전하고 있는 것을 알수 있습니다(역전하는 시점은, 색인열의 데이터나 데이터 길이에 따라서 다릅니다).
 |
|
그림4 색인 스캔대 전표 스캔의 처리 속도 비교 |
색인을 사용하고 있음에도 불구하고 의도한 퍼포먼스를 얻을 수 없는 경우에는, 그림4 와 같이 검색 레코드의 비율이 너무 많을 가능성을 생각할 수 있습니다.그러한 경우에는, DBA_TABLES 뷰나DBA_TAB_COLUMNS 뷰,DBA_HISTOGRAMS 뷰 등에 격납되고 있는 통계 정보 주2 로부터 테이블에 격납되고 있는 레코드 건수나, 어느 열에는 몇 종류의 데이터가 존재하고 있는가 한 정보를 확인할 수 있습니다. 또는 이하와 같이SQL을 실행하는 것도 데이터의 편향을 확인할 수 있습니다.
|
주2 ： 통계 정보 |
 |
|
그림5 데이터 편향의 조사 방법 |
그림5 의 결과를 보면 「99999 」인 레코드가3 건 밖에 없는데 비해서 값이 「EEEEE 」인 레코드는5 만건이므로 전레코드 건수의 반을 차지하고 있어 「INFO_TXT 」열에는 큰 데이터의 편향이 있는 것을 알수 있습니다. 그렇다면 실제로 퍼포먼스에 얼마나의 차이가 나오는지를 봅시다.
 |
|
그림6 색인 스캔으로 값이 「99999 」의 레코드를 취득했을 경우 |
 |
|
그림7 색인 스캔으로 값이 「EEEEE 」의 레코드를 취득했을 경우 |
그림6 , 그림7 의 붉은선 부분을 비교하면, 양쪽 모두 색인 스캔을 실시하고 있음에도 불구하고, 실행 시간, 액세스 블록수에 큰 차이가 나와 있는 것을 확인할 수 있습니다.「INFO_TXT = '99999' 」의 조건으로 검색하는 경우가 색인 스캔이 효율적인 것을 알수 있습니다.
 |
|
그림8 풀테이블 스캔으로「INFO_TXT = 'EEEEE'의 레코드를 취득했을 경우 |
그림8은 FULL 힌트를 사용해 풀테이블 스캔을 실시했을 경우의 실행 계획입니다. 그림 7 과 비교하면 「elapsed 」가 약2 분의1 , 「disk 」은 약4 분의1 되므로 검색 레코드의 비율이 많은 경우에는 풀테이블 스캔이 효율적인 것을 알수 있습니다. 이것은 풀테이블 스캔이 멀티 블록 읽기를 실시하고 있는데 대해서 색인 스캔에서는 싱글 블록 액세스를 실시하고 있기 때문입니다. 그 결과 색인 스캔에서는 같은 블록을 몇번이나 액세스 하게 되기 때문에 액세스 블록수가 증가하고 있습니다.
이와 같이 조건치에 의해 최적인 실행 계획이 다른 경우 데이터 분포의 통계 정보를 취득(색인열의 히스토그램의 취득)해, 코스트 베이스의 어프로치를 실시합니다. 그 결과 옵티마이져가 적절한 실행 계획을 선택하기 위해서 정보를 제공합니다. 예를 들면 조건에 일치하는 건수가 전건수의 대부분을 차지하는SQL은 풀스캔, 조건에 일치하는 건수가 적은 경우에는 색인 스캔이라고 하는 실행 계획을 옵티마이져가 판단합니다.
색인의 격납 효율의 악화
색인의 격납 효율은 작성 직후가 가장 좋은 상태이며, 테이블 데이터의 갱신/삽입/삭제에 의해, 서서히 격납 효율이 악화되어서 갑니다.「처음은 퍼포먼스에 문제는 없었는데 , 서서히 퍼포먼스가 떨어지고 있다」라고 하는 문제의 가장 많은 원인의1 개라고 할 수 있습니다. 격납 효율이 악화된 색인에서는 색인 스캔에 의한 액세스할 블록수가 증가하기 때문에 색인 작성 직후에 비해 퍼포먼스가 열화 해 버립니다.
 |
|
그림9 색인 블록의 분할 |
그림9 는 UPDATE 문장을 실행했을 때에 색인 블록이 분할되는 모습을 나타내고 있습니다.(2)의UPDATE 문장의 실행으로 갱신 대상이 된 「100 」의 레코드가 삭제되어 블록2 에 「550 」의 레코드가 삽입됩니다. 그러나 이 블록에(PCTFREE 주3 의 영역도 포함해) 빈영역이 없는 경우에는 신규 블록(블록3 )이 작성되어 블록2 안의 「550 」이후의 레코드(여기에서는 「600 」)을 블록3 에 삽입합니다.
|
주3 ： 색인의PCTFREE 파라미터에 대해 |
색인의 격납 효율이 약간 악화되었을 경우에는 프라이머리·키를 조건으로 지정한 검색등에는 거의 영향은 없습니다만, 범위 검색등으로 많은 색인 블록을 읽어들일 필요가 있는 처리라면 퍼포먼스에 영향을 줍니다. 그림10 , 그림11 는, 작성 직후 상태와 격납 효율이 악화된 상태로, 색인 구조와 퍼포먼스를 조사한 결과가 입니다.
 |
|
그림10 색인 작성 직후의 결과 |
 |
|
그림11 색인의 격납 효율이 악화되었을 경우의 결과 |
색인의 구조를 비교하면, 그림11에서 색인이 사용하고 있는 블록수(BLOCKS), 및 색인의 계층의 높이(HEIGHT)가 증가했고 색인의 격납 효율이 악화된 것을 알수 있습니다. 또 색인의 범위 스캔을 실시했을 경우의 실행 결과에서는「disk 」「query 」의 값에 큰 차이가 발생하고 있는 것을 확인할 수 있습니다.
이와 같이 갱신 빈도가 격렬한 테이블의 색인은 정기적인 재작성(Rebuild )을 검토할 필요가 있습니다. 색인을 재작성하는 것으로 블록의 격납 효율을 최적화해, 퍼포먼스를 회복시킬 수 있습니다.
■ 색인의 구조를 확인하는 순서
이하에 색인의 구조를 확인하기 위한 순서예를 나타냅니다
1 .ANALYZE 커멘드로 색인의 구조를 분석한다
|
2 .INDEX_STATS 뷰로부터 분석 결과를 취득
INDEX_STATS 뷰에서는 직전에 실행된 ANALYZE 커멘드의 분석 결과만 취득할 수 있습니다.
다른 세션에서는 참조할 수 없습니다 (분석 결과를 데이타베이스내에 보존해 두는 경우는 테이블을 작성해, 데이터를 신규테이블에 카피합니다).
|
◇
이번회에서는 색인을 사용하기 위한SQL과 사용하고 있는 색인에 문제가 없는가의 확인 방법을 설명했습니다. 다음 회에서도 계속 색인에 관한 테크닉을 설명합니다.
댓글 없음:
댓글 쓰기