오라클 테이블 락 & analyze처리 및 테이블스페이스 확인방법

업무 중 프로시저를 호출할 때, 1시간이 지나도 실행이 완료되지 않는 문제가 발생했습니다.

이 문제를 해결하기 위해 여러 가지 원인을 단계적으로 확인했습니다.

아래는 그 과정과 각 단계에서 수행한 작업들입니다.

1) 테이블 락(Lock) 확인

먼저, 테이블에 락이 걸려 있는지 확인했습니다. 락이 걸려 있는 경우, 쿼리 성능이 저하되거나 실행이 완료되지 않을 수 있습니다.

락 확인을 위한 쿼리는 다음과 같습니다.

위 쿼리를 통해 락이 걸린 테이블을 조회할 수 있습니다.

만약 락이 걸린 테이블이 확인되었다면, 아래 명령어를 사용하여 해당 세션을 종료(kill)할 수 있습니다:

예시 > ALTER SYSTEM KILL SESSION 'SID, SERIAL#'

Toad를 사용 중이라면 세션 브라우저에서 락 걸린 테이블 리스트를 확인하고, 마우스 우클릭을 통해 직접 세션을 종료할 수 있습니다.

하지만 테이블 락은 없었고 그다음은 테이블 및 인덱스 analyze 처리를 해보았다.

다음으로, 테이블과 인덱스의 통계를 수집하여 쿼리 계획을 최적화하기 위해 Analyze 처리를 수행했습니다.

오래된 통계는 쿼리 성능 저하의 원인이 될 수 있습니다.

아래 명령어를 사용하여 통계를 수집했습니다:

ANALYZE TABLE 스키마명.테이블명 COMPUTE STATISTICS;

ANALYZE INDEX 스키마명.인덱스명 VALIDATE STRUCTURE;

관련된 모든 테이블과 인덱스에 대해 Analyze 처리를 수행했지만, 이 방법으로도 문제는 해결되지 않았습니다.

다음으로, 테이블 스페이스의 용량을 확인했습니다.

테이블 스페이스의 용량이 부족하면 쿼리 성능이 저하될 수 있습니다.

아래 쿼리를 사용하여 테이블 스페이스의 용량 정보를 확인했습니다.

쿼리 결과를 통해 테이블 스페이스의 용량을 확인했으며, 사용량이 최대 용량을 넘지 않는 선에서 할당 용량을 초과할 경우 자동적으로 스페이스 공간이 확보되도록 설정되어 있음을 확인했습니다.

그러나 이 방법도 문제 해결에 도움이 되지 않았습니다.

마지막으로, Toad에서 현재 서버에 접속된 세션의 개수를 확인해보았습니다.

세션 개수가 90개를 넘는 것을 확인하였습니다.

서버 부팅 시 접속 세션이 최대 30개로 설정되어 있는데, 이보다 많은 세션이 존재하는 것은 비정상적인 상황입니다.

서버를 다시 부팅할 때 세션이 제대로 종료되지 않는지 확인이 필요했습니다.

서버를 다시 부팅한 후, 세션이 정상적으로 종료되고 새로 생성된 세션만 유지되는지 확인하는 작업을 수행했습니다.

위의 모든 단계를 통해 문제를 진단하고 해결 방안을 모색했습니다.

최종적으로 서버 부팅 시 세션 관리에 문제가 있었음을 확인하고, 이를 해결하여 문제를 해결할 수 있었습니다.

이러한 과정을 통해 앞으로도 비슷한 문제 발생 시 신속하게 대처할 수 있을 것입니다.

이 글이 비슷한 문제를 겪고 있는 다른 분들께 도움이 되기를 바라며, 혹시 다른 방법으로 해결이 되신 분은 해결과정을 알려주시면 감사 하겠습니다.

읽어주셔서 감사합니다! 😊