DATOR


NESTED LOOPS JOIN 의 RANDOM ACCESS 비효율 줄이기. 튜닝[SQL]


아래와 같은 SQL 문장이 있습니다.

간단하게 재 작성한 SQL 입니다.

조회 조건은 A TABLE 에도 있고 B TABLE은 LIKE 조건이 있습니다.

온라인 시스템에서 하루 또는 몇일 기준으로 조회하는 SQL 입니다.

 

SELECT 
       A.SP_YMD,
       A.TRNO,        
       A.RMK,
       B.INTG_ARTC_NO,
       B.INOUTUT_CD,
       B.SPRC,
       B.VAT,
       B.STD_UAMT,
       A.RTGS_RSN_CD......
  FROM SA_SP_MST       A,
       AA_SP_DTL               B,
       AA_IO_SAL_REL       C,
       AA_IO_MST               D
  WHERE A.CO_DVCD           = :as_co_dvcd
   AND :as_dflag           = 'S'
   AND A.TRNO           LIKE :as_trno
   AND A.TRCO_MDEPT_SQ  LIKE :as_mdept_sq
   AND A.SP_YMD      BETWEEN :as_fdate AND :as_tdate
   AND NVL(A.DEL_YN, 'N')  = 'N'
   AND A.CO_DVCD           = B.CO_DVCD
   AND A.SP_YMD            = B.SP_YMD
   AND A.GHQ_NO            = B.GHQ_NO
   AND A.SP_NO             = B.SP_NO
   AND B.TXBL_TYPE_DVCD LIKE :as_isvat
   AND B.CO_DVCD           = C.CO_DVCD
   AND B.SP_YMD            = C.SP_YMD
   AND B.GHQ_NO            = C.GHQ_NO
   AND B.SP_NO             = C.SP_NO
   AND B.SP_SQ             = C.SP_SQ
   AND C.CO_DVCD           = D.CO_DVCD
   AND C.IO_NO             = D.IO_NO

call         count         cpu    elapsed        disk       query    current        rows
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Parse            1        0.00       0.00           0           0          0           0
Execute          1        0.02       0.02           0           2          0           0
Fetch           97        0.85      40.58        8310       58758          0        2393
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total           99        0.87      40.59        8310       58760          0        2393

Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 62  (UHATIS)

Rows     Row Source Operation
-------  ---------------------------------------------------
   2393  FILTER  (cr=58758 pr=8310 pw=0 time=1283368891 us)
   2393   NESTED LOOPS  (cr=58758 pr=8310 pw=0 time=1283368887 us)
   7120    NESTED LOOPS  (cr=37301 pr=8303 pw=0 time=49970665 us)
   7120     NESTED LOOPS  (cr=15684 pr=8238 pw=0 time=49856734 us)
   2054      TABLE ACCESS BY INDEX ROWID SA_SP_MST (cr=2169 pr=1272 pw=0 time=7103579 us)
   2054       INDEX RANGE SCAN SA_SP_MST_IX01 (cr=115 pr=18 pw=0 time=6212 us)(object id 406566)
   7120      TABLE ACCESS BY INDEX ROWID SA_SP_DTL (cr=13515 pr=6966 pw=0 time=32525345 us)   <--- 1) 이부분 감소가 핵심...
   7120       INDEX RANGE SCAN SA_SP_DTL_PK (cr=6395 pr=41 pw=0 time=27125 us)(object id 407338)
   7120     INDEX RANGE SCAN SA_IO_SAL_REL_PK (cr=21617 pr=65 pw=0 time=152015 us)(object id 407435)
   2393    INDEX UNIQUE SCAN SA_IO_MST_PK (cr=21457 pr=7 pw=0 time=94590 us)(object id 407033)

결과는 2393건 입니다.

그렇치만 가장 큰 테이블인 1) 번 부분을 ACCESS 할때 가장 많은 비용이 발생하고 있습니다.

이부분을 줄이는데 목적이 있습니다.

 

아래의 SQL 을 보시면...

 

SELECT
        X.SP_YMD,
        X.TRNO,
        A0.SP_NO,
        A0.SP_DVCD,
        X.IO_NO,
        D0.IO_YMD,
        A0.RMK,
        X.INTG_ARTC_NO,
        B0.INOUTUT_CD,
        B0.SAL_QTY,
        B0.FXD_SAL_UAMT,
        B0.SPRC,
        B0.VAT,
        B0.STD_UAMT,
        A0.RTGS_RSN_CD
  FROM
