메뉴 건너뛰기

bysql.net

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

2011.02.26 22:31

휘휘 조회 수:9498

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

 

(1) 인덱스 컬럼 추가

 

     -쿼리에서 참조되는 컬럼을 index에 추가함으로써 table random 액세스를 최소화 한다

     -운영환경에서 인덱스 구성을 바꾸기 쉽지 않으므로 기존 index에 컬럼 추가?

     -새로운 index를 추가할 경우 index 관리비용이 증가할 수 있으므로 기존 index 사용

 

<TEST>

FUll table scan 시

 select /*+ FULL(A) */ * from emp A where deptno=30 and sal >=2000;

----------------------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=93 us)

 

//한건의 data를 찾기 위해 8개의 블록에 액세스함//

INDEX scan (deptno ,job) - deptno 는 index scan ,sal table acess

             select /*+ INDEX(A emp_x01) */ * from emp A where deptno=30 and sal >=2000;

              ------------------------------------------------------------------------------------------------------------------

              Rows     Row Source Operation
              -------  ---------------------------------------------------
                   0  STATEMENT
                   1   TABLE ACCESS BY INDEX ROWID EMP (cr=4 pr=0 pw=0 time=90 us)
                   6    INDEX RANGE SCAN EMP_X01 (cr=2 pr=0 pw=0 time=69 us)OF EMP_X01 (NONUNIQUE)

 

INDEX scan(deptno,job,sal) - 기존 index 에 sal 추가

select /*+ INDEX(A emp_x02) */ * from emp A where deptno=30 and sal >=2000;

------------------------------------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   TABLE ACCESS BY INDEX ROWID EMP (cr=3 pr=0 pw=0 time=82 us)
      1    INDEX RANGE SCAN EMP_X02 (cr=2 pr=0 pw=0 time=67 us)OF EMP_X02 (NONUNIQUE)

 

 

(2)PK 인덱스 컬럼 추가

?

    -PK index에 컬럼을 추가할 수 없지만 [PK컬럼 + 필터조건 컬럼]형태의 NON-Unique 인덱스를 이용하여 PK 제약

    을 설정한다면 불필요한 random 액세스를 없앨 수 있음.

 

 PK index 사용(deptno) - 불필요한 11건의 random 액세스후 d.loc에 의해 fitering 

select /*+ ordered use_nl(d) */ * from emp e, dept d where d.deptno=e.deptno and d.loc='NEW YORK

------------------------------------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      3   NESTED LOOPS  (cr=25 pr=0 pw=0 time=474 us)
     14    TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=195 us)
      3     TABLE ACCESS BY INDEX ROWID DEPT (cr=17 pr=0 pw=0 time=560 us)
     14       INDEX UNIQUE SCAN PK_DEPT1 (cr=3 pr=0 pw=0 time=258 us)OF PK_DEPT1 (UNIQUE)

NON-unique 인덱스에 PK 설정(deptno,loc 컬럼 추가)

             select /*+ ordered use_nl(d) */ * from emp e, dept d where d.deptno=e.deptno and d.loc='NEW YORK'

-------------------------------------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      3   TABLE ACCESS BY INDEX ROWID DEPT (cr=14 pr=0 pw=0 time=344 us)
     18    NESTED LOOPS  (cr=12 pr=0 pw=0 time=2230 us)
     14     TABLE ACCESS FULL EMP (cr=8 pr=0 pw=0 time=183 us)
      3      INDEX RANGE SCAN DEPT_X01 (cr=4 pr=0 pw=0 time=194 us)OF DEPT_X01 (NONUNIQUE)

        //emp 조인과 d.loc fitering 이 모두 index에서 처리된 것을 확인

 

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

-인덱스에 컬럼을 추가함으로써 테이블 random 액세스 부하를 줄일 수 있지만 인덱스 클러스터링 팩터가

  나빠지는 부작용을 초래할 수 있음

  • GOOD 클러스터링 팩터

-INDEX_NAME  CL_FACTOR   TAB_BLKS   TAB_ROWS
 ----------                  ----------                ----------       ----------
  T_IDX                        570                       587           40765

 

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

----------------------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=686 pr=0 pw=0 time=37202 us)
  40765    TABLE ACCESS BY INDEX ROWID T (cr=686 pr=0 pw=0 time=570757 us)
  40765     INDEX RANGE SCAN T_IDX (cr=116 pr=0 pw=0 time=123485 us)OF T_IDX (NONUNIQUE)

// table 을 40765방문하지만 블록 I/O 는 (686-116)는 670 만 발생되었음

 

BAD 클러스터링 팩터(기존 index에 컬럼 추가로 팩터가 나빠짐)

INDEX_NAME  CL_FACTOR   TAB_BLKS   TAB_ROWS
----------                   ----------            ----------          ----------
T_IDX01                  30048                   587            40765

 

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

