'Query Tuning'에 해당되는 글 1건

  1. 2007.12.17 [Oracle] Qry Tunning

[Oracle] Qry Tunning

2007.12.17 17:36
출처 카페 > http://sqlplus... / 네오
원본 http://cafe.naver.com/neocip/178

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 */ .... ;


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. 테이블을 조정한다.



=> union 을 쓰면 프로그램 속도가 아주 저하될 수도 있으므로 피한다.(인덱스를 탄다면 문제없음.)

=> IN 대신 Exists를 사용하면 속도를 개선한다.

신고
Posted by 나비 나비:D

BLOG main image
by 나비:D

공지사항

카테고리

분류 전체보기 (278)
Programming? (0)
----------------------------- (0)
나비의삽질 (5)
Application (177)
SQL (51)
Web (27)
etc. (14)
Omnia (0)
---------------------------.. (0)

글 보관함

달력

«   2017/10   »
1 2 3 4 5 6 7
8 9 10 11 12 13 14
15 16 17 18 19 20 21
22 23 24 25 26 27 28
29 30 31        
Total : 882,506
Today : 2 Yesterday : 218