SQL Cursor Flush 방법

수행한 SQL Cursor를 Flush 해아만 하는 상황이 종종 발생한다.

명령문은 아래와 같으며 살제 사용하는 방법은 아래 글을 참고 하시기 바랍니다.

[ 명령문 ]

 EXEC SYS.DBMS_SHARED_POOL.PURGE ( SQL의 ADDRESS, SQL의 HASH_VALUE ,'C');

​

[1] 아키텍처 확인 ​

SQL을 수행하면 내부적으로 SQL Cursor(LCO = Library Cache Object) 로써,

SGA -> Shared Pool -> Shared SQL Area에 저장됩니다.

​

 

[2] SQL을 10개 실행하여 Cursor 10개 생성

주석에 다른 숫자를 넣어 SQL FULL TEXT 서로 다르게 함.

SELECT /* KTH 1  */ * FROM EMP;
SELECT /* KTH 2  */ * FROM EMP;
SELECT /* KTH 3  */ * FROM EMP;
SELECT /* KTH 4  */ * FROM EMP;
SELECT /* KTH 5  */ * FROM EMP;
SELECT /* KTH 6  */ * FROM EMP;
SELECT /* KTH 7  */ * FROM EMP;
SELECT /* KTH 8  */ * FROM EMP;
SELECT /* KTH 9  */ * FROM EMP;
SELECT /* KTH 10 */ * FROM EMP;

 

 

​[3] 10개 Sql Cursor 확인​

3.1) 먼저 SQL_ID를 확인 위해 아래와 같이 V$SQL 조회

 

SELECT SQL_TEXT

             , SQL_ID

             , 'EXEC SYS.DBMS_SHARED_POOL.PURGE ('''|| ADDRESS ||',' || HASH_VALUE ||''',''C'');' AS SHARED_POOL_PURGE

             , ADDRESS

             , HASH_VALUE

             , PARSING_SCHEMA_NAME

   FROM V$SQL

WHERE SQL_TEXT LIKE '%FROM EMP%' AND UPPER(SQL_TEXT) NOT LIKE '%V$%'

ORDER BY LAST_ACTIVE_TIME ;

 

3.2) 위 3.1) 쿼리의 결과인 SQL_ID를 가지고, V$SQL_SHARED_CURSOR 를 조회해보자.

 

 SELECT *

   FROM V$SQL_SHARED_CURSOR

 WHERE SQL_ID IN (

'7xpsu3brxrg12', '5d6mfs1xns4tt', '9cfb962x4b8yq', '11jtvjd4gngf5', 'd8kfdavxhs867', '2y348xbtut5fq', 'fapz9k8qprcw4', '1ja9m71nv63th', '612fmbxanc3mw', '1uspurz5n97j2'

) ;

아래와 같이 모두 존재함을 알 수 있다.

Cursor LCO를 FLUSH 하기 위해서는, SQL의 ADDRESS와 HASH VALUE 를 알아야한다.이 정보는 V$SQL에도 있기 때문에 굳이 V$SQL_SHARED_CURSOR를 검색할 필요가 없다.아키텍처와 유사한 명칭의 DPV(Dynamic Performance View)를 보여주기 위해 참조 했을 뿐이다.

V$SQL도 Cursor의 메타데이터와 성능 수치를 제공한다.

​

 

[4] 10개 Sql Cursor Flush

4.1) 위 3.1 SQL 조회 하면 아래와 같이 출력된다. 여기 SHARE_POOL_PURGE 컬럼에 나온 실행문 모두 실행

​실행 ) 3번째 인자 'C'는 Cursor를 의미한다.

 SHARED_POOL_PURGE

EXEC SYS.DBMS_SHARED_POOL.PURGE ('000000006BFB0320,4024155170','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('0000000077300E70,2068583225','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('0000000071E014C0,3125126102','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('00000000712F3038,1224359365','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('0000000067475420,4211876039','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('000000008C96E020,4088174038','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('00000000802AA478,760984452','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('00000000663E65A8,1773342512','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('00000000897DD780,1430654588','C');

EXEC SYS.DBMS_SHARED_POOL.PURGE ('0000000088FEBBC0,3410271778','C');

 

4.2) 실행 후 확인

- 3.1의 SQL을 재수행하면, SQL 10개가 Flush 되서 조회된 건수가 0 이다.

​

==================================================================

Hard Parsing 유도를 위해 위 스크립트로 Sql Cursor를 Flush 해도 되지만, 기존 Sql에 "스페이스" or "아무 문자"만 넣어도 Hard Parsing 처리가 가능하기 때문에 튜닝 프로젝트 진행 중에 사용해 본 적은 없다. 개인 공부를 위해 많은 테스트로 불필요한 Sql Cursor가 많아서 직관성이 떨어질때 가끔 수행 편이다.

하지만, 전반적인 어플리케이션 테스트를 위해 Sql Area에 등록되어 있는 Application Sql Cursor 모두 없애고 싶을때나,반드시 동일한 SQL을 Hard Parsing을 유도하여 튜닝 or 점검할때는 필요로 해보인다.