[Oracle] DDL 사용시 주의사항 (공유 커서 invaild)

grant, revoke와 같은 DDL 문 수행시, shared pool 내 정보(cursor, sql 등 ) 순간 invalid된다.
v$sql에서 invalidations 수치 확인
자주 사용하는 오브젝트에 대한 DDL은 대량의 library 관련 대기 이벤트 발생시키고
동일 쿼리 수십개의 (서비스 접근하는 양에따라) tm lock 발생시켜 서비스 불가능한 장애가 생긴다.

 

--1.테스트 구문 실행
select /*cursortest*/ *
  from xtmp_cursor_test
 where col = 'n';
 
--2. 테스트구문 실행 후 v$sql 확인
select sql_text,sql_id , parse_calls, loads, executions, invalidations,
       loads-invalidations as reloads
  from v$sql 
where sql_text like '%cursortest%';

--1회 실행시

--3회 더 실행시 

-- grant 권한 부여
grant select on xtmp_cursor_test to [DB스키마명];

--권한 부여 후 바로 v$sql 확인

--권한 부여 후 쿼리 재실행

--권한 부여 후 쿼리 4회 더 실행

 

권한 부여 후, 커서 무효화되어 하드파싱이 일어나 라이브러리 캐시에 재적재되었다.

grant뿐만아니라, 여러 상황에서 공유 커서 invalid 되므로 확인하고 운영해 나갈 것

 

 

parse_calls : 라이브러리 캐시에서 sql 커서를 찾으려고 요청한 수

loads : 하드파싱을 거쳐 sql 실행계획을 라이브러리 캐시에 적재한 수 

executions : 쿼리 실행 횟수

invalidations :  커서가 무효화 된 수. 참조중인 오브젝트에 변화 발생 의미

 

- 공유커서 invalid 되는 경우

• 참조하고 있는 오브젝트에 컬럼 추가 / 삭제 되는 경우
• 인덱스 생성
• 오브젝트 통계 수집
• DDL 문 수행 

 

참고 ) 

http://wiki.gurubee.net/pages/viewpage.action?pageId=26741308