INDEX_FFS 힌트를 이용한 튜닝
[환경]
로컬노트북 사양 : Windows XP Professional
Intel Core2, Pentium4 2.0GHz, 1G RAM
Oracle Version : Oracle 9.2 (C:\oracle\ora92에 설치)
실습 테이블 : EMP(3,670,016건), CUSTOMER(67,108,864건)
인덱스 : EMP테이블에 대해 급여(SAL), 성명(ENAME), 입사일(HIREDATE)
컬럼에 대해 각각 인덱스 생성
각 컬럼에서 분포도를 확인 하기 위해 Unique한(중복되지 않은)
컬럼 건수는 아래와 같다.
(SAL, HIREDATE인 경우 중복되는 레코드가 많이 있음)
SQL> SELECT DISTINCT ENAME FROM EMP; //3,502,248 건
SQL> SELECT DISTINCT SAL FROM EMP ; //126 건
SQL> SELECT DISTINCT HIREDATE FROM EMP //211 건
CUSTOMER 테이블에 대해 성별(SEX) 컬럼에 인덱스 생성
(성별 컬럼에 대한 컬럼 값의 분포도는 50% 정도)
테이블스페이스 : DATA 영역(USERS), INDEX 영역(USERS_IDX)
옵티마이저 모드 : CHOOSE
쿼리(Query)를 사용하여 개발을 하다 보면 흔히 어떤 테이블에 원하는 데이터가 있는지 세어보는 경우가 가끔 있습니다. 이러한 경우 count(*)를 사용하는데 INDEX_FFS 힌트를 이용하면 빠르게 데이터 건수를 셀 수 있다.
COUNT를 할 때 전체 테이블을 FULL SCAN 할 수도 있고 INDEX 영역을 FULL SCAN할 수도 있다. TABLE을 전체 FULL SCAN 할 때와 INDEX영역을 FULL SCAN할 때 어디가 빠르다고는 장담하지 못한다. 이러한 경우 테이블 이나 인덱스의 단일 블록을 SCAN하기 때문에 예측하기가 애매하다. 그러나 인덱스를 SCAN하는데 하나의 블록이 아니라 여러 개의 블록(MULTI BLOCK)을 스캔(SCAN)한다면 어떨까? 당연히 단일 블럭(SINGLE BLOCK)을 스캔(SCAN)하는 것보다 빠르다. 이때 사용하는 것이 INDEX FAST SCAN이라는 것인데 count를 하는 경우 장점이 있다.
아래 실험을 통해 결과를 확인 해 보자.
(실습용 테이블인 EMP 테이블의 건수는 3,670,016건)먼저 count(*)를 사용하여 건수를 확인해 보도록 하자.
(5초 정도 소요됨)
SQL> select count(*) from emp;
COUNT(*)
----------
3670016
경 과: 00:00:05.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'
Statistics
0 recursive calls
0 db block gets
30957
consistent gets
28809
physical reads
0 redo size
386 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
이번에는 hiredate 컬럼의 인덱스를 이용하여 전체 테이블 count를 한다. 전체 테이블 FULL SCAN보다 1초 정도 성능 향상됨을 알 수 있다.
(약 4초 정도 소요됨)
SQL> select /*+ index_asc(emp idx_emp_hiredate) */ count(hiredate)
2 from emp;
COUNT(HIREDATE)
---------------
3670016
경 과: 00:00:04.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'IDX_EMP_HIREDATE' (NON-UNIQUE) (C
ost=26 Card=2546048)
Statistics
0 recursive calls
0 db block gets
9712
consistent gets
9712
physical reads
0 redo size
393 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
이번에는 INDEX_FFS 힌트를 사용하여 COUNT 한다.
(약 2초 정도 소요됨)
SQL> select /*+ index_ffs(emp idx_emp_hiredate) */ count(hiredate)
2 from emp;
COUNT(HIREDATE)
---------------
3670016
경 과: 00:00:02.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX_EMP_HIREDATE' (NON-UNIQU
E) (Cost=4 Card=2546048)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9756
consistent gets
9741
physical reads
0 redo size
393 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hiredate 인덱스를 단일 블록 스캔(Scan)하는 방법과 멀티 블록 스캔(Scan) 하도록 index_ffs 힌트를 사용했을 때 실제 읽어 들이는 블록의 전체 개수 차이는 별로 없다. 단지 블록을 하나씩 읽어 들이느냐 아니면 여러 블록을 한번에 읽어 들이느냐에 따라 응답시간(Response Time) 차이가 나타난다.
인덱스를 사용하지 않고 COUNT 하는 방법보다 인덱스를 사용하면서 INDEX_FFS힌트를 이용한 COUNT 방법이 수행 성능이 많이 향상 되었음을 알 수 있다.(5초 à 2초) 대부분의 시스템에서 데이터베이스 테이블을 사용하고 거의 모든 테이블이 2~3개 정도의 인덱스는 사용하고 있다. 프로그래밍 개발을 하거나 데이터베이스 쿼리 작업을 할 때 수시로 사용되는 쿼리(Query) 문이 COUNT(*)인데 위 예 에서 처럼 적절한 인덱스를 사용하고 Fast Full Scan을 이용 한다면 성능은 50% 정도 이상 향상 될 수 있다.
[환경]
로컬노트북 사양 : Windows XP Professional
Intel Core2, Pentium4 2.0GHz, 1G RAM
Oracle Version : Oracle 9.2 (C:\oracle\ora92에 설치)
실습 테이블 : EMP(3,670,016건), CUSTOMER(67,108,864건)
인덱스 : EMP테이블에 대해 급여(SAL), 성명(ENAME), 입사일(HIREDATE)
컬럼에 대해 각각 인덱스 생성
각 컬럼에서 분포도를 확인 하기 위해 Unique한(중복되지 않은)
컬럼 건수는 아래와 같다.
(SAL, HIREDATE인 경우 중복되는 레코드가 많이 있음)
SQL> SELECT DISTINCT ENAME FROM EMP; //3,502,248 건
SQL> SELECT DISTINCT SAL FROM EMP ; //126 건
SQL> SELECT DISTINCT HIREDATE FROM EMP //211 건
CUSTOMER 테이블에 대해 성별(SEX) 컬럼에 인덱스 생성
(성별 컬럼에 대한 컬럼 값의 분포도는 50% 정도)
테이블스페이스 : DATA 영역(USERS), INDEX 영역(USERS_IDX)
옵티마이저 모드 : CHOOSE
쿼리(Query)를 사용하여 개발을 하다 보면 흔히 어떤 테이블에 원하는 데이터가 있는지 세어보는 경우가 가끔 있습니다. 이러한 경우 count(*)를 사용하는데 INDEX_FFS 힌트를 이용하면 빠르게 데이터 건수를 셀 수 있다.
COUNT를 할 때 전체 테이블을 FULL SCAN 할 수도 있고 INDEX 영역을 FULL SCAN할 수도 있다. TABLE을 전체 FULL SCAN 할 때와 INDEX영역을 FULL SCAN할 때 어디가 빠르다고는 장담하지 못한다. 이러한 경우 테이블 이나 인덱스의 단일 블록을 SCAN하기 때문에 예측하기가 애매하다. 그러나 인덱스를 SCAN하는데 하나의 블록이 아니라 여러 개의 블록(MULTI BLOCK)을 스캔(SCAN)한다면 어떨까? 당연히 단일 블럭(SINGLE BLOCK)을 스캔(SCAN)하는 것보다 빠르다. 이때 사용하는 것이 INDEX FAST SCAN이라는 것인데 count를 하는 경우 장점이 있다.
아래 실험을 통해 결과를 확인 해 보자.
(실습용 테이블인 EMP 테이블의 건수는 3,670,016건)먼저 count(*)를 사용하여 건수를 확인해 보도록 하자.
(5초 정도 소요됨)
SQL> select count(*) from emp;
COUNT(*)
----------
3670016
경 과: 00:00:05.08
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE
1 0 SORT (AGGREGATE)
2 1 TABLE ACCESS (FULL) OF 'EMP'
Statistics
0 recursive calls
0 db block gets
30957
consistent gets
28809
physical reads
0 redo size
386 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
이번에는 hiredate 컬럼의 인덱스를 이용하여 전체 테이블 count를 한다. 전체 테이블 FULL SCAN보다 1초 정도 성능 향상됨을 알 수 있다.
(약 4초 정도 소요됨)
SQL> select /*+ index_asc(emp idx_emp_hiredate) */ count(hiredate)
2 from emp;
COUNT(HIREDATE)
---------------
3670016
경 과: 00:00:04.00
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=26 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FULL SCAN) OF 'IDX_EMP_HIREDATE' (NON-UNIQUE) (C
ost=26 Card=2546048)
Statistics
0 recursive calls
0 db block gets
9712
consistent gets
9712
physical reads
0 redo size
393 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
이번에는 INDEX_FFS 힌트를 사용하여 COUNT 한다.
(약 2초 정도 소요됨)
SQL> select /*+ index_ffs(emp idx_emp_hiredate) */ count(hiredate)
2 from emp;
COUNT(HIREDATE)
---------------
3670016
경 과: 00:00:02.04
Execution Plan
----------------------------------------------------------
0 SELECT STATEMENT Optimizer=CHOOSE (Cost=4 Card=1)
1 0 SORT (AGGREGATE)
2 1 INDEX (FAST FULL SCAN) OF 'IDX_EMP_HIREDATE' (NON-UNIQU
E) (Cost=4 Card=2546048)
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
9756
consistent gets
9741
physical reads
0 redo size
393 bytes sent via SQL*Net to client
503 bytes received via SQL*Net from client
2 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
1 rows processed
hiredate 인덱스를 단일 블록 스캔(Scan)하는 방법과 멀티 블록 스캔(Scan) 하도록 index_ffs 힌트를 사용했을 때 실제 읽어 들이는 블록의 전체 개수 차이는 별로 없다. 단지 블록을 하나씩 읽어 들이느냐 아니면 여러 블록을 한번에 읽어 들이느냐에 따라 응답시간(Response Time) 차이가 나타난다.
인덱스를 사용하지 않고 COUNT 하는 방법보다 인덱스를 사용하면서 INDEX_FFS힌트를 이용한 COUNT 방법이 수행 성능이 많이 향상 되었음을 알 수 있다.(5초 à 2초) 대부분의 시스템에서 데이터베이스 테이블을 사용하고 거의 모든 테이블이 2~3개 정도의 인덱스는 사용하고 있다. 프로그래밍 개발을 하거나 데이터베이스 쿼리 작업을 할 때 수시로 사용되는 쿼리(Query) 문이 COUNT(*)인데 위 예 에서 처럼 적절한 인덱스를 사용하고 Fast Full Scan을 이용 한다면 성능은 50% 정도 이상 향상 될 수 있다.
댓글 없음:
댓글 쓰기