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 41939 게시물 읽기
No. 41939
ORACLE DB SQL 쿼리 튜닝[DB 튜닝]
작성자
조장복(cjb1609)
작성일
2023-03-21 10:54
조회수
890

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

 

3. SQL 쿼리

3.1 튜닝전 일부 쿼리
FROM SDB_FM_INTG_TRXR_INFO A
              , FM_COMM_DETL_CD B
          WHERE B.COMM_CLSS_CD  = '0104600'
            AND A.INTG_TRXR_DIV = B.COMM_DETL_CD
            AND (A.INTG_TRXR_CD LIKE 'B'|| '%'
 OR A.INTG_TRXR_CD LIKE NVL((SELECT SUBSTR(MNG_ARTC_1,1,1)
 FROM  FM_COMM_DETL_CD X
 ,FM_ORG_MAST Y 
 WHERE X.COMM_DETL_CD = Y.LOCA_DIV 
 AND   X.COMM_CLSS_CD = '0100600'
 AND   Y.ORG_CD = 'B106000390'
 AND   Y.ESTB_DIV = '2'
 AND   Y.SCHL_CLSS_DIV IS NOT NULL 
 AND   Y.ORG_USE_YN = 'Y'),'B') || '%')
AND A.INTG_TRXR_DIV LIKE '06' || '%'
AND A.INTG_TRXR_CD LIKE '' || '%'
AND UPPER(A.INTG_TRXR_NM) LIKE '%' || UPPER('') || '%'
AND NVL(A.USE_YN, 'Y')  LIKE 'Y' || '%'
         ) X ,
         FM_GENE_TRXR_INFO Y ,

 


3.2 튜닝후 일부 쿼리
결합 인덱스 생성 : FM_INTG_TRXR_INFO_N02

SQL> CREATE INDEX FM_INTG_TRXR_INFO_N02 ON SDB_FM_INTG_TRXR_INFO
  2  (INTG_TRXR_DIV, INTG_TRXR_CD)
  3  LOGGING
  4  TABLESPACE TS_FM_I_01
  5  PCTFREE    10
  6  INITRANS   2
  7  MAXTRANS   255
  8  STORAGE    (
  9              INITIAL          64K
 10              MINEXTENTS       1
 11              MAXEXTENTS       2147483645
 12              PCTINCREASE      0
 13              BUFFER_POOL      DEFAULT
 14             )
 15  NOPARALLEL;

Index created.

 


4. 실행 PLAN

4.1 튜닝전
Rows     Row Source Operation
-------  ---------------------------------------------------
    192      NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,142,120 us)
    192       NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,141,541 us)
    192        NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,140,575 us)
    192         NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,139,803 us)
    192          NESTED LOOPS OUTER (cr=33792 pr=33468 pw=0 time=5,138,837 us)
    192           HASH JOIN OUTER (cr=33593 pr=33468 pw=0 time=5,135,546 us)
    192            HASH JOIN  (cr=33590 pr=33468 pw=0 time=2,062,612 us)
      1             TABLE ACCESS BY INDEX ROWID FM_COMM_DETL_CD (cr=3 pr=0 pw=0 time=62 us)
      1              INDEX RANGE SCAN FM_COMM_DETL_CD_PK (cr=2 pr=0 pw=0 time=49 us)(object id 78005)
    192             TABLE ACCESS FULL SDB_FM_INTG_TRXR_INFO (cr=33587 pr=33468 pw=0 time=2,061,879 us)

 


4.2 튜닝 후
Rows     Row Source Operation
-------  ---------------------------------------------------
    192        NESTED LOOPS OUTER (cr=407 pr=5 pw=0 time=8,452 us)
    192         NESTED LOOPS OUTER (cr=407 pr=5 pw=0 time=7,491 us)
    192          NESTED LOOPS OUTER (cr=407 pr=5 pw=0 time=6,911 us)
    192           NESTED LOOPS OUTER (cr=208 pr=5 pw=0 time=4,978 us)
    192            NESTED LOOPS OUTER (cr=208 pr=5 pw=0 time=4,019 us)
    192             NESTED LOOPS  (cr=208 pr=5 pw=0 time=3,056 us)
      1              TABLE ACCESS BY INDEX ROWID FM_COMM_DETL_CD (cr=3 pr=0 pw=0 time=61 us)
      1               INDEX RANGE SCAN FM_COMM_DETL_CD_PK (cr=2 pr=0 pw=0 time=45 us)(object id 78005)
    192              TABLE ACCESS BY INDEX ROWID SDB_FM_INTG_TRXR_INFO (cr=205 pr=5 pw=0 time=2,806 us)
    206               INDEX RANGE SCAN FM_INTG_TRXR_INFO_N02 (cr=11 pr=3 pw=0 time=1100 us)(object id 187319)

 

 

[Top]
No.
제목
작성자
작성일
조회
41942ORACLE DB SQL 쿼리 튜닝-2번째[DB 튜닝]
조장복
2023-03-21
753
41941ORACLE DB SQL 쿼리 튜닝-1번째[DB 튜닝]
조장복
2023-03-21
671
41940ORACLE DB SQL 쿼리 튜닝-1번째[DB 튜닝]
조장복
2023-03-21
1002
41939ORACLE DB SQL 쿼리 튜닝[DB 튜닝]
조장복
2023-03-21
890
41938대량 데이터 SQL 튜닝 및 병렬처리 활용 방법[DB 튜닝 및 Parallel 처리]
조장복
2023-03-21
877
41937병렬처리(Parallel Processing) 관련 사항[병렬 Parallel 처리]
조장복
2023-03-21
79974
41936Oracle 온라인 백업중 HA Failover가 실패하는 이유 및 조치 방법[DB 복구]
조장복
2023-03-21
667
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2024 DSN, All rights reserved.
작업시간: 0.016초, 이곳 서비스는
	PostgreSQL v16.4로 자료를 관리합니다