Tip
2011.03.21 23:01

쿼리 튜닝 사례

조회 수 14449 추천 수 0 댓글 0

5년전에 했던 추억의 쿼리 튜닝...

 

select RNUM,
ID,
RGSID,
MNFCNM,
MDLNM,
CLSNM,
to_char(MDFDT, 'MM/DD') MDFDT,
TRNS,
CNTCSD,
DMNDPRC,
to_char(DMNDPRC, '99,999,999,999') DMNDPRC2,
HOTMARK,
to_char(NSPDT, 'YYYYMMDD') NSPDT,
CRPCTPTH1,
WRNTNB,
MYWRNTYN,
substr(YR, 1, 4) || '년 ' || substr(YR, 5, 2) || '월식' YR,
substr(YR, 3, 2) || '/' || substr(YR, 5, 2) YR2,
YR YR3,
CLR,
CTGR,
DSP,
MSRM,
LOADAGE,
ISVALID
from (select /*+ INDEX_DESC(CAR_CR CAR_CR_IND3) */
rownum RNUM,
CAR_CR.ID ID,
CAR_CR.RGSID,
CAR_CR.MNFCNM,
CAR_CR.MDLNM,
CAR_CR.CLSNM,
CAR_CR.MDFDT,
CAR_CR.TRNS,
CAR_CNTCINFO.CNTCSD,
DMNDPRC,
CAR_CR.CRPCTPTH1 HOTMARK,
CAR_CR.NSPDT,
CAR_PIC.CRPCTPTH CRPCTPTH1,
CAR_CR.WRNTNB,
CAR_CR.MYWRNTYN,
CAR_CR.YR YR,
CAR_CR.CLR,
CLS.CTGR,
NVL(CLS.DSP, 0) DSP,
NVL(CLS.MSRM, 0) MSRM,
NVL(CLS.LOADAGE, 0) LOADAGE,
ISVALID
from CAR_CR, CAR_PIC, CAR_CNTCINFO, CAR_CLS CLS
where CAR_CR.MDFDT <= trunc(sysdate + 1)
AND CAR_CR.RGSID = CAR_PIC.RGSID(+)
AND CAR_PIC.PIC_TYPE(+) = '012'
AND CAR_CR.RGSID = CAR_CNTCINFO.RGSID
AND CAR_CR.SETTLEYN = '1'
AND CAR_CR.MNFCCD >= '011'
AND CAR_CR.ISVALID IN ('00')
-AND CAR_CR.MNFCNM = 'BMW'
AND CAR_CR.MNFCCD = CLS.MNFCCD
AND CAR_CR.MDLCD = CLS.MDLCD
AND CAR_CR.CLSCD = CLS.CLSCD
AND rownum <= 40)
where RNUM > 0
AND RNUM <= 40;

1)========================================================================================================
Execution Plan
----------------------------------------------------------
Plan hash value: 226458337

