[Oracle] oracle dba_hist_sqlstat를 통해 특정 sql실행시간 조회 (ORA-01555)

Oracle DB 운영중에 가끔씩 ORA-01555 error를 볼 수 있다.

ORA-01555의 발생원인은 대형 테이블을 스캔하거나 복잡한 조인을 수행하는 긴 실행시간 동안, 실행이 시작 된 후 데이터가 변경되고 해당사항이 커밋되면,
쿼리가 참조해야 할 이전 데이터의 스냅샷이 더 이상 유효하지 않게 되어서 발생하는 현상이다.

alert_log에서 ORA-01555에 대한 sql_id를 얻을 수 있고 아래 sql문을 통해 해당 sql의 실행시간을 조회 할 수 있다.

-- elapsed_time_delta : 각 스냅샷 간의 총 실행 시간 (마이크로 초 즉 백만분의 1초)
-- executions_delta : 각 스냅샷 간의 실행 횟수

SELECT 
	sql_id,
	sum(executions_delta) AS total_executions,
	sum(elapsed_time_delta) / 1000000 / sum(executions_delta) AS avg_elapsed_time_seconds
FROM dba_hist_sqlstat
WHERE sql_id = '<sql_id>'
GROUP BY sql_id;

 

 

그리고 아래 쿼리를 통해 undo_retention의 파라미터 값을 조회할 수 있다.

sqlplus "/as sysdba"

show parameter undo_retention

만약

undo_retention의 값이 3600으로 설정되어있고,
avg_elapsed_time_seconds의 값이 3600이상 일 때

ORA-01555가 발생할 수 있다.
이유는 sql문 실행시간이 3600을 넘어가고 조회하기 전 데이터가 변경되었으며,

이전 버전의 데이터 세그먼트의 보관 시간인 3600초가 넘어버리면 해당 세그먼트를 찾을 수 없게 되기 때문이다.

 

이 문제를 해결하기 위해서는 

* UNDO tablespace의 크기를 늘리기
* undo_retention의 값을 늘리기
* sql문 최적화를 통해 조회 시간을 줄이기.
* commit주기 변경하기

 

를 통해 해결 할 수 있다.