5. 테이블 Random 액세스 최소화 튜닝

2011.02.24 21:58

suspace 조회 수:7028

5. 테이블 Random 액세스 최소화 튜닝

(1) 인덱스 컬럼 추가

테이블 액세스를 하고 필터조건에 의해 버려지는 레코드가 상당한 경우

사례
- '서비스번호' 로 구성된 단일 컬럼 인덱스(로밍렌탈_N2)

select 렌탈관리번호, 고객명, 서비스관리번호, 서비스번호, 예약접수일시,,방문국가코드1, 방문국가코드2, 방문국가코드3, 로밍승인번호, 자동로밍여부
from 로밍렌탈
where 서비스번호 like ‘010%’
and 사용여부 = ‘Y’
-------------------------------------------------------------------------------------------
|   Rows | Row Source Operation                                                          |
-------------------------------------------------------------------------------------------
|    1909 | TABLE ACCESS BY INDEX ROWID 로밍렌탈 (cr=266968 pr=27830 pw=0 time= …..)     |
| 266476 |   INDEX RANGE SCAN 로밍렌탈_N2 (cr=1011 pr=900 pw=0 time=1893462 us)OF ….    |
-------------------------------------------------------------------------------------------
튜닝
- 기존 인덱스 구성을 바꾸면?
: 실 운영 환경에서는 기존 인덱스를 사용하는 sql 이 있을 수 있기 때문에 바꾸기 쉽지 않음

- 인덱스를 추가하면?
: 인덱스가 많아지면 인덱스 관리 비용 증가, DML 부하에 따른 트랜잭션 성능 저하

- 기존 인덱스에 컬럼 추가
: 인덱스 스캔량은 줄지 않지만 테이블 Random 액세스 횟수 감소
-------------------------------------------------------------------------------------------
|   Rows | Row Source Operation                                                          |
-------------------------------------------------------------------------------------------
|    1909 | TABLE ACCESS BY INDEX ROWID 로밍렌탈 (cr=2902 pr=0 pw=0 time= …..)           |
|    1909 |   INDEX RANGE SCAN 로밍렌탈_N2 (cr=1001 pr=0 pw=0 time=198557 us)OF ….      |
-------------------------------------------------------------------------------------------

(2) PK 인덱스에 컬럼 추가

NL 조인할 때

사례
select /*+ ordered use_nl(d) */ *
from emp e, dept d
where d.deptno = e.deptno
and d.loc = 'NEW YORK'
-------------------------------------------------------------------------------------------
|   Rows | Row Source Operation                                                          |
-------------------------------------------------------------------------------------------
|       3 | NESTED LOOPS (cr=25 pr=0 pw=0 time=422 us)
|      14 |   TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=139 us)
|       3 |   TABLE ACCESS BY INDEX ROWID DEPT (cr=17 pr=0 pw=0 time=568 us)
|      14 |     INDEX UNIQUE SCAN DEPT_PK (cr=3 pr=0 pw=0 time=255 us)
-------------------------------------------------------------------------------------------

※ 참고
ONE PLUS SCAN
RANGE LOOKUP의 경우 발생하는 연결시도 횟수만큼의 LOOKUP INDEX 스캔량을 의미한다. UNIQUE LOOKUP이 아닌 경우, 매 연결시도마다 한번 더 인덱스를 스캔해야 하므로 발생한다.
튜닝
- [PK컬럼 + 필터조건 컬럼]형태의 새로운 Non-Unique 인덱스를 추가

alter table dept drop primary key;

create index dept_x01 on dept(deptno, loc);

alter table dept add constraint dept_pk primary key(deptno) using index dept_x01;

-------------------------------------------------------------------------------------------
|   Rows | Row Source Operation                                                          |
-------------------------------------------------------------------------------------------
|       3 | TABLE ACCESS BY INDEX ROWID DEPT (cr=14 pr=0 pw=0 time=302 us)
|      18 | NESTED LOOPS (cr=12 pr=0 pw=0 time=1483 us)
|      14 |     TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=145 us)
|       3 |     INDEX RANGE SCAN DEPT_X01 (cr=4 pr=0 pw=0 time=220 us)
-------------------------------------------------------------------------------------------

