SQL Version Count , DBMS_SHARED_POOL.PURGE 패키지

Oracle DB 운영중 여러가지 원인으로 SQL Version Count(Child cursor개수) 가 증가 하면서 메모리 공간 낭비, library cache 관련 latch 발생 빈도 상승(경합), soft parse 시간 증가, ora-600 에러 발생 등 성능 이슈가 발생할 수 있습니다. 대부분  바인드변수를 사용하는 SQL문이 실행될 때, 기존 커서를 재활용하지 못할 경우 BIND_EQUIV_FAILURE되고 이 때 CHILD_NUMBER가 증가됩니다. *관련 파라미터  _optimizer_use_feedback = FALSE _optimizer_adaptive_cursor_sharing = FALSE _optimizer_extended_cursor_sharing = NONE _optimizer_extended_cursor_sharing_rel= NONE 변경시 CHILD_NUMBER가 더 이상 증가하지 않게됨

SQL VERSION_COUNT 1000이상 인 SQL조회

select inst_id, sql_id, version_count, sql_text from gv$sqlstats
where version_count>=1000;

SQL VERSION_COUNT 1000이상 인 SQL은 2건으로 1tfdt5fpan9cv(1,2 인스턴스) 8x2qt04vkf3hy(1인스턴스)

Oracle DB에 SQL구문에 대한 특정 부하나 서비스 지연이 없는 상태이며

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

해당 명령어 사용시 DB 내의 모든 ShreadPool 영역이 모두 버려지기 때문에 사용할 수 없는 위험한 명령어입니다. 다시 SharedPool공간에 적재 작업을 위해 많은 작업을 수행해야하는 오히려 더 큰 단점이 존재하는 명령어라 할 수 있습니다.
그렇다고 바인드변수 관련된 히든파라미터를 변경하기에도 매우 위험할수 있습니다.

Oracle에서는 DBMS_SHARED_POOL 패키지의 PURGE 프로시저를 사용하면 특정 커서를 제거할 수 있습니다.

부분적으로 SQL 커서에 대한 메모리 해제를 수행.

SQL 상태 조회

SELECT sql_text , sql_id , address, hash_value, executions, loads, version_count, invalidations, parse_calls FROM gv$sqlarea WHERE SQL_ID ='1tfdt5fpan9cv';

ADDRESS , HASH_VALUE 값을 구합니다.

 

Cursor remove 수행

SQL> exec dbms_shared_pool.purge (‘000000008EB0B178 , 2863277467','C');

 

SQL 상태 조회

SELECT sql_text , sql_id , address, hash_value, executions, loads, version_count, invalidations, parse_calls FROM gv$sqlarea WHERE SQL_ID ='1tfdt5fpan9cv';

0 rows -- 부분적으로 특정 커서만 메모리 해제 된것을 확인 합니다.

 

참고 : https://docs.oracle.com/en/database/oracle/oracle-database/12.2/arpls/DBMS_SHARED_POOL.html#GUID-EC3047BF-BA54-4131-9E73-F44BD6FB097D