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를 사용하면 속도를 개선한다.
