Oracle - version_count 증가로 인한 shared_pool 부족 문제

특정 쿼리로 인한 library cache lock 및 cursor: pin S wait on X 이벤트가 발생함.
 
-- 임시조치
-- shared memory 부족으로 인해 DB 접속 실패 날 경우(ORA-04031: unable to allocate nn bytes of shared memory)
ps -ef | grep oracle
kill -9 PID 로 PGA 공간 확보 필요
 
-- 전체 메모리 flush(권장하진 않음) : 기존 대용량 쿼리들이 메모리에서 지워지면서 새로 parsing 할때 부하가 옴

alter system flush shared_pool;

 
-- 메모리 점유율 높은 sql purge

select sql_id, hash_value, round(sharable_mem/1024/1024) || 'M' MB, module , version_count, loaded_versions,
    'exec dbms_shared_pool.purge('''||address||','||hash_value||''',''C'');'
from v$sqlarea
where sharable_mem >= 10 * 1024 * 1024
        and last_active_time > SYSDATE - 7
order by sharable_mem desc;

 

-- 특정 쿼리 purge

exec dbms_shared_pool.purge ('00000001BE7D4860,759585230 ','C');

'00000001BE7D4860,759585230': 커서의 주소
'C' : 커서 (SQL 커서)
'P' : PL/SQL 프로시저, 함수, 패키지 등
 
 

-- 문제 분석

-- 문제  쿼리 version_count 확인

select t1.sql_id ,t1.sql_text, t1.version_count, t1.optimizer_mode, t1.address, t1.hash_value
from v$sqlarea t1
where t1.sql_text like '%문제 쿼리%'
and t1.sql_text not like '%v$sql%';

parse_calls : 라이브러리 캐시에서 SQL 커서를 찾으려는 요청 횟수
loads : 하드파싱을 거친 SQL 실행계획을 라이브러리 캐시에 적재한 횟수
executions : SQL을 수행한 횟수
invalidations : 커서가 무효화된 횟수, 커서가 참조하고 있는 오브젝트에 중요한 변화가 일어났음을 의미함
is_bind_senstive : bind 변수 값에 따라 실행계획이 변경되어야 하는지 결정하고 실행계획이 변경된다면 "Y" 로 표시됨
 

-- V$SQL_SHARED_CURSOR 뷰의 REASON 컬럼을 통해 원인 확인

select * from v$sql_shared_cursor
where sql_id = 'SQL_ID'

 
-- BIND 변수 확인 쿼리

SELECT NAME , POSITION , DATATYPE_STRING , VALUE_STRING  
        FROM DBA_HIST_SQLBIND  
        WHERE SQL_ID  = 'SQL_ID'
        ORDER BY snap_id ASC;

 

* bind capture가 이루어지는 시점
SQL 문장이 hard parse되는 시점에 bind variable이 사용되고 bind 값이 부여된 경우(was_captured가 no인경우 바인드 캡쳐를 못한거기 때문에 바인드 변수를 사용했는지 확인 필요)
bind capture가 이루어진 이후, bind값이 변경된 경우. 단, 오라클의 성능상의 오버헤드를 최소화하기 위해 15분 이상의 간격을 두고 capture를 수행한다.
즉, bind capture된 값이 항상 최신의 값을 나타내는 것은 아니다.

 

 

-- 문제 쿼리의 리터럴 변수를 바인드 변수로 변경했음에도 version_count 증가, bind_mismatch : N이 해결 되지않음

 

-- 임시 조치2

-- 쿼리 SQL_ID를 5개로 분리하여 library cache 경합 및 cursor: pin S wait on X 확률을 줄임

 

-- 해결

문제 쿼리의 조건절에 데이터 타입이 timestamp인 컬럼과 to_date를 비교하는 부분 확인

to_date => to_timestamp로 변경하여 문제 해결

 

 

* library cache lock 문제는 다양한 원인으로 발생할 수 있으며, 시스템 환경에 맞춘 적절한 해결책을 통해 효과적으로 대응해야 함.(Hard Parsing 문제로 생기는 경우가 많음)

 

 

 
참조 : https://m.post.naver.com/viewer/postView.naver?volumeNo=32129399&memberNo=18071586

자식커서로 인한 높은 파싱율 - 뮤텍스 대기를 초래하는 자식 커서 문제 해결.