ORA-4031 진단

- shared pool size를 늘리기 전에 다음과 같은 사항을 먼저 체크한다

  ○ shared sql을 이용하는 application 튜닝
  ○ shared_pool_size, shared_pool_reserved_size 튜닝
  ○ shared_pool_reserved_min_alloc 튜닝

 

1. ora-4031 에러가 library cache나 shared_pool reserved space내의 fragmentation으로 발생했는지 다음 명령어로 확인한다.

 

select free_space,avg_free_size,used_space,avg_used_size,request_failures,last_failure_size
from v$shared_pool_reserved;

 

=> shred pool reserved space 내에 연속된 공간이 부족해서 ora-4031 에러가 발생했다면 위 쿼리의 결과 값은 다음과 같은 조건을 만족해야 한다

 

request_failures > 0
and
last_failure_size > shared_pool_reserved_min_alloc

 

shared pool reserved space 내에 연속된 공간이 부족한 문제를 해결하려면, shared pool reserved space 내에 캐시될 objects의 개수를 맞추기 위해서 shared_ool_reserved_min_alloc parameter 값을 늘려준다.  또한 shard pool reserved space 내에서 이용 가능한 메모리를 증가시키려면 shared_pool_reserved_size와 shared_pool_size parameter 값을 증가시킨다.

 

2. library cache 내에 존재하는 sapce 중에서 연속된 space의 부족으로 ora-4031 에러가 발생했다면, 앞의 sql 문의 결과 값은 다음과 같은 조건을 만족해야 한다.

 

request_failures > 0
and
last_failure_size < shared_pool_reserved_min_alloc

- or -

request_failures = 0
and
last_failure_size < shared_pool_reserved_min_alloc

 

library cache 내에 존재하는 space 중에서 연속된 space의 부족으로 발생한 문제를 제거하려면 shared pool reserved space 내에 더 많은 objects 들을 두기 위해 shared_pool_reserved_min_alloc parameter 값을 낮춰주고, shared_pool_size parameter 값을 증가 시킨다.

 

3. 진단 후의 솔루션

 

- 최신 patchset 적용
- shared pool 이 조각나면 실제 운용 환경에서는 사용자들이 느낄 수 있을 정도의 성능 저하 현상이 발생한다.
  wait_event 상으로는 shard pool latch가 발생한다. 그리고 연속된 조각을 발견할수 없다는 ora-04031 에러를 발생시킨다.

-literal values 와 bind variable을 포함하는 candidates들을 볼 수 있는 쿼리

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;

 

=> having 부분의 30이라는 숫자는 사용자 환경에 맞춰 변경해 주면 된다.

 

- X$KSMLRU view
: shared pool 내에 있는 다른 objects들을 내보낸(age out)것을 추적하는데 이용된다. 이것은 large allocation을 유발시킨 것을 찾아낼 수 있게 해준다. 많은 object들이 지속적으로 shared pool 에서 빠져나간다면 응답 시간이 느려지는 문제가 발생할 수 있고, flush된 objects들이 shared pool 내로 재진입 할때는 library cache latch 이벤트를 유발하는 원인이 될 수도 있다.

 

select * from X$KSMLRU where ksmlrsiz > 0;

 

- miss ratio 결과 값이 1% 이상이라면 shared pool size를 증가시켜 library cache miss ratio를 줄이도록 하자.

select sum(pins) "EXECUTIONS",sum(reloads) "CACHE MISSES WHILE EXECUTING" from V$LIBRARYCACHE;

 

4. 최후 수단

event = "4031 trace name errorstack level 3"