database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41948 게시물 읽기
No. 41948
ORACLE DB SQL 쿼리 튜닝-8번째[DB 튜닝]
작성자
조장복(cjb1609)
작성일
2023-03-21 11:05
조회수
5,934

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]

 

[Top]
No.
제목
작성자
작성일
조회
41963조건에 따른 order by 설정 [3]
차상환
2023-06-16
2865
41962GLOBAL TEMPORARY TABLE COMMIT 옵션 변경가능 한지요 ? [1]
이규동
2023-06-15
2787
41961NLS_DATE_FORMAT변경관련 질문입니다.
우태열
2023-05-13
4922
41948ORACLE DB SQL 쿼리 튜닝-8번째[DB 튜닝]
조장복
2023-03-21
5934
41947ORACLE DB SQL 쿼리 튜닝-7번째[DB 튜닝]
조장복
2023-03-21
5170
41946ORACLE DB SQL 쿼리 튜닝-6번째[DB 튜닝]
조장복
2023-03-21
3285
41945ORACLE DB SQL 쿼리 튜닝-5번째[DB 튜닝]
조장복
2023-03-21
3166
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.018초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다