ORACLE DB DELETE / SHRINK SPACE

✍ 금요일 오후 에러 발생했음. 

 

☁ Tomcat로그를 보니 응답은 성공으로 주지만 소요 시간이 최소 10초 이상이 걸리고있는 상황이었다.

☁ 이전에는 잘 동작하더니 갑자기 느려지는 상황..

☁ 특히 오래걸리는 API를 살펴봤고 LOG를 기록하는 테이블에 접근하기만 해도 속도가 현저히 떨어지는 현상이 있었다.

 

☁ DB와 관련된 문제임을 확인하고 바로 DB 상태를 체크. 

8천만 건의 데이터 + 10G의 양을 차지하고있다.

 

☁ 전에도 이런 현상이 있던 적이 있어서, 

☁ 1. 인덱스를 설정한 값이 잘못되었는지 체크.

☁ 2. API에서 쓰는 쿼리가 지나치게 속도를 많이 잡아먹는 지 체크. 

 

✍ 위 두가지 상황은 아니었고, 2021년도 자료를 백업하고 테이블을 정리하려 했다. 

✍ 자주 수정이 일어나는 데이터가 많은 테이블이면서, 또 INSERT를 많이 처리하는 테이블이다. 

 

☁ 대용량 배치 작업이 X ,  스케쥴러가 한 번에 마구 돌아가는 일 이 없다면 

☁ 쿼리가 갑자기 느려지는 경우는 아래와 같이 생길 수 있다고 한다. 

 

출처:https://dataonair.or.kr/db-tech-reference/d-guide/dbms-2/?mod=document&uid=62485

☁ 이제와 생각해보니, 해당 에러가 났을 때에 5번,6번 항목도 체크했어야 했다. 

☁ 실시간으로 작업이 밀리면 안되는 상황이라 당황했고, 금요일 오후라 그런지 식은땀이 났다.

 

☁ 다음에 비슷한 상황이 온다면, 먼저 체크 할 항목을 구분해 놓는 것이 좋겠다. 

 

1. 디스크 사용량이 100%에 근접했는가?

2. 인덱스가 설정되어있는가?

3. 데이터파일의 파일 증가 설정값이 설정되어있는가?  ( ++ 작업수행에 필요한 공간 )

4. 너무 많은  DATA를 들고 있는가? 

 

5. 테이블이 락이 걸려있는가? ( 이번에 난 에러 및 이 경우도 포함 되었음. ) 

 

---

✍ 테이블 스페이스 용량 ::

전체 테이블 스페이스 경로 + 용량 // 출처 :: https://coding-factory.tistory.com/411
SELECT    
A.TABLESPACE_NAME "테이블스페이스명",
A.FILE_NAME "파일경로",
(A.BYTES - B.FREE)    "사용공간",
B.FREE                "여유 공간",
A.BYTES               "총크기",
TO_CHAR( (B.FREE / A.BYTES * 100) , '999.99')||'%' "여유공간"
FROM
(
    SELECT FILE_ID,
    TABLESPACE_NAME,
    FILE_NAME,
    SUBSTR(FILE_NAME,1,200) FILE_NM,
    SUM(BYTES) BYTES
    FROM DBA_DATA_FILES
    GROUP BY FILE_ID,TABLESPACE_NAME,FILE_NAME,SUBSTR(FILE_NAME,1,200)
)A,
(
    SELECT TABLESPACE_NAME,
    FILE_ID,
    SUM(NVL(BYTES,0)) FREE
    FROM DBA_FREE_SPACE
    GROUP BY TABLESPACE_NAME,FILE_ID
)B
WHERE A.TABLESPACE_NAME=B.TABLESPACE_NAME
AND A.FILE_ID = B.FILE_ID;

$ 테이블스페이스 용량을 변경할 경우.

✍   $ ALTER TABELSPACE ' 테이블 스페이스 명 ' ADD DATAFILE ' 데이타 파일 명 ' SIZE 1024M;

 

$ 테이블스페이스 용량이 다 찰 경우 자동으로 증가 시킬 경우. 

✍   $ ALTER DATABASE DATAFILE ' 데이타 파일 경로 ' AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED;

 

---

✍ 인덱스 설정 여부 ::

DBEAVER와 같은 툴에서도 확인 가능.

 

추가하는 방법은 생략.

 

✍ 테이블 락 여부 ::

 

☁ 간단한 조회 / 업데이트 쿼리문이 아예 먹히질 않는 상황이었다. --> 락이 걸렸나 의심하게 됨.

 

☁ v$locked_object 테이블을 찾지 못하는 경우라면, DB 접속 계정의 권한을 의심해야 한다.

 

안죽을 수도 있나보다

✍ 나는 관리자 계정을 가지고 있지 못한다. 

✍ 관리자에게 Table Lock이 걸린것 같으니 확인해달라고 요청했고, 3개의 세션의 Lock을 죽여달라고 요청했다.

 

