아빠개발자의 노트

select 절 앞에 explain plan for 을 적으면 된다

예) select * from 테이블 의 실행계획을 보고싶다면 explain plan for select * from 테이블 적으면 질의 결과에 설명되었습니다.

그후 쿼리문에 SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY('plan_table',NULL,'serial')); 날리면

실행 계획을 보여줍니다. 쉽죠?

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. 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 참고).

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

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 [투씨에스지 기술 블로그] 

​

로그를 구글 구글 검색 찾아보니 내가만든 db (db명.dbf) 파일이 깨져있다고 한다,

​

위와 같이 .dbf 파일이 깨져있었었는데 필자는 210개의 테이블에 1row 씩 select , insert , delete를 하고있는 상태로

12시간을 냅두니 db가 뻑나서 서버를 강제 리부팅을 한거라서

복구 방법을 찾아보니 dbf 파일을 백업해논게있으면 그걸로 복구하거나 해야한다 하더라...

뭐 db 복구의 방법에 대헤선 엄청 내용이 많이 나와있었는데........ 엄청 복잡하고 막 그렇더라

필자는 db.dbf 의 파일의 전부가 아닌 내가 사용 햇던 210개의 테이블에서 문제가 된거라 생각해서

210개의 테이블들을 그대로 drop 하고 다시 create 로 만들엇다 그리고 기존에있던 데이터는 필요없던것들이라

다시 데이터 insert 하고 사용하니 정상복구 되더라...

​

만약 이글을 보는 당신도 똑같은 문제가 발생한다면 본인이 사용햇던 테이블들을 drop 하고 create로 만들어서 사용해라

db 백업 복구보단 그게더 빠르다 그러나 문제는.. 그테이블에 있는 데이터는 날릴수밖에 없다는 것..

오라클을 재기동 하려고 shutdown 하고 실행시 아래 사진처럼 에러가 뜸

ora-01172

ora-01151

다른 계정 접속도 안되고 오로지 / as sysdba 만 접속이되는대

구글 구글 해서 여러 삽질을 한결과 해결방법을 찾아서 알려드림

​

이렇게 된후 정상 작동됨