oracle pre complier의 그 유명한 hold_cursor/release_cursor option

이 두개의 OPTION은 Precompile된 모든 program의 implicit, exeplicit cursor 에 모두 영향을 준다. (pro*ada의 경우는 약간의 예외가 있다.)

CURSOR에는 program cursor와 oracle cursor 두 가지가 있다.

program cursor는 SQL문으로 인해 생기는 data 구조이다. program cursor는 procompiler에 의해 발견된 각 SQL문 마다 선언된다. 다음의 문장이 program 안에 있다고 하자.

EXEC SQL DECLARE SEL_EMP_CURS CURSOR FOR...
EXEC SQL INSERT...

그렇다면 c1과 c2의 두개의 program cursor가 선언될 것이다.

Oracle cursor ( context area 라고도 한다.)는 실행 중에 생성이 된다. 이 공간은 parse된 문장과, host 변수의 주소값, 그 외에 SQL문을 실행하기 위해 필요한 정보를 가지고 있다.

이 두 개의 cursor는 cursor cache를 통해서 서로 연결되어 있다. 이 cursor cache의 초기 크기는 MAXOPENCURSORS option에 의해 결정이 된다. 아래의 그림은 이러한 연관 관계를 설명한다.




이러한 관점에서 HOLD_CURSOR와 RELEASE_CURSOR는 연관 관계는 다음과 같다.

HOLD_CURSOR option는 program cursor와 해당 cache와의 관계를 다룬다. 반면, RELEASE_CURSOR는 Oracle cursor와 cache와의 관계를 다룬다. 만약 자주 재사용되는 SQL문이 보다 빠른 속도를 갖게 하기 위해서는 이 SQL문과 예상되는 Oracle cursor가 붙어 있도록 하는 것이 좋다.

SQL문이 Oracle cursor와 붙어있다는 말은 SQL문과 해당 Oracle cursor 사이의 연결이 지속적으로 유지되고 있는것을 의미한다. 위에서 언급한 바와 같이 Oracle cursor(context area)에서는 parse된 문장이나 host변수의 주소값 등과 같이 중요한 정보가 들어 있게 되므로 SQL문장과 Oracle cursor(context area) 를 붙여두는 것이 유용하다.


이와 같이 문장과 cache를 붙여두기 위해서 HOLD_CURSOR와 RELEASE_CURSOR가 사용된다.

HOLD_CURSOR=YES option이 사용되면 cache들은 재사용이 가능하도록 flag을 표시 할 수 없도록 한다.
이것은 매우 중요한 의미를 가지는데, 만약 cache들이 사용이 되어지고 새로운 SQL문마다 각기 새로운 cache들이 할당이 된다면, MAXOPENCURSORS에 의해 결정된 수만큼의 cache가 할당된 후에 추가의 cache는 재사용이 가능하도 flag가 표시 된 cache를 사용하기 때문이다.

위의 그림을 참조로 예를 들면 다음과 같다.
C(1) cache가 재사용이 사능하도록 표시가 되어있고 EXEC SQL SELECT 문이 실행이 된다고 가정을 할 경우, program cursor P(MAXOPENCURSORS+1)가 생성되고 이것은 cache와 Oracle cursor가 필요하다. 그러나 이때 MAXOPENCURSORS에 의해 결정된 값만큼의 숫자의 cache가 이미 사용되어졌다면, 이 문장은 C(1)의 cache와 거기에 해당하는 Oracle cursor를 할당받는다. 그리고 이 cache과 Oracle cursor는 비워지고 새로운 SQL문으로 다시 parse가 되어진다.


위의 option과 함께 사용되는 것이 RELEASE_CURSOR=NO이다. 이것은 cache들과 Oracle cursor사이의 관계를 규정한다. 이 option은 parse된 문장이 실행하고 난후의 상태를 관리한다. 이때 할당된 memory는 사용가능한 상태로 유지 되어진다. 이 memory를 풀어주기 위해서는 RELEASE_CURSOR=YES를 사용한다. 이 option을 사용 하면 이 cache에 연결되어 있는 다음 문장들은 추가적으로 parse를 다시 해야하는 부담이 있다.

HOLD_CURSOR와 MAXOPENCURSORS는 밀접한 관계를 가진다.

만약 모든 cursor cache들이 현재 "재사용 불가"로 표시되어있다면 ( 이런 경우는 explicit하게 열린 cursor가 close되지 않은 상태로 fetch를 진행하는 경우와 같이 cursor cache를 사용하는 모든 문장이 실행중인 경우와 HOLD_CURSOR option을 사용한 경우등이 있다.),  새로운 cursor를 위해서는 실행중에 cursor cache를 확장해야 한다. (즉,  MAXOPENCURSORS가 10이라면, 11번째의 cursor cache를 생성한다.) 이때 11 번째 생성된 cache는 cursor가 close되어도 제거되지 않는다.

MAXOPENCURSOR를 작게 잡는 것은 memory를 절약 할수있으나 cache가 추가될때는 비용이 많이든다. 반면, 높게 잡을 경우는 추가 비용에 대한 부담이 줄어드는 많큼 필요이상의 memory를 사용하게 된다. 그리고 무조건 가장 오래된 cache를 재사용하도록 허용하는 것이 옳바른 것은 아니다.

만약 10개의 explicit cursor를 선언해서 open한 user가 11번째의 cursor를 사용하기 위하여 가장 오래된 program cursor를 재사용하게 된다면 user는 첫번째 cursor에 대한 위치를 잃어버리게 되어 이 곳에서 fetch를 수행할 수 없게 된다.

만약 program 안에서 문장의 재사용이 일어나지 않을 경우는 HOLD_CURSOR=NO, RELEASE_CURSOR=YES를 사용한다. HOLD_CURSOR=NO 는 cache들이 필요에 따라 자동적으로 "재사용"으로 표시되게 한고, RELEASE_CURSOR=YES는 Oracle cursor 가 자동적으로 해제되고 parse된 문장을 잃어 버리게 한다. site의 memory에 대한 문제로 인해 Oracle cursor들의 숫자가 제한을 받을 경우는 이 option을 사용 해야 한다.

이 때 만약 RELEASE_CURSOR=YES를 사용하게되면 자동적으로 HOLD_CURSOR=YES는 사용할수 없게 된다. RELEASE_CURSOR=YES가 Oracle cursor와 cache사이의 연결 을 끊어 버리고 Oracle cursor를 해제 시켜 버린다. 그러므로 심지어 program cursor가 cache와 HOLD_CURSOR=YES에 의해 연결되어 있어도 memory를 다시 할당하고 다시 parse를 해야 한다.그러므로 RELEASE_CURSOR=YES를 주면 HOLD_CURSOR=YES를 준 이점이 하나도 없다.

이 내용은 oracle discussion forum의 "hold_cursor & release_cursor"와 아래 첨부한 oracle internet seminar 자료, 그리고 oracle metalink의  Note 2055810.6 "Precompiler HOLD_CURSOR and RELEASE_CURSOR Options"를 참고했습니다.
.

tech-iseminar_precompiler.pdf