✍ 락이 걸린 이유??

✍ 애초에 Oracle DB에서 DML문이 실행될 때에 해당 트랜잭션이 걸린 데이터가 다른 사용자( 작업 ) 등에 의해 변경되지 않도록 Lock을 걸어버린다. <-- commit / rollback이 실행되기 전까지...

 

why? 

    * 데이터의 일관성 보장.

    * 변경중인 세션에서만 변경중인 데이터를 확인 할 수 있고 다른 세션에서는 확인 할 수 없다.

              -- 대규모 데이터 변경작업 시, 중간 중간에 커밋을 하는게 좋은 이유는 이것 때문이 아닐까 싶다. 

 

---

☁ 만약 내가 권한을 가진 계정이 있다면 아래와 같이 처리하자.

☁ lock 테이블 확인 :: 
select *
from v$locked_object 
where oracle_username = 'oracle user name'; 

☁ lock session 확인 ::
select a.sid, a.serial#
from v$session a, v$lock b, dba_objects c
where a.sid=b.sid 
and b.id1=c.object_id
and b.type='TM'

☁ lock 제거
ALTER system KILL SESSION 'sid, serial#'

---

어쨌든 체크하고, 

락 지우고,

데이터를 특정 기간 만큼의 양을 지우도록 했다. 

쿼리를 나눠서 지우도록 했는데, 총 걸린시간은 40분 정도 2번의 쿼리로 나눠서 진행됐다. 

당시에는 급해서 일단 (장비가 우리 것이 아니었고 data file 추가 없이 최대한 내부 자원으로 처리해보고자 했다...) DELETE 처리를 했었지만,  나중에 검색해보니 2억건의 데이터를 지우려고 시도한 사람도 있었다. 

 

☁  2억건의 데이터를 update / delete하는 경우이다. 아래의 홈페이지에서는 1만건식 커밋하면서 pl 문을 돌렸다.

☁ 효율적인 방법이라고 생각한다.

https://blog.naver.com/PostView.nhn?blogId=tpgpfkwkem0&logNo=221795025687&parentCategoryNo=&categoryNo=62&viewDate=&isShowPopularPosts=false&from=postView

[Oracle] 대량 데이터 delete, update시 (feat. ora-1628)

 

---

✍ 문제가 또 있다. 

✍ 이미 늘어난 테이블은 다시 줄어들지 않는다. ( 나는 6000만건을 DELETE 한 상황이다. )

 

그렇다면 용량은 10G를 먹고 데이터는 1GB만 먹는데 무슨 상관이냐고 생각했지만, 그렇지 않다고 한다. 

이런 상황이 방치된다면 

☁ 빈공간 -> 데이터 흩어짐 -> 테이블 스캔시 I/O 낭비

☁ 공간 낭비 

 

 

✍ 왜 이런게 문제가 되는지는 다음과 같다.

 

segment / extent를 이해 해야하지만 간단히...

 

☁  segment 

    * 테이블 스페이스 내의 논리적 저장구조, 

    * 인덱스 테이블 등의 오브젝트가 segment에 포함됨. 

    * 저장 공간을 가지는 오브젝트 == segment 

 

☁  extent 

    * 하나 이상의 연속된 데이터 블록

    * segment에 공간 할당.

 

☁  HWM ( High Water Mark )

    * EXTENT의 확장 기준이다. 모든 segment에 하나씩 존재. HWM의 이전 블록에만 저장. 

https://positivemh.tistory.com/350

 

--> 관리자가 별도로 초기화하거나 축소시키지 않는다면...

     data full scan시 scan의 범위는 HWM의 최고 수위가 된다..

     물론 DB는 용량이고 HWM이 다 찼는지 아닌지는 알 바가 아니고 그냥 데이터 블록 전체를 스캔한다. 

 

✍ 위와 같은 문제가 싫다면,

 

1. 애초에 TABLE을  TRUNCATE 또는 DROP 처리해서, HWM 정보를 초기화해야한다.

    * 문제는 전체 다 날리므로, 백업은 필수이다. 

2. Oracle 10g 이상이라면 Shring 기능을 사용한다. 

    * Archive log 파일이 대량으로 생성될 수 있다.

3. INDEX 를 REBUILD 한다.

    $ ALTER INDEX 'index_name' rebuild;

✍ $ ALTER TABLE TABLE_NAME SHRINK SPACE [COMPACT] [CASCADE]

    * cascade는 신중하게..

 

쿼리 날리기 전 / 후로 블록 개수를 확인하면 좋다. 

    SELECT COUNT(DISTINCT DBMS_ROWID.ROWID_BLOCK_NUMBER(ROWID)) BLOCKS FROM TABLE_NAME;

 

나와 같은 고민을 한 사람도 있었다.

https://okky.kr/article/156000

OKKY | 테이블 삭제 후 재 생성시 속도 향상이 되는 이유는?