오라클 메모리 누수? , full scan시 메모리 누수 문제

W먼저 본인은 1초당 210개의 테이블에 INSERT,DELETE 가 일어난다.

하루에 1테이블당 86400건씩 INSERT와 DELETE 가 일어난다.

결과적으로 테이블엔 1 row 씩 밖엔 없는대.....

select 쿼리시 메모리 누수문제가 발견되었다.

나의 select 쿼리는 이 210개의 테이블에 SELECT 를 INNERT JOIN 으로 3개씩 묶고 UNION ALL 로 70개씩 하여서 1쿼리로 날렷는대...

처음 insert와 delete가 안일어낫을경우엔 0.01초면 갖고오던게

하루 이틀 동안 insert 와 delete 가 반복되고 나서는 80초~160초로 왔다리 갔따리 했다... 이거 해결할려고 2주동안 헤메었는데.... 해결방법을 말하려고 여기다 적는다..

일단 문제는 delete 시 row는 1개지만 찌꺼기 데이터가 남아 그 찌꺼기 데이터가 각 테이블에 storage (스토리지)에 블록으로 영역은 그대로 남아있다.

이걸 select로 full scan 할경우 찌꺼기 데이터영역까지 scan을 해버려서 쿼리가 늦어지는 거였고 full scan을 캐시메모리에 올려버리니 메모리 누수처럼 select시 메모리가 계속 올라가던거였다.

해결방법은

1. truncate로 각테이블마다 데이터를 날린다. (문제는 백업이 안됨)

2. index를 생성하여 select 를 full sacn을 하지않는다. (insert의 성능이 조금 저하됨)

​

이 두가지 방안으로 해결하였다 하고나니

select시 매우 쾌적하다..... 쾌? 퀘? 모르겠네 ...

그래서 본인이 만든 테이블들은 백업이 전혀필요가없는 실시간 모니터링 테이블이라 하루단위로 truncate를 하고 index를 생성하였다.

​

먼저 index를 생성하는 문법

create index 인덱스명 on 테이블명(컬럼명1 ASC|DESC, 컬럼명2....);

위 문법 으로 index를 생성한 후 인덱스를 할 컬럼명을 설정해주면 select 시 full scan을 하지않고 where 절에 있는 컬럼명(인덱스컬럼명)

으로 조회를 한다. 여기서 주의할점은 select 시 꼭 where 조건을 써야하며 where 조건으로 index를 건 컬럼을 써줘야한다 그래야

인덱스를 타서 조회를 한다

그렇기 때문에 select 시 속도가 빨라진다.

​

​

요약

1. insert,delete가 엄청많이 일어나는 테이블을 (row 는 1개) full scan시 문제

2. 해결방안 index 생성 or truncate 로 insert,delete 할떄 남는 찌꺼기 데이터 truncate로 제거

아래는 찌꺼기 데이터가 남는 거에 대한 설명이다 (퍼옴)

​

​

1. Reorg 등장배경 & 세그먼트의 관리

Oracle Database 10g가 새로 제공하는 공간 재 확보 기능, 온라인 테이블 재구성, 스토리지 증가량 예측 기능 등을 이용하요 세그먼트 공간을 효율적으로 관리할 수 있습니다.

오래 전, Oracle Database의 경쟁 RDBMS 제품을 평가해 달라는 요청을 받은 일이 있습니다. 경쟁사의 프리젠테이션이 진행되는 동안, 청중들이 가장 감탄했던 기능이 바로 온라인 재구성(online reorganization) 기능이었습니다. 이 제품은 (오라클로 따지면 세그먼트에 해당하는) 영역의 데이타 블록을 온라인 상태에서 재배치하는 기능을 제공했습니다.

당시 오라클이 제공하던 Oracle9i Database는 이러한 기능을 제공하지 못했습니다. 이제 Oracle Database 10g는 온라인 상태에서 낭비되는 공간을 재 확보하고 오브젝트를 보다 컴팩트(compact)한 형태로 관리할 수 있게 하는 기능을 추가적으로 제공합니다.

