from OTN http://download.oracle.com/docs/cd/B10501_01/server.920/a96536/ch392.htm#1116696
from OTN http://download.oracle.com/docs/cd/B10501_01/server.920/a96533/memory.htm#31325
Oracle 9i Database : SQL Tuning Workshop R2 2-25
Library Cache는 Shared Pool에 속한다. 이 캐시의 목표는 적당한 크기의 메모리에 캐시된 SQL 문장을 여러 번 공유해 수행할 수 있게 하는 것이다.
NAMESPACE
---------------
BODY
CLUSTER
INDEX
JAVA DATA
JAVA RESOURCE
JAVA SOURCE
OBJECT
PIPE
SQL AREA
TABLE/PROCEDURE
TRIGGER
NAMESPACE는 위와 같다. 개중에서 SQL AREA가 중요하다.
GETS는 해당 네임스페이스에서 락이 요구된 횟수를 말하며 락은 타겟 객체를 탐색, 변경하려고 할 때 요구되며 일관성 유지를 위해 사용된다. 간단히 총 요청(조회) 수이다.
GETHITS는 객체의 핸들이 메모리상에서 발견된 횟수를 의미한다. 객체의 핸들은 라이브러리캐쉬 탐색시 해당 해쉬밸류에 대응하는 버켓에 체인드 링크로 연결되어있는 리스트의 일원으로 구성되어있다. 또 핸들은 LCO( LIBRARY CACHE OBJECT ) 의 메타정보를 가지고 있다. LCO의 유,무는 SOFT PARSING, HARD PARSING으로 연결되는 문제로써 이 값이 의미하는 바는 소프트파싱이 아닐까 싶다.
GETHITRATIO 는 GETHITS / GETS 메모리상에서 발견 / 총 요구 이다.
PINS는 해당 네임스페이스의 객체를 위해 요구된 PIN 의 수를 의미한다. 책에서는 라이브러리 캐시의 객체수라고 써놓고 뒷장에서는 캐시의 객체가 실행된 횟수라 써놓았다. 값이 높을수록 이러한 명령문이 메모리에서 실행되었음을 나타낸다라 하였다.
( 어 앞장과 뒷장이 딴소리 하네라고 잠시 착각했지만 다시보니 같은 소리다 -_-; )
요구된 PINS의 수가 캐시의 객체가 실행된 횟수라 하였다. 그리고 이 객체는 메모리 상에 있으므로 로직컬한 I/O 가 발생했을 것이다. LCO에는 실행계획 정보가 들어있는데 프로세서가 접근하여 사용시(EXECUTE) 실행계획이 사용중에 변경되지 않게 PIN 한다. 말그대로 핀으로 꽂아 고정한다는 이미지이다.
PINSHITS는 객체의 모든 조각이 메모리에서 발견된 횟수라 한다.
PINHITRATIO 는 PINHITS/PINS를 의미한다.
RELOADS 는 REPARSE를 의미한다. 파싱을 또 했다는 이야기로 파싱했었으나 LRU에 의해 AGE OUT 되었다가 사용자의 요구가 다시 발생한 경우를 의미한다. 이 수치는 적절한 shard pool size와 SQL 을 효과적인 재이용이 일어난다면 0에 가까울 것이다.
INVALIDATIONS 는 네임스페이스의 OBJECT가 INVALID 되는 상태를 말하며 일례로 실행계획의 경우 참조하는 테이블의 ANALYZE가 실시되면 통계정보가 새로 수집되기 때문에 기존의 그것은 INVALID가 된다.
/*+ DLM_* 부분은 더 알아봐야 할 듯… */
GETHITRATIO와 PINHITRATIO는 이 VIEW의 다른 컬럼의 연산으로 얻어질 수 있는 값이며 1에 가까워야 한다.
Library Cache Hit Ratio =
sum(pinhits) / sum(pins)
Library Cache의 Hit Ratio는 위와같이 구한다.
SQL> @flsh
캐쉬를 flush 했다.
System altered.
SQL> declare
2 temp number;
3 begin
4 for i in 1..100000 loop
5 execute immediate 'select '||i||' from dual' into temp;
6 end loop;
7 end;
8 /
DYNAMIC SQL을 사용해
쓰레기 코드를 잔뜩 캐시에 넣었다.
PL/SQL procedure successfully completed.
SQL> select namespace,gethitratio,pinhitratio,reloads,invalidations
2 from v$librarycache;
NAMESPACE GETHITRATIO PINHITRATIO RELOADS INVALIDATIONS
--------------- ----------- ----------- ---------- -------------
SQL AREA .022104659 .355004905 54 0
TABLE/PROCEDURE .995305671 .995850868 2 0
BODY .75 .583333333 2 0
TRIGGER 0 0 0 0
INDEX .50617284 .111111111 0 0
CLUSTER .978835979 .976377953 0 0
OBJECT 1 1 0 0
PIPE 1 1 0 0
JAVA SOURCE 1 1 0 0
JAVA RESOURCE 1 1 0 0
JAVA DATA 1 1 0 0
11 rows selected.
위의 결과를 보면 SQL AREA의 결과가 상당히 좋지 않음을 볼 수 있다. GETHITRATIO에 대해 이야기 하자면 0.02… 수준인데 요청을 했는데 그 요청이 발견되지 않았다란 이야기이며 달리 말해 라이브러리 캐시에 공유할 LCO가 없었다란 소리며 그런 이유로 하드파싱이 일어났다는 이야기이다.
PIN은 0.35… 로 비교적 높게 나왔다. 이 부분에 있어선 이 수치에 있어선
/*+ 생각이 필요할 듯 하다 */
추측을 하자면 우선 상대적으로 PINHITS가 높게 나왔다는 것이고 공유되었던 실행계획이 있었다는 이야기다. 위의 PL/SQL 에서는 다이나믹 SQL을 사용해(제대로 사용했다면; ) 매번 하드파싱이 일어나게 했다만 코드를 잘못썼기 때문일지도 모른다…
SQL> @lchr
SUM(PINHITS)/SUM(PINS)
----------------------
.608102762
전체적인 LIBRARY CACHE HIT RATIO를 구해보았고 그 결과는 나쁘다.