첨부파일의 일부 인덱스명에 오타가 있으니 댓글을 참조하기 바람(2009.11.05)

SQL
튜닝을 할 때 가장 기본적으로 익혀야 하는 것을 꼽으라면 무엇일까?

첫 번째, Data Access Pattern

두 번째, Join Method

위의 두 가지는 기본 중에 기본이다. 이 두 가지는 너무도 중요하기 때문에 튜닝을 시작하는 사람 혹은 튜너를 지망하는 사람들은 보고 또 보아야 한다. 실제로 이두가지를 모두 정복한다면 SQL 튜닝중의 많은 부분을 커버할수 있다. 이것은 비단 오라클 SQL 튜닝만이 아니라 모든 DBMS가 동일 할 것이다.

오늘은 두가지 중에 첫 번째의 Data Access Pattern의 개념에 대해서 이야기 해보자.

 

Data Access Pattern을 최대한 간단하게 표현하면 아래와 같다.

 

Index Scan       : 인덱스 사용

Full Table Scan  : 인덱스 미사용

Rowid            : Rowid로 테이블 엑세스

 

하지만 위의 3가지는 너무나 추상적이다.
위의 3가지를 좀더 상세히 나타내 보자.


Rowid

Index Unique Scan    

Index Range Scan

Index Inlist Iterator

Index Skip Scan 

Index Full Scan

Index Fast Full Scan 

Bit Map Index Combination

Full Table Scan        

 

3가지가 9가지가 되었다. 이제 제법 실전의 Plan에서 볼 수 있는 모습이 나왔다고 생각할 것이다.

하지만 아직 많이 부족하다.
실전의 Plan에는 위의 모습 보다 더욱 상세한 것이 필요하기 때문이다.

 

Rowid

Index Unique Scan    

Index Range Scan

Index Range Scan Descending

Index Range Scan (min/max)    

Index Inlist Iterator

Index Inlist Iterator Descending

Index Skip Scan 

Index Skip Scan Descending

Index Full Scan

Index Full Scan Descending 

Index Full Scan (min/max)   

Index Fast Full Scan 

BIT MAP OR           

BIT MAP AND          

BIT MAP MINUS        

BIT MAP MERGE

Full Table Scan        

 

드디어 3가지가 18가지가 되었고 실전적인 모습이 되었다.

위의 18가지 중에 한가지라도 찜찜한것이 있으면 첨부파일을 다운받아서 개념을 익히길 바란다.
파일을 배포 할 경우  출처를 밝혀주기 바란다.
파일 3개를 모두 다운 받은후 압축을 풀면 된다.

사용자 삽입 이미지

사용자 삽입 이미지



PS :
여기에 나오지 않은것들이 몇가지 있다. Partition Access Pattern은 이문서에서 언급되지 않았다.
다음 기회에..
Index Join 은 Data Access Pattern에 속하는 것이 아니고 Join method 에 포함되어야 한다.

Posted by extremedb

