데이터베이스 질문과 답변
안녕하세요
처음으로 남기는 글이 문의글이네요..
SQL 실행속도가 너무 느려서 도움을 구하고자 합니다.
두개의 SQL 각각은 빠르게 수행되지만, WITH 절을 이용해서 조인할 경우에는 엄청나게 속도가 느려지네요..
아래는 각각의 SQL 과 PLAN 정보입니다.
1. 업무시스템에서 'Key' 정보가 변경된 이력이 있는 데이터를 조회합니다.
SELECT TO_CHAR(CI.OLDSTRING) AS FROM_CSR,
JI.PKEY TO_CSR
FROM CHANGEGROUP CG,
CHANGEITEM CI,
JIRAISSUE JI
WHERE JI.ID = CG.ISSUEID
AND CG.ID = CI.GROUPID
AND CI.FIELD = 'Key'
AND TRUNC(JI.CREATED) >= '2016-01-01'
| Description | Object owner | Object name | Cost | Cardinality | Bytes |
| SELECT STATEMENT, GOAL = FIRST_ROWS | 1,125 | 23 | 2,507 | ||
| NESTED LOOPS | 1,125 | 23 | 2,507 | ||
| NESTED LOOPS | 801 | 324 | 27,540 | ||
| TABLE ACCESS BY INDEX ROWID | ****** | CHANGEITEM | 164 | 324 | 24,300 |
| INDEX RANGE SCAN | ****** | CHGITEM_FIELD | 5 | 327 | |
| TABLE ACCESS BY INDEX ROWID | ****** | CHANGEGROUP | 2 | 1 | 10 |
| INDEX UNIQUE SCAN | ****** | PK_CHANGEGROUP | 1 | 1 | |
| TABLE ACCESS BY INDEX ROWID | ****** | JIRAISSUE | 1 | 1 | 24 |
| INDEX UNIQUE SCAN | ****** | PK_JIRAISSUE | 0 | 1 |
2. 'Key' 정보가 변경되기 이전의 key 에 등록된 Effort 를 조회하기 위해 전체 Effort 를 조회 합니다.
SELECT EC.USERNAME, EC.EFFORT_DATE, EC.EFFORT_TIME FROM EFFORT_CSR EC WHERE EC.EFFORT_TIME IS NOT NULL AND TRUNC(EC.EFFORT_DATE) >= '2016-01-01'
| Description | Object owner | Object name | Cost | Cardinality | Bytes |
| SELECT STATEMENT, GOAL = FIRST_ROWS | 1,712 | 19,525 | 644,325 | ||
| TABLE ACCESS FULL | ****** | EFFORT_CSR | 1,712 | 19,525 | 644,325 |
3. 'Key' 정보가 변경된적이 있는 데이터에 등록된 Effort 만 조회합니다.
WITH T AS (
SELECT TO_CHAR(CI.OLDSTRING) AS FROM_CSR,
JI.PKEY TO_CSR
FROM CHANGEGROUP CG,
CHANGEITEM CI,
JIRAISSUE JI
WHERE JI.ID = CG.ISSUEID
AND CG.ID = CI.GROUPID
AND CI.FIELD = 'Key'
AND TRUNC(JI.CREATED) >= '2016-01-01'
)
SELECT T.TO_CSR, T.FROM_CSR, EC.USERNAME, EC.EFFORT_DATE, EC.EFFORT_TIME
FROM EFFORT_CSR EC, T
WHERE EC.EFFORT_TIME IS NOT NULL
AND EC.PKEY = T.FROM_CSR
AND TRUNC(EC.EFFORT_DATE) >= '2016-01-01'
| Description | Object owner | Object name | Cost | Cardinality | Bytes |
| SELECT STATEMENT, GOAL = FIRST_ROWS | 3,186,237 | 14 | 2,142 | ||
| NESTED LOOPS | 3,186,237 | 14 | 2,142 | ||
| NESTED LOOPS | 3,186,039 | 197 | 25,413 | ||
| NESTED LOOPS | 3,185,652 | 197 | 23,443 | ||
| TABLE ACCESS FULL | ****** | EFFORT_CSR | 1,712 | 19,525 | 859,100 |
| TABLE ACCESS BY INDEX ROWID | ****** | CHANGEITEM | 163 | 1 | 75 |
| INDEX RANGE SCAN | ****** | CHGITEM_FIELD | 4 | 327 | |
| TABLE ACCESS BY INDEX ROWID | ****** | CHANGEGROUP | 2 | 1 | 10 |
| INDEX UNIQUE SCAN | ****** | PK_CHANGEGROUP | 1 | 1 | |
| TABLE ACCESS BY INDEX ROWID | ****** | JIRAISSUE | 1 | 1 | 24 |
| INDEX UNIQUE SCAN | ****** | PK_JIRAISSUE | 0 | 1 |
3번이 문제 입니다.
다른 복잡한 SQL을 실행할때도 어느정도 20~30초 정도면 데이터 조회가 되었는데 3번 SQL은 10분 가량 시간이 소요되네요.
특별히 더 추가해 줄만한 조건도 없는데 어떻게 개선을 해야 할까요?
참고적으로 데이터 건수는
EFFORT_CSR 테이블은 43만여건 -- SQL에서 사용하는 PKEY는 primary key가 아닙니다.
감사합니다.
1. 조건 줄 때 컬럼을 가공하지 말고 조건을 변형하세요. TRUNC(JI.CREATED)
2. 굳이 WITH 문 쓸 필요 없구요.
3. 불필요한 함수를 제거하세요. TO_CHAR
SELECT ci.oldstring AS from_csr
, ji.pkey AS to_csr
, ec.username
, ec.effort_date
, ec.effort_time
FROM changegroup cg
, changeitem ci
, jiraissue ji
, effort_csr ec
WHERE ji.id = cg.issueid
AND cg.id = ci.groupid
AND ci.field = 'Key'
AND ji.created >= TO_DATE('2016-01-01', 'yyyy-mm-dd')
AND ec.pkey = ci.oldstring
AND ec.effort_time IS NOT NULL
AND ec.effort_date >= TO_DATE('2016-01-01', 'yyyy-mm-dd')
;
1, 2번은 적용했고
3번은 ci.oldstring 아 CLOB 형이라 String으로 치환한 것입니다.
알려주신 SQL에 ci.oldstring --> TO_CHAR(ci.oldstring) 으로 변경해서 수행해도 비슷하게 12분 정도가 소요되네요
with 구문을 사용한 이후도 ci.oldstring을 치환하는 시간을 단축해 보고자 사용한 것입니다.
혹, 다른 방안이 있을까요?
SELECT /*+ LEADING(t) USE_HASH(ec) */
t.from_csr
, t.to_csr
, ec.username
, ec.effort_date
, ec.effort_time
FROM (SELECT /*+ no_merge */
TO_CHAR(ci.oldstring) AS from_csr
, ji.pkey AS to_csr
FROM changegroup cg
, changeitem ci
, jiraissue ji
WHERE ji.id = cg.issueid
AND cg.id = ci.groupid
AND ci.field = 'Key'
AND ji.created >= TO_DATE('2016-01-01', 'yyyy-mm-dd')
) t
, effort_csr ec
WHERE ec.pkey = t.oldstring
AND ec.effort_time IS NOT NULL
AND ec.effort_date >= TO_DATE('2016-01-01', 'yyyy-mm-dd')
;
마농님 감사합니다.
10 초 정도로 월등하게 성능이 개선되었네요.
사용하신 힌트를 아래처럼 이해하는것이 맞는지요?
/*+ LEADING(t) USE_HASH(ec) */
-- LEADING : From 절의 순서와 무관하게 조인시 t 테이블을 Driving 테이블 지정
-- USE_HASH : ec 테이블을 메모리에 올린 후 나머지 테이블과 비교하면서 매칭
/*+ no_merge */
-- ec 테이블을 access 하기전 인라인뷰(t) 를 먼저 생성
SQL 작성에 많은 도움이 되네요
메모리에 올라가는건 t 입니다.
) 버튼을 클릭하여 작성 하시면 됩니다.