나의 향기에 젖다...

1. before SQL & plan

DELETE      TIMAGECOMPLETE
      WHERE (OWNERID, ORDERID) IN (
               (SELECT OWNERID, ORDERID
                  FROM TIMAGECOMPLETE
                INTERSECT
                SELECT OWNERID, ORDERID
                  FROM TTASKTRANSHI)
               MINUS
               SELECT OWNERID, ORDERID
                 FROM TTASKTRANS)
Execution Plan
--------------------------------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=260K Card=3K Bytes=108K)
   1    0   DELETE OF 'TIMAGECOMPLETE'
   2    1     NESTED LOOPS (Cost=260K Card=3K Bytes=108K)
   3    2       VIEW OF 'SYS.VW_NSO_1' (Cost=260K Card=3K Bytes=62K)
   4    3         MINUS
   5    4           INTERSECTION
   6    5             SORT (UNIQUE) (Card=3K Bytes=46K)
   7    6               INDEX (FAST FULL SCAN) OF 'TIMAGECOMPLETE_PK' (UNIQUE) (Cost=3 Card=3K Bytes=46K)
   8    5             SORT (UNIQUE) (Card=32M Bytes=549M)
   9    8               TABLE ACCESS (FULL) OF 'TTASKTRANSHI' (Cost=133K Card=32M Bytes=549M)
  10    4           SORT (UNIQUE) (Card=378K Bytes=6M)
  11   10             TABLE ACCESS (FULL) OF 'TTASKTRANS' (Cost=2K Card=378K Bytes=6M)
  12    2       INDEX (UNIQUE SCAN) OF 'TIMAGECOMPLETE_PK' (UNIQUE) (Card=1 Bytes=18)
 

1.1 문제점 및 해결책

  - INTERSECT 와 MINUS 인해 TTASKTRANSHI, TTASKTRANS 테이블에 대해  full table scan이 발생하여 성능저하 현상이 발생과 동시에 Lock holding이 발생함.
  - INTERSECT와 MINUS를 join 및 not exists로 변경하여 성능개선 가능.
  - 업무담당자와 협의 및 데이터 검증 필요.
  
 
2. After SQL & plan

DELETE      TIMAGECOMPLETE
      WHERE (OWNERID, ORDERID) IN (
               SELECT A.OWNERID, A.ORDERID
                 FROM TIMAGECOMPLETE A, TTASKTRANSHI B
                WHERE 1 = 1
                  AND A.OWNERID = B.OWNERID
                  AND A.ORDERID = B.ORDERID
                  AND NOT EXISTS (
                         SELECT 1
                           FROM TTASKTRANS C
                          WHERE 1 = 1
                            AND A.OWNERID = C.OWNERID
                            AND A.ORDERID = C.ORDERID))
                       
Execution Plan
--------------------------------------------------------------------------------
   0      DELETE STATEMENT Optimizer=CHOOSE (Cost=8K Card=1 Bytes=42)
   1    0   DELETE OF 'TIMAGECOMPLETE'
   2    1     MERGE JOIN (SEMI) (Cost=8K Card=1 Bytes=42)
   3    2       INDEX (FULL SCAN) OF 'TIMAGECOMPLETE_PK' (UNIQUE) (Cost=12 Card=3K Bytes=46K)
   4    2       SORT (UNIQUE) (Cost=8K Card=2M Bytes=37M)
   5    4         VIEW OF 'SYS.VW_NSO_1' (Cost=5M Card=2M Bytes=37M)
   6    5           FILTER
   7    6             NESTED LOOPS (Cost=136 Card=2M Bytes=55M)
   8    7               INDEX (FAST FULL SCAN) OF 'TIMAGECOMPLETE_PK' (UNIQUE) (Cost=3 Card=132 Bytes=2K)
   9    7               INDEX (RANGE SCAN) OF 'TTASKTRANSHI_IDX02' (NON-UNIQUE) (Cost=3 Card=12K Bytes=214K)
  10    6             INDEX (RANGE SCAN) OF 'TTASKTRANS_IDX02' (NON-UNIQUE) (Cost=3 Card=1 Bytes=18)

## 테이블 사이즈 조회 쿼리

SELECT A.SEGMENT_NAME,
             ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB",

             ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB",
             A.SEGMENT_TYPE
  FROM DBA_SEGMENTS A,
            DBA_TABLES B
WHERE A.SEGMENT_NAME = B.TABLE_NAME
     AND A.SEGMENT_TYPE IN ('TABLE','TABLE PARTITION')
     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE
 ORDER BY 2 DESC;

## 인덱스 사이즈 조회 쿼리

SELECT A.SEGMENT_NAME,
             ROUND(SUM(A.BYTES)/1024/1024) "SIZE_MB",

             ROUND(SUM(A.BYTES)/1024/1024/1024) "SIZE_GB",
             A.SEGMENT_TYPE
  FROM DBA_SEGMENTS A,
            DBA_INDEXES B
WHERE A.SEGMENT_NAME = B.INDEX_NAME
     AND A.SEGMENT_TYPE IN ('INDEX','INDEX PARTITION')
     AND A.OWNER = '유저아이디'
 GROUP BY A.SEGMENT_NAME, A.SEGMENT_TYPE
 ORDER BY 2 DESC;

*** TABLE PARTITION

 세그먼트가 분할된 테이블을 말한다.

 분할에 사용되는 정보입력은 Value 이며 이는 Hash / Range / List 등으로 나뉘어진다.

// TABLESPACE_NAME 별로 합처리

SELECT  SUBSTR(A.TABLESPACE_NAME,1,30) TABLESPACE,
              ROUND(SUM(A.TOTAL1)/1024/1024,1) "TOTALMB",
              ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1) "USEDMB",
              ROUND(SUM(A.SUM1)/1024/1024,1) "FREEMB",
              ROUND((ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1))

              /ROUND(SUM(A.TOTAL1)/1024/1024,1)*100,2) "USED%"
  FROM
             (SELECT  TABLESPACE_NAME,0 TOTAL1,SUM(BYTES) SUM1,MAX(BYTES) MAXB,

                            COUNT(BYTES) CNT
                 FROM  DBA_FREE_SPACE
               GROUP  BY TABLESPACE_NAME
              UNION
              SELECT  TABLESPACE_NAME,SUM(BYTES) TOTAL1,0,0,0
                 FROM  DBA_DATA_FILES
               GROUP  BY TABLESPACE_NAME) A
GROUP BY A.TABLESPACE_NAME
ORDER BY TABLESPACE;

// 전체 합처리

SELECT  ROUND(SUM(A.TOTAL1)/1024/1024/1024,1) "TOTALGB",
              ROUND(SUM(A.TOTAL1)/1024/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024/1024,1) "USEDGB",
              ROUND(SUM(A.SUM1)/1024/1024/1024,1) "FREEGB",
              ROUND((ROUND(SUM(A.TOTAL1)/1024/1024,1)-ROUND(SUM(A.SUM1)/1024/1024,1))

              /ROUND(SUM(A.TOTAL1)/1024/1024,1)*100,2) "USED%"
  FROM
             (SELECT  TABLESPACE_NAME,0 TOTAL1,SUM(BYTES) SUM1,MAX(BYTES) MAXB,

                            COUNT(BYTES) CNT
                 FROM  DBA_FREE_SPACE
               GROUP BY TABLESPACE_NAME
              UNION
              SELECT  TABLESPACE_NAME,SUM(BYTES) TOTAL1,0,0,0
                 FROM  DBA_DATA_FILES
               GROUP BY TABLESPACE_NAME) A