ORACLE shared pool내의 특정 cursor purge 하기

 

오라클은 사용자 작업을 위해 많은 Package/Fuction 등 을 지원합니다. 

이런 놈들을 다 알필요는 없지만 아는 많큼 손발이 고생할 여지가 조금씩 줄어 듭니다. 그러니 기회가 있을 때마다 조금씩 알아 놓는게 좋겠죠 ^^;

얼마전 작업하다기 DBMS_SHARED_POOL.PURGE procedure를 유용하게 사용할 수 있는 기회가 있었어 간략하게 정리합니다. 

 

주말의 몇몇 table의 re-org 작업이 있었는데, 

시간 계산을 잘못해서 마지막 즈음에 기다리던 작업자를 위해 사용자 세션을 풀어 주었습니다. 주말이라 얼마나 들어오겠나 싶었죠.. 

많은 세션들이 들어오진 않았지만, 몇몇 세션들이 주로 수행되는 SQL을 이것 저것 수행하고 나갔습니다. 

 

근데 작업 전보다 성능이 않좋다고 하더군요. 

지금 re-org 작업이 있으니 조금 느릴 수도 있겠다 싶었죠. 근데, 작업 후에도 느리다고 하더군요. 

Plan이 바뀌었습니다. 통계정보도 같이 넣어 주었는데 말이죠.. 

 

import로 data를 loading 하면 마지막에 해당 segment의 통계정보를 같이 적제하게 됩니다. 근데 통계정보가 다 들어가기 전에 수행된 SQL은 통계정보가 없는 상태에서 cost를 계산해서 sql plan을 만들어 버리죠. 또 이렇게 만들어진 plan 정보는 해당 cursor가 purge 되기 전까지는 계속 사용하게 됩니다. 

 

이러한 비슷한 경우는 bind peeking 기능에 의해 일반적이지 않은 bind 값이 들어가도 발생할 수가 있습니다. 

 

이러한 경우 shared pool flush를 시켜 해당 cursor를 purge 시킬 수 도 있겠지만 11g 이후 부터는 단일 cursor 별로 purge가 가능합니다. 10.2.0.4 version에서도 사용가능한데, 이 버전의 경우 event 설정이 필요합니다. 

 

event="5614566 trace name context forever"

다음의 내용은 참조 노트의 사용 방법에 대한 간단한 예 입니다. 

 

SESSION 1 

 

sqlplus scott/tiger 

SQL> select ename from emp where empno=7900;

 

SESSION 2 

 

sqlplus / as sysdba 

 

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls 

     from v$sqlarea 

     where sql_text = 'select ename from emp where empno=7900';

 

ADDRESS          HASH_VALUE EXECUTIONS      LOADS VERSION_COUNT INVALIDATIONS PARSE_CALLS 

---------------- ---------- ---------- ---------- ------------- ------------- ----------- 

000000007A6CF430 1052545619          1          1             1             0           1

 

SQL> exec dbms_shared_pool.purge ('000000007A6CF430,1052545619 ','C'); 

 

SQL> select address, hash_value, executions, loads, version_count, invalidations, parse_calls 

     from v$sqlarea 

     where sql_text = 'select ename from emp where empno=7900';

 

no rows selected

 

 

이 내용은 아래의 문서를 참조했습니다. 

 

Note 457309.1 How To Flush an Object out the Library Cache 

Note 751876.1 DBMS_SHARED_POOL.PURGE Is Not Working On 10.2.0.4