ORACLE DB SQL 쿼리 튜닝을 통한 처리속도 저하 요인을 파악하고 개선한 사항 참조하세요[DB 튜닝]

3. SQL 쿼리

3.1 튜닝전 일부 쿼리
SELECT  /*+ A A.REQ_DT */ A.ORG_C,
              E.C_KOR_NM,
              F.C_KOR_NM,
<§>
FROM     PROXY.TIA_DQ_SQLNET_LOG A,     
            MIDDLEMAN2.TIA_DQ_C_INFO B, 
            PROXY.TIA_DQ_SESSION_LOG G, 
            MIDDLEMAN2.TIA_DQ_USER I,  
            PROXY.TIA_DQ_DBMS J,
 <§>
WHERE  A.ORG_C = B.ORG_C(+)
AND  A.ORG_C = D.ORG_C(+)
AND  A.ORG_C = E.ORG_C(+)
AND  A.ORG_C = F.ORG_C(+)
AND  A.ORG_C = G.ORG_C
AND  A.ORG_C = I.ORG_C(+)
AND  G.ORG_C = J.ORG_C(+)
AND  A.ORG_C = K.ORG_C(+)
AND  A.QUERY_TYPE = B.C(+)
AND  B.C_ID(+) = 'QUERY_TC‘
AND A.REQ_DT   BETWEEN   
                   TO_DATE('20090202000000','YYYYMMDDHH24MISS')
             AND TO_DATE('20090202100000','YYYYMMDDHH24MISS')
 <§>

3.2 튜닝후 일부 쿼리
SELECT     A.ORG_C,
              E.C_KOR_NM,
              F.C_KOR_NM,
<§>
FROM     PROXY.TIA_DQ_SQLNET_LOG_TEMP A,     
            MIDDLEMAN2.TIA_DQ_C_INFO B, 
            PROXY.TIA_DQ_SESSION_LOG G, 
            MIDDLEMAN2.TIA_DQ_USER I,  
            PROXY.TIA_DQ_DBMS J,
 <§>
WHERE  A.ORG_C = B.ORG_C
AND  A.ORG_C = D.ORG_C
AND  A.ORG_C = E.ORG_C
AND  A.ORG_C = F.ORG_C
AND  A.ORG_C = G.ORG_C
AND  A.ORG_C = I.ORG_C
AND  G.ORG_C = J.ORG_C
AND  A.ORG_C = K.ORG_C
AND  A.QUERY_TYPE = B.C
AND  B.C_ID = 'QUERY_TC'
AND A.REQ_DT   BETWEEN
                   TO_DATE('20090202000000','YYYYMMDDHH24MISS')
             AND TO_DATE('20090202100000','YYYYMMDDHH24MISS')
 <§>

4. 실행 PLAN

4.1 튜닝전
Rows     Execution Plan
-------  -------------------------------------------
    757  SORT ORDER BY
    757   HASH JOIN RIGHT OUTER
     25    TABLE ACCESS FULL TIA_DQ_C_INFO
    757    HASH JOIN RIGHT OUTER
      3     TABLE ACCESS FULL TIA_DQ_C_INFO
    757     HASH JOIN RIGHT OUTER
      1      TABLE ACCESS BY INDEX ROWID TIA_DQ_C_INFO
      1       INDEX UNIQUE SCAN PK_TIA_DQ_C_INFO
    757      HASH JOIN RIGHT OUTER
      2       TABLE ACCESS FULL TIA_DQ_C_INFO
    757       HASH JOIN RIGHT OUTER
    193        TABLE ACCESS FULL TIA_DQ_USER
    757        HASH JOIN RIGHT OUTER
      2         TABLE ACCESS FULL TIA_DQ_C_INFO
    757         HASH JOIN RIGHT OUTER
     10          TABLE ACCESS FULL TIA_DQ_DBMS
    757          HASH JOIN
    757           TABLE ACCESS FULL TIA_DQ_SQLNET_LOG
 682620           TABLE ACCESS FULL TIA_DQ_SESSION_LOG

4.2 튜닝 후
Rows     Execution Plan
-------  -------------------------------------------
    757  SORT ORDER BY
    757   FILTER 
    757    HASH JOIN OUTER
    757     NESTED LOOPS 
    757      FILTER
    757       HASH JOIN OUTER
    757        NESTED LOOPS
    757         NESTED LOOPS 
    757          HASH JOIN 
      3           TABLE ACCESS FULL TIA_DQ_C_INFO
    757           HASH JOIN
     25            TABLE ACCESS FULL TIA_DQ_C_INFO
    757            NESTED LOOPS 
      1             TABLE ACCESS BY INDEX ROWID TIA_DQ_C_INFO
      1              INDEX UNIQUE SCAN PK_TIA_DQ_C_INFO
    757            TABLE ACCESS BY INDEX ROWID TIA_DQ_SQLNET_LOG_TEMP
    757              INDEX RANGE SCAN REQ_DT_SQLNET_LOG_TEMP
    757          TABLE ACCESS BY INDEX ROWID TIA_DQ_C_INFO
    757           INDEX UNIQUE SCAN PK_TIA_DQ_C_INFO
    757         TABLE ACCESS BY INDEX ROWID TIA_DQ_C_INFO
    757          INDEX UNIQUE SCAN PK_TIA_DQ_C_INFO
    193        TABLE ACCESS FULL TIA_DQ_USER
    757      TABLE ACCESS BY INDEX ROWID TIA_DQ_SESSION_LOG
    757       INDEX UNIQUE SCAN PK_TIA_DQ_SESSION_LOG
     10     TABLE ACCESS FULL TIA_DQ_DBMS