티스토리 뷰

Oracle/Oracle-Admin

ORA-4031 expdp가 실패

니플하임_ 2016.08.26 14:47

오라클에서 expdp가 실패했다.  

ORA-04031: unable to allocate 376 bytes of shared memory 

("streams pool","unknown object","streams pool","kwqbsinfy:cco")


shared memory 내의 streams_pool 영역을 할당을 더 못해서 발생하는 문제.


[SGA공간확인]

select name, value, display_value

    , ISDEFAULT, ISSES_MODIFIABLE

    , ISSYS_MODIFIABLE, ISINSTANCE_MODIFIABLE 

from v$parameter where name like 'sga%';


streams_pool size 를 늘려주는 것이 좋다고 판단할 수 있으나,


[stream_pool설정확인]

show parameters streams_pool_size; 


위 실행결과 값이 '0'이고 이는 자동할당이므로 

shared memory 영역이 가득 찬 것이라 볼 수 있다.

또한, streams_pool_size 늘려주게 되면, 해당 크기만큼 

sga내 공간을 고정으로 점유하므로 다른 영역의 메모리 문제가 야기될 수 있다


[유사 SQL에 대해 동일 플랜을 가지는 케이스가 shared memory 내에 1000개 이상 겹쳐있는지 확인]

select *

from   (select plan_hash_value,

               count(plan_hash_value) cnt ,

               substr(sql_text, 1, 100) 

        from   v$sql

        where  plan_hash_value > 0

        group by plan_hash_value , substr(sql_text, 1, 100)

        order by count(plan_hash_value) )

where  cnt >= 1000;


3000개가 넘는 쿼리 발견.. 게다가 들어온 곳이 개발팀의 배치서버이며, 쿼리 플랜을 확인해보니 엉망;;

원인은 shared pool 공간 단편화로 인해 발생한 것으로 파악되었다.


[shared pool 조각모음]

alter system flush shared_pool; 


로 단편화를 일시적으로 해결할 수 있지만, 발생하게된 원인이 파악되지 않았으므로 일단 패스;;

원인 쿼리는 v$sqlarea 에서 동일한 쿼리가 여러개 겹쳐있는지, 

위와 같이 v$sql 에서 동일 플랜을 가지는 유사 쿼리가 여러개 겹쳐있는지 등으로 확인이 가능하다.


단편화가 발생하는 원인은. DB로 다음과 같은 쿼리를 실행할때, (idval 값이 계속 변경되면서 적용된다고 가정하자)


String sql = "select name from tab1 where id=?";

pstmt = conn.prepareStatement(sql); 
pstmt.setInt(idval); 

rs = pstmt.executeQuery();


하면 이 SQL은 1번만 파싱하고


String sql = "select name from tab1 where id="+ idval;

stmt = conn.createStatement();

ResultSet rs = stmt.executeQuery(sql);


을 사용할 경우 oracle 이 계속 새로 파싱하므로 전자의 경우에 v$sql 이나 v$sqlarea에 

동일한 쿼리들이 동일 실행계획으로 쌓이게 된다. 


개발팀에 가장 건수가 많았떤 대상 쿼리 1개를 제공해드리고 제거 혹은 prepare 방식으로 전환요청으로 일단 마무리.

이제 shared_pool 조각모음 한번 돌려주고 쉬어줌.



댓글
댓글쓰기 폼