OPEN_CURSORS 파라미터를 매우 크게 잡을 경우 고려할 사항

원본출처 : http://211.209.69.159:8000/Orastudy/board.jsp?bbs=tiptech&pg=0&seq=365&act=view

OPEN_CURSORS 파라미터의 default 값은 50이나, 경우에 따라서 이 값을 매우 크게 잡게 될 경우 영향을 미칠 수 있는 점과 cursor가 commit 후에도 open 상태로 있는 것을 방지할 수 있는 방법에 대해 알아보기로 한다.

1. OPEN_CURSORS = n

이 파라미터는 한 session이 한번에 open할 수 있는 cursor의 최대 갯수이다.

이 값이 1이 증가할 때마다 약 25bytes를 fixed UG를 사용하므로, 이 값이 너무 크면 oracle의 memory 성능을 저해시키는 요인이 될 수 있다.

OPEN_CURSORS=1000 으로 설정하였을 경우 25 * 1000 = 25Kbytes 크기의 연속된 영역을 메모리에 할당하게 되는 것이다. 만약 MTS로 운영 중이라면 session의 UGA 정보는 PGA가 아닌 SGA에 저장이 된다는 것을 고려해야 하므로, 한 session의 cursor를 위한 영역을 할당하기 위해 shared pool에 cache되어 있는 다른 object에 영향을 미치면서까지 cache되어야 한다면 open_cursors 파라미터를 줄이는 것이 바람직하다.

2. CLOSE_CACHED_OPEN_CURSORS = TRUE

PL/SQL 내에서 close cursor를 하지 않았지만, commit은 자주 수행하도록 되어 있다면 process가 kill되거나 하면 session이 끊기면서 cursor는 자동으로 close되겠지만, close cursor를 했다 하더라도 cursor가 즉시 release되지 않는 경우가 있을 수 있다.

이 cursor가 자동으로 memory에서 release되는 time을 예측할 수 있으면 좋겠지만, 그렇지 못한 경우 아래와 같은 파라미터를 initSID.ora 화일에 셋팅해 놓으면 PL/SQL 또는 Pro*c 와 같은 application 내에서 commit 또는 rollback을 수행하면 cursor는 자동으로 close가 된다.

close_cached_open_cursors = true

즉, cursor를 memory에서 빨리 release시키는 것이 목적이라면 위와 같이 파라미터를 셋팅하는 방법도 있다는 것이다.

이 파라미터가 false로 되어 있다면 동일한 형태의 연속적인 executions가 새로운 cursor를 open할 필요가 없도록 하기 위하여 PL/SQL에 의해 open된 cursor를 open 상태 그대로 유지하겠다는 것을 의미한다.

이 파라미터에 대한 보다 자세한 내용은 다음과 같다.

PL/SQL을 사용할 때 해당 procedure에서 참조하는 object에 대해 그 안의 cursor들이 memory에 cache되어 있는데, commit 또는 rollback이 일어날 때 이 cursor들이 자동으로 close되도록 하는 역할을 한다.

즉, transaction 단위로 close한다. (commit, rollback)그러나, 이 값이 true로 되어 있으면 latch bottle-neck이 발생할 수 있으므로, true로 할지 false로 할지에 대해 신중하게 값을 결정해야 한다.

만약, application 내에서 재사용의 빈도가 매우 높은 SQL 문들을 사용한다면 memory 내에서 현재 수행 중인 object에 대해 그 object를 참조하는 cursor가 close되는 것이 좋지 않으므로, 이 값을 false로 설정해 두는 것이 바람직하다.

3. SESSION_CACHED_CURSORS = N

과거에 session_cached_cursors=n 이라는 파라미터도 존재했었다. session_cached_cursors 파라미터는 7.1까지만 존재하는 파라미터이다. 이 파라미터에 지정한 갯수는 한 session 동안 open된 상태로 cache에 유지할 수 있는 cursor의 갯수이다.

4. Pro*c 또는 PL/SQL 내에서 COMMIT WORK RELEASE;

commit 을 수행하면 현재 수행 중인 transaction을 종료시키고, db에 그 때까지의 변화들을 반영시키는 작업이 일어나고, 사용 중이던 resource들을 반환한다.PL/SQL 내에서는 commit을 했다 하더라도 아직 END가 완벽하게 마무리된 것은 아니므로, COMMIT WORK RELEASE; 를 해주면 모든 resource들을 free시키고 해당 session은 database로부터 disconnect하게 된다.

참고로 8.1.5 부터는 commit force; 라는 명령이 있는데 이것은 current transaction만을 commit 시키는 option이다.

5. CURSOR_SPACE_FOR_TIME = TRUE

이 파라미터는 shared sql 영역의 object를 참조하는 open cursor가 있는 한, 해당 object는 항상 shared pool에 keep시키는 역할을 한다