태터데스크 관리자

도움말
닫기
적용하기   첫페이지 만들기

태터데스크 메시지

저장하였습니다.

Session cached cursors와 v$open_cursor

오라클 2008.02.05 15:18
누가 이런 질문을 한 적이 있다.

"PL/SQL 내에서 Cursor를 선언해서 사용했는데, PL/SQL 수행이 끝난 후에 v$open_cursor 뷰를 보면 아직 Cursor가 Close되지 않고 Open 상태로 남아 있다. 원래 그런 건가? 아니면 버그인가? Cursor가 계속 Open 되어 있으면 문제가 되지 않는가? 언제 이 Cursor가 닫히는가?"

정답은 Session Cached Cursor와 관련이 있다.

Session Cached Cursor는 한 세션 내에서 세번 이상 수행된 Cursor을 Cache하는 기능을 의미한다. Cache된 Cursor는 Session이 Close되거나 LRU 알고리즘에 의해 최신 Cursor에 자리를 양보할 때 까지 유지된다. 즉, 우리가 강제로 Cursor를 Close 하더라도 실제로는 Cursor를 계속 Open해서 재활용할 수 있도록 해주는 것이다. Session Cached Cursor 영역은 일반 SQL Cursor 뿐만 아니라 PL/SQL에서 사용된 Cursor들을 Cache하는 역할도 같이 수행한다.  이렇게 함으로써 Cursor를 매번 Open하고 Close하는 오버헤드를 줄이게 된다.

하나의 Session이 Cache할 수 있는 Cursor의 수는 SESSION_CACHED_CURSORS 파라미터에 의해 결정된다.

아래 예를 보자.

-- 버전 정보
SQL> select * from v$version;
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.3.0 - 64bi
PL/SQL Release 10.2.0.3.0 - Production
CORE 10.2.0.3.0 Production
TNS for Linux: Version 10.2.0.3.0 - Production
NLSRTL Version 10.2.0.3.0 - Production

-- cursor.sql
declare
v_cursor sys_refcursor;
v_id number;
begin
for idx in 1 .. 10 loop  -- 같은 Cursor를 여러 번 수행
open v_cursor for 'select /* open_cursor */ * from t_session t';
fetch v_cursor into v_id;
close v_cursor;
end loop;
end;
/

-- check.sql. Cursor가 Open되어 있는지 확인
select sql_id, sql_text from v$open_cursor
where upper(sql_text) like 'SELECT /* OPEN_CURSOR%'
/

-- Session A. session_cached_cursors 파라미터 값이 20인 경우
alter session set session_cached_cursors=20;
@cursor.sql

-- Session B
@check.sql
8mt5gcq2a5wjx select /* open_cursor */ * from t_session t
--> Cursor를 Close했음에도 불구하고 Open되어 있다.

-- Session A. session_cached_cursors 파라미터 값이 0인 경우
alter session set session_cached_cursors=0;
@cursor.sql

-- Session B
@check.sql
No rows selected
<-- Cursor가 Close 상태이다.

위의 테스트 결과를 보면 알 수 있듯이 Cached된 Cursor들은 명시적으로 Close하더라도
실제로는 Close가 되지 않는 것을 확인할 수 있다.
이런 현상 때문에 V$OPEN_CURSOR 뷰에서 계속 관찰이 되는 것이다.

9.2.0.5까지는 PL/SQL에서 사용된 Cursor의 Cache를 결정하는 파라미터는 OPEN_CURSOR 였다.
하지만 그 이후로는 SESSION_CACHED_CURSORS 파라미터에 의해 결정된다.

Oracle Reference Manual에 이런 사실이 잘 표현되어 있다.

-- In Oracle 9i Reference Manual
OPEN_CURSORS specifies the maximum number of open cursors
(handles to private SQL areas) a session can have at once.
You can use this parameter to prevent a session from opening an
excessive number of cursors. This parameter also constrains the size of
the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as
statements are reexecuted by a user.

-- In Oracle 10g Reference Manual
This parameter(SESSION_CACHED_CURSORS) also constrains the size of
the PL/SQL cursor cache which PL/SQL uses to avoid having to reparse as
statements are re-executed by a user.