이 기능을 자세히 살펴 보기에 앞서, 이전에는 세그먼트 관리를 어떤 방법으로 수행했는지 설명하도록 하겠습니다.

1.1 기존의 관리방법

그림 1과 같은 형태로 채워진 세그먼트를 가정해 봅시다. 작업이 수행되면서 그림 2와 같이 일부 로우(row)가 삭제되고 나면 낭비되는 공간이 생기게 됩니다. 낭비되는 공간은 (i) 남아있는 블록의 마지막 영역과 기존 테이블의 마지막 영역의 사이에서, 그리고 (ii) 로우가 부분적으로만 삭제된 블록 내부에서 발생합니다.

오라클이 이 영역에 대한 할당을 바로 해제하지 않고, 새로운 insert 작업 및 기존 로우의 확장에 대비한 예비 공간으로 활용합니다.

지금까지의 점유되었던 공간의 최고점을 High Water Mark(HWM)이라 부릅니다.

(그림 2 참고).

하지만 이와 같은 접근 방식에는 두 가지 문제점이 존재합니다:

- 사용자의 쿼리가 풀 테이블 스캔을 발생시키는 경우, 오라클은 (설사 관련된 데이타가 전혀 존재하지 않는 경우라 하더라도) HWM 아래쪽의 모든 영역을 스캔합니다. 이로 인해 풀 테이블 스캔에소요되는 시간이 길어질 수 있습니다. - 로우가 direct path 정보와 함께 insert 되는 경우 (예를 들어 APPEND 힌트를 사용한 Insert, 또는 SQL*Loader direct path를 통해 insert 되는 경우) 새로 추가 되는 데이타 블록은 HWM의 위쪽 영역에추가됩니다. 따라서 HWM의 아래쪽 영역은 낭비된 채로 남게 됩니다.

Oracle9i와 그 이전 버전에서 공간을 재확보하려면, 테이블을 drop하고 다시 생성한 다음 데이터를 다시 로드하는 방식, 또는 ALTER TABLE MOVE 명령을 사용하여 테이블을 다른 테이블스페이스로 이동하는 방식을 사용해야 했습니다. 이 두 가지 방식은 모두 오프라인 상태에서 수행되어야 한다는 문제가 있습니다. 그 대안으로 online table reorganization 기능을 사용할 수도 있지만, 이를 위해서는 기존 테이블 크기의 두 배나 되는 공간이 필요했습니다.

10g의 경우 이러한 작업은 훨씬 간소화되었습니다. 10g의 Automatic Segment Space Management(ASSM)이 해당 테이블스페이스에 활성화되어 있는 경우, 세그먼트, 테이블, 인덱스를 shrink하고 free block을 재 확보한 뒤 다른 용도로 할당하도록 데이터베이스로 반환됩니다. 자세한 방법을 마지막 장에서 설명해 드리겠습니다.

2. Reorg 란?

대상 Table의 Data를 Physical하게 재편성하여 DML 작업으로 인한 Fragmentation을 제거하는 작업 입니다.

테이블이 여러 번 수정되어 데이터가 분할되고 액세스 성능이 현저하게 느려지게 되면 REORG TABLE 명령이 최우선적인 튜닝 요소로 지적 됩니다.

Reorg 명령은 조각난 데이터 및 인덱스를 조각나지 않은 실제로 연속된 페이지로 재 빌드 함으로써, 복잡하고 불필요하게 공간을 차지하고 있는 오브젝트(테이블 및 인덱스)들을 최적화 할 수 있으며, 이로 인해 액세스 되는 블록도 감소시켜 SQL 쿼리의 성능 향상도 어느 정도 꾀 할 수 있습니다.

​

​

출처: http://tocsg.tistory.com/33 [투씨에스지 기술 블로그]