데이터베이스 질문과 답변
쿼리는 아래와 같구요. 쿼리튜닝은 초보라 잘 안되서 질문 드립니다.
일단 건수자체가 기간별로 조회인데 하루치만 해도 조인하면 20만건정도 됩니다.
QWK_UPDETAlL 이란 테이블은 19억건정도 되구요. 조인하면서 updetail 쪽에 발생하는 랜덤 엑세스가
많아서 줄이보고자 여러가지 생각을 했는데 그중 한가지가 아예 updetail테이블쪽 랜덤엑세스를 줄이고자
인덱스를 컬럼있는걸 다 생성하는거 였는데, 시간이 줄긴 하겠지만 정답은 아니라고 생각 되서요.
선행집합을 gropu by 한후에 join을 하고 싶어 시도해봤는데 데이터 조건이나 이런게 잘 안맞아서 잘 안되네요 혹시 이런경우에 최적화 하는 방법이 어떤것이 있을 까요?? 플랜이랑 쿼리는 파일 첨부 합니다.
일단 건수자체가 기간별로 조회인데 하루치만 해도 조인하면 20만건정도 됩니다.
QWK_UPDETAlL 이란 테이블은 19억건정도 되구요. 조인하면서 updetail 쪽에 발생하는 랜덤 엑세스가
많아서 줄이보고자 여러가지 생각을 했는데 그중 한가지가 아예 updetail테이블쪽 랜덤엑세스를 줄이고자
인덱스를 컬럼있는걸 다 생성하는거 였는데, 시간이 줄긴 하겠지만 정답은 아니라고 생각 되서요.
선행집합을 gropu by 한후에 join을 하고 싶어 시도해봤는데 데이터 조건이나 이런게 잘 안맞아서 잘 안되네요 혹시 이런경우에 최적화 하는 방법이 어떤것이 있을 까요?? 플랜이랑 쿼리는 파일 첨부 합니다.
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 TOP
FROM
(
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
)A
WHERE ROWNUM <= 50
AND A.NGCNT != 0
--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 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$5
Outline 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]
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흰트
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 TOP
FROM
(
--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
)A
WHERE 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를 구하는 것이 어떨까하네용..
) 버튼을 클릭하여 작성 하시면 됩니다.