temp 테이블의 사람 중 emp_level 테이블에서의 부장직급을 받아야 할 나이를 가지고 있는 사람의
사번,성명,생일,현재나이,현재 직급을 출력하세요.
사용할 테이블 구조 및 인덱스 확인
쿼리 수정
결과요약 :
1. COST = 3+2+2+1+1 = 9로 이전 쿼리(12)보다 COST가 25% 개선됨.
2. consistent gets가 11 --> 9로 2 줄어들었음.
3. bytes가 기존 총합 102bytes에서 101bytes로 줄어들었음
4. 쿼리 결과가 사번(temp테이블의 emp_id기준으로) 정렬되어서 나왔음
(주의 : SQL Trace 로 정교하게 본 것이 아니므로, 절대적인 성능개선은 확인해봐야 함)
비교
원래 쿼리, 변경 후 쿼리
사번,성명,생일,현재나이,현재 직급을 출력하세요.
사용할 테이블 구조 및 인덱스 확인
SQL> desc temp
Name Null? Type
----------------------------------------------------------- -------- -----------------------
EMP_ID NOT NULL NUMBER
EMP_NAME NOT NULL VARCHAR2(10)
BIRTH_DATE DATE
DEPT_CODE NOT NULL VARCHAR2(6)
EMP_TYPE VARCHAR2(4)
USE_YN NOT NULL VARCHAR2(1)
TEL VARCHAR2(15)
HOBBY VARCHAR2(30)
SALARY NUMBER
LEV VARCHAR2(4)
SQL> desc emp_level
Name Null? Type
----------------------------------------------------------- -------- -----------------------
LEV NOT NULL VARCHAR2(10)
FROM_SAL NUMBER
TO_SAL NUMBER
FROM_AGE NUMBER
TO_AGE NUMBER
SQL> select TABLE_NAME, INDEX_NAME, COLUMN_NAME from user_ind_columns
2 where table_name in('TEMP','EMP_LEVEL');
TABLE_NAME INDEX_NAME COLUMN_NAM
------------------------------ ------------------------------ ----------
EMP_LEVEL SYS_C0010945 LEV
TEMP SYS_C0010971 EMP_ID
SQL> select count(EMP_ID) from temp;
COUNT(EMP_ID)
-------------
20
SQL> select count(lev) from emp_level;
COUNT(LEV)
----------
5
원래 쿼리
SELECT emp.emp_id 사번 ,
emp.emp_name 이름 ,
emp.birth_date 생일 ,
TRUNC( (sysdate-emp.birth_date)/365 ) 나이,
emp.lev 현재직급
FROM temp emp,
emp_level lvlinfo
WHERE lvlinfo.lev ='부장'
AND TRUNC( (sysdate-emp.birth_date)/365 ) >= lvlinfo.from_age;
결과 및 실행계획, COST확인
눈여겨 볼 점 : TEMP table(Id 4)에 TABLE ACCESS FULL발생
COST : 4+4+1+0+3 = 12
결과 및 실행계획, COST확인
SQL> SELECT emp.emp_id 사번 ,
2 emp.emp_name 이름 ,
3 emp.birth_date 생일 ,
4 TRUNC( (sysdate-emp.birth_date)/365 ) 나이,
5 emp.lev 현재직급
6 FROM temp emp,
7 emp_level lvlinfo
8 WHERE lvlinfo.lev ='부장'
9 AND TRUNC( (sysdate-emp.birth_date)/365 ) >= lvlinfo.from_age;
사번 이름 생일 나이 현재
---------- ---------- ------------------- ---------- ----
19970101 김길동 1974-01-25:00:00:00 38 부장
19960101 홍길동 1973-03-22:00:00:00 39 과장
19970201 박문수 1975-04-15:00:00:00 36 과장
19930331 정도령 1976-05-25:00:00:00 35 차장
.... 생략 ....
20000407 윤주왕 1980-08-15:00:00:00 31 수습
20000308 강월악 1980-09-25:00:00:00 31 수습
20000119 장금강 1980-11-05:00:00:00 31 수습
20000210 나한라 1980-12-15:00:00:00 31 수습
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 43388086
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 34 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP_LEVEL | 1 | 8 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0010945 | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEMP | 1 | 26 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
3 - access("LVLINFO"."LEV"='부장')
4 - filter("LVLINFO"."FROM_AGE"<=TRUNC((SYSDATE@!-"EMP"."BIRTH_DATE")/365))
Statistics
----------------------------------------------------------
0 recursive calls
0 db block gets
11 consistent gets
0 physical reads
0 redo size
1614 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
눈여겨 볼 점 : TEMP table(Id 4)에 TABLE ACCESS FULL발생
COST : 4+4+1+0+3 = 12
쿼리 수정
Think :
1. where절에 지나친 함수사용
--> 서브쿼리를 이용해 COST를 줄이자.
(이왕이면, 자료가 적은 테이블에 함수를 사용해서 COST를 최대한 줄여보자)
2. temp에 hint를 줘서 index를 사용하게 하자.
SQL> SELECT /*+ INDEX(emp SYS_C0010971) */
2 emp.emp_id 사번 ,
3 emp.emp_name 이름 ,
4 emp.birth_date 생일 ,
5 TRUNC( (sysdate-emp.birth_date)/365 ) 나이,
6 emp.lev 현재직급
7 FROM temp emp,
8 emp_level lvlinfo
9 WHERE lvlinfo.lev ='부장'
10 AND emp.birth_date <= trunc(sysdate - (select from_age*365 from emp_level where lev='부장'));
사번 이름 생일 나이 현재
---------- ---------- ------------------- ---------- ----
19930331 정도령 1976-05-25:00:00:00 35 차장
19930402 강감찬 1972-08-15:00:00:00 39 차장
19950303 이순신 1973-06-15:00:00:00 38 대리
19960101 홍길동 1973-03-22:00:00:00 39 과장
19960212 배뱅이 1972-12-15:00:00:00 39 과장
19960303 설까치 1971-09-25:00:00:00 40 사원
19966102 지문덕 1972-07-05:00:00:00 39 과장
19970101 김길동 1974-01-25:00:00:00 38 부장
19970112 연흥부 1976-11-05:00:00:00 35 대리
19970201 박문수 1975-04-15:00:00:00 36 과장
20000101 이태백 1980-01-25:00:00:00 32 수습
20000102 김설악 1980-03-22:00:00:00 32 수습
20000119 장금강 1980-11-05:00:00:00 31 수습
20000203 최오대 1980-04-15:00:00:00 31 수습
20000210 나한라 1980-12-15:00:00:00 31 수습
20000305 정북악 1980-06-15:00:00:00 31 수습
20000308 강월악 1980-09-25:00:00:00 31 수습
20000334 박지리 1980-05-25:00:00:00 31 수습
20000407 윤주왕 1980-08-15:00:00:00 31 수습
20006106 유도봉 1980-07-05:00:00:00 31 수습
20 rows selected.
Execution Plan
----------------------------------------------------------
Plan hash value: 3430645905
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0010945 | 1 | 5 | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID | TEMP | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | SYS_C0010971 | 20 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP_LEVEL | 1 | 8 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0010945 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("LVLINFO"."LEV"='부장')
3 - filter("EMP"."BIRTH_DATE"<=TRUNC(SYSDATE@!- (SELECT "FROM_AGE"*365 FROM
"EMP_LEVEL" "EMP_LEVEL" WHERE "LEV"='부장')))
6 - access("LEV"='부장')
Statistics
----------------------------------------------------------
1 recursive calls
0 db block gets
9 consistent gets
0 physical reads
0 redo size
1614 bytes sent via SQL*Net to client
531 bytes received via SQL*Net from client
3 SQL*Net roundtrips to/from client
0 sorts (memory)
0 sorts (disk)
20 rows processed
결과요약 :
1. COST = 3+2+2+1+1 = 9로 이전 쿼리(12)보다 COST가 25% 개선됨.
2. consistent gets가 11 --> 9로 2 줄어들었음.
3. bytes가 기존 총합 102bytes에서 101bytes로 줄어들었음
4. 쿼리 결과가 사번(temp테이블의 emp_id기준으로) 정렬되어서 나왔음
(주의 : SQL Trace 로 정교하게 본 것이 아니므로, 절대적인 성능개선은 확인해봐야 함)
Key :
1. TABLE FULL ACCESS --> INDEX사용
2. where절에서 함수를 꼭 써야 한다면, 테이터가 적은 테이블에 사용하기
비교
원래 쿼리, 변경 후 쿼리
-------------기존쿼리-------------
SELECT emp.emp_id 사번 ,
emp.emp_name 이름 ,
emp.birth_date 생일 ,
TRUNC( (sysdate-emp.birth_date)/365 ) 나이,
emp.lev 현재직급
FROM temp emp,
emp_level lvlinfo
WHERE lvlinfo.lev ='부장'
AND TRUNC( (sysdate-emp.birth_date)/365 ) >= lvlinfo.from_age;
---------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
---------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 34 | 4 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 34 | 4 (0)| 00:00:01 |
| 2 | TABLE ACCESS BY INDEX ROWID| EMP_LEVEL | 1 | 8 | 1 (0)| 00:00:01 |
|* 3 | INDEX UNIQUE SCAN | SYS_C0010945 | 1 | | 0 (0)| 00:00:01 |
|* 4 | TABLE ACCESS FULL | TEMP | 1 | 26 | 3 (0)| 00:00:01 |
---------------------------------------------------------------------------------------------
-------------변경 후 쿼리-------------
SELECT /*+ INDEX(emp SYS_C0010971) */
emp.emp_id 사번 ,
emp.emp_name 이름 ,
emp.birth_date 생일 ,
TRUNC( (sysdate-emp.birth_date)/365 ) 나이,
emp.lev 현재직급
FROM temp emp,
emp_level lvlinfo
WHERE lvlinfo.lev ='부장'
AND emp.birth_date <= trunc(sysdate - (select from_age*365 from emp_level where lev='부장'));
----------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | 31 | 3 (0)| 00:00:01 |
| 1 | NESTED LOOPS | | 1 | 31 | 2 (0)| 00:00:01 |
|* 2 | INDEX UNIQUE SCAN | SYS_C0010945 | 1 | 5 | 0 (0)| 00:00:01 |
|* 3 | TABLE ACCESS BY INDEX ROWID | TEMP | 1 | 26 | 2 (0)| 00:00:01 |
| 4 | INDEX FULL SCAN | SYS_C0010971 | 20 | | 1 (0)| 00:00:01 |
| 5 | TABLE ACCESS BY INDEX ROWID| EMP_LEVEL | 1 | 8 | 1 (0)| 00:00:01 |
|* 6 | INDEX UNIQUE SCAN | SYS_C0010945 | 1 | | 0 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------
** HINT 추가 및 WHERE조건 변경 **
'Oracle > 튜닝' 카테고리의 다른 글
| Autotrace, SQL Trace파일생성, TKPROF이용하기 (0) | 2012.03.14 |
|---|