문제점을 발견한 SQL 문의 튜닝.....

1. 시스템을 파악해야 한다.

  - 데이터의 성격을 명확히 알아야만 한다.
  - 튜닝 방법을 선택하기 위한 체크리스트를 작성한다.
  - 사용하는 SQL 쿼리를 분석한다.
    1) SQL*PLUS 에서 "set autotrace on" 명령을 수행하고 TKPROF를 이용하여 분석한다.
    2) 문제가되는 SQL 문의 explain plan을 조회한다.
    3) "set timing on" 명령을 수행하여 실행 시간을 검사한다.

  ** 한 세션만 트레이스를 거는 방법
      1) select sid, serial# from v$session where username='SCOTT';
            해당 유저의 id를 알아낸다.
      2) execute dbms_system.set_sql_trace_in_session({SID},{Serial#},TRUE);
2. Analyze 를 한다.

  Optimizer의 파싱연산을 더욱 정확하게 수행토록 유도할 수 있다.

  -Analyze table [table 명] compute statistics;  테이블 전체 연산.
  -Analyze table [table 명] estimate statistics;  테이블이 큰 경우 1064row 까지만 연산.
  -Analyze table [table 명] delete statistics;    이전 연산결과 삭제.
  - 한 유저의 모든 테이블을 분석할경우
    exec dbms_utility.analyze_schema('SCOTT','COMPUTE');
3. Hint 를 사용한다.



- table Full Scan을 유도

select /*+ FULL(table_name) */ column1, 2 .. from table_name ... ;



- index 사용을 유도

select /*+ INDEX(table_name index_name1, 2 .. ) */ column1,2 .. from table_name ... ;



- table 을 순서대로 조인하도록 유도

select /*+ ORDERED */ column1,2 .. from table1, table2 ... ;



- Cost-based 연산시 또는 모든 row 를 캐쉬하도록 유도(인덱스를 사용 않하도록 유도)

select /*+ ALL_ROWS */ .... ;



- Cost-based 연산시 응답속도를 빠르게 하기위해 (인덱스 걸린 컬럼 쿼리시)

select /*+ FIRST_ROWS */ .... ;

[/pre]

4. 인덱스를 생성/삭제 한다.

  EX1) 인덱스가 항상 좋은가 ??
      EMP 테이블은 10만건의 데이터가 있고 DEPT_NO=10 것은 2만5천건
      존재한다고 할때 수행시간 비교.

    select ENAME from EMP where DEPT_NO=10;
     
  1) 인덱스가 없을때 : 약 55초 소요
  2) 인덱스가 있을때 : 약 70초 소요
  3) dept_no, ename 의 복합 인덱스가 있을때 : 약 0.1초 소요.
      (단, ename은 unique)

  ** 인덱스가 걸리는 컬럼은 중복값이 적은것이 좋다. 만일 전체의 25% 이상의 중복
      값을 가지는 값을 쿼리시 시간이 더 걸리게 된다.


  EX2) 인덱스와 힌트의 적절한 사용....
      EMP 테이블은 1만건의 데이터가 있고, DEPT_NO에 인덱스가 걸려있고,
      DEPT_NO>9990 것은 5천건 존재한다고 할때 수행시간 비교.

    select ENAME, DEPT_NO from EMP where DEPT_NO>9990;
    -> 약 53초 소요

    select /*+ FULL(EMP) */ ENAME, DEPT_NO from EMP where DEPT_NO>9990;
    -> 약 4초 소요


  EX3) join 테이블의 쿼리시 속도비교
        EMP 테이블은 10만건, DEPT 테이블은 10건의 데이터가 있을때.

    select ENAME, DEPT_NO from EMP, DEPT
    where EMP.DEPT_NO = DEPT.DEPT_NO and EMP_NO=5 and DEPT_NO=10 ;
    -> 약 4분 소요 된다면

    select /*+ ORDERED */ ENAME, DEPT_NO from EMP, DEPT
    where EMP.DEPT_NO = DEPT.DEPT_NO and EMP_NO=5 and DEPT_NO=10 ;
    -> 약 15초 소요
 
    select ENAME, DEPT_NO from EMP where EMP_NO=5 and EXISTS
            ( select 'X' from DEPT where EMP.DEPT_NO = DEPT.DEPT_NO
                          and DEPT_NO=10 ) ;
    -> 약 8초 소요

    ** Exists, Union 등의 연산자는 속도를 개선시켜줌.
5. 테이블을 조정한다.