-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |    40 | 14240 |  1314   (1)| 00:00:16 |
|*  1 |  VIEW                             |                 |    40 | 14240 |  1314   (1)| 00:00:16 |
|*  2 |   COUNT STOPKEY                   |                 |       |       |            |          |
|   3 |    NESTED LOOPS                   |                 |    40 |  7400 |  1314   (1)| 00:00:16 |
|   4 |     NESTED LOOPS                  |                 |    40 |  6960 |  1313   (1)| 00:00:16 |
|   5 |      NESTED LOOPS OUTER           |                 |   117 | 17433 |  1312   (1)| 00:00:16 |
|*  6 |       TABLE ACCESS BY INDEX ROWID | CAR_CR          |   187 | 18887 |  1311   (1)| 00:00:16 |
|*  7 |        INDEX RANGE SCAN DESCENDING| CAR_CR_IND3     |   318K|       |    75   (0)| 00:00:01 |
|   8 |       TABLE ACCESS BY INDEX ROWID | CAR_PIC         |     2 |    96 |     1   (0)| 00:00:01 |
|*  9 |        INDEX UNIQUE SCAN          | CAR_PIC_PK      |     1 |       |     1   (0)| 00:00:01 |
|  10 |      TABLE ACCESS BY INDEX ROWID  | CAR_CLS         |     1 |    25 |     1   (0)| 00:00:01 |
|* 11 |       INDEX UNIQUE SCAN           | CAR_CLS_PK      |     1 |       |     1   (0)| 00:00:01 |
|  12 |     TABLE ACCESS BY INDEX ROWID   | CAR_CNTCINFO    |     1 |    11 |     1   (0)| 00:00:01 |
|* 13 |      INDEX UNIQUE SCAN            | CAR_CNTCINFO_PK |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------
2)===========================================================================================================
-----------------------------------------------------------------------------------------------------
| Id  | Operation                         | Name            | Rows  | Bytes | Cost (%CPU)| Time     |
-----------------------------------------------------------------------------------------------------
|   0 | SELECT STATEMENT                  |                 |    40 | 14240 |    11   (0)| 00:00:01 |
|*  1 |  VIEW                             |                 |    40 | 14240 |    11   (0)| 00:00:01 |
|   2 |   SORT ORDER BY                   |                 |    40 |  7400 |    11   (0)| 00:00:01 |
|*  3 |    COUNT STOPKEY                  |                 |       |       |            |          |
|   4 |     NESTED LOOPS OUTER            |                 |    40 |  7400 |    11   (0)| 00:00:01 |
|   5 |      NESTED LOOPS                 |                 |    27 |  3699 |    10   (0)| 00:00:01 |
|   6 |       NESTED LOOPS                |                 |    27 |  3402 |     9   (0)| 00:00:01 |
|*  7 |        TABLE ACCESS BY INDEX ROWID| CAR_CR          |   187 | 18887 |     8   (0)| 00:00:01 |
|*  8 |         INDEX RANGE SCAN          | CAR_CR_IND11    |  2005 |       |     1   (0)| 00:00:01 |
|   9 |        TABLE ACCESS BY INDEX ROWID| CAR_CLS         |     1 |    25 |     1   (0)| 00:00:01 |
|* 10 |         INDEX UNIQUE SCAN         | CAR_CLS_PK      |     1 |       |     1   (0)| 00:00:01 |
|  11 |       TABLE ACCESS BY INDEX ROWID | CAR_CNTCINFO    |     1 |    11 |     1   (0)| 00:00:01 |
|* 12 |        INDEX UNIQUE SCAN          | CAR_CNTCINFO_PK |     1 |       |     1   (0)| 00:00:01 |
|  13 |      TABLE ACCESS BY INDEX ROWID  | CAR_PIC         |     1 |    48 |     1   (0)| 00:00:01 |
|* 14 |       INDEX UNIQUE SCAN           | CAR_PIC_PK      |     1 |       |     1   (0)| 00:00:01 |
-----------------------------------------------------------------------------------------------------


1)의 실행 계획, 위 쿼리에서 index /*+ INDEX_DESC(CAR_CR CAR_CR_IND3) */ 이걸 사용하게 되면 cost가 올라 갑니다.
왜냐하면 CAR_CR_IND3 인덱스에는 일짜 컬럼만이 포함 되어 있기 때문에 실제 car_cr 테이블에 가서 "체크조건"
을 모두 검색을 해야 합니다.
그래서

1) 에서 보이듯이 Cost가 1311이나 됩니다.

2) 의 실행 계획을 보면 car_cr Cost가 8로 보입니다.

1) 보다 훨씬 좋은 것이지요

여기서는 /*+ INDEX_DESC(CAR_CR CAR_CR_IND3) */ 힌트를 제거만 하고 CAR_CR_IND11 인덱스를 사용했습니다.

CAR_CR_IDX11은

