database.sarang.net
UserID
Passwd
Database
DBMS
MySQL
PostgreSQL
Firebird
ㆍOracle
Informix
Sybase
MS-SQL
DB2
Cache
CUBRID
LDAP
ALTIBASE
Tibero
DB 문서들
스터디
Community
공지사항
자유게시판
구인|구직
DSN 갤러리
도움주신분들
Admin
운영게시판
최근게시물
Oracle Q&A 41800 게시물 읽기
No. 41800
Procedure의 Cursor와 DB Link의 Close 관련
작성자
jmh
작성일
2020-06-04 11:44
조회수
2,407

Package 안에 여러 Procedure가 존재하고, 하나의 Main Procedure에서 각각의 Procedure 를 호출하고 있습니다.

Procedure에서 Cursor를 사용할 때, Exception시, Cursor를 Close해야 하나요?

프로그램을 간략히 설명드리겠습니다.

DB Link를 이용하여 Remote DB를 조회후 Cursor를 생성하여, Local DB에 값을 저장합니다.

정상인 경우에는 commit과 cursor close를 하지만, Exception시에는 rollback 코드만 있습니다.

Exception에 Cursor Close를 해야하는지 궁금합니다.

그리고, 각각의 Procedure 호출후에도 DB Link를 Close 해야 하는지요? 현재는 전체 프로그램의 종료 또는 Exception시에 Close 하고 있습니다.

DBA가 말하기를, Remote DB에 종료되지 않는 ACTIVE한 Session 이 너무 많이 남아 있다고 합니다. 질문을 드리는 가장 큰 이유는 이것입니다.

그러나 실행중에 Exception이 발생한 적은 없습니다.

질문을 요약하자면,

1. Procedure의 Exception 코드 내에 Close Cursor를 작성해야 하는지

2. DB Link를 이용하여 조회/저장 작업을 하는 각각의 Procedure내에 DB Link 를 Close 하는 코드를 작성해야 하는지


프로그램 코드를 첨부합니다. 고맙습니다.

