database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41944 게시물 읽기
No. 41944
ORACLE DB SQL 쿼리 튜닝-4번째[DB 튜닝]
작성자
조장복(cjb1609)
작성일
2023-03-21 11:01
조회수
1,160

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

[Top]
No.
제목
작성자
작성일
조회
41947ORACLE DB SQL 쿼리 튜닝-7번째[DB 튜닝]
조장복
2023-03-21
5173
41946ORACLE DB SQL 쿼리 튜닝-6번째[DB 튜닝]
조장복
2023-03-21
3294
41945ORACLE DB SQL 쿼리 튜닝-5번째[DB 튜닝]
조장복
2023-03-21
3170
41944ORACLE DB SQL 쿼리 튜닝-4번째[DB 튜닝]
조장복
2023-03-21
1160
41943ORACLE DB SQL 쿼리 튜닝-3번째[DB 튜닝]
조장복
2023-03-21
510
41942ORACLE DB SQL 쿼리 튜닝-2번째[DB 튜닝]
조장복
2023-03-21
595
41941ORACLE DB SQL 쿼리 튜닝-1번째[DB 튜닝]
조장복
2023-03-21
528
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.016초, 이곳 서비스는
	PostgreSQL v16.2로 자료를 관리합니다