데이터베이스 질문과 답변
쿼리는 아래와 같구요. 쿼리튜닝은 초보라 잘 안되서 질문 드립니다.
일단 건수자체가 기간별로 조회인데 하루치만 해도 조인하면 20만건정도 됩니다.
QWK_UPDETAlL 이란 테이블은 19억건정도 되구요. 조인하면서 updetail 쪽에 발생하는 랜덤 엑세스가
많아서 줄이보고자 여러가지 생각을 했는데 그중 한가지가 아예 updetail테이블쪽 랜덤엑세스를 줄이고자
인덱스를 컬럼있는걸 다 생성하는거 였는데, 시간이 줄긴 하겠지만 정답은 아니라고 생각 되서요.
선행집합을 gropu by 한후에 join을 하고 싶어 시도해봤는데 데이터 조건이나 이런게 잘 안맞아서 잘 안되네요 혹시 이런경우에 최적화 하는 방법이 어떤것이 있을 까요?? 플랜이랑 쿼리는 파일 첨부 합니다.
일단 건수자체가 기간별로 조회인데 하루치만 해도 조인하면 20만건정도 됩니다.
QWK_UPDETAlL 이란 테이블은 19억건정도 되구요. 조인하면서 updetail 쪽에 발생하는 랜덤 엑세스가
많아서 줄이보고자 여러가지 생각을 했는데 그중 한가지가 아예 updetail테이블쪽 랜덤엑세스를 줄이고자
인덱스를 컬럼있는걸 다 생성하는거 였는데, 시간이 줄긴 하겠지만 정답은 아니라고 생각 되서요.
선행집합을 gropu by 한후에 join을 하고 싶어 시도해봤는데 데이터 조건이나 이런게 잘 안맞아서 잘 안되네요 혹시 이런경우에 최적화 하는 방법이 어떤것이 있을 까요?? 플랜이랑 쿼리는 파일 첨부 합니다.
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | SELECT A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.STEPNAME, A.ITEM, TO_CHAR(A.TOTCNT) AS TOTCNT, TO_CHAR(A.NGCNT) AS NGCNT, A.RATE AS RATE, 'DATA' AS DATA, ROWNUM AS TOPFROM ( SELECT A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.COMMENT1 AS STEPNAME, A.ITEM, COUNT(*) AS TOTCNT, SUM(DECODE(A.JUDGE, '0', 1, 0)) AS NGCNT, ROUND(SUM(DECODE(A.JUDGE, '0', 1, 0)) * 100 / COUNT(*), 2) AS RATE FROM QWK_UPDETAIL A INNER JOIN QWK_UPHEAD B ON A.PLANT = B.PLANT AND A.TSTCLS = B.TSTCLS AND A.HOGI = B.HOGI AND A.MIP = B.MIP AND A.DBKEY = B.DBKEY WHERE B.PLANT = 'E' AND B.TSTCLS = 'VB' AND B.DBKEY >= '201508170759' AND B.DBKEY <= '201508180759' AND NOT EXISTS ( SELECT 'X' FROM QCD_STAND WHERE PLANT = B.PLANT AND TSTCLS = B.TSTCLS AND A.ITEM = ITEM AND SLT01 = '1' ) AND B.TMCODE NOT IN ( SELECT SNO FROM QCD_MASTER WHERE PLANT = B.PLANT AND TSTCLS = B.TSTCLS ) AND B.INSPCHASU = '1' GROUP BY A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.COMMENT1, A.ITEM )AWHERE ROWNUM <= 50 AND A.NGCNT != 0 |
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 | --------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| Id | Operation | Name | Starts | E-Rows |E-Bytes| Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------| 0 | SELECT STATEMENT | | 1 | | | 11 (100)| | 50 |00:00:03.78 | 66871 | 1833 | | | ||* 1 | COUNT STOPKEY | | 1 | | | | | 50 |00:00:03.78 | 66871 | 1833 | | | || 2 | VIEW | | 1 | 1 | 132 | 11 (19)| 00:00:01 | 50 |00:00:03.78 | 66871 | 1833 | | | ||* 3 | SORT ORDER BY STOPKEY | | 1 | 1 | 117 | 11 (19)| 00:00:01 | 50 |00:00:03.78 | 66871 | 1833 | 9216 | 9216 | 8192 (0)||* 4 | FILTER | | 1 | | | | | 76 |00:00:03.78 | 66871 | 1833 | | | || 5 | HASH GROUP BY | | 1 | 1 | 117 | 11 (19)| 00:00:01 | 2344 |00:00:03.78 | 66871 | 1833 | 828K| 828K| 1338K (0)||* 6 | FILTER | | 1 | | | | | 214K|00:00:03.62 | 66871 | 1833 | | | || 7 | NESTED LOOPS ANTI | | 1 | 1 | 117 | 9 (0)| 00:00:01 | 214K|00:00:03.53 | 66749 | 1833 | | | || 8 | NESTED LOOPS | | 1 | 1 | 104 | 7 (0)| 00:00:01 | 214K|00:00:03.47 | 66687 | 1833 | | | ||* 9 | TABLE ACCESS BY INDEX ROWID| QWK_UPHEAD | 1 | 1 | 39 | 4 (0)| 00:00:01 | 2011 |00:00:00.11 | 976 | 8 | | | ||* 10 | INDEX RANGE SCAN | QWK_UPHEAD_IDX04 | 1 | 1 | | 3 (0)| 00:00:01 | 2176 |00:00:00.04 | 20 | 3 | | | || 11 | TABLE ACCESS BY INDEX ROWID| QWK_UPDETAIL | 2011 | 1 | 65 | 5 (0)| 00:00:01 | 214K|00:00:03.31 | 65711 | 1825 | | | ||* 12 | INDEX RANGE SCAN | IDX_SCMR_010 | 2011 | 1 | | 4 (0)| 00:00:01 | 214K|00:00:01.68 | 11231 | 971 | | | ||* 13 | INDEX RANGE SCAN | TEST_IDX | 31 | 54 | 702 | 2 (0)| 00:00:01 | 0 |00:00:00.01 | 62 | 0 | | | ||* 14 | INDEX UNIQUE SCAN | XPKQCD_MASTER | 122 | 1 | 18 | 0 (0)| | 0 |00:00:00.01 | 122 | 0 | | | |--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------Query Block Name / Object Alias (identified by operation id):------------------------------------------------------------- 1 - SEL$1 2 - SEL$312E5F5B / A@SEL$1 3 - SEL$312E5F5B 9 - SEL$312E5F5B / B@SEL$2 10 - SEL$312E5F5B / B@SEL$2 11 - SEL$312E5F5B / A@SEL$2 12 - SEL$312E5F5B / A@SEL$2 13 - SEL$312E5F5B / QCD_STAND@SEL$4 14 - SEL$5 / QCD_MASTER@SEL$5Outline Data------------- /*+ BEGIN_OUTLINE_DATA IGNORE_OPTIM_EMBEDDED_HINTS OPTIMIZER_FEATURES_ENABLE('11.2.0.3') DB_VERSION('11.2.0.3') ALL_ROWS OUTLINE_LEAF(@"SEL$5") OUTLINE_LEAF(@"SEL$312E5F5B") UNNEST(@"SEL$4") OUTLINE_LEAF(@"SEL$1") OUTLINE(@"SEL$64EAE176") MERGE(@"SEL$2") OUTLINE(@"SEL$4") OUTLINE(@"SEL$3") OUTLINE(@"SEL$2") NO_ACCESS(@"SEL$1" "A"@"SEL$1") INDEX_RS_ASC(@"SEL$312E5F5B" "B"@"SEL$2" ("QWK_UPHEAD"."PLANT" "QWK_UPHEAD"."TSTCLS" "QWK_UPHEAD"."DBKEY")) INDEX_RS_ASC(@"SEL$312E5F5B" "A"@"SEL$2" ("QWK_UPDETAIL"."DBKEY" "QWK_UPDETAIL"."PLANT" "QWK_UPDETAIL"."TSTCLS" "QWK_UPDETAIL"."HOGI" "QWK_UPDETAIL"."ITEM" "QWK_UPDETAIL"."PATTERN" "QWK_UPDETAIL"."STEP" "QWK_UPDETAIL"."MIP")) INDEX(@"SEL$312E5F5B" "QCD_STAND"@"SEL$4" ("QCD_STAND"."PLANT" "QCD_STAND"."TSTCLS" "QCD_STAND"."ITEM" "QCD_STAND"."SLT01")) LEADING(@"SEL$312E5F5B" "B"@"SEL$2" "A"@"SEL$2" "QCD_STAND"@"SEL$4") USE_NL(@"SEL$312E5F5B" "A"@"SEL$2") USE_NL(@"SEL$312E5F5B" "QCD_STAND"@"SEL$4") USE_HASH_AGGREGATION(@"SEL$312E5F5B") INDEX(@"SEL$5" "QCD_MASTER"@"SEL$5" ("QCD_MASTER"."PLANT" "QCD_MASTER"."TSTCLS" "QCD_MASTER"."SNO")) END_OUTLINE_DATA */Predicate Information (identified by operation id):--------------------------------------------------- 1 - filter(ROWNUM<=50) 3 - filter(ROWNUM<=50) 4 - filter(SUM(DECODE("A"."JUDGE",'0',1,0))<>0) 6 - filter( IS NULL) 9 - filter("B"."INSPCHASU"=1) 10 - access("B"."PLANT"='E' AND "B"."TSTCLS"='VB' AND "B"."DBKEY">='201508170800' AND "B"."DBKEY"<='201508180759') 12 - access("A"."DBKEY"="B"."DBKEY" AND "A"."PLANT"='E' AND "A"."TSTCLS"='VB' AND "A"."HOGI"="B"."HOGI" AND "A"."MIP"="B"."MIP") filter(("A"."DBKEY">='201508170800' AND "A"."DBKEY"<='201508180759' AND "A"."MIP"="B"."MIP")) 13 - access("PLANT"='E' AND "TSTCLS"='VB' AND "A"."ITEM"="ITEM" AND "SLT01"='1') 14 - access("PLANT"=:B1 AND "TSTCLS"=:B2 AND "SNO"=:B3)Column Projection Information (identified by operation id):----------------------------------------------------------- 1 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."STEPNAME"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], "A"."TOTCNT"[NUMBER,22], "A"."NGCNT"[NUMBER,22], "A"."RATE"[NUMBER,22], ROWNUM[4] 2 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."STEPNAME"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], "A"."TOTCNT"[NUMBER,22], "A"."NGCNT"[NUMBER,22], "A"."RATE"[NUMBER,22] 3 - (#keys=3) ROUND(SUM(DECODE("A"."JUDGE",'0',1,0))*100/COUNT(*),2)[22], COUNT(*)[22], "A"."STEP"[NUMBER,22], "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], SUM(DECODE("A"."JUDGE",'0',1,0))[22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7] 4 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], COUNT(*)[22], SUM(DECODE("A"."JUDGE",'0',1,0))[22] 5 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], COUNT(*)[22], SUM(DECODE("A"."JUDGE",'0',1,0))[22] 6 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], "A"."JUDGE"[VARCHAR2,1] 7 - "B"."PLANT"[VARCHAR2,1], "B"."TSTCLS"[VARCHAR2,2], "B"."SNO"[VARCHAR2,20], "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], "A"."JUDGE"[VARCHAR2,1] 8 - "B"."PLANT"[VARCHAR2,1], "B"."TSTCLS"[VARCHAR2,2], "B"."SNO"[VARCHAR2,20], "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], "A"."JUDGE"[VARCHAR2,1] 9 - "B"."PLANT"[VARCHAR2,1], "B"."TSTCLS"[VARCHAR2,2], "B"."HOGI"[VARCHAR2,3], "B"."DBKEY"[VARCHAR2,30], "B"."MIP"[VARCHAR2,30], "B"."SNO"[VARCHAR2,20] 10 - "B".ROWID[ROWID,10], "B"."PLANT"[VARCHAR2,1], "B"."TSTCLS"[VARCHAR2,2], "B"."DBKEY"[VARCHAR2,30] 11 - "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."MIP"[VARCHAR2,30], "A"."STEP"[NUMBER,22], "A"."COMMENT1"[VARCHAR2,100], "A"."ITEM"[VARCHAR2,7], "A"."JUDGE"[VARCHAR2,1] 12 - "A".ROWID[ROWID,10], "A"."TSTCLS"[VARCHAR2,2], "A"."HOGI"[VARCHAR2,3], "A"."ITEM"[VARCHAR2,7], "A"."STEP"[NUMBER,22], "A"."MIP"[VARCHAR2,30] |
by 마농
[2015.08.31 08:58:05]
NOT IN 을 NOT EXISTS 로 바꿔보세요.
by 콩나무
[2015.08.31 10:08:09]
결과가 다르게 나옵니다
by 마농
[2015.08.31 10:29:11]
잘못 바꾸신 듯.
by 마농
[2015.08.31 10:47:26]
1 2 3 4 5 6 | AND NOT EXISTS (SELECT 1 FROM qcd_master WHERE plant = b.plant AND tstcls = b.tstcls AND sno = b.tmcode ) |
by 콩나무
[2015.08.31 13:39:02]
네 제가 잘못 바꿧네요 바꾸었지만 일단 부하가 되는 UPDETAIL 쪽 랜덤엑세스는 그대로인것 같아요
말씀하신 부분도 버퍼가 줄어들었습니다 감사합니다.
by 부쉬맨
[2015.08.31 10:36:20]
플랜이나 정확한 정보가 없지만
일단 변경초점을 QWK_UPDETAIL 가 드라이브되어서
하단의 in이나 exists 로 빠지는 부분에서 반복 access를 방지하셔야됩니다.
저정도 데이터라하면 full로 자동으로 풀리긴하겟지만
하단의 서브쿼리부분을 filter 조건으로 푸는걸 목적으로 두시고
head 테이블과 하단의 서브쿼리절을 먼저 드라이빙하시고 (unnest라든지 no_unnest 힌트 참조)
tail테이블과 조인될 수 있게 조정해보세요.
by 콩나무
[2015.08.31 13:40:12]
플랜 추가 하였습니다!! 의견 감사합니다.
by 부쉬맨
[2015.08.31 13:53:36]
플랜만보면 해당 tail 인덱스를 읽고 dbkey등의 다른 컬럼의 값을 찾으로 table access 한 비용이 높게 잡혀있으므로 그냥 해당 테이블에 대해서 index -> table 을 가지않고
table만 읽게 1안
2안은 tail 테이블을 full 로 읽을경우 해당 쿼리의 플랜이 변경 될 여지가 있으므로
leading흰트
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 | SELECT A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.STEPNAME, A.ITEM, TO_CHAR(A.TOTCNT) AS TOTCNT, TO_CHAR(A.NGCNT) AS NGCNT, A.RATE AS RATE, 'DATA' AS DATA, ROWNUM AS TOPFROM ( --SELECT /*+ full(a) */ 1안 --SELECT /*+ leading(b a) full(a) */ 2안 A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.COMMENT1 AS STEPNAME, A.ITEM, COUNT(*) AS TOTCNT, SUM(DECODE(A.JUDGE, '0', 1, 0)) AS NGCNT, ROUND(SUM(DECODE(A.JUDGE, '0', 1, 0)) * 100 / COUNT(*), 2) AS RATE FROM QWK_UPDETAIL A INNER JOIN QWK_UPHEAD B ON A.PLANT = B.PLANT AND A.TSTCLS = B.TSTCLS AND A.HOGI = B.HOGI AND A.MIP = B.MIP AND A.DBKEY = B.DBKEY WHERE B.PLANT = 'E' AND B.TSTCLS = 'VB' AND B.DBKEY >= '201508170759' AND B.DBKEY <= '201508180759' AND NOT EXISTS ( SELECT 'X' FROM QCD_STAND WHERE PLANT = B.PLANT AND TSTCLS = B.TSTCLS AND A.ITEM = ITEM AND SLT01 = '1' ) AND B.TMCODE NOT IN ( SELECT SNO FROM QCD_MASTER WHERE PLANT = B.PLANT AND TSTCLS = B.TSTCLS ) AND B.INSPCHASU = '1' GROUP BY A.TSTCLS, A.HOGI, A.MIP, A.STEP, A.COMMENT1, A.ITEM )AWHERE ROWNUM <= 50 AND A.NGCNT != 0 |
by 콩나무
[2015.08.31 15:55:44]
시도를 해보왔는데 엄청 느려지는 것 같아요 일단 updetail 건이 19억건정도 table이다보니
full 로 바꾸면 b와 a가 hash 조인으로 풀리면서 uphaed에서 걸러진 3만건정도와 19억건이
조인되는것 같습니다. 지금은 하루치라 평상시 쿼리 플랜으로도 어느정도 나오는데 1달정도가 되면 건수가 더많아져서 updetail쪽 엑세스하는 건수 + 랜덤엑세스 이걸 줄이려고 하는데 잘안되네요
감사합니다.
by 부쉬맨
[2015.08.31 16:37:12]
filter(("A"."DBKEY">='201508170800' AND "A"."DBKEY"<='201508180759' AND "A"."MIP"="B"."MIP"))
여부분 이 핃터로 풀리는데
해당 필터로 풀리는 2개의 컬럼의 결합인덱스를 만들어보세요.
by 아발란체
[2015.08.31 11:05:34]
최종 50건씩 보는 관점에서 풀어볼려니 많이 복잡해보이네영.... OTL
파티션 정책에 따라 영향도 있어보이지만
정렬 기준이 명확하지 않은 것 같은데, 먼저 50건을 빠르게 추출하고 해당 50건에 대해 TOTCNT와 NGCNT를 구하는 것이 어떨까하네용..
) 버튼을 클릭하여 작성 하시면 됩니다.