-----------------------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=30309 pr=0 pw=0 time=125674 us)
  40765    TABLE ACCESS BY INDEX ROWID T (cr=30309 pr=0 pw=0 time=774619 us)
  40765     INDEX RANGE SCAN T_IDX01 (cr=261 pr=0 pw=0 time=164538 us)OF T_IDX01 (NONUNIQUE)

 

//good 클러스터링 팩터와 비교할 때 table 과 index 에서 발생하는 블록 I/O가 많이 증가한 것을 확인

 인덱스에 컬럼 추가시에는 클러스터링 팩터 변화에 주의

 

        ##########################################################################

//index 에 object_type,object_name,owner를 모두 추가한 경우

 -운영환경에서는 index에 컬럼이 추가된만큼 dml 작업이나 index 관리비용에 부하 발생 염두

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
      1   SORT AGGREGATE (cr=294 pr=0 pw=0 time=32404 us)
  40765    INDEX RANGE SCAN T_IDX02 (cr=294 pr=0 pw=0 time=203872 us)OF T_IDX02 (NONUNIQUE)

 

//변별력이 좋지 않은 컬럼 뒤에 변별력이 좋은 다른 컬럼을 추가할 때는 클러스터링 팩터 변화에 주의//

-변별력이 좋은 object_name 의 위치에 따른 클러스터링팩터 변화-

create index t_idx01 on t(object_type,object_name);
create index t_idx02 on t(object_type,object_name,owner);
create index t_idx03 on t(object_name,object_type);
create index t_idx04 on t(object_type,owner);

 

NDEX_NAME                     TABLE_BLOCK NUM_ROWS    CLUSTERING_
------------------------------ ----------- ----------- -----------
T_IDX01                                587       40765       30048
T_IDX02                                587       40765       30048
T_IDX03                                587       40765       38112
T_IDX04                                587       40765         657

##########################################################################

 

(4)인덱스만 읽고 처리??

-필터조건에 버려지는 레코드가 없을 때 테이블 액세스가 발생하지 않도록 모든 필요한 컬럼을 인덱스에

 포함시키는 방법

  • index 스캔시 table random acess 발생

//index(job,deptno)           

select * from
(select /*+ INDEX(A bigemp_ind01) */ empno,job,deptno from bigemp A where job='SALESMAN' and deptno=30)
where rownum <=500; ?

---------------------------------------------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
    500   COUNT STOPKEY (cr=116 pr=0 pw=0 time=6149 us)
    500    TABLE ACCESS BY INDEX ROWID BIGEMP (cr=116 pr=0 pw=0 time=5143 us)
    500     INDEX RANGE SCAN BIGEMP_IND01 (cr=55 pr=0 pw=0 time=550 us)OF BIGEMP_IND01        

 

//(116-55) 61번의 random I/O를 발생시킴

 

 

  • index 스캔시 table random acess 발생 X

          //index(job,deptno,empno)   =>인덱스에 empno 추가

          select * from
          (select /*+ INDEX(A bigemp_ind02) */ empno,job,deptno from bigemp A where job='SALESMAN' and deptno=30)
          where rownum <=500;?

-------------------------------------------------------------------------------------------------------------------------------------------

Rows     Row Source Operation
-------  ---------------------------------------------------
      0  STATEMENT
    500   COUNT STOPKEY (cr=54 pr=6 pw=0 time=2189 us)
    500    INDEX RANGE SCAN BIGEMP_IND02 (cr=54 pr=6 pw=0 time=1177 us)OF BIGEMP_IND02

 

?//table random acess I/O가 발생하지 않음

 

 

(5)버퍼 Pinning 효과 활용                                              

 ##optimizing oracle optimizer에서 참고했습니다##

  • 오라클은 한번의 fetch에서 두 번 연속 방문하는? Block에 대해 pinning(cache에서 밀려나지 않도록
    고정시킨다는 의미)을 수행한다 pinned block에 대해서는 cache buffer chains latch를 획득하는 일련

    의 과정을 거치지 않고 memory address를 이용해 직접 읽는 것이 가능한데 이것을 버퍼 pinning

    이라 부름

  •   TEST

          -T table 생성 후 클러스터링 팩터가 좋은 인덱스(good_cf index) 와 나쁜 인덱스(bad_cf index) 생성

INDEX_NAME                     TABLE_BLOCK NUM_ROWS    CLUSTERING_
------------------------------ ----------- ----------- -----------
GOOD_CF                                 20       10000          18
BAD_CF                                    20       10000        9464

 

<good_cf>

----------------------------------------------------------------------------------------------------------

select /*+ index(A good_cf) */ * from scott.t1 A where c1 between 1 and 100

?

NAME                                          DIFF         VALUE1         VALUE2
----------------------------------- -------------- -------------- --------------
buffer is pinned count                         184          2,056          2,240
session logical reads                              54          3,205          3,259

-----------------------------------------------------------------------------------------------------------

 