9.2.0.5 이후에는 SESSION_CACED_CURSORS 파라미터 값을 충분히 키워줄 이유가 되는 것이다.
SESSION_CACHED_CURSORS 파라미터에 대한 자세한 설명은 아래 URL을 참조한다.

http://wiki.ex-em.com/index.php/SESSION_CACHED_CURSORS

신고
Trackback 0 : Comments 4
  1. 욱짜 2008.02.13 08:41 신고 Modify/Delete Reply

    위의 테스트 결과는 10g에서만 의미가 있습니다. 9i까지는 다른 결과가 나올 수 있습니다. 버전별로 내부 메커니즘이 다르기 때문에 생기는 현상입니다.

  2. 이민규 2008.06.03 11:35 신고 Modify/Delete Reply

    안녕하세요 select sid, count(*) from v$open_cursor group by sid 의 결과와 파라메터 입니다.


    아래 보시면 파라메터 값이 100인데 cursor가 300개에 달합니다.
    이렇다면 파라메터를 300 이상으로하는 것이 성능을 향상 시킬까요?

    SID COUNT(*)
    ---------- ----------
    6130 292
    9315 296
    6215 297
    9766 298


    show parameter cached

    NAME TYPE VALUE
    ------------------------------------ ----------- ------------------------------
    session_cached_cursors integer 100

  3. 이민규 2008.06.03 11:41 신고 Modify/Delete Reply

    참고로 v$sesstat 에서 값은 매우 작습니다. 그렇다면

    v$open_cursor와 session cursor cache count 의 차이는 왜 나는 걸까요?

    저는 튜닝시 session cursor cache count를 바탕으로 session_cache_cursor를 술였습니다.

    288 : session cursor cache count

    SID STATISTIC# VALUE
    ---------- ---------- ----------
    6146 288 10
    6147 288 9
    6149 288 9
    6150 288 10
    6151 288 14
    6152 288 9
    6153 288 12
    6154 288 7
    6156 288 14
    6157 288 14
    6091 288 10

  4. 욱짜 2008.06.03 13:06 신고 Modify/Delete Reply

    session_cached_cursors의 값을 open_cursors나 v$open_cursor에 비례해서 맞출 필요는 없습니다. v$open_cursor에서 Count가 290개라는 것은 특정 작업을 수행했거나 현재 수행하면서 현재 Open 상태로 볼 수 있는 Cursor가 290개라는 것을 의미합니다.(여기에는 현재 Cache되거나 Keep된 Cursor들도 포함됩니다)

    즉 v$open_cursor = (현재 진짜로 Open되어 있는 Cursor들) + (Cache된 Cursor들) + (Keep된 Cursor들) 로 관찰된다고 보면 될 거 같습니다. 이 Max 값을 OPEN_CURSORS 파라미터로 제어하게 되구요.

    SESSION_CACHED_CURSORS 파라미터는 실제로 Cache Hit가 생기는 정도를 예상해서 적절하게 세팅하면 됩니다. 일단 OPEN_CURSORS와 무관하게 세팅한다고 봐도 무방할 겁니다. OPEN_CURSORS는 그냥 적절히 크게 지정해주면 됩니다.

    v$open_cursor에서 Cursor가 Open되어 있다는 것은 무엇을 의미할까?가 기술적으로는 더 중요한 문제입니다. v$open_cursor의 정의를 v$fixed_view_definition을 통해서 보면 v$kgllk(Library Cache Lock)에서 namespace가 0, 즉 Cursor인 것을 필터링한 결과라는 것을 알 수 있습니다. 즉, Shared Pool에 있는 Cursor Type의 Library Cache Object(LCO)에 대해 Library Cache Lock을 점유하고 있는 상태를 Cursor가 Open되었다고 부릅니다.

    v$open_cursor 뷰에서 관찰되었다는 것은 현재 특정 Cursor Type의 LCO를 사용하기 위해 Library Cache Lock을 점유하고 있는 상태를 의미합니다. Cursor를 Cache하거나 Keep한다는 것은 Library Cache Lock을 놓지 않고 계속 점유해서 LCO가 Shared Pool에서 밀려나지 않도록 방지한다는 것을 의미하구요.(여기에 대해서는 나중에 기회가 있으면 자세하게 논의하기로 하구요...)

Write a comment

티스토리 툴바