SQL 실행속도를 개선하고자 합니다. 도움 부탁 드립니다. 0 5 2,010

by 박군two [SQL Query] [2017.05.26 13:56:20]


안녕하세요

처음으로 남기는 글이 문의글이네요..

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가 아닙니다.

 

감사합니다.

by 마농 [2017.05.26 14:19:49]

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')
;

 


by 박군two [2017.05.26 16:00:51]

1, 2번은 적용했고

3번은 ci.oldstring 아  CLOB 형이라 String으로 치환한 것입니다.

 

알려주신 SQL에 ci.oldstring --> TO_CHAR(ci.oldstring) 으로 변경해서 수행해도 비슷하게 12분 정도가 소요되네요

with 구문을 사용한 이후도 ci.oldstring을 치환하는 시간을 단축해 보고자 사용한 것입니다.

 

혹, 다른 방안이 있을까요?


by 마농 [2017.05.26 16:21:31]
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')
;

 


by 박군two [2017.05.26 16:57:47]

마농님 감사합니다.

10 초 정도로 월등하게 성능이 개선되었네요.

사용하신 힌트를 아래처럼 이해하는것이 맞는지요?

 

/*+ LEADING(t) USE_HASH(ec) */

-- LEADING : From 절의 순서와 무관하게 조인시 t 테이블을 Driving 테이블 지정
-- USE_HASH : ec 테이블을 메모리에 올린 후 나머지 테이블과 비교하면서 매칭

/*+ no_merge */

-- ec 테이블을 access 하기전 인라인뷰(t) 를 먼저 생성

 

SQL 작성에 많은 도움이 되네요


by 마농 [2017.05.26 20:27:00]

메모리에 올라가는건 t 입니다.

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입