댓글을 달아 주세요

  1. Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.10.29 09:51  댓글주소  수정/삭제  댓글쓰기

    항상 좋은 컨텐츠를 제공해 주셔서 감사드립니다.
    동규님의 열정은 따라갈수가 없군요... ^^

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.10.29 12:16 신고  댓글주소  수정/삭제

      열정이라고 하시니 부끄럽군요.
      스터디는 요즘도 하시나요?

    • Favicon of http://blog.naver.com/xsoft BlogIcon 강정식 2009.10.29 12:44  댓글주소  수정/삭제

      네.. ^^
      요즘은 비투엔에서 나온 '오라클 성능 고도화 원리와 해법 I'을 보고 있습니다.

      internal한 내용에 대해 조시형님이 잘 설명해 놓으셔서 읽기 좋더라구요..

      아래는 저희 스터디 위키 url입니다.

      http://wiki.oracleclub.com/display/CORE/Home

      동규님 책도 기다리고 있으니 출판되시면 알려주세요.

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.10.29 14:07 신고  댓글주소  수정/삭제

      이미 많은 수의 책을 스터디 하셨군요.
      부럽습니다.

  2. feelie 2009.10.29 12:53  댓글주소  수정/삭제  댓글쓰기

    바쁘신데 정리된 자료까지 정말 감사합니다.
    가끔 공부에 대한 열정이 식으려고할때 올려주시는 자료가 다시 열정을 일으키게해주십니다.

  3. Favicon of http://lemonfish.egloos.com BlogIcon killy 2009.10.29 23:40  댓글주소  수정/삭제  댓글쓰기

    아흑! 좋은 자료 너무 감사합니다.
    3가지 -> 9가지 -> 18가지 3단콤보~ 극적전개에 떡실신입니다.
    역시 기본이 중요하군요~

    더불어 위에 댓글에 강정식님께도 감사드려요~ 스터디 위키에 금쪽같은 자료들이네요~ 잘보겠습니다.

  4. 타락천사 2009.11.04 14:30  댓글주소  수정/삭제  댓글쓰기

    좋은 자료 항상 감사드립니다.
    자~알 보겠습니다.
    제 블로그에 출처 밝히고 올리겠습니다.^^

  5. 2009.11.05 13:17  댓글주소  수정/삭제  댓글쓰기

    비밀댓글입니다

  6. 타락천사 2009.11.05 13:17  댓글주소  수정/삭제  댓글쓰기

    비밀글로 달았더니, 저도 안보여서 ^^;
    다시 올립니다.

    1. 오타
    특징
    - 첫번째 인덱스와 두번째 인덱스를 이용하여 두집합간에
    AND, OR, MINUS, MERGE 연산을 하여 데이터를 엑세스한다.
    - 하지만 하나의 조건만 Filter 되어 나오는 결과건수와 두개의 조건으로 Filter되어
    나오는 결과건수가 비슷하다면 비효율이 발생함으로 무조건 적용해서는 안됨

    index_combine( emp IX_EMP_N2 IX_EMP_N3) <== 2번째 인덱스명이 모두 잘못표기됨

    2. 아래 부분은 그대로 테스트 해봤는데. 실행계획이 BIT Map Minus 로 풀리지 않더라구요
    select /*+ index_combine(emp IX_EMP_N2 IX_EMP_N3 ) */
    empno, mgr, deptno
    from emp
    where not(mgr=7698)
    and deptno = 20

  7. Favicon of https://scidb.tistory.com BlogIcon extremedb 2009.11.05 13:54 신고  댓글주소  수정/삭제  댓글쓰기

    인덱스명이 잘못된것이 있군요.
    minus 가 풀리지 않는 문제는 원인 이 여러가지 입니다.
    일단 아래의 스크립트를 그대로 따라해보시기 바랍니다.
    그래도 안된다면 dbms_xplan.display_cursor(null,null,'advanced allstats last') 의 결과물을 저에게 주시기 바랍니다,

    --CONNECT SCOTT/TIGER
    DROP TABLE EMP;
    DROP TABLE DEPT;
    CREATE TABLE DEPT
    (DEPTNO NUMBER(2) CONSTRAINT PK_DEPT PRIMARY KEY,
    DNAME VARCHAR2(14) ,
    LOC VARCHAR2(13) ) ;
    CREATE TABLE EMP
    (EMPNO NUMBER(4) CONSTRAINT PK_EMP PRIMARY KEY,
    ENAME VARCHAR2(10),
    JOB VARCHAR2(9),
    MGR NUMBER(4),
    HIREDATE DATE,
    SAL NUMBER(7,2),
    COMM NUMBER(7,2),
    DEPTNO NUMBER(2) CONSTRAINT FK_DEPTNO REFERENCES DEPT);
    INSERT INTO DEPT VALUES
    (10,'ACCOUNTING','NEW YORK');
    INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
    INSERT INTO DEPT VALUES
    (30,'SALES','CHICAGO');
    INSERT INTO DEPT VALUES
    (40,'OPERATIONS','BOSTON');
    INSERT INTO EMP VALUES
    (7369,'SMITH','CLERK',7902,to_date('17-12-1980','dd-mm-yyyy'),800,NULL,20);
    INSERT INTO EMP VALUES
    (7499,'ALLEN','SALESMAN',7698,to_date('20-2-1981','dd-mm-yyyy'),1600,300,30);
    INSERT INTO EMP VALUES
    (7521,'WARD','SALESMAN',7698,to_date('22-2-1981','dd-mm-yyyy'),1250,500,30);
    INSERT INTO EMP VALUES
    (7566,'JONES','MANAGER',7839,to_date('2-4-1981','dd-mm-yyyy'),2975,NULL,20);
    INSERT INTO EMP VALUES
    (7654,'MARTIN','SALESMAN',7698,to_date('28-9-1981','dd-mm-yyyy'),1250,1400,30);
    INSERT INTO EMP VALUES
    (7698,'BLAKE','MANAGER',7839,to_date('1-5-1981','dd-mm-yyyy'),2850,NULL,30);
    INSERT INTO EMP VALUES
    (7782,'CLARK','MANAGER',7839,to_date('9-6-1981','dd-mm-yyyy'),2450,NULL,10);
    INSERT INTO EMP VALUES
    (7788,'SCOTT','ANALYST',7566,to_date('13-7-1987','dd-mm-yyyy')-85,3000,NULL,20);
    INSERT INTO EMP VALUES
    (7839,'KING','PRESIDENT',NULL,to_date('17-11-1981','dd-mm-yyyy'),5000,NULL,10);
    INSERT INTO EMP VALUES
    (7844,'TURNER','SALESMAN',7698,to_date('8-9-1981','dd-mm-yyyy'),1500,0,30);
    INSERT INTO EMP VALUES
    (7876,'ADAMS','CLERK',7788,to_date('13-7-1987','dd-mm-yyyy')-51,1100,NULL,20);
    INSERT INTO EMP VALUES
    (7900,'JAMES','CLERK',7698,to_date('3-12-1981','dd-mm-yyyy'),950,NULL,30);
    INSERT INTO EMP VALUES
    (7902,'FORD','ANALYST',7566,to_date('3-12-1981','dd-mm-yyyy'),3000,NULL,20);
    INSERT INTO EMP VALUES
    (7934,'MILLER','CLERK',7782,to_date('23-1-1982','dd-mm-yyyy'),1300,NULL,10);
    DROP TABLE BONUS;
    CREATE TABLE BONUS
    (
    ENAME VARCHAR2(10) ,
    JOB VARCHAR2(9) ,
    SAL NUMBER,
    COMM NUMBER
    ) ;
    DROP TABLE SALGRADE;
    CREATE TABLE SALGRADE
    ( GRADE NUMBER,
    LOSAL NUMBER,
    HISAL NUMBER );
    INSERT INTO SALGRADE VALUES (1,700,1200);
    INSERT INTO SALGRADE VALUES (2,1201,1400);
    INSERT INTO SALGRADE VALUES (3,1401,2000);
    INSERT INTO SALGRADE VALUES (4,2001,3000);
    INSERT INTO SALGRADE VALUES (5,3001,9999);
    COMMIT;

    create bitmap index IX_EMP_N2 on emp (mgr);
    create bitmap index IX_EMP_N3 on emp (deptno);

    select /*+ GATHER_PLAN_STATISTICS index_combine(emp IX_EMP_N2 IX_EMP_IDX3) */
    empno, mgr, deptno
    from emp
    where NOT( mgr = 7698 )
    and deptno = 20 ;

    SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'ALLSTATS LAST')) ;

  8. 타락천사 2009.11.06 15:36  댓글주소  수정/삭제  댓글쓰기

    잘되네요..
    흠 저랑 샘플 테이블 차이는 Relation 이 있는것 뿐인데..
    제 실수였나보군요..
    감사드립니다.
    뒤에 인덱스명이 틀려도 잘 풀리네요 ^^;

  9. 서은아빠 2010.01.08 16:25  댓글주소  수정/삭제  댓글쓰기

    해당문서에 index range scan 의 경우 기존 index 힌트로 사용가능하다는 부분이 있습니다.
    10.2.0.3부터 range scan 의 경우 INDEX_RS_ASC, INDEX_RS_DESC 의 힌트가 추가되었습니다.

    참고하시기 바랍니다

    • Favicon of https://scidb.tistory.com BlogIcon extremedb 2010.01.08 16:52 신고  댓글주소  수정/삭제

      말씀하신것 처럼 힌트가 추가되었습니다.
      하지만 공식적인 힌트가 아니므로 조심해서 사용하시기 바랍니다. 11gR2 메뉴얼에도 나오지 않습니다.메타링크에 버그도 보고되고 있습니다.

  10. 초보자 2011.05.26 15:35  댓글주소  수정/삭제  댓글쓰기

    첨부파일이 손상되었다고 하면서 pdf파일이 열리지가 않습니다..

    괜찮으시면 taek58@gmail.com으로 파일 좀 첨부해서 보내주시면 안될까요?

    이제 막 오라클 공부하는 초보로서 여러가지 배울게 너무 많네요..^^