(
  SELECT /*+ ORDERED USE_NL(A B C D) */
       A.ROWID ARI,
       B.ROWID BRI,
       D.ROWID DRI,
       A.CO_DVCD,
       A.TRNO,
       A.TRCO_MDEPT_SQ,
       A.SP_YMD,
       SUBSTR(D.IO_NO, 11, 20) IO_NO,
       B.INTG_ARTC_NO
  FROM SA_SP_MST           A,
       AA_SP_DTL                   B,
       AA_IO_SAL_REL          C,
       AA_IO_MST                  D
 WHERE A.CO_DVCD           = :as_co_dvcd
   AND :as_dflag           = 'S'
   AND A.SP_YMD      BETWEEN :as_fdate AND :as_tdate
   AND C.CO_DVCD           = D.CO_DVCD
   AND C.IO_NO             = D.IO_NO
   AND A.TRNO           LIKE :as_trno
   AND A.TRCO_MDEPT_SQ  LIKE :as_mdept_sq
   AND NVL(A.DEL_YN, 'N')  = 'N'
   AND A.CO_DVCD           = B.CO_DVCD
   AND A.SP_YMD            = B.SP_YMD
   AND A.GHQ_NO            = B.GHQ_NO
   AND A.SP_NO             = B.SP_NO
--   AND B.TXBL_TYPE_DVCD LIKE :as_isvat
   AND B.CO_DVCD           = C.CO_DVCD
   AND B.SP_YMD            = C.SP_YMD
   AND B.GHQ_NO            = C.GHQ_NO
   AND B.SP_NO             = C.SP_NO
   AND B.SP_SQ             = C.SP_SQ
   ) X,
   AA_SP_MST           A0,
   AA_SP_DTL           B0,
   AA_IO_MST           D0
WHERE B0.TXBL_TYPE_DVCD LIKE :as_isvat   <---- 1)  조인이후에 FILTER 역할만 함. 
  AND X.ARI = A0.ROWID
  AND X.BRI = B0.ROWID
  AND X.DRI = D0.ROWID

call         count         cpu    elapsed        disk       query    current        rows
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Parse            1        0.00       0.00           0           0          0           0
Execute          1        0.02       0.01           0           4          0           0
Fetch           97        0.24       0.26        3641       56785          0        2393
------- ----------  ---------- ---------- ----------- ----------- ----------  ----------
Total           99        0.26       0.27        3641       56789          0        2393

Misses in library cache during parse: 0
Optimizer goal: All_Rows
Parsing user id: 62  (UHATIS)

Rows     Row Source Operation
-------  ---------------------------------------------------
   2393  FILTER  (cr=56785 pr=3641 pw=0 time=4038500 us)
   2393   NESTED LOOPS  (cr=56785 pr=3641 pw=0 time=4038495 us)
   2393    NESTED LOOPS  (cr=54392 pr=3142 pw=0 time=3818393 us)
   2393     NESTED LOOPS  (cr=51999 pr=761 pw=0 time=3744203 us)
   2393      NESTED LOOPS  (cr=49606 pr=170 pw=0 time=3715447 us)
   7120       NESTED LOOPS  (cr=28149 pr=157 pw=0 time=93071 us)
   7120        NESTED LOOPS  (cr=6532 pr=88 pw=0 time=28863 us)
   2054         INDEX RANGE SCAN AA_SP_MST_IX01 (cr=115 pr=21 pw=0 time=4339 us)(object id 406566)
   7120         INDEX RANGE SCAN AA_SP_DTL_IX02 (cr=6417 pr=67 pw=0 time=21697 us)(object id 406567)
   7120        INDEX RANGE SCAN AA_IO_SAL_REL_PK (cr=21617 pr=69 pw=0 time=57436 us)(object id 407435)
   2393       INDEX UNIQUE SCAN AA_IO_MST_PK (cr=21457 pr=13 pw=0 time=33944 us)(object id 407033)  <---  1) 이부분에서 최종 조회 건수가 결정됨.
   2393      TABLE ACCESS BY USER ROWID SA_SP_MST (cr=2393 pr=591 pw=0 time=23892 us)    <---- 2)  테이블 RANDOM ACCESS 는 최종 건수에서만 수행됨.
   2393     TABLE ACCESS BY USER ROWID SA_SP_DTL (cr=2393 pr=2381 pw=0 time=72800 us)
   2393    TABLE ACCESS BY USER ROWID SA_IO_MST (cr=2393 pr=499 pw=0 time=24286 us)

 

변경된 SQL의 핵심은 JOIN 은 인덱스만 가지고 수행되며 SELECT LIST 절의 칼럼들은 JOIN 된 ROW들의 ROWID 를 가지고

테이블을 ACCESS 하는 것입니다.

 

또한 인덱스에 없는 칼럼들이 조회 조건에 있는경우도 먼저 테이블에서 FILTER 처리하지 않고 가장 범위를 많이 줄인 상태에서

FILTER 되도록 할수 있습니다. 1) 

 

 

 

Tag :

Leave Comments