ORA-00604

ERROR:
ORA-00604: error occurred at recursive SQL level 2
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","TRIGGER$SYS","sga heap","state objects")
ORA-00604: error occurred at recursive SQL level 1
ORA-04031: unable to allocate 4048 bytes of shared memory ("shared
pool","unknown object","sga heap","state objects")

DB를 관리하면서 다음과 같은 에러를 직면할때가 있느데 이럴 경우에는 신속하게
대처할 수 있는 방법
  1. 오래된 세션을 끊어서 shared pool 에 잡고 있는 메모리를 해제 시켜준다.
  2. alter system flush shared_pool; 명령어를 통해 메모리 조각모임을 해준다.
장기적인 대책은
  단편화 시키는 SQL를 찾아내고 바인딩 변수화 시킨다.

ORA-04031 의 경우 shared pool 내에 메모리 조각화에 따라서 연속된 parsing 공간을 제공하지 못하기 때문에 발생하는 에러입니다.
다시 말해서 parsing 에러가 발생하는 것입니다.

이를 해소 해주기 위한 방법으로
개체가 큰 자주 사용되는 프로시져등을 메모리에 pined 해주면 되며,
shared pool 사이즈를 늘려 주는것이 가장 좋으며,
상황이 여의치 않는 경우 단편화된 shared pool 의 조각화을 다시 flush 해주는 방법
등이 있습니다.

SQL> alter system flush shared_pool;

shared pool 의 hit 율을 잘 분석해 보시고 오라클의 권장사항에 따라서 튜닝 가이드 라인을 정하는게 중요합니다.

너무 크게 줘서 free size 가 너무 많이 남는 경우 즉 hit 율은 좋은데, free size 가 너무 큰 경우는 메모리 낭비를 하게 되며, 각각의 o/s 에 따라서  paging 이나 swap 이 발생할 가능성이 있으니, 튜닝후 적적할 모니터를 통해서 사이즈를 잡아 가는 것이 좋습니다.

SQL> select name, bytes/1024/1024 "Size in MB" from v$sgastat where name='free memory';

NAME                       Size in MB
-------------------------- ----------
free memory                451.496498
free memory                  .5859375
free memory                    .03125

즉 오라클의 동적 뷰들의 대부분은 current 한 내용이 아닌 축적용으로 평균치를 나타 내기 때문에 의미가 없습니다.