데이터베이스 질문과 답변

실행계획과 실행 시간 관련 질문, 대용량 자료 update 관련 질문.입니다. 0 6 5,886

by 커피요쿠르트d [SQL Query] 실행계획 merge 시간 [2013.09.05 16:00:39]

수고하십니다. 

대용량이라고하기에는 좀 부끄럽지만서도.. 제게는 대용량인..
30만건 row가 있는 테이블에 update 작업도중 질문이 있어 글을 적습니다. 

내용을 간단히 설명드리면..
영문 이름이 있는 칼럼이 있는데. 이를 성과 이름으로 분리하는 작업입니다. 
임시 테이블(TBL_TEMP_ENG_NAME_TEST)을 하나 만들어서 성과 이름을 저장해놓은 상태입니다. 

이제 임시 테이블에서 원래 목적 테이블(TBL)의 칼럼에 성,과 이름을 update 하는 작업만 남았습니다. 

그런데..문제가.. 이게 시간이 오래걸리네요..  ㅜㅜ
예 물론.. 자료가 많으니 오래걸리겠죠. 그런데.. 이게 좀 이상하게 생각되는게 있어서요..

일단 보시고.. 마저 이야기를...


TBL 자료 건수 : 대략 30만건
TBL 자료 중 영문성명 정보가 존재하는 건수 : 대략 16만건
TBL 칼럼 개수 : 약 100개

TBL_TEMP_ENG_NAME_TEST 자료 건수 : 대략 16만건

1번 쿼리와 실행계획입니다. 
?
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
MERGE
INTO TBL A
USING (SELECT
 KEY,
 ENG_NAME2,
 ENG_NAME3
 FROM TBL_IMSI_ENG_NAME_TEST
) B
ON (A.KEY = B.KEY AND A.ENG_NAME IS NOT NULL)
WHEN MATCHED THEN
UPDATE
SET A.ENG_NAME2 = B.ENG_NAME2 , A.ENG_NAME3 = B.ENG_NAME3;
Execution Plan
-----------------------------------------------------------
 0 MERGE STATEMENT Optimizer=ALL_ROWS (Cost=6K Card=167K Bytes=50M)
 1 0 MERGE OF 'TBL'
 2 1 VIEW
 3 2 HASH JOIN (Cost=6K Card=167K Bytes=51M)
 4 3 TABLE ACCESS (FULL) OF 'TBL_TEMP_ENG_NAME_TEST' (TABLE) (Cost=392 Card=188K Bytes=9M)
 5 3 TABLE ACCESS (FULL) OF 'TBL' (TABLE) (Cost=3K Card=167K Bytes=43M)
-----------------------------------------------------------
 
Predicate information (identified by operation id):
-----------------------------------------------------------
 3 - access("A"."KEY"="KEY")
 5 - filter("A"."ENG_NAME" IS NOT NULL)
-----------------------------------------------------------
2번 쿼리와 실행계획입니다. 별 의미 없지만서도...

?
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
MERGE
INTO TBL A
USING (SELECT
 B.ROWID RID,
 A.KEY,
 A.ENG_NAME2,
 A.ENG_NAME3
 FROM TBL_IMSI_ENG_NAME_TEST A, TBL B
 WHERE A.KEY = B.KEY
 AND B.ENG_NAME IS NOT NULL
) B
ON (A.ROWID = B.RID)
 WHEN MATCHED THEN
UPDATE
SET A.ENG_NAME2 = B.ENG_NAME2 , A.ENG_NAME3 = B.ENG_NAME3;
 
Execution Plan
-----------------------------------------------------------
 0 MERGE STATEMENT Optimizer=ALL_ROWS (Cost=13K Card=167K Bytes=50M)
 1 0 MERGE OF 'TBL'
 2 1 VIEW
 3 2 HASH JOIN (Cost=13K Card=167K Bytes=56M)
 4 3 HASH JOIN (Cost=4K Card=167K Bytes=13M)
 5 4 TABLE ACCESS (FULL) OF 'TBL' (TABLE) (Cost=3K Card=167K Bytes=5M)
 6 4 TABLE ACCESS (FULL) OF 'TBL_TEMP_ENG_NAME_TEST' (TABLE) (Cost=392 Card=188K Bytes=9M)
 7 3 TABLE ACCESS (FULL) OF 'TBL' (TABLE) (Cost=3K Card=366K Bytes=95M)
-----------------------------------------------------------
 
Predicate information (identified by operation id):
-----------------------------------------------------------
 3 - access("A".ROWID="B".ROWID)
 4 - access("A"."KEY"="B"."KEY")
 5 - filter("B"."ENG_NAME" IS NOT NULL)
-----------------------------------------------------------


3. 그리고 추가로.. 
원본 테이블 (TBL)에서 자료 가져와 자른 후에 임시테이블(TBL_TEMP_ENG_NAME_TEST) 에 UPDATE 하는 
실행 계획입니다. 
?
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
Execution Plan
-----------------------------------------------------------
 0 MERGE STATEMENT Optimizer=ALL_ROWS (Cost=3K Card=188K Bytes=31M)
 1 0 MERGE OF 'HSHJHG01_IMSI_ENG_NAME_TEST'
 2 1 VIEW
 3 2 HASH JOIN (Cost=3K Card=188K Bytes=30M)
 4 3  TABLE ACCESS (FULL) OF 'HSHJHG01_IMSI_ENG_NAME_TEST' (TABLE) (Cost=392 Card=188K Bytes=6M)
 5 3  TABLE ACCESS (FULL) OF 'HSHJHG01_IMSI_ENG_NAME_TEST' (TABLE) (Cost=392 Card=188K Bytes=25M)
