Shared Pool-2

▣ Mutex

메모리가 모자라면 객체 1개당 sql문 1개 shared pool에 배치

생성여부 : wait의 존재여부 확인 가능(mutext의 발생은 sharedpool이 부족함을 나타낸다)

▣ Data Dictionary Cache 적중률

※ Row cache 모자라면 shared pool 늘려줌

   

▣ Tuning the Shared pool Reserved Space (큰 객체 사용시에만 사용)

if Request_failures				Action
>0 and increasing		And	Request_misses>0	Increase shared_pool_reserved_size
>0 and increasing		And	Free_memory=>50% of shared_pool_reserved_size	Increase shared_pool_size
=0		Or	Free_memory=>50% of shared_pool_reserved_size	Decrease shared_pool_reserved_size

   

▣ Keeping Large Objects

자주 쓰고 큰 객체는 메모리에 계속 상주하게 할것

   

관련질의

select * from v$db_object_cache

where sharable_mem>10000

and(type='PACKAGE' or type='PACKAGE BODY' or

type='FUNCTION' or type='PROCEDURE')

and kept='NO'

/

 

execute dbms_shared_pool.keep('package_name');