About DATABASE

SHARED POOL에 대한 점검 사항들

제품 : ORACLE SERVER




SHARED POOL에 대한 점검 사항들
======================


PURPOSE
-------
다음은 shared pool에 관련된 performance 에 대한 점검 사항들이다.


Explanation
-----------

1. literal SQL Statements

SELECT      substr(sql_text,1,40) "SQL", count(*) ,
sum(executions) "TotExecs"
FROM      v$sqlarea
WHERE      executions < 5
     GROUP BY substr(sql_text,1,40)
     HAVING count(*) > 30
ORDER BY 2
/

shared SQL문들 중에서 literal SQL문들을 찾아내어 bind variable을
사용할 수 있는 경우 bind variable로 전환하도록 한다.
ORACLE cost based optimizer는 bind variable 보다 literal value를
사용하는 SQL에 대하여 보다 최적화된 execution plan을 결정하게 된다.
하지만 과도한 literal SQL문들을 사용하게 되면 hard parsing 이
빈번하게 되고 library cache와 dictionary cache의 사용율을 높이게 된다.


2. Library cahe hit ratio

SELECT to_char(trunc(sum(reloads)/sum(pins)*100, 5),99.99999)||
'%(less than 1%)' "Library Cache MISS RATIO"
FROM v$librarycache
/

만일 miss ratio가 1%보다 큰 경우 library cache miss를 줄이는 노력이
필요하다. 예를 들어 적절한 크기의 shared pool을 사용하거나 dynamic SQL
(literal SQL) 사용을 줄이도록 한다.


3. Checking hash chain lengths
SELECT hash_value, count(*)
FROM v$sqlarea
GROUP BY hash_value
HAVING count(*) > 5
/

위 query에 대한 결과가 없어야 한다. 만일 동일한 HASH_VALUE를 갖는
sql 수가 많다면 다음의 query를 이용하여 이 hach chain에 의하여 관리되는
sql 들을 확인하여 본다.
대부분 literal sql문들에 의하여 이런 문제가 발생하는 경우가 많다.

SELECT sql_text FROM v$sqlarea WHERE hash_value= <XXX>;


4. Checking for high version counts

     SELECT      address, hash_value,
     version_count ,
users_opening ,
users_executing
sql_text
FROM v$sqlarea
WHERE version_count > 10
     /

SQL의 version은 문장 상으로 완벽히 일치하지만 참조 object가 틀리는
SQL문들을 의미한다. 만일 이해할 수 없을 정도의 version count를 갖는
row가 있다면 한국 오라클 기술지원팀으로 문의하도록 한다.


5. Finding statement/s which use lots of shared pool memory

SELECT      substr(sql_text,1,40) "Stmt", count(*),
sum(sharable_mem) "Mem",
sum(users_opening) "Open",
sum(executions) "Exec"
FROM v$sql
GROUP BY substr(sql_text,1,40)
HAVING sum(sharable_mem) > <MEMSIZE>
/


6. Allocations causing shared pool memory to be 'aged' out

SELECT      *
FROM      sys.x$ksmlru
WHERE      ksmlrnum>0
/

x$ksmlru는 shared pool의 object에 대한 list로 object 할당 시 age out된
object 수에 정보를 담고 있어 age out으로 인한 응답율 저하나 latch
병합들의 원인을 추적하는 데 유용하다.
이 table은 8i부터 sys user로만 조회가 가능하며 한번 조회되면 reset된다.

(x$ksmlru.ksmlrnum : Number of items flushed from the shared pool)


Example
-------


Reference Documents
-------------------