-----------------------------------------------------------
 
Predicate information (identified by operation id):
-----------------------------------------------------------
 3 - access("A"."HAKBUN"="HAKBUN")
 4 - filter("ENG_NAME" IS NOT NULL)
-----------------------------------------------------------

이제 드리고 싶은 말씀은..

마지막에 보여드린 실행계획의 실제 실행 시간은 15초정도입니다. 
칼럼이 몇개 안되니까요.. 

그런데 왜.. 
실제 1번이나 2번 쿼리를 실행하면 5분이 지나도.. 끝나지를 않을까요?


30만건 테이블 업데이트 작업을 5분내에 하려고 하는건 욕심일까요?

실은 처음에 아무생각없이 프로시저로 커서로 돌리고 있었는데.. 1시간이 넘어가는 데.. 
사이트 내부 여기저기서 사고가 막 터지더라고요. 
원인 분석하는데. 다른 원인이긴 했는데.. 
제가 돌리던 프로시저랑 아무 상관 없다고 하기에는 껄적지근해서요.. 보고는 드렸는데 별 말씀이 없으시더라고요. ㅠㅠ

그래서 급히 MERGE로 변경하고 현재 작업중입니다. 
이 말씀을 드리는건.. 쿼리가 오래돌지 않았으면 해서요. 

실은 경험이 없어서 COST 3K, 6K 16K 가 얼마나 차이가 있는건지. BYTE도.. 
감이 없으니.. 대략 난감이네요.. 


해서 도움을 요첨합니다. 

왜 

  (Cost=6K Card=167K Bytes=50M) 1번 실행계획
  (Cost=3K Card=188K Bytes=31M)  3번 실행계획

눈으로 보기에는 별 차이 없어보이는데.. 왜 시간이 오래 걸리는지..
물론 환경에 따라 다를  수 있겠지만.. 이게 정상적인건지..

TBL의 칼럼이 많아서 BYTE가 많은거 같은데요.. 이를 해결할 수 있는지.. MERGE 에 서브쿼리를 넣어버리고 싶은마음에..ㅠㅠ


다른 좋은 방법이 있는지.. 

긍금합니다. 


전 질문올리고.. 

데이터 나눠서 올려봐야겠네요.. 1만건씩 하면 17번이면 되네요!!!! 호..
이거 질문하는 시간에 올릴 수도 있겠는데요!!! 아닌가? ㅠㅠ

그럼 도움 바랍니다. 

더 필요한 정보 있으시면 말씀해주시구요.

즐거운 오후 되시길 바랍니다. 

그런데.. 이런 내용의 질문은.. 제목을 뭐라고 해야할까요?..
제목학원을 다녔어야 되는건데.. 젠장... 



by 신이만든짝퉁 [2013.09.05 16:10:52]
먼저 추정되는 것은 업데이트 대상이 되는 TBL 테이블이 다른세션에서 사용중이라(row단위 lock이 걸려) 시간이 오래걸리는 것 같습니다.

30만건도 자주사용되는 테이블이라면 업무시간을 피해 업데이트 하시면 문제가 없을 것 같습니다.
by 커피요쿠르트d [2013.09.05 16:15:01]
아.. 사용자의 기본적인 인적사항이 저장되는 테이블이라 자주 사용되겠군요. 생각해보니. ㅠㅠ

알려주셔서 감사합니다!!

by 커피요쿠르트d [2013.09.05 16:11:26]
아.... 자료가 처리되서 들어가는 임시테이블에는 16만건이 들어가있습니다. 
영어 이름 정보가 있는 자료만요..

본문 수정할게요.
by 마농 [2013.09.05 17:14:42]
원본 > 임시 > 원본 과정이 너무 복잡해 보입니다.
임시 빼고 원본을 바로 업데이트 가능하리라 생각되네요.
그러면 조인으로 인한 부하는 없겠네요.
by 커피요쿠르트d [2013.09.05 17:21:50]
아.. 그 생각을 못했네요. 

사실 간단한 문자열 처리라.. 
바로 업데이트 해도 되긴하는데 확인도 좀하고.. 테스트도 겸해서 임시 테이블을 사용했던 것이라.. 
까맣게 잊고 있었네요. 

바로 처리해보겠습니다. 

감사합니다.!!!
by 커피요쿠르트d [2013.09.06 09:08:47]
작업은 되었습니다. 

음.. 시간이 오래 걸리는 이유는 
원본테이블(TBL)에 트리거가 상당히 걸려 있었습니다. ㅠㅠ

저는 왜.. MERGE문이 실행될때 트리거는 실행안된다고 알고 있었을까요? 흑흑...
어줍잖은 지식이 화를 불러일으켰네요. 

밤에 작업했고, 원본테이블 한방에 처리해도 시간이 걸리길래.. 
수천건 단위로 LOOP 돌면서 처리했습니다. 

나중에 확인해보니 트리거가 모두 동작을 했더라고요.

결론은 트리거 때문에 시간이 오래 걸리더라로 내려도 될까요? ㅎㅎㅎ;;

도움주신 2분 감사드립니다. 즐거운 하루 되시길 바랍니다...
댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입