|
ORACLE DB SQL 쿼리 튜닝을 통한 처리속도 저하 요인을 파악하고 개선한 사항 참조하세요[DB 튜닝]
2. 튜닝 개선 사항
- TB_EK_CC_PRESCH_STAT, TB_EK_CC_PUPIL_STAT
테이블 각각 1회 full scan 처리
- 힌트(/*+ FULL(TB_EK_CC_PRESCH_STAT) PARALLEL(TB_EK_CC_PRESCH_STAT 4) */),
(/*+ FULL(TB_EK_CC_PUPIL_STAT) PARALLEL(TB_EK_CC_PUPIL_STAT 4) */)를 각각
지정하여 병렬처리
3. SQL 쿼리
3.1 튜닝전 일부 쿼리
<§>
select
city_edu_office_cd
, sum( nvl( approval_cnt, 0 ) ) as presch_cnt_1_1
, 0 as presch_cnt_1_2
From
tb_ek_cc_presch_stat
Where found_type in ('FD100', 'FD200')
UNION
from
tb_ek_cc_presch_stat
Where found_type = 'FD300‘
UNION
from
tb_ek_cc_presch_stat
Where found_type = 'FD400'
<§>
select a.city_edu_office_cd
, ( CASE WHEN age = 3 THEN a.cnt ELSE 0 END ) as pupil_cnt_1_3
, 0 as pupil_cnt_2_3
from
(
select city_edu_office_cd
, age, aid_apply_state, sum(nvl(cnt, 0 )) as cnt
from TB_EK_CC_PUPIL_STAT
where aid_apply_state = 'APA02‘
UNION
from TB_EK_CC_PUPIL_STAT
where aid_apply_state = 'APA01‘
UNION
from TB_EK_CC_PUPIL_STAT
where aid_apply_state = 'APA06‘
UNION
from TB_EK_CC_PUPIL_STAT
where aid_apply_state = 'APA06' and grant_state = 'ST001'
<§>
3.2 튜닝후 일부 쿼리
<§>
select city_edu_office_cd
, SUM(CASE WHEN found_type in ('FD100', 'FD200') THEN
approval_cnt_s
END) presch_cnt_1_1
, SUM(CASE WHEN found_type = 'FD300' THEN approval_cnt_s
END) presch_cnt_1_2
from
( select /*+ FULL(tb_ek_cc_presch_stat)
PARALLEL(tb_ek_cc_presch_stat 4) */
city_edu_office_cd, found_type
, nvl( approval_cnt, 0 ) AS approval_cnt_s
from
tb_ek_cc_presch_stat
Where (found_type in ('FD100', 'FD200') OR found_type = 'FD300‘
OR found_type = 'FD400'))
<§>
Select city_edu_office_cd
, SUM(CASE WHEN aid_apply_state = 'APA02' THEN
(CASE WHEN age = 3 THEN cnt ELSE 0 END)
END) pupil_cnt_1_3
, SUM(CASE WHEN aid_apply_state = 'APA01' THEN
(CASE WHEN age = 3 THEN cnt ELSE 0 END)
END) pupil_cnt_2_3
from
(
select /*+ FULL(TB_EK_CC_PUPIL_STAT)
PARALLEL(TB_EK_CC_PUPIL_STAT 4) */
city_edu_office_cd
, age, aid_apply_state, grant_state, cnt
from TB_EK_CC_PUPIL_STAT
where (aid_apply_state = 'APA02'
OR aid_apply_state = 'APA01'
OR aid_apply_state = 'APA06'
OR (aid_apply_state = 'APA06' and grant_state = 'ST001')))
<§>
4. 실행 PLAN
4.1 튜닝전
Rows Execution Plan
------ --------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
17 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TB_EK_CC_EDU_OFFICE' (TABLE)
17 INDEX MODE: ANALYZED (UNIQUE SCAN) OF 'SYS_C0013632'
(INDEX (UNIQUE))
<§>
286 TABLE ACCESS MODE: ANALYZED (FULL) OF
'TB_EK_CC_PRESCH_STAT' (TABLE)
385 TABLE ACCESS MODE: ANALYZED (FULL) OF
'TB_EK_CC_PRESCH_STAT' (TABLE)
46 TABLE ACCESS MODE: ANALYZED (FULL) OF
'TB_EK_CC_PRESCH_STAT' (TABLE)
18752 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'TB_EK_CC_PUPIL_STAT' (TABLE)
18752 INDEX MODE: ANALYZED (RANGE SCAN) OF
'SYS_C0020929' (INDEX (UNIQUE))
4154 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'TB_EK_CC_PUPIL_STAT' (TABLE)
4154 INDEX MODE: ANALYZED (RANGE SCAN) OF
'SYS_C0020929' (INDEX (UNIQUE))
27844 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'TB_EK_CC_PUPIL_STAT' (TABLE)
27844 INDEX MODE: ANALYZED (RANGE SCAN) OF
'SYS_C0020929' (INDEX (UNIQUE))
15511 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID)
OF 'TB_EK_CC_PUPIL_STAT' (TABLE)
15511 INDEX MODE: ANALYZED (RANGE SCAN) OF
'SYS_C0020929' (INDEX (UNIQUE))
4.2 튜닝 후
Rows Execution Plan
------ --------------------------------------------
0 SELECT STATEMENT MODE: ALL_ROWS
0 TABLE ACCESS MODE: ANALYZED (BY INDEX ROWID) OF
'TB_EK_CC_EDU_OFFICE' (TABLE)
0 INDEX MODE: ANALYZED (UNIQUE SCAN)
OF 'SYS_C0013632' (INDEX (UNIQUE))
<§>
0 PX RECEIVE [:Q1004]
0 PX SEND (HASH) OF ':TQ10002' [:Q1002]
0 PX RECEIVE [:Q1002]
0 PX SEND (HASH) OF ':TQ10001' [:Q1001]
0 PX BLOCK (ITERATOR) [:Q1001]
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'TB_EK_CC_PRESCH_STAT' (TABLE) [:Q1001]
0 PX RECEIVE [:Q1004]
0 PX SEND (HASH) OF ':TQ10003' [:Q1003]
0 PX BLOCK (ITERATOR) [:Q1003]
0 TABLE ACCESS MODE: ANALYZED (FULL) OF
'TB_EK_CC_PUPIL_STAT' (TABLE) [:Q1003]
|
