원본 : DBGUIDE 김형일님의 "오라클 문제해결" 커뮤니티
ORA-4031의 솔루션은 그 원인에 따라 다양한 방법이 있습니다.
먼저 ORA-4031가 발생하는 원인은, SHARED_POOL을 관리하는 과정에, 많은 조각화(Fragment)가 발생하고 Free Memory가 아주 적은 상태에서, 커다란 SQL(PL/SQL)이 Memory로 Load 될 때 공간이 부족해서 발생할 수 있습니다.
이 ora-4031 Error가 발생하게 되면, Shared pool의 관리가 원활히 되지
않아, 이후에 수행되는 모든 SQL이 error가 발생합니다. 그러므로 이는
반드시 예방되어야 합니다.
이러한 Memory관리상의 문제를 해결하기 위해 조치 할 수 있는 것은 아래의 것들이 있습니다.
1. v$sql 내의 Literal SQL이 많은지 확인한다.
많은 경우 Literal SQL을 사용하는 SQL을 찾아서 공유 할수 있도록 Bind Variable을 사용토록 하면 됩니다.
=> Literal SQL을 찾는 방법.
select substr(sql_text, 1, 40) "SQL",
count(*) cnt,
sum(executions) "TotExecs",
sum(sharable_mem) mem,
min(first_load_time) start_time,
max(first_load_time) end_time,
max(hash_value) hash
from v$sqlarea
where executions < 5 --> 수행 횟수가 5번 이하인 것.
group by substr(sql_text, 1, 40)
having count(*) > 30 --> 비슷한 문장이 30개 이상.
order by 2 desc;
2. v$sql 내의 sharable Memory가 큰것들을 확인 한다.
1M byte이상의 SQL이 있다면 확인 후 SQL의 복잡도를 줄인다(recursive call을 많이 한다든지..). 대부분의 경우 크기가 큰 것들은 일반 SQL이 아니라 PL/SQL이므로 이러한 것들은 Memory에서 내려오지 않도록 Pin을 시키는 방법도 있습니다. (그렇다고 memory에서 완전히 안내려 오는 것은 아닙니다.)
=> PL/SQL을 Memory에 Pin시키는 방법.
execute dbms_shared_pool.keep('SCOTT.HELLO_WORLD');
3. SHARED_POOL_SIZE와 SHARED_POOL_RESERVED_SIZE의 크기를 늘린다.
항상 Shared pool의 Free가 여유가 있도록 shared_pool_size를 크기를 좀 늘리시고
특히 Shared_pool_reserved_size의 크기를 100M정도 되도록 지정하세요. 경험적으로 shared_pool_reserved_size가 100M정도 지정하면 ora-4031가 많이 발생하지는 않더군요.
Free공간 확인 .
SELECT free_space, avg_free_size, used_space,
avg_used_size, request_failures, last_failure_size
FROM v$shared_pool_reserved;
4. 이것이 진짜 마약처럼 잘 듯는 방법인데, 9i부터는 Shared_pool의 관리를 좀더 효율적으로 하고 System의 CPU를 효과적으로 사용하기 위해 하나의 heap memory를 사용하던 것을 subheap으로 나누어 관리를 하고 있지요. 이렇게 sub-heap으로 나누어 관리하다 보니 작은 공간이 sub heap에 동시에 있더라도 이를 잘 활용하지 못해서 발생하는 경우가 있습니다. 이러한 이유로 ORA-4031 Error의 원인이 되는 경우가 종종 있습니다.
현재 시스템이 Multi CPU인 경우에는 아마도 1보다 큰 값으로 되어 있을 겁니다.
그래서 아래의 Query로 조회해 본 후 그 값이 1보다 큰 값이라면 init.ora에서
_kghdsidx_count=1로 지정한 후 restart해서 사용해 보세요. 어지간해서 ORA-4031가발생 하지 않을 겁니다.
select x.ksppinm, y.ksppstvl
from x$ksppi x , x$ksppcv y
where x.indx = y.indx
and x.ksppinm like '_kghdsidx_count%' escape ''
order by x.ksppinm;
원본 : DBGUIDE 김형일님의 "오라클 문제해결" 커뮤니티
ORA-4031의 솔루션은 그 원인에 따라 다양한 방법이 있습니다.
먼저 ORA-4031가 발생하는 원인은, SHARED_POOL을 관리하는 과정에, 많은 조각화(Fragment)가 발생하고 Free Memory가 아주 적은 상태에서, 커다란 SQL(PL/SQL)이 Memory로 Load 될 때 공간이 부족해서 발생할 수 있습니다.
이러한 Memory관리상의 문제를 해결하기 위해 조치 할 수 있는 것은 아래의 것들이 있습니다.
1. v$sql 내의 Literal SQL이 많은지 확인한다.
많은 경우 Literal SQL을 사용하는 SQL을 찾아서 공유 할수 있도록 Bind Variable을 사용토록 하면 됩니다.
=> Literal SQL을 찾는 방법.
select substr(sql_text, 1, 40) "SQL",
count(*) cnt,
sum(executions) "TotExecs",
sum(sharable_mem) mem,
min(first_load_time) start_time,
max(first_load_time) end_time,
max(hash_value) hash
from v$sqlarea
where executions < 5 --> 수행 횟수가 5번 이하인 것.
group by substr(sql_text, 1, 40)
having count(*) > 30 --> 비슷한 문장이 30개 이상.
order by 2 desc;
2. v$sql 내의 sharable Memory가 큰것들을 확인 한다.
1M byte이상의 SQL이 있다면 확인 후 SQL의 복잡도를 줄인다(recursive call을 많이 한다든지..). 대부분의 경우 크기가 큰 것들은 일반 SQL이 아니라 PL/SQL이므로 이러한 것들은 Memory에서 내려오지 않도록 Pin을 시키는 방법도 있습니다. (그렇다고 memory에서 완전히 안내려 오는 것은 아닙니다.)
=> PL/SQL을 Memory에 Pin시키는 방법.
execute dbms_shared_pool.keep('SCOTT.HELLO_WORLD');
3. SHARED_POOL_SIZE와 SHARED_POOL_RESERVED_SIZE의 크기를 늘린다.
항상 Shared pool의 Free가 여유가 있도록 shared_pool_size를 크기를 좀 늘리시고
특히 Shared_pool_reserved_size의 크기를 100M정도 되도록 지정하세요. 경험적으로 shared_pool_reserved_size가 100M정도 지정하면 ora-4031가 많이 발생하지는 않더군요.
Free공간 확인 .
SELECT free_space, avg_free_size, used_space,
avg_used_size, request_failures, last_failure_size
FROM v$shared_pool_reserved;
4. 이것이 진짜 마약처럼 잘 듯는 방법인데, 9i부터는 Shared_pool의 관리를 좀더 효율적으로 하고 System의 CPU를 효과적으로 사용하기 위해 하나의 heap memory를 사용하던 것을 subheap으로 나누어 관리를 하고 있지요. 이렇게 sub-heap으로 나누어 관리하다 보니 작은 공간이 sub heap에 동시에 있더라도 이를 잘 활용하지 못해서 발생하는 경우가 있습니다. 이러한 이유로 ORA-4031 Error의 원인이 되는 경우가 종종 있습니다.
현재 시스템이 Multi CPU인 경우에는 아마도 1보다 큰 값으로 되어 있을 겁니다.
그래서 아래의 Query로 조회해 본 후 그 값이 1보다 큰 값이라면 init.ora에서
_kghdsidx_count=1로 지정한 후 restart해서 사용해 보세요. 어지간해서 ORA-4031가발생 하지 않을 겁니다.
select x.ksppinm, y.ksppstvl
from x$ksppi x , x$ksppcv y
where x.indx = y.indx
and x.ksppinm like '_kghdsidx_count%' escape ''
order by x.ksppinm;