CREATE OR REPLACE PACKAGE BODY sample_pkg IS

    /********************************************************************************
     * main_prc
     ********************************************************************************
    PROCEDURE main_prc (

    )
    IS
       
    BEGIN
   
        -----------------------------------------------------------------------------
        -- a_prc
        -----------------------------------------------------------------------------
        BEGIN
            sample_pkg.a_prc();
        EXCEPTION
            WHEN OTHERS
            THEN ROLLBACK;
                 RAISE_APPLICATION_ERROR (-20010, 'xxx'); 
        END;
       
        -----------------------------------------------------------------------------
        -- b_prc
        -----------------------------------------------------------------------------       
        BEGIN
            sample_pkg.b_prc();
        EXCEPTION
            WHEN OTHERS
            THEN ROLLBACK;
                 RAISE_APPLICATION_ERROR (-20010, 'xxx'); 
        END;
       
        -----------------------------------------------------------------------------
        -- c_prc
        -----------------------------------------------------------------------------                
       
       
        COMMIT;                              
        DBMS_SESSION.CLOSE_DATABASE_LINK('sample_link');    --> Close DB Link
                                              
       
    EXCEPTION
        WHEN OTHERS
        THEN ROLLBACK;
             DBMS_SESSION.CLOSE_DATABASE_LINK('sample_link');   --> Close DB Link
       
    END main_prc;
   
    /********************************************************************************
     * a_prc --> 각각의 Procedure내에 DB Link 를 Close 하는 코드를 작성해야 하나요?
     ********************************************************************************   
    PROCEDURE a_prc (
   
    )
    IS
        CURSOR sample_cur IS
           
            SELECT a
                  ,b
            FROM   remote_table@sample_link    --> DB Link 사용
            ;
       
    BEGIN
   
        OPEN sample_cur;
           
        LOOP
               
            FETCH sample_cur BULK COLLECT INTO sample_type LIMIT 3000;
               
            FORALL i IN sample_type.FIRST..sample_type.LAST
                   
                INSERT INTO local_table (
                     a
                    ,b
                ) VALUES (
                     sample_type(i).a
                    ,sample_type(i).b
                );               
                   
                COMMIT;           
            
            EXIT WHEN sample_cur%NOTFOUND;
               
        END LOOP;
        CLOSE sample_cur;    --> Close Cursor!!!    
           
    EXCEPTION
        WHEN OTHERS
        THEN ROLLBACK;       --> 여기에서도 Cursor를 Close해야 하나요?
             RAISE;
       
    END;   
   
END sample_pkg;               

 

이 글에 대한 댓글이 총 3건 있습니다.

EXCEPTION 에서 커서를 닫을 필요는 없습니다. 

메인 패키지와 DB링크가 들어간 프로시저의 
수행 시간을 한번 기록해보세요. 
--> DBMS_UTILITY.GET_TIME 

패키지가 자주 수행되는 경우,
1) 메인 패키지가 전체적으로 느리다 --> 패키지이므로 여러 프로시저 처리가 다 되야 끝남. 
2) DB링크를 통한 처리가 늦다 

이런 경우라면 
적으신 대로 DB링크 처리가 active 으로 잡힐 수 있습니다. 

그리고 
단순하게 조건의 데이터를 특정 테이블에 insert into 하는 거라면 
커서 없이 한방 SQL로 끝내는게 좋습니다. 

lucky님이 2020-06-04 16:26에 작성한 댓글입니다.
이 댓글은 2020-06-04 17:24에 마지막으로 수정되었습니다. Edit

lucky 님, 답변 고맙습니다.
Package 내의 여러 Procedure들을 월별로 반복해서 호출하므로, 총 150여회 호출합니다.
어떤 Procedure는 10여분이 소요되고, 어떤 것들은 몇 초만에도 끝납니다.
총 실행시간은 보통 4시간 정도인데, 요즘 가끔 운영DB에서 종료까지 며칠씩 걸리는 현상이 발견됩니다.
데이터는 Freezing하고, 독립DB라서 타 프로그램에 의한 영향도 없습니다.
어떤 Procedure에서는 위에서처럼 조회된 데이터를 가공하여 부분 Commit을 하기도 하고,
어떤 것에서는 한방 Query로 데이터를 생성하기도 합니다.
호출되는 각각의 Procedure에서 DB Link를 Close하는 것은 말이 안 되는 것이겠죠?

jmh님이 2020-06-04 18:01에 작성한 댓글입니다. Edit

db 링크 close 구문은 첨이네요. 

0. 먼저 DB 링크 부분을 고속 처리할 수 있도록 튜닝한다.

이게 힘든 경우, 

1. DB 링크로 호출되는 경우가 빈번하고, DB 링크 호출 프로그램이 시간적으로 중첩되는 경우가 있다. 

--> 중첩되는 경우가 많을 수록 active 세션에서 DB 링크 세션은 증가합니다. 

2. DB 링크 처리 부분을 분리하도록 한다. 

--> PK 및 인덱스 조회 수준의 처리가 아니라면 분리하는게 좋을 듯 합니다. 

3. DB 링크의 데이터 부분을 로컬 DB 테이블로 담고 (여기서 한 트랜잭션을 끝내고)

그 로컬 DB 테이블의 데이터를 기반으로 패키지 처리를 하면 문제 해결이 될 가능성이 높습니다.  

lucky님이 2020-06-05 09:25에 작성한 댓글입니다.
이 댓글은 2020-06-05 09:31에 마지막으로 수정되었습니다. Edit
[Top]
No.
제목
작성자
작성일
조회
41804컬럼별 데이터 조합? [2]
궁금이
2020-06-19
2290
41803게시판을 짜고 primary key가 걸려있는 [2]
이대연
2020-06-05
2109
41802테이블에 전문데이타와 전문 레이아웃을 관리
정희철
2020-06-05
1932
41800Procedure의 Cursor와 DB Link의 Close 관련 [3]
jmh
2020-06-04
2407
41799aix crontab 에서 sqlldr 실행이 안되는데~~ [3]
장현옥
2020-05-28
2441
41798oracle DB 외부 네트워크 접속 [2]
zerros
2020-05-27
1913
41797오라클 Function 오류 ㅠㅠ 한번만 봐주세요. ㅠㅠ [1]
ORACLE
2020-05-18
1984
Valid XHTML 1.0!
All about the DATABASE... Copyleft 1999-2023 DSN, All rights reserved.
작업시간: 0.051초, 이곳 서비스는
	PostgreSQL v16.1로 자료를 관리합니다