※ 참고
PK제약을 위해 사용되는 인덱스는 PK 제약 순서와 일치하지 않아도 상관없다.
중복 값 유무를 체크하는 용도이므로 PK 제약 컬럼들이 선두에 있기만 하면 된다.

(3) 컬럼 추가에 따른 클러스터링 팩터 변화

인덱스 컬럼 추가의 부작용
테이블 Random 액세스 부하를 줄이는 효과가 있지만 인덱스 클러스터링 팩터가 나빠지는 부작용을 초래할 수 있음.

사례
create index t_idx on t(object_type);

exec dbms_stats.gather_table_stats(user, 't'); -- 통계정보 수집

-------------------------------------------------------------------------------------------
|   INDEX_NAME | CL_FACTOR | TAB_BLKS | TAB_ROWS |
-------------------------------------------------------------------------------------------
|      T_IDX     |      689     |      709   |   50,137    |
-------------------------------------------------------------------------------------------
- 클러스터링 팩터가 매우 좋은 상태.

select /*+ index(t t_idx) */ count(object_name), count(owner) from t
where object_type > ' ';

-------------------------------------------------------------------------------------------
|   Rows | Row Source Operation                                                          |
-------------------------------------------------------------------------------------------
|       1 | SORT ACCESGATE (cr=829 pr=0 pw=0 time=37213 us)
| 50137 | TABLE ACCESS BY INDEX ROWID T (cr=829 pr=0 pw=0 time=752094 us)
|      14 |     INDEX RANGE SCAN T_IDX (cr=140 pr=0 pw=0 time=151867 us)
-------------------------------------------------------------------------------------------

- 테이블 액세스는 50,137이지만 블록 I/O는 689 발생.
컬럼 추가 클러스터링 팩터 변화

drop index t_idx;

create index t_idx on t(object_type, object_name);

exec dbms_stats.gather_index_stats(user, 't_idx');

-------------------------------------------------------------------------------------------
|   INDEX_NAME | CL_FACTOR | TAB_BLKS | TAB_ROWS |
-------------------------------------------------------------------------------------------
|      T_IDX     |      33,843     |      709   |   50,137    |
-------------------------------------------------------------------------------------------

- 컬럼 추가로 인덱스 클러스터링 팩터가 689에서 33,843로 나빠짐.
- 변별력이 좋은 object_name 같은 컬럼을 추가하면 rowid 이전에 object_name 순으로 정렬되므로 클러스터링 팩터를 나쁘게 만드는 요인으로 작용.
결론
변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의.

(4) 인덱스만 읽고 처리

테이블 액세스가 아무리 많더라도 필터조건에 의해 버려지는 레코드가 거의 없는 경우
결론 : 테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에 포함

사례
- 인덱스 구성
서비스번호_N4 : 식별번호 + 번호상태코드 + 번호사용코드 + 라인번호 + 선호번호여부 + 번호구분코드

select *
from (select 식별번호 || 국번호 || 라인번호 as 서비스번호, 번호사용코드, 번호구분코드, 관리조직id,
   사용자id, decode(선호번호여부, ‘Y’, ‘예’, ‘N’, ‘아니오’, ‘ ‘) 선호번호여부
   from 서비스번호
   where 식별번호 = :식별번호
   and 라인번호 between :라인시작번호 and :라인종료번호
   and 번호상태코드 = ‘AV’
   and 번호사용코드 = :번호사용코드
   and 번호구분코드 = :번호구분코드
   and 선호번호여부 = :선호번호여부
   order by 서비스 번호)
where rownum <= 500

-------------------------------------------------------------------------------------------
|   Call | Count | CPU Time | Elapsed Time |   Disk | Query | Current | Rows |
-------------------------------------------------------------------------------------------
| Parse |     1   |    0.010   |      0.011     |     0    |     0   |     0    |    0   |
| Execute |     1   |    0.020   |      0.018     |     0    |     2   |     0    |    0   |
| Fetch |    21   |   18.780   |     96.381    |   11948 | 692668 |    0   |   500   |
-------------------------------------------------------------------------------------------
   Total       23         18.810         96.410        11948       692668        0         500