<bad cf>

----------------------------------------------------------------------------------------------------------

select /*+ index(A bad_cf) */ * from scott.t1 A where c2 between 1 and 100;

?

NAME                                          DIFF         VALUE1         VALUE2
----------------------------------- -------------- -------------- --------------
session logical reads                           150          5,072          5,222
buffer is pinned count                          99          3,830          3,929

----------------------------------------------------------------------------------------------------------

 

//buffer is pinned count 통계 값이 차이 95(184-99)  와 logical read의 차이 96(150-54)가 거의 비슷.

클러스터 팩터가 불량한 경우 buffer pinning의 발생률이 떨어지고 이로 인해 logical read가 증가.

클러스터 팩터가 불량하면 data block을 재방문하는 경우가 많아지고 그로 인해 data block이 cache

에서 밀려나게 된다

클러스터 팩터가 좋은 경우에는 fetch call내에서 동일한 블록을 재방분하기 때문에 그 만큼 buffer

pinning이 많이 발생하게 된다

 

 

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

  • 테이블에는 데이터가 무작위로 입력되는 반면 인덱스는 키순으로 정렬되기 때문에 클러스터링 팩터가 좋지 않음

                    

    <해결방안>

    인덱스 기준으로 테이블을 재생성함으로써 cf를 인위적으로 좋게 만듬

  • TEST

INDEX_NAME                     TABLE_BLOCK NUM_ROWS    CLUSTERING_
------------------------------ ----------- ----------- -----------
BAD_CF                                  20       10000        9464

 

-테이블을 인덱스를 기준으로 재생성-

create table t2 as select * from t1 order by c2;

  INDEX_NAME                     TABLE_BLOCK NUM_ROWS    CLUSTERING_
------------------------------ ----------- ----------- -----------
BAD_CF1                                          10000          18

 

<주의사항>

-여러 개의 인덱스가 있는 경우 기준으로 테이블을 재정렬하면 다른 인덱스의 CF가 나빠질 수 있으니 주의

-다른 인덱스의 키 컬럼 간의 상관관계나 주요 자주 사용하는 인덱스를 기준으로 reorg

 

 


#######################  추가  #########################

<oracle 10g 와11g buffer pin test>

-교재에 나와 있는 test를 직접 oracle 10g와 11g에서 실행

 

--ORACLE10G--

select /*+ ordered use_nl(b) rowid(b) */ b.*


from (select /*+ index(emp1 emp_pk) no_merge */ rowid rid


from emp1


order by rowid)a,emp1 b


where b.rowid=a.rid

______________________________________________________________

Rows     Row Source Operation

-------  ---------------------------------------------------


      0  STATEMENT


  14000   NESTED LOOPS  (cr=14036 pr=0 pw=0 time=340418 us)


  14000    VIEW  (cr=36 pr=0 pw=0 time=102372 us)


  14000     SORT ORDER BY (cr=36 pr=0 pw=0 time=74366 us)


  14000      INDEX FULL SCAN EMP_PK (cr=36 pr=0 pw=0 time=14097 us)(Object ID 10219)


  14000    TABLE ACCESS BY USER ROWID EMP1 (cr=14000 pr=0 pw=0 time=199993 us)

 

 

--ORACLE11G--

select /*+ ordered use_nl(b) rowid(b) */ b.*


from (select /*+ index(emp1 emp_pk) no_merge */ rowid rid


from emp1


order by rowid)a,emp1 b


where b.rowid=a.rid

--------------------------------------------------------------------------------------

Rows     Row Source Operation


-------  ---------------------------------------------------


  14000  NESTED LOOPS  (cr=1517 pr=0 pw=0 time=198169 us cost=15690 size=1927952 card=15548)


  14000   VIEW  (cr=36 pr=0 pw=0 time=65413 us cost=118 size=186576 card=15548)


  14000    SORT ORDER BY (cr=36 pr=0 pw=0 time=38815 us cost=118 size=186576 card=15548)


  14000     INDEX FULL SCAN EMP_PK (cr=36 pr=0 pw=0 time=18707 us cost=43 size=186576 card=15548)(object id 14316)


  14000   TABLE ACCESS BY USER ROWID EMP1 (cr=1481 pr=0 pw=0 time=0 us cost=1 size=112 card=1)

 

--교재보다 10배 정도 차이는 있지만 oracle10와 비교했을때는 1/10으로 블록 I/O가 줄어듬





  • 오라클 고도화 원리와 해법 2 (bysql.net 2011년 1차 스터디)
  • 작성자: 임재규 (오라클잭)
  • 최초작성일: 2011년 2월 27일
  • 본문서는 bysql.net 스터디 결과입니다 .본 문서를 인용하실때는 출처를 밝혀주세요. http://www.bysql.net
  • 문서의 잘못된 점이나 질문사항은 본문서에 댓글로 남겨주세요. ^^