데이터베이스 질문과 답변
안녕하세요 데이터베이스 초보라 해결 방법을 못찾아 이렇게 문의 드립니다.
INSERT 구문인데요.. LC_MEM_MSTR에는 800만건, LC_DGB에는 300만건, LC_OFFER에는 1000만건
의 데이터가 있습니다. 구조는 단순하지만 프로그램에서 아래와 같이 돌리니 대략 30분 정도 걸리고여 toad에서
실행시에 약 7분 가량 걸립니다. 변수로 받는 부분은 LATELY_AGREE_DT, BIRTH_DAY, YYYYMM과 하단에 NOT
EXISTS 부분은 ibatis에서 변수값에 따라 붙기도 하고 안붙기도 하는 부분입니다.
==========================================================================================
INSERT /*+ APPEND */INTO LC_OFFER(
SPON_NO, CRM_MSTR_NO, CONTENTS_NO, OFFER_DD, APP_DT, UNIQUE_ID_NA, UNIQUE_ID_YA, UNIQUE_ID_HA, OFFER_TM, YY, GENDER, KEY_VAL
)
SELECT /*+ FULL(LC_MEM_MSTR) PARALLEL(LC_MEM_MSTR,4) */
SPON_NO,CRM_MSTR_NO,CONTENTS_NO,OFFER_DD,LATELY_AGREE_DT,N,Y,H,OFFER_TM,YY,GENDER,'' FROM(
SELECT '111' SPON_NO, CRM_MSTR_NO, '200002' CONTENTS_NO, SUBSTR('20141113162830',0,8) OFFER_DD, LATELY_AGREE_DT,
BLOWFISH_ENC(NAME) N, BLOWFISH_ENC(BIRTH_DAY) Y, BLOWFISH_ENC(HP3) H,
SUBSTR('20141113162830',9,6) OFFER_TM, SUBSTR(BIRTH_DAY,0,2) YY, DECODE(GENDER,'M','1','2') GENDER,
HP KEY_VAL
FROM LC_MEM_MSTR A
WHERE LATELY_AGREE_DT BETWEEN '20121101'||'000000' AND '20141112'||'235959'
AND
BIRTH_DAY BETWEEN '30'||00000 AND '99'||99999
AND HP_AUTH_YN = 'Y'
AND CRM_MSTR_NO IS NOT NULL
AND
EXISTS(
SELECT /*+ INDEX (LC_DGB UK1_LC_DGB )*/ * FROM LC_DGB E
WHERE A.AUTHCODE = E.AUTHCODE
AND
YYYYMM IN (
'200912'
,
'201001'
,
'201002'
,
'201003'
,
'201011'
,
'201104_2'
,
'201110'
,
'201204'
,
'201211'
,
'201307'
)
)
AND
NOT EXISTS(
SELECT /*+ INDEX (LC_OFFER IDX1_LC_OFFER )*/* FROM LC_OFFER
WHERE SPON_NO = 16
AND CONTENTS_NO = 200002
AND OFFER_DD >= '20140807'
AND A.CRM_MSTR_NO = CRM_MSTR_NO
)
AND
NOT EXISTS(
SELECT /*+ INDEX (LC_OFFER IDX1_LC_OFFER )*/* FROM LC_OFFER
WHERE SPON_NO = 111
AND CONTENTS_NO = 200002
AND A.CRM_MSTR_NO = CRM_MSTR_NO
)
)
800만건이 테이블 전체건수인가요? 조회결과건수인가요?
만약 전체건수라면?
- 조회결과건수는 몇건이며?
- Exists, Not Exists 없이 순수하게 조회되는 시간은 얼마나 걸리는지?
성능 저하의 주범이 누구인지?
- lc_mem_mstr 조회 자체가 느린지?
- Exists / Not Exists 가 느린지?
인덱스 등은 어떻게 구성되어 있는지?
각 테이블간의 관계는 어떻게 되는지?
우선 제가 가입하고 처음 올리느라 질문에 있어 미흡한 점 사과 드립니다.
800만건은 테이블 전체건수이며 조회 결과수는 위 쿼리 실행시 insert 되는 건수는 대략 3만건입니다.
EXISTS와 NOT EXISTS 제외하고 조회하면 300만건에 1초가량 걸리구요, EXISTS 시에는
1.5초, NOT EXISTS 걸었을때 3초 가량 걸립니다.
index는 lc_mem_mstr 테이블의 authcode 컬럼 유일키이고 Unique-index이며 crm_mstr_no는
nonunique-index입니다.
lc_dgb의 컬럼은 authcode, yyyymm 단 두개이며, unique-index는 두 컬럼 다 걸려있습니다.
따라서 lc_mem_mstr 과의 관계는 authcode가 키값이며 일대다로 형성되어 있습니다.
lc_offer는 lc_mem_mstr과 crm_mstr_no 컬럼이 키값이며 마찬가지로 일대다 이고,
SPON_NO, CRM_MSTR_NO, CONTENTS_NO, OFFER_DD가 unique index 입니다.
그리고 힌트를 주기 위하여 crm_mstr_no만 따로 non unique index로 생성하였습니다.
나름 손을 본 쿼리 같은데요. 우선 insert 가 몇건정도 인지 알려주시고요.
insert 건수가 과도하지 않다면 select 문을 따로 분리해서 Tuning 하는 것이 바람직할 것으로 판단됩니다.
가능 하시면 아래 보시고 select 문의 trace 를 떠서 update해 주시면 좀더 Idea가 생기지 않을 까 하네요.
select 문의 처음 부분에 아래 hint를 넣어 주시고요.
select /*+ gather_plan_statistics */ ...
from
실행 후 아래 query실행에서 결과를 update해 주시면 됩니다.
select *
from table(DBMS_XPLAN.DISPLAY_CURSOR(NULL,NULL,'advanced allstats last'));
User has no SELECT privilege on V$SESSION
라고 뜹니다. 권한을 줘야 하는거 같은데 방법을 모르겠네요..
감사합니다.
v$session 권한이 없네요.
계속 튜닝을 하시는 상황이면 대상 권한을 부여받아서
윗분이 말씀하신것을 사용하여서 어떤쿼리에서
문제(속도저하)가 발생하는지 확인하시는것도 좋은방법입니다.
SELECT /*+ INDEX (LC_DGB UK1_LC_DGB )*/ * FROM LC_DGB E
WHERE A.AUTHCODE = E.AUTHCODE
AND
YYYYMM IN (
'200912'
,
'201001'
,
'201002'
,
'201003'
,
'201011'
,
'201104_2'
,
'201110'
,
'201204'
,
'201211'
,
'201307'
)
)
AND
NOT EXISTS(
SELECT /*+ INDEX (LC_OFFER IDX1_LC_OFFER )*/* FROM LC_OFFER
WHERE SPON_NO = 16
AND CONTENTS_NO = 200002
AND OFFER_DD >= '20140807'
AND A.CRM_MSTR_NO = CRM_MSTR_NO
)
AND
NOT EXISTS(
SELECT /*+ INDEX (LC_OFFER IDX1_LC_OFFER )*/* FROM LC_OFFER
WHERE SPON_NO = 111
AND CONTENTS_NO = 200002
AND A.CRM_MSTR_NO = CRM_MSTR_NO
)
요 쿼리만 실행시에는 속도가 얼마나 걸리나요?
위글만보았을경우 각각의 쿼리로만 돌려보신거같은데
해당 exists 시 대상이 누구냐에 따라서
속도는 천차만별이 될것입니다.
현재로 보았을경우 위쿼리가 문자기 있어보이지만
대상 쿼리가 정상적으로?? 속도가 나온다면
LC_MEM_MSTR 테이블이 -> LC_OFFER 테이블과 조인되는 과정에서 생기는
문제 일 것입니다.
힌트를 SELECT /*+ NL_SJ, NL,AJ(LC_OFFER) INDEX (LC_OFFER IDX1_LC_OFFER )*/* 형태로
변경해보시기 바랍니다.
답변 감사드립니다.
v$session에 grant 가 안걸리네요.. 이부분 먼저 해결해야 할 것 같습니다.
또한
LC_OFFER 테이블을 필더 대상으로하는 데이터가 너무많으니
index가 아닌 hash 조건으로 푸셔서 랜덤액세스를 줄이는것도 방법입니다.
index힌트를 없애고 플랜을 보시면 옵티마이저가 어떤걸 좋게 푸시는것도 한번 확인해보세요.
많은 분들이 말씀하신것 처럼 실행계획을 확인해야 할듯합니다.
다만 쿼리만을 보고 얘기한다면
1. select list 에서 SUBSTR('20141113162830',0,8),SUBSTR('20141113162830',9,6)
같은 컬럼은 .'20141113', '162830' 로 처리해서 substr 함수의 호출을 줄일수 있을것 같네요..
3만건이면 6만번의 호출을 줄일 수 있구요..
2. select list 에서 BLOWFISH_ENC(NAME) N, BLOWFISH_ENC(BIRTH_DAY) Y, BLOWFISH_ENC(HP3) H
함수 호출을 하고 있는데요..
스칼라 서브쿼리로 변경해서 해보세요..
일반함수 보다 스칼라 서브쿼리가 성능면에서 아주 우수합니다...
답변 감사드립니다. substr은 빼긴 해야 겠네요..
그리고 blowfish_enc는 암호화 하는것을 java에서 가져오는데요.. 이부분은 공통적으로 쓰이는 부
분이라 건들수 없는 영역입니다.
CREATE OR REPLACE function MILC.BlowFish_Enc(I_STR IN VARCHAR2)
return varchar2
as
language java
name 'BlowfishEasyMplus.encrypt(java.lang.String) return java.lang.String';
/
1. 가변적인 조건에 따라 plan이 변경될 수 있으므로,
조합가능한 모든 SQL 문(+hint)을 ibatis 에 등록하거나,
하나의 SQL에 exists 로 추가되는 부분을 고려한 hint 추가 조건을 같이 넣는 것이 좋을 것 같습니다.
2. not exists 는 가능하면 조건을 or로 통합하여 테이블간 1회만 join
3. function 을 (select function() from dual) 형태로 바꿔 캐시값 이용 또는 deterministic function 으로 생성 (input->output 일관적일때)
4. 모든 테이블 full / hash join / parallel (hash+hash) 도 고려 (INOUT: P->P)
SELECT /*+ FULL(LC_MEM_MSTR) PARALLEL(LC_MEM_MSTR,4) */
SPON_NO,CRM_MSTR_NO,CONTENTS_NO,OFFER_DD,LATELY_AGREE_DT,N,Y,H,OFFER_TM,YY,GENDER,'' FROM(
SELECT '111' SPON_NO, CRM_MSTR_NO, '200002' CONTENTS_NO, SUBSTR('20141113162830',0,8) OFFER_DD, LATELY_AGREE_DT,
(select BLOWFISH_ENC(NAME) from dual) N, (select BLOWFISH_ENC(BIRTH_DAY) from dual) Y, (select BLOWFISH_ENC(HP3) from dual) H,
SUBSTR('20141113162830',9,6) OFFER_TM
, SUBSTR(BIRTH_DAY,0,2) YY
, DECODE(GENDER,'M','1','2') GENDER,
HP KEY_VAL
FROM LC_MEM_MSTR A
WHERE LATELY_AGREE_DT BETWEEN '20121101'||'000000' AND '20141112'||'235959'
AND
BIRTH_DAY BETWEEN '30'||00000 AND '99'||99999
AND HP_AUTH_YN = 'Y'
AND CRM_MSTR_NO IS NOT NULL
AND
EXISTS(
SELECT /*+ INDEX (LC_DGB UK1_LC_DGB )*/ * FROM LC_DGB E
WHERE A.AUTHCODE = E.AUTHCODE
AND
YYYYMM IN (
'200912'
,
'201001'
,
'201002'
,
'201003'
,
'201011'
,
'201104_2'
,
'201110'
,
'201204'
,
'201211'
,
'201307'
)
)
AND
NOT EXISTS(
SELECT /*+ INDEX (LC_OFFER IDX1_LC_OFFER )*/* FROM LC_OFFER
WHERE SPON_NO = 16
AND CONTENTS_NO = 200002
AND OFFER_DD >= '20140807'
AND A.CRM_MSTR_NO = CRM_MSTR_NO
UNION ALL
SELECT /*+ INDEX (LC_OFFER IDX1_LC_OFFER )*/* FROM LC_OFFER
WHERE SPON_NO = 111
AND CONTENTS_NO = 200002
AND A.CRM_MSTR_NO = CRM_MSTR_NO
)
)
이런식으로 not exists 구문은 하나로 했구요.. or는 좀 쓰기가 까다롭기도 하고 애매하기도 하네요..
그리고 blowfish_enc 이부분도 말씀주신 대로 바꿔보았으나 개인정보를 암호화하는 부분인데요
input이 같아도 output은 달라지므로 무의미 한것 같습니다.
답변 감사합니다.
1. 근데 힌트가 잘못된 것 같은데 병렬 처리 의도된게 맞나요?
병렬 처리하려면 안쪽 SELECT 에서 alias 로 줘야 할 것 같네요.
(필요시 EXISTS, NOT EXISTS SELECT 절에도 추가)
SELECT /*+ FULL(A) PARALLEL(A 4) */ '111' SPON_NO, ...
2. NOT EXISTS 는 아래와 같이 했을때 동일한지 테스트 해보세요.
AND NOT EXISTS (
SELECT /*+ UNNEST */ *
FROM LC_OFFER LO
WHERE CONTENTS_NO = 200002
AND A.CRM_MSTR_NO = LO.CRM_MSTR_NO
AND ((SPON_NO = 16 AND OFFER_DD >= '20140807') OR
(SPON_NO = 111))
)
3. 암호화 관련해서 input이 같아도 output이 달라진다는 말씀이 이해가 안가네요. key, 입력값이 같으면 암호화된 값도 동일하지 않나요? key가 내부에서 바뀌는건지...
SELECT /*+ FULL(A) PARALLEL(A 4) */
SPON_NO,CRM_MSTR_NO,CONTENTS_NO,OFFER_DD,LATELY_AGREE_DT,N,Y,H,OFFER_TM,YY,GENDER,'' FROM(
SELECT '111' SPON_NO, CRM_MSTR_NO, '200002' CONTENTS_NO, SUBSTR('20141113162830',0,8) OFFER_DD, LATELY_AGREE_DT,
(select BLOWFISH_ENC(NAME) from dual) N, (select BLOWFISH_ENC(BIRTH_DAY) from dual) Y, (select BLOWFISH_ENC(HP3) from dual) H,
SUBSTR('20141113162830',9,6) OFFER_TM
, SUBSTR(BIRTH_DAY,0,2) YY
, DECODE(GENDER,'M','1','2') GENDER,
HP KEY_VAL
FROM LC_MEM_MSTR A
WHERE LATELY_AGREE_DT BETWEEN '20121101'||'000000' AND '20141112'||'235959'
AND
BIRTH_DAY BETWEEN '30'||00000 AND '99'||99999
AND HP_AUTH_YN = 'Y'
AND CRM_MSTR_NO IS NOT NULL
AND
EXISTS(
SELECT /*+ INDEX (LC_DGB UK1_LC_DGB )*/ * FROM LC_DGB E
WHERE A.AUTHCODE = E.AUTHCODE
AND
YYYYMM IN (
'200912'
,
'201001'
,
'201002'
,
'201003'
,
'201011'
,
'201104_2'
,
'201110'
,
'201204'
,
'201211'
,
'201307'
)
)
AND NOT EXISTS(
SELECT /*+ UNNEST*/* FROM LC_OFFER LO
WHERE A.CRM_MSTR_NO = LO.CRM_MSTR_NO
AND (
(CONTENTS_NO = 200002 AND SPON_NO = 16 AND OFFER_DD >= '20140807')
OR
(CONTENTS_NO = 200002 AND SPON_NO = 111)
)
)
결과수는 같고요 속도는 엄청 빨라졌습니다.
그리고 저희가 쓰는 blowfish_enc 이 함수는 말씀드린것처럼
select blowfish_enc(1111) from dual 이런식으로 조회했을때 결과값이 매번 다르게
나옵니다.
select blowfish_dec('247fb022e575a9436640eb9972975d209e586c19e34d1fe7') from dual
select blowfish_dec('04cb3ca67e94923c519bb677d2b1fbbe23e51c99fe229bca') from dual
반대로 복호화 했을 시 위 두개의 결과값 1111로 같습니다.
감사합니다.
빨라져서 다행이지만, 병렬 처리가 잘못 풀릴까봐 불안하네요. 개선 여지가 남아있을 수 있구요.
실행시 시스템 상황(배치 window)을 고려하시고, 다른 분들이 말씀해주신 것처럼 실제 실행계획, 통계정보까지 확인한 후 보완하는 것이 좋겠습니다.
grant 권한 줘서 플랜 떠왔습니다. 근데 보는법을 모르겟네요..
PLAN_TABLE_OUTPUT
SQL_ID ga3s9dt5bj6rs, child number 0
-------------------------------------
select /*+ gather_plan_statistics */ * from( SELECT /*+ FULL(A)
PARALLEL(A 4) */ SPON_NO,CRM_MSTR_NO,CONTENTS_NO,OFFER_D
D,LATELY_AGREE_DT,N,Y,H,OFFER_TM,YY,GENDER,'' FROM( SELECT
'111' SPON_NO, CRM_MSTR_NO, '200002' CONTENTS_NO,
SUBSTR('20141113162830',0,8) OFFER_DD, LATELY_AGREE_DT,
(select BLOWFISH_ENC(NAME) from dual) N, (select
BLOWFISH_ENC(BIRTH_DAY) from dual) Y, (select BLOWFISH_ENC(HP3) from
dual) H, SUBSTR('20141113162830',9,6) OFFER_TM
, SUBSTR(BIRTH_DAY,0,2) YY ,
DECODE(GENDER,'M','1','2') GENDER, HP KEY_VAL
FROM LC_MEM_MSTR A WHERE LATELY_AGREE_DT
BETWEEN '20121101'||'000000' AND '20141112'||'235959' AND
BIRTH_DAY BETWEEN '30'||00000 AND '99'||99999
AND HP_AUTH_YN = 'Y' AND CRM_MSTR_NO IS NOT NULL
AND EXISTS( SELECT /*+ INDEX (LC_DGB UK1_LC_DGB )*/ * FROM LC_DGB E
WHERE A.AUTHCODE = E.AUTHCODE
AND
YYYYMM IN (
'200912'
,
'201001'
,
'201002'
,
'201003'
,
'201011'
,
'201104_2'
,
'201110'
,
'201204'
,
'201211'
,
'201307'
)
)
AND NOT EXISTS(
SELECT /*+ UNNEST*/* FROM LC_OFFER LO
WHERE A.CRM_MSTR_NO = LO.CRM_MSTR_NO
AND (
(CONTENTS_NO = 200002 AND SPON_NO = 16 AND OFFER_DD >= '20140807')
OR
(CONTENTS_NO = 200002 AND SPON_NO = 111)
)
)
)
)
Plan hash value: 3916600911
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Starts | E-Rows |E-Bytes|E-Temp | Cost (%CPU)| E-Time | A-Rows | A-Time | Buffers | Reads | OMem | 1Mem | Used-Mem |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 1 | | | | 63686 (100)| | 500 |00:00:01.88 | 82464 | 1279 | | | |
| 1 | FAST DUAL | | 500 | 1 | | | 2 (0)| 00:00:01 | 500 |00:00:00.01 | 0 | 0 | | | |
| 2 | FAST DUAL | | 500 | 1 | | | 2 (0)| 00:00:01 | 500 |00:00:00.01 | 0 | 0 | | | |
| 3 | FAST DUAL | | 500 | 1 | | | 2 (0)| 00:00:01 | 500 |00:00:00.01 | 0 | 0 | | | |
|* 4 | HASH JOIN RIGHT ANTI | | 1 | 493K| 44M| 8320K| 63686 (1)| 00:12:45 | 500 |00:00:01.88 | 82464 | 1279 | 8533K| 3594K| 12M (0)|
|* 5 | INDEX FAST FULL SCAN| XPKLC_OFFER | 1 | 230K| 5620K| | 17973 (1)| 00:03:36 | 234K|00:00:00.91 | 65621 | 0 | | | |
|* 6 | HASH JOIN RIGHT SEMI| | 1 | 538K| 35M| 14M| 43241 (1)| 00:08:39 | 1647 |00:00:00.69 | 16843 | 1279 | 17M| 3482K| 25M (0)|
|* 7 | TABLE ACCESS FULL | LC_DGB | 1 | 580K| 7939K| | 4317 (2)| 00:00:52 | 564K|00:00:00.17 | 15695 | 0 | | | |
|* 8 | TABLE ACCESS FULL | LC_MEM_MSTR | 1 | 2729K| 143M| | 29545 (1)| 00:05:55 | 34075 |00:00:00.02 | 1148 | 1279 | | | |
---------------------------------------------------------------------------------------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$4 / DUAL@SEL$4
2 - SEL$5 / DUAL@SEL$5
3 - SEL$6 / DUAL@SEL$6
4 - SEL$2805CC51
5 - SEL$2805CC51 / LO@SEL$8
7 - SEL$2805CC51 / E@SEL$7
8 - SEL$2805CC51 / A@SEL$3
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
IGNORE_OPTIM_EMBEDDED_HINTS
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
DB_VERSION('11.2.0.1')
ALL_ROWS
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$6")
OUTLINE_LEAF(@"SEL$2805CC51")
UNNEST(@"SEL$7")
UNNEST(@"SEL$8")
OUTLINE(@"SEL$5C160134")
MERGE(@"SEL$335DD26A")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$8")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$335DD26A")
MERGE(@"SEL$3")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$3")
FULL(@"SEL$2805CC51" "A"@"SEL$3")
FULL(@"SEL$2805CC51" "E"@"SEL$7")
INDEX_FFS(@"SEL$2805CC51" "LO"@"SEL$8" ("LC_OFFER"."SPON_NO" "LC_OFFER"."CRM_MSTR_NO" "LC_OFFER"."CONTENTS_NO" "LC_OFFER"."OFFER_DD"))
LEADING(@"SEL$2805CC51" "A"@"SEL$3" "E"@"SEL$7" "LO"@"SEL$8")
USE_HASH(@"SEL$2805CC51" "E"@"SEL$7")
USE_HASH(@"SEL$2805CC51" "LO"@"SEL$8")
PX_JOIN_FILTER(@"SEL$2805CC51" "LO"@"SEL$8")
SWAP_JOIN_INPUTS(@"SEL$2805CC51" "E"@"SEL$7")
SWAP_JOIN_INPUTS(@"SEL$2805CC51" "LO"@"SEL$8")
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."CRM_MSTR_NO"="LO"."CRM_MSTR_NO")
5 - filter((("SPON_NO"=111 AND "CONTENTS_NO"=200002) OR ("SPON_NO"=16 AND "OFFER_DD">='20140807' AND "CONTENTS_NO"=200002)))
6 - access("A"."AUTHCODE"="E"."AUTHCODE")
7 - filter(("YYYYMM"='200912' OR "YYYYMM"='201001' OR "YYYYMM"='201002' OR "YYYYMM"='201003' OR "YYYYMM"='201011' OR "YYYYMM"='201104_2' OR "YYYYMM"='201110'
OR "YYYYMM"='201204' OR "YYYYMM"='201211' OR "YYYYMM"='201307'))
8 - filter(("HP_AUTH_YN"='Y' AND "CRM_MSTR_NO" IS NOT NULL AND "LATELY_AGREE_DT">='20121101000000' AND "BIRTH_DAY">='300' AND
"LATELY_AGREE_DT"<='20141112235959' AND "BIRTH_DAY"<='9999999'))
Column Projection Information (identified by operation id):
-----------------------------------------------------------
4 - (#keys=1) "A"."CRM_MSTR_NO"[NUMBER,22], "NAME"[VARCHAR2,100], "GENDER"[CHARACTER,1], "BIRTH_DAY"[VARCHAR2,8], "HP3"[VARCHAR2,4],
"LATELY_AGREE_DT"[VARCHAR2,14]
5 - "LO"."CRM_MSTR_NO"[NUMBER,22]
6 - (#keys=1) "CRM_MSTR_NO"[NUMBER,22], "NAME"[VARCHAR2,100], "GENDER"[CHARACTER,1], "BIRTH_DAY"[VARCHAR2,8], "HP3"[VARCHAR2,4], "LATELY_AGREE_DT"[VARCHAR2,14]
7 - "E"."AUTHCODE"[NUMBER,22]
8 - "A"."AUTHCODE"[NUMBER,22], "NAME"[VARCHAR2,100], "GENDER"[CHARACTER,1], "BIRTH_DAY"[VARCHAR2,8], "HP3"[VARCHAR2,4], "LATELY_AGREE_DT"[VARCHAR2,14],
"CRM_MSTR_NO"[NUMBER,22]
이제 글이 메인에서 내려갔네요.
힌트 위치가 내부로 가야하는데 밖에 주셔서 병렬처리가 안된 것 같습니다.
그런데도 속도가 빠르다면, (파라미터에 따라 차이가 있겠지만) 굳이 병렬처리가 필요없을 거구요,
위쪽 PARALLEL 힌트 제거하시고, 내부 힌트를 아래와 같이 줘보시거나 인덱스 힌트로 바꿔서도 테스트 해보시고 괜찮은 쪽으로 결정하시면 될 것 같습니다.
SELECT /*+ LEADING(A) FULL(A) */ '111' SPON_NO, ...FROM LC_MEM_MSTR A ... AND EXISTS (SELECT /*+ UNNEST FULL(E) USE_HASH(E) NO_SWAP_JOIN_INPUTS(E) */ * FROM LC_DGB E ... AND NOT EXISTS (SELECT /*+ UNNEST FULL(LO) USE_HASH(LO) NO_SWAP_JOIN_INPUTS(LO) */ * FROM LC_OFFER LO
말씀주신 대로 해보았으나 시간이 기존 12분에서 23분으로 배로 늘었습니다..
PLAN_TABLE_OUTPUT
Plan hash value: 3785370990
SELECT /*+ LEADING(A) FULL(A) */
SPON_NO,CRM_MSTR_NO,CONTENTS_NO,OFFER_D
D,LATELY_AGREE_DT,N,Y,H,OFFER_TM,YY,GENDER,'' FROM( SELECT
'111' SPON_NO, CRM_MSTR_NO, '200002' CONTENTS_NO,
SUBSTR('20141113162830',0,8) OFFER_DD, LATELY_AGREE_DT,
(select BLOWFISH_ENC(NAME) from dual) N, (select
BLOWFISH_ENC(BIRTH_DAY) from dual) Y, (select BLOWFISH_ENC(HP3) from
dual) H, SUBSTR('20141113162830',9,6) OFFER_TM
, SUBSTR(BIRTH_DAY,0,2) YY ,
DECODE(GENDER,'M','1','2') GENDER, HP KEY_VAL
FROM LC_MEM_MSTR A WHERE LATELY_AGREE_DT
BETWEEN '20121101'||'000000' AND '20141112'||'235959' AND
BIRTH_DAY BETWEEN '30'||00000 AND '99'||99999
AND HP_AUTH_YN = 'Y' AND CRM_MSTR_NO IS NOT NULL
AND EXISTS( SELECT /*+ UNNEST FULL(E) USE_HASH(E) NO_SWAP_JOIN_INPUTS(E) */ * FROM LC_DGB E
WHERE A.AUTHCODE = E.AUTHCODE
AND
YYYYMM IN (
'200912'
,
'201001'
,
'201002'
,
'201003'
,
'201011'
,
'201104_2'
,
'201110'
,
'201204'
,
'201211'
,
'201307'
)
)
AND NOT EXISTS(
SELECT /*+ UNNEST FULL(LO) USE_HASH(LO) NO_SWAP_JOIN_INPUTS(LO) */ * FROM LC_OFFER LO
WHERE A.CRM_MSTR_NO = LO.CRM_MSTR_NO
AND (
(CONTENTS_NO = 200002 AND SPON_NO = 16 AND OFFER_DD >= '20140807')
OR
(CONTENTS_NO = 200002 AND SPON_NO = 111)
)
)
)
-------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes |TempSpc| Cost (%CPU)| Time |
-------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | 493K| 44M| | 116K (1)| 00:23:14 |
| 1 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 2 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
| 3 | FAST DUAL | | 1 | | | 2 (0)| 00:00:01 |
|* 4 | HASH JOIN ANTI | | 493K| 44M| 41M| 116K (1)| 00:23:14 |
|* 5 | HASH JOIN SEMI | | 538K| 35M| 174M| 43238 (1)| 00:08:39 |
|* 6 | TABLE ACCESS FULL| LC_MEM_MSTR | 2729K| 143M| | 29545 (1)| 00:05:55 |
|* 7 | TABLE ACCESS FULL| LC_DGB | 580K| 7939K| | 4317 (2)| 00:00:52 |
|* 8 | TABLE ACCESS FULL | LC_OFFER | 230K| 5620K| | 70420 (1)| 00:14:06 |
-------------------------------------------------------------------------------------------
Query Block Name / Object Alias (identified by operation id):
-------------------------------------------------------------
1 - SEL$3 / DUAL@SEL$3
2 - SEL$4 / DUAL@SEL$4
3 - SEL$5 / DUAL@SEL$5
4 - SEL$16C18E5F
6 - SEL$16C18E5F / A@SEL$2
7 - SEL$16C18E5F / E@SEL$6
8 - SEL$16C18E5F / LO@SEL$7
Outline Data
-------------
/*+
BEGIN_OUTLINE_DATA
USE_HASH(@"SEL$16C18E5F" "LO"@"SEL$7")
USE_HASH(@"SEL$16C18E5F" "E"@"SEL$6")
LEADING(@"SEL$16C18E5F" "A"@"SEL$2" "E"@"SEL$6" "LO"@"SEL$7")
FULL(@"SEL$16C18E5F" "LO"@"SEL$7")
FULL(@"SEL$16C18E5F" "E"@"SEL$6")
FULL(@"SEL$16C18E5F" "A"@"SEL$2")
OUTLINE(@"SEL$2")
OUTLINE(@"SEL$1")
OUTLINE(@"SEL$7")
OUTLINE(@"SEL$6")
MERGE(@"SEL$2")
OUTLINE(@"SEL$F5BB74E1")
UNNEST(@"SEL$7")
UNNEST(@"SEL$6")
OUTLINE_LEAF(@"SEL$16C18E5F")
OUTLINE_LEAF(@"SEL$5")
OUTLINE_LEAF(@"SEL$4")
OUTLINE_LEAF(@"SEL$3")
ALL_ROWS
DB_VERSION('11.2.0.1')
OPTIMIZER_FEATURES_ENABLE('11.2.0.1')
IGNORE_OPTIM_EMBEDDED_HINTS
END_OUTLINE_DATA
*/
Predicate Information (identified by operation id):
---------------------------------------------------
4 - access("A"."CRM_MSTR_NO"="LO"."CRM_MSTR_NO")
5 - access("A"."AUTHCODE"="E"."AUTHCODE")
6 - filter("HP_AUTH_YN"='Y' AND "CRM_MSTR_NO" IS NOT NULL AND
"LATELY_AGREE_DT">='20121101000000' AND "BIRTH_DAY">='300' AND
"LATELY_AGREE_DT"<='20141112235959' AND "BIRTH_DAY"<='9999999')
7 - filter("YYYYMM"='200912' OR "YYYYMM"='201001' OR "YYYYMM"='201002' OR
"YYYYMM"='201003' OR "YYYYMM"='201011' OR "YYYYMM"='201104_2' OR "YYYYMM"='201110'
OR "YYYYMM"='201204' OR "YYYYMM"='201211' OR "YYYYMM"='201307')
8 - filter("SPON_NO"=111 AND "CONTENTS_NO"=200002 OR "SPON_NO"=16 AND
"OFFER_DD">='20140807' AND "CONTENTS_NO"=200002)
Column Projection Information (identified by operation id):
-----------------------------------------------------------
4 - (#keys=1) "A"."CRM_MSTR_NO"[NUMBER,22], "NAME"[VARCHAR2,100],
"GENDER"[CHARACTER,1], "BIRTH_DAY"[VARCHAR2,8], "HP3"[VARCHAR2,4],
"LATELY_AGREE_DT"[VARCHAR2,14]
5 - (#keys=1) "CRM_MSTR_NO"[NUMBER,22], "NAME"[VARCHAR2,100],
"GENDER"[CHARACTER,1], "BIRTH_DAY"[VARCHAR2,8], "HP3"[VARCHAR2,4],
"LATELY_AGREE_DT"[VARCHAR2,14]
6 - "A"."AUTHCODE"[NUMBER,22], "NAME"[VARCHAR2,100], "GENDER"[CHARACTER,1],
"BIRTH_DAY"[VARCHAR2,8], "HP3"[VARCHAR2,4], "LATELY_AGREE_DT"[VARCHAR2,14],
"CRM_MSTR_NO"[NUMBER,22]
7 - "E"."AUTHCODE"[NUMBER,22]
8 - "LO"."CRM_MSTR_NO"[NUMBER,22]
LC_MEM_MSTR -> TABLE FULL으로 전체건수 스캔시 5분 으로
LC_MEM_MSTR = LC_DGB 조인시 발생 시간은 8분
LC_OFFER -> FAST FULL 스캔시 3분
해서
(LC_MEM_MSTR + LC_DGB ) = LC_OFFER 조인시 발생시간 12분
-- 목표
1) LC_MEM_MSTR 대상테이블에 대한 조건을 걸어서 생긴 비율을 확인해보세요.
2) LC_MEM_MSTR 먼저 스캔후 exists 조건은 체크조건으로 처리해야되보입니다.
-- 방안1
전체 테이블을 다 INDEX 힌트보다 FULL 힌트를 박으세요
-- 방안2
플랜방향을
TABLE FULL LC_MEM_MSTR
HASH JOIN
TABLE FULL LC_OFFER
TABLE FULL LC_DGB
형태로 만들어보세요.
비주류 님이 말씀해주신방향으로도 해보시면 가능해보입니다.
하지만 LC_OFFER 테이블이 1000만건이라고 정보가 있는데
SWAP조인은 메모리에 LC_OFFER을 메모리에 올려서 해쉬 조인을 하겠따라인데
적은 데이터양의 테이블을 하는게 좋습니다.
그건 쏭님이 판단하시기 바랍니다.
아무리 힌트를 주고 해도 LC_MEM_MSTR 이 테이블이 최상위 트리로 가질 않네요..
좋은 정보 주셨는데 제가 알아듣지를 못하니 답답하네요ㅎㅎ
hash 조인시 build(앞쪽) 가 작은 집합이 되게 바꿔주세요. (LO<E<A 순서로 커지는게 맞나요?)
1) 두개의 NO_SWAP_JOIN_INPUTS -> SWAP_JOIN_INPUTS 해서 위 기준대로 되는지 보시고,
2) LO쪽은 NOT EXISTS 는 기존처럼 분리하여 개별 INDEX 타는게 나은지 지금처럼 OR 로 합치는게 나을지 모르겠지만, 후자라면, 모든 컬럼이 INDEX에 있는 것으로 보이니 FULL(LO) 을 INDEX_FFS(LO (SPON_NO, CRM_MSTR_NO, CONTENTS_NO, OFFER_DD)) 로 바꿔주시면 될 것 같습니다.
3) 최상단의 힌트가 아직 안쪽으로 들어오지 않은 것 같습니다. FULL(A) 라인...
추가로 여기까지 정리되면 parallel 사용 여부를 검토하시면 될 것 같습니다.
) 버튼을 클릭하여 작성 하시면 됩니다.