[Oracle]Shared Pool tuning 공유풀 튜닝

[Oracle]Shared Pool tuning 공유풀 튜닝

shared pool에 Library cache는 sql파싱결과를 저장해놓는 공간입니다.

Shared pool의 메모리가 부족하다면 자주파싱되는 sql문이 밀려나면서 악순환이 이어집니다.

그리고 사용자들이 잘못 작성한 sql문들이 Library cache를 점령하고 있다면 공간낭비가 되겠죠.

이때문에 Library cache를 효율적으로 관리하는것이 중요합니다.

LIbrary cache에 sql문장들이 잘 적재되어 재사용되고 있는지 확인하는법입니다.

V$LIBRARYCACHE를 조회해 보시면 위와같은 칼럼을 보실 수 있는데요

GETS는 파싱된 SQL문이 있는지 LIBRARY에요청한 총 횟수 입니다.

GETHITS는 LIBRARY에 요청했을때 파싱결과가 있어서 HIT한 횟수입니다.

GETHITRATIO는 이 두가지의 비율입니다. 비율이 높을수록 재사용이 잘 된것입니다.

위는 0.2로 정말 낮은 HIT율을 보이죠

V$LIBRARYCACHE에서는 위와같은 정보도 볼 수 있는데요

PINS는 LIBRARY CACHE에 저장된 객체수입니다.

RELOADS는 LIBRARY CACHE영역에서 지워진 SQL파싱결과를 재 요청해서 다시 적재하는 횟수입니다.

이 비율이 1퍼센트보다 크다면 문제가 있을 여지가 있습니다.

위와같이 INVALIDATIONS라는 항목도 볼 수 있는데요

LIBRARY CACHE에 파싱된 SQL문이 있는데 해당하는 테이블에 변화가 생기면

INVALIDATIONS 값이 올라가게 됩니다. 이 뜻은 다시 파싱해야되는 SQL문이 늘어난다는 뜻이죠.

INVALIDATION값이 점점 증가하고 있다면 안 좋은 뜻입니다.

LIBRARY CACHE에 관련된 파라미터로는 CURSOR관련된 파라미터가 있습니다.

 

CURSOR_SHARING은 DEFAULT가 EXACT입니다.

CUSRSOR_SPACE_FOR_TIME은 기본값이 FALSE인데요

TRUE로 바꾼다면 PL/SQL에서  CURSOR를 한번열고 PIN을 유지시켜서 CLOSE될때까지

공유풀에서 사라지지 않게 하는 기능입니다. 동시에 여러 CURSOR를 실행시키는 프로그램이 있다면 

유용하겠지만 그렇지 않은 프로그램에서는 공유풀을 낭비하겠죠.

OPEN_CURSOR는 OPEN될수 있는 커서수를 뜻합니다.

SESSION_CACHED_CURSORS는 CURSOR가 3번 열리고 닫히면 이를 따로 캐쉬해 

CURSOR는 닫힌상태지만 나중에 빠르게 OPEN할 수 있게 하는 기능입니다.

CURSOR에 대한 부분은 더많은 설명이 필요하니... 여기에서는 그만 하겠습니다.

오라클 성능고도화원리 1권을 참고하시거나 ORACLE문서를 참고하시면 자세히 나와있습니다.

CURSOR_SHARING 옵션 EXACT : DEFAULT입니다. 정확히 같은 SQL문장만 파싱하지않고 공유풀에서 가져옵니다. FORCE : 상수부분을 BIND변수로 바꿔서 파싱하지않고 공유풀에서 파싱결과를 가져올 수 있습니다. SIMILAR : 히스토그램분포에 따라서 다른 파싱결과를 공유풀에 저장하고 가져올 수 있습니다.

V$DB_OBJECT_CACHE를 조회해보시면 캐싱된 PL/SQL을 볼 수 있습니다.

PL/SQL을 임의로 만들어 보겠습니다.

EXCUTE DBMS_SHARED_POOL.KEEP(' ')이라고 해주시면

해당 PL/SQL을 SHARED POOL에 KEEP하게 됩니다.

V$DB_OBJECT_CACHE에 KEPT= YES 된것을 조회해보면

위에서 실행한 PL/SQL을 볼 수 있습니다.

SHARED POOL과,, CURSOR에 대한 간략한 내용만 담았는데요

차후에 하나씩 자세한 내용을 포스팅하도록 하겠습니다.