[오라클] TABLE ANALYZE 하기

얼마전에 있었던 경험을 바탕으로 내용을 적어 본다.

 

갑자기 DBA의 실수로 운영DB의 테이블이 DROP 되었다.

신속한 시스템 복구를 위해 운 좋게 직전에 백업해두었던 테이블이 있어서 임시로 복구를 했다.

그냥 저냥 업무시간대를 잘 넘기고 있어서 별 문제 없이 지나가나 했었다.

갑자기 퇴근시간이 다되어 DB의 CPU 사용률이 높아지더니 떨어질 기미가 보이지 않는다.

여러 쿼리들의 수행시간이 엄청 길어졌고 계속 부하가 걸려온다.

느려진 쿼리의 PLAN을 떠봤다. 갑자기 타지 않던 FULL SCAN 플랜이 자꾸 뜬다.

분명히 DROP된 테이블과 관련이 있는듯 했으나, 새로 생성된 INDEX를 봐도 기존과 동일하게 문제가 없었다.

암만 봐도 인덱스의 문제가 없다.. 이거 왜이러지 하는 순간... 번뜩 생각이 났다.

보통 DB의 옵티마이져는 COST 베이스 방식으로 운영이 되고, 옵티마이저에 따라 DB가 플랜을 만들어서 돌린다.

이때 통계정보를 활용한다.... 바로 이거인듯 했다.

 

그래서 ANALYZE 된 정보를 확인해 보았다.

 

SELECT TABLE_NAME,
       NUM_ROWS,
       BLOCKS,
       EMPTY_BLOCKS,
       AVG_SPACE,
       CHAIN_CNT,
       AVG_ROW_LEN,
       SAMPLE_SIZE,
       LAST_ANALYZED
  FROM USER_TABLES
 WHERE TABLE_NAME = 'SAMPLE_TABLE'

;

 

역시나 TABLE이 DROP되면서 통계정보도 날아갔던 것이다.

즉, 아무리 인덱스가 생성되어 있어도 데이터의 형태에 따라 만들어진 통계정보가 없으면 FULL_SCAN이 발생한다.

DROP된 테이블이 시스템 사용시 무조건 사용되는 테이블이라 그 영향력은 크다.

 

그럼 통계정보를 생성해 본다.

 

ANALYZE TABLE SAMPLE_TABLE

COMPUTE STATISTICS

;

 

[문법 설명]

ANALYZE object_name operation STATISTICS

[VALIDATE STRUCTURE[CASCADE]]

[LIST CHAINED ROWS[INTO tables]]

* object는 TABLE, INDEX, CLUSTER 중에 해당하는 오브젝트 기술

* operation 

  - COMPUTE : 각각의 값등을 정확하게 계산한다. 가장 정확한 통계를 얻을 수 있지만 처리속도가 가장 느리다.

  - ESTIMATE : 자료사전의 값과 데이터 견본을 가지고 통계를 예상한다. 덜정확하지만 처리속도가 훨씬 빠르다.

  - DELETE : 테이블의 모든 통계정보를 삭제한다.

 

수행하고 나서 상단의 조회 쿼리를 다시 조회하면 결과가 나온다.

 

자 이제 느린 쿼리를 다시 수행해 본다... 음 역시 예전의 상태를 되찾았다!

 

[출처] [오라클] TABLE ANALYZE 하기|작성자 귀차니즘극복