INDEX_NAME                     COLUMN_NAME          OWNER      COLUMN_POSITION UNIQUENES
------------------------------ -------------------- ---------- --------------- ---------
CAR_CR_IND11                   MDFDT                ENCAR                    4 NONUNIQUE
CAR_CR_IND11                   MNFCNM               ENCAR                    3 NONUNIQUE
CAR_CR_IND11                   SETTLEYN             ENCAR                    2 NONUNIQUE
CAR_CR_IND11                   ISVALID              ENCAR                    1 NONUNIQUE

이런 구조로 되어있기 때문에 CAR_CR 테이블에서 "체크조건" 확인 할 필요가 없기 때문에 COST가 낮게 나옵니다.

그러나 출력 결과가 다르게 나온다는 문제점이 있습니다.

해결책 : INDEX쪽을 건드리지 않고는 방법이 없습니다. (일자별 정렬이 꼭 DRIVING 컬럼이 되어야 하기 때문)

인덱스 제안 : 어차피 CAR_CR_IDX11 의 첫번째, 두번째 POSITION 컬럼이 모두 분포도가 아주 나쁘기 때문에 인덱스
              첫번째, 두번째로 사용되어야 할 이유가 없습니다. (skip scan으로 사용가치도 없음)
             
              CAR_CR_IDX11 이걸 drop하고 MDFDT DESC로 인덱스 다시 생성하기
             
              예) Create index car_cr_idx11 on car_cr (MDFDT DESC,ISVALID,SETTLEYN,MNFCNM);
             
              물리적으로 MDFDT descending으로 index를 생성해 준다.
              쿼리는 힌트만 제거를 하면 car_cr_idx11을 사용할 것으로 보입니다.
              꼭 테스트 후에 적용하세요....


List of Articles
번호 분류 제목 글쓴이 날짜 조회 수
공지 Q&A Oracle관련 게시물만 Sean 2014.04.09 84911
60 Tip Complete Checklist for Manual Upgrades to 11gR2 [ID 837570.1] 담벼락 2011.03.23 17484
59 Tip NL JOIN에 대하여... 1 고구마 2011.03.21 13165
58 Tip Outer Join의 정확한 이해 file 고구마 2011.03.21 13028
» Tip 쿼리 튜닝 사례 고구마 2011.03.21 14449
56 Tip windows os 에서 운영중 listener.log 삭제하기 2 file dbkill 2011.02.21 22267
55 Tip partition table에 index 생성시 perfstat 2011.01.28 18168
54 Tip DB계정에 profile설정 perfstat 2011.01.05 14595
53 Tip em 실행시 에러 발생 조치 1 dbkill 2010.12.15 27311
52 Tip NetCA 실행시 VM 관련 에러 대처법 2 송기성 2010.12.09 18180
51 Tip Lock Check 승현짱 2010.05.30 11795
50 Tip 11g Newfeature 승현짱 2010.05.30 11477
49 Tip EXPORT/IMPORT 이용한 TABLE과 INDEX 분리하는 방법 고구마 2010.05.24 25463
48 Tip TABLE(INDEX) 다른 TABLESPACE로 옮기기(8i) 고구마 2010.05.24 23009
47 Tip reoder column order 1 유주환 2010.05.19 11394
46 Tip RECOVERY 2 고구마 2010.05.19 19916
45 Tip TEMP TABLESPACE 사용현황 조회하기 1 고구마 2010.05.19 43950
44 Tip 통계복원하기 고구마 2013.05.13 8054
43 Tip 유저별 Segment 개수 및 사이즈 조회 쿼리, n2bfree 2013.03.12 11055
42 Tip online reorg script 담벼락 2013.01.10 10630
41 Tip 오라클11g에서 SE1,SE,EE제품의 옵션비교입니다. file perfstat 2012.03.07 27015
Board Pagination Prev 1 2 3 4 5 6 7 8 Next
/ 8