-------------------------------------------------------------------------------------------
|   Rows | Row Source Operation                                                          |
-------------------------------------------------------------------------------------------
|       0 | STATEMENT
|    500 |   COUNT STOPKEY (cr=692666 pr=11948 pw=0 time=96380650 us)
|    500 |     VIEW (cr=692666 pr=11948 pw=0 time=96380140 us)
|    500 |       SORT ORDER BY STOPKEY (cr=692666 pr=11948 pw=0 time=96380140 us)
| 691237 |         FILTER (cr=692666 pr=11948 pw=0 time=65667547 us)
| 691237 |          TABLE ACCESS BY INDEX ROWID 서비스번호 (cr=692666 pr=11948 pw=…)
| 691237 |             INDEX RANGE SCAN 서비스번호_N4 (cr=7223 pr=3731 pw=0 time=…)
-------------------------------------------------------------------------------------------
튜닝
- 인덱스 구성
서비스번호_N4 : 식별번호 + 번호상태코드 + 번호사용코드 + 라인번호 + 선호번호여부 + 번호구분코드
                 + 국번호 + 관리조직ID + 사용자ID

-------------------------------------------------------------------------------------------
|   Call | Count | CPU Time | Elapsed Time |   Disk | Query | Current | Rows |
-------------------------------------------------------------------------------------------
| Parse |     1   |    0.000   |      0.000     |     0    |     0   |     0    |    0   |
| Execute |     1   |    0.000   |      0.000     |    0    |     0   |     0    |    0   |
| Fetch |    21   |    1.700   |      1.728     |     0    | 7223 |     0     |   500   |
-------------------------------------------------------------------------------------------
   Total       21         1.700         1.728           0          7223         0         500

-------------------------------------------------------------------------------------------
|   Rows | Row Source Operation                                                          |
-------------------------------------------------------------------------------------------
|       0 | STATEMENT
|    500 |   COUNT STOPKEY (cr=7223 pr=11948 pw=0 time=1727339 us)
|    500 |     VIEW (cr=7223 pr=0 pw=0 time=1726826 us)
|    500 |       SORT ORDER BY STOPKEY (cr=7223 pr=0 pw=0 time=1726816 us)
| 691237 |         FILTER (cr=7223 pr=0 pw=0 time=1381986 us)
| 691237 |           INDEX RANGE SCAN 서비스번호_N4 (cr=7223 pr=0 pw=0 time=691026 us)
-------------------------------------------------------------------------------------------

- 인덱스 컬럼이 많아지면 그만큼 DML 속도가 느려지는 측면이 있지만 쿼리의 사용빈도를 감안한다면 얻는 것이 많다.

(5) 버퍼 Pinning 효과 활용

인덱스를 통해 아무리 많은 테이블 레코드를 액세스하더라도 Random 액세스에 의한
(I/0측면에서의) 비효율은 거의 존재하지 않게 되고, SQL 튜닝 시 가장 자주 사용되는 기법 중 하나가 됨.

※ 참고
BUFFER PINNING
과다한 체인 탐색에 의한 래치 경합을 줄이기 위해 사용. 특정 세션의 동일 SQL 콜(Call)내에서 2번 이상 스캔된 버퍼는 Pinned 상태로 변환된다.

사례
select /*+ ordered use_nl(b) rowid(b) */ b.*
from (select /*+ index(emp emp_pk) no_merge */ rowid rid
      from emp
      order by rowid) a, emp b
where b.rowid = a.rid

-------------------------------------------------------------------------------------------
| Row Source Operation                                                                   |
-------------------------------------------------------------------------------------------
0        SELECT STATEMENT Optimizer=ALL_ROWS (Cost=16 Card=14 Bytes=686)
1   0      NESTED LOOPS (Cost=16 Card=14 Bytes=686)
2   1        VIEW (Cost=2 Card=14 Bytes=168)
3   2          SORT (ORDER BY) (Cost=2 Card=14 Bytes=168)
4   3            INDEX (FULL SCAN) OF ‘EMP_PK’ (INDEX (UNIQUE)) (Cost=1 …)
5   4       TABLE ACCESS (BY USER ROWID) OF ‘EMP’ (TABLE) (Cost=1 Card=1 …)
-------------------------------------------------------------------------------------------
10g 에서는 버퍼 Pinning 효과 나타나지 않음.
11g 에서는 버퍼 Pinning 효과 나타남.

(6) 수동으로 클러스터링 팩터 높이기

해당 인덱스 기준으로 테이블을 재생성함으로써 CF를 인위적으로 좋게 만드는 방법을 말함.

- 인덱스가 여러 개인 상황에서 특정 인덱스를 기준으로 테이블을 재정렬하면 다른 인덱스의 CF가 나빠질 수 있다.
- 가장 자주 사용되는 인덱스를 기준으로 삼는다.
- 혹시 다른 인덱스를 사용하는 중요한 쿼리 성능에 나쁜 영향을 주지 않는지 반드시 체크한다.
- 테이블과 인덱스를 Rebuild하는 부담이 적고 효과가 확실할 때만 사용하는 것이 바람직하다.

사례
회사에서 가장 많이 조회되고 입력되는 상품거래 테이블이 있다.
입력은 거래가 일어나는 순서로 데이터가 입력되고 대부분의 쿼리들은 상품번호를 기준으로 조회한다면
테이블 Random 액세스 부하가 심각하게 된다.
튜닝
상품번호를 선두 키로 갖는 PK인덱스 컬럼 순으로 테이블을 정렬한다면 Random I/O 발생량을 상당히 줄일 수 있다.
PK인덱스와 상관관계가 높은 인덱스가 있다면 함께 효과를 볼 수 있다.

오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)

작성자: 이주영 (suspace)

최초작성일: 2011년 02월 27 일

본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net

문서의 잘못된 점이나 질문사항은 본 문서에 댓글로 남겨주세요. ^^

이 게시물을

번호	제목	글쓴이	날짜	조회 수
40	2. 서브쿼리 Unnesting	darkbeom	2011.05.15	19841
39	2. 소트를 발생시키는 오퍼레이션	휘휘	2011.06.12	3508
38	2. 파티션 Pruning	실천하자	2011.06.21	26102
37	3. 병렬 조인	실천하자	2011.06.28	7220
36	3. 다양한 인덱스 스캔 방식	멋진넘	2011.02.18	33881
35	3. 해시 조인	darkbeom	2011.03.20	21602
34	3. 옵티마이저의 한계	멋진넘	2011.04.19	7903
33	3. 뷰 Merging	실천하자	2011.05.15	23516
32	3. 데이터 모델 측면에서의 검토	멋진넘	2011.06.13	5922
31	3. 인덱스 파티셔닝	darkbeom	2011.06.19	54156
30	4. 테이블 Random 액세스 부하 [1]	darkbeom	2011.02.23	14755
29	4. 조인 순서의 중요성	운영자	2011.03.27	14374
28	4. 통계정보 Ⅰ	darkbeom	2011.04.25	18214
27	4. 조건절 Pushing	실천하자	2011.05.31	7114
26	4. 소트가 발생하지 않도록 SQL 작성	멋진넘	2011.06.12	7940
»	5. 테이블 Random 액세스 최소화 튜닝	suspace	2011.02.24	7028
24	6. 스칼라 서브쿼리를 이용한 조인 [1]	휘휘	2011.03.28	6686
23	5. Outer 조인	실천하자	2011.03.29	5084
22	5. 카디널리티	suspace	2011.04.26	5989
21	5. 조건절 이행	휘휘	2011.05.29	5509

첫 페이지 1 2 3 끝 페이지

쓰기

태그

bysql.net

로그인

5. 테이블 Random 액세스 최소화 튜닝

댓글 0