작년에 회사에서 기술면접에 필요한 문제를 여러 개 만들었는데 그 중에 하나를 소개한다. PL/SQL의 예외처리에 관련된 것이고 개념문제이므로 응시자들이 어렵지 않게 풀 수 있을 거라 생각하였다. 하지만 결과는 예상 밖이었다.

 

오라클 내부구조, OWI, AWR, Query Transformation 등 어려운 개념은 맞추는 사람이 있는 반면 PL/SQL의 기본에 속하는 예외처리에 대해서는 아무도 정답을 맞추지 못했다. 정답에 근접한 사람도 아무도 없었다. 도대체 얼마나 어려운 문제이길래? 여러분도 아래의 문제를 풀어보기 바란다.

 

문제3)

1. Built In Exceptions

2. User-Defined Exceptions

3. RAISE_APPLICATION_ERROR  

4. EXCEPTION_INIT Pragma


위의 네 가지는 오라클 PL/SQL 상에서 예외처리방법을 나열한 것이다.
이 네 가지의 차이점을 설명하시오.

 

 

이 네 가지의 차이점을 정확히 알고 있는 사람은 아래의 파일을 다운받을 필요가 없다. 하지만 차이점을 정확히 설명할 수 없다면 이 기회에 정리하기 바란다. 4(8 페이지) 이므로 10분만에 볼 수 있을 것이다. 정답은 마지막 페이지에 있다.

 

모든 분야에는 기본이라고 불리는 것이 있다. 오라클의 세계에서도 예외는 아닌것 같다. 필자 또한 기본을 놓치지 않으려고 노력하는 사람중의 하나이다.


invalid-file

Oracle PL/SQL - Exceptions 정리

Posted by extremedb
,
얼마전에 필자는 다음과 같은 질문을 받았다.
"PL/SQL 의 기능중에 커서(Select 문)을 인자로 받아서 복잡한 계산을 수행후 결과를 집합으로 RETURN 하는 기능이 있습니까?"
이런 경우 필자는 예외없이 Pipelined Table Function 을 권장한다.(단 버젼이 8i 이상이라면)
Pipelined Table Function 를 사용하여야 하는 이유는 4가지 이다.

1.PL/SQL 의 유일한 단점은 부분범위처리가 안된다는 것이다.
  즉 모든처리가 끝나야만 결과가 화면에 Return 된다는 것이다.
  Pipelined Table Function 을 사용하면 이런단점을 극복할수 있다.
  당연히 조회화면등에서 성능이 개선된다.
  이개념을 이용하려면 Pipe Row 기능을 이해해야한다.
  Pipe Row 기능은 9i 이상에서만 사용가능하며 8i 라면 Table Function 만 사용이 가능하므로
  부분범위 처리가 불가능하다.

2.SQL 이 길어서 A4 용지 기준으로 1 ~ 2 페이지가 넘어가는 경우가 있다.
  이런경우 모니터링을 해보면 엄청난양의 SQL 이 네트웍을 타고 DBMS 에 전달 된다.
  이런 SQL 들이 여러명이 사용하고 자주 사용된다면 네트웍의 부하가 상당하므로
  Pipelined Table Function 을 사용하면 SQL 이 1~2줄로 줄어들므로 네트웍 튜닝이 가능해진다.
  이부분의 모니터링은 AutoTrace 의  "bytes received via SQL*Net from client" 부분을
 살펴보면 된다.
 아래그림의 선택된 부분이 문제의 네트웍 전송량이다.
 아래를 결과를 보면 DB 서버로 부터 결과를 전송받은 양보다 Client 에서 SQL 문을
 DB 서버로 전송한 데이터양이 더크다.
사용자 삽입 이미지















이런일이 많을경우 전체적인 시스템이 느려지게 되는데 왜느린지 알수가 없는경우가 많다.
왜냐하면 시스템 Wait Event 모니터링을 해도 이런종류의 Event 는 대부분의 DBA 들이 Idle Event 로 생각하기 때문이다.
현재 시중에 있는 일반적인 Wait Event 책이 사람들을 그렇게 생각하도록 만든다.
이런경우는 Idle Event 로 생각하면 안된다.
대부분의 그런종류의 책들은 Event 들의 원인 + 조치방법으로 되어 있다.
하지만 이런경우 해법을 찾을수 있는 책은 거의없다.
Rechmond See 의 "Oracle Wait Interface" 라는 책을 보면 SQL 에 문제가 있거나 네트웍 성능이 문제라고 되어 있지만 그렇지 않은 경우가 대부분이다.
왜냐하면 SQL 이 길다고 그 SQL 이 잘못된것은 아니며, 대부분 네트웍을 점검해봐도 정상이기 때문이다.  
유일하게 욱짜님의 책에 "실행횟수가 많은경우 DBMS CALL 을 줄이고 PL/SQL 로 처리하라" 고 되어있다.
하지만 SQL이 조회화면의 SELECT 문일 경우라면?
이경우는 DBMS CALL 을 줄일수도 없고 DML(INSERT/UPDATE/DELETE) 처럼 PL/SQL로 바꿔서 Array Processing 으로 처리할수도 없는 노릇이다.
이때의 Solution 은 단한가지이다.
SQL 이 Select 이면 아래 예제에서 사용될 Table 함수나 Ref 커서를 사용한 Procedure 를 이용하면 된다.
위의 기능들은 대부분의 사람들이 알고 있지만 위의 기능을 SQL*Net message from client Event  의 해법으로 생각하는 사람들이 거의없는 이유는 무었일까?

3.SQL 을 인자로 던질수 있으며 결과가 Multi Column + Multi Row 로 Return 될수 있다는 점이다.

4.모듈로써 공유가 가능하다는점
  이것이 안된다면 복잡한 계산을 해야하는 모든곳에서 기능을 구현하여야만 한다.

필자는 1,2번이 맘에 들지만 개발자들은 3,4 번을 가장 맘에 들어한다.(아마도 입장 차이인가 보다.^^)
아래의 Script 를 보자.
Script 상의 오른쪽의 주석을 참조하기 바란다.(Oracle 의 HR 스키마에서 테스트 하면됨)

1.먼저 패키지 Header 를 만든다.

CREATE OR REPLACE PACKAGE refcur_pkg IS
 
    TYPE refcur_t IS REF CURSOR            -- cursor type 을 선언한다.
    RETURN employees%ROWTYPE; 
   
    TYPE outrec_typ IS RECORD (            -- structure type을 선언한다.
       var_num employees.employee_id%type,
       var_char1 VARCHAR2(30),
       var_char2 VARCHAR2(30)   );
                             
    TYPE outrecset IS TABLE OF outrec_typ; -- 위에서 선언한 structure 를 배열로 type 으로 선언한다
     
    FUNCTION f_trans(p refcur_t)         -- 커서를 인자로 받아서 Structure 배열을 Return 하는 함수를 선언한다.
    RETURN outrecset PIPELINED;    -- 위에서 선언한 Structure 배열을 사용함.
                                                     -- 반드시 PIPELINED를 명시해야함.
   
END refcur_pkg;
/

 
2.패키지 Body 를 만든다.

CREATE OR REPLACE PACKAGE BODY refcur_pkg IS

    FUNCTION f_trans(p refcur_t)
    RETURN outrecset PIPELINED IS -- Structure 배열을 Return 하는 함수임.  
 
        out_rec outrec_typ;               -- PACKAGE Header 에서 선언한 structute type 을 변수로 선언한다.
        in_rec p%ROWTYPE;            -- p cursor 네의 의 모든컬럼을 변수로 선언한다.

        BEGIN

          LOOP

              FETCH p INTO in_rec;
              EXIT WHEN p%NOTFOUND;
              -- first row
              out_rec.var_num := in_rec.employee_id;
              out_rec.var_char1 := in_rec.first_name;
              out_rec.var_char2 := in_rec.last_name;
              PIPE ROW(out_rec);     --> employee_id, first_name, last_name 으로 1 row 를 즉시 return 한다.
              -- second row
              out_rec.var_char1 := in_rec.email;
              out_rec.var_char2 := in_rec.phone_number;
              PIPE ROW(out_rec);     --> employee_id, email, phone_number 으로 1 row 를 즉시 return 한다.

          END LOOP;

          CLOSE p;

        RETURN; -- return 하는 변수를 지정하지 않는다.(LOOP 내에서 모두 Return 되었기 때문이다.)

        END;
END refcur_pkg;
/

위 함수의 Logic 을  설명하면 함수는 사원 성명에 대하여 1줄 return 하고
사원의 번화번호및 email 에 대하여 또 한줄 return 한다.
위 함수의 특징은 Pipe Row 에 있다.
Pipe Row 를 명시하면 Loop 내에서 결과를 즉시  Return 한다.
즉 모든 Loop 가 끝나길 기다릴 필요가 없는것이다.
물론 전체를 처리해야만 하는경우는 Pipe Row 를 명시하지 않으면 되고 Bulk Collect 기능을 권장한다.
이때는 함수 선언시 PIPELINED 를 명시하면 안되며 RETURN 시의 변수도 지정해야한다.
Pipe Row 와 PIPELINED 는 항상 Pair 로 움직여야 한다.

3.만들어진 Pipelined Table Function 를 사용한다.


SELECT *
FROM  TABLE(refcur_pkg.f_trans(CURSOR(SELECT *
                                                               FROM employees
                                                             WHERE department_id = 60) ) );

4.결과

사용자 삽입 이미지














결론 : Pipelined Table Function 함수는 부분범위 처리가 가능하며 결과를 Row Set 으로 Return 할수있다.
         이기능은 SQL*Net message from client Event  과다현상의 훌륭한 해결책이다.
         이기능을 잘 사용하면 다양한 분야에 활용할수 있다
.

Reference : 10g PL/SQL User's Guide and Reference 의 Tuning PL/SQL Applications for Performance 부분.

편집후기 :
Table 함수와 테이블간의 조인이 가능한지 질문이 들어왔다.
당연히 된다.
한가지 주의할점은 조인절이 따로 필요없고 Table 함수의 인자로 컬럼의 Value 가 필요하다는 것이다.
아래에 예제를 참조하라.
아래 예제에서 CAST 함수를 쓴이유는 버젼이 8i 이기 때문이다. (9i 이상은 필요없음)

select X.SUBCON_CD,
           X.SUBCON_NM,
           X.SUBCON_CONTI_CLS,
           X.SUBCON_DESC,
           Y.COM_CLS4_NM,
           Y.COM_CLS4_ALIAS_CD,
           Y.COM_CLS2,
           Y.COM_CLS4_DESC
   From TOLC_S_SUBCONTINENT X,
           TABLE( CAST( COMM.get_com_info(X.SUBCON_CONTI_CLS) AS COMLIST_T) ) Y
        

'Oracle > PL/SQL Tuning' 카테고리의 다른 글

Cursor For Loop 사용시 DML 문의 튜닝  (0) 2008.11.24
Posted by extremedb
,

묵시적인 형변환을 피하라

SQL 에서 묵시적인 형변환(Implicit Datatype Conversion) 을 피하라.
위와같은 말을 언젠가 들어보았을것이다.
WHERE 절의 묵시적 형변환에 의한 Full table scan 은 잘알려져 있다 .
이문제는 Oracle 10g 부터 SQL 이 PL/SQL내에서 사용된다면 dbms_warning 패키지나 plsql_warnings 기능을 사용하면 식별될수 있다.
아래 예제를 보자.

SQL> ALTER SESSION SET PLSQL_WARNINGS='ENABLE:ALL'; --> warning 가능 활성화

SQL> CREATE TABLE t ( a VARCHAR2(10) ); --> varchar2 타입으로 컬럼생성
Table created.

SQL> CREATE OR REPLACE PROCEDURE p
2 IS
3 BEGIN
4 INSERT INTO t VALUES ( 10 ); --> number 타입으로 insert
5 END p;
6 /

SP2-0804: Procedure created with compilation warnings

SQL> SHO ERR
Errors for PROCEDURE P:

4/26 PLW-07202: bind type would result in conversion away from column type
-->4번째 라인에서 묵시적인 형변환이 일어나고 있음을 알려준다.

그런데 이런 묵시적인 형변환에의한 성능저하가 SQL에만 해당되는 이야기인가?
묵시적인 형변환에 의한 PL/SQL 자체의 성능도 한번쯤 의문을 가져볼 필요가 있다.

아래 예제는 데이터 타입선정을 잘못하면 프로그램이 얼마나 망가질수 있는지를 잘나타내고 있다.

create or replace procedure imp_type_conv_test is
x date;
y varchar2(12) := '01-MAR-03'
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 묵시적인 형변환
end loop; dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/

결과 : 826hsec

create or replace procedure exp_type_conv_test is
x date;
y x%type := to_date('01-MAR-03'); --> date 형으로 선언하고 미리 conversion 함
t number := dbms_utility.get_time;
begin
for i in 1 .. 1000000 loop
x := y; --> 형변환이 일어나지 않음
end loop;
dbms_output.put_line((dbms_utility.get_time-t)'hsec');
end;
/

결과 : 38hsec --> 무려 2200% 가까이 성능이 향상됬다.

결론 :
Implicit Datatype Conversion 은 SQL 의 성능 뿐만아니라 PL/SQL 자체의 성능도 저하시킴을 알수 있다.
명시적으로 TO_DATE, TO_NUMBER 등의 함수를 사용함으로써 좀더 직관적이고
성능면에서도 유리한 프로그램을 개발할수 있다.

참조서적 : Mastering Oracle PL/SQL(Connor McDonald )

'Oracle > PL/SQL Pattern' 카테고리의 다른 글

PL/SQL-면접문제  (808) 2010.05.07
오라클에서 is_number, is_date 함수 사용하기  (457) 2009.11.11
PL/SQL 에서 NUMBER 타입의 성능 테스트  (0) 2008.05.02
Posted by extremedb
,

PL/SQL 에서 NUMBER 타입의 성능 테스트

10g 버젼까지는 소수점없는 숫자타입 연산에서 제일빠른 숫자타입은 pls_integer 였다.
이제 11g 에서 기존 pls_integer 의 sub type 인 simple_integer 이 나왔으므로 성능관점에서
어떤것이 가장빠른것인지 test를 해볼 필요가 있다.
PL/SQL 의 성능관점에서 11g 에 새로운 simple_integer 타입에 대하여 간단한 테스트를 진행하고자 한다.
진행하기전에 먼저 simple_integer 의 특징에 대해 간단히 설명한다.

simple_integer 의 특징
1)simple_integer 는 기존 pls_integer 의 sub type 이다.
2)simple_integer 의 사용범위는 2,147,483,648 ~ 2,147,483,647 이다.
3)null 값을 가지지 못한다. --> default 로 0 을 주어야 한다.
4)성능면에서 최적이다. 특히 native 로 컴파일 할때는 pls_integer 보다 4~5 배 빠르다.

아래는 number 와 pls_interger,simple_integer 를 비교실험하는 간단한 프로시져이다.
로직은 단순히 1씩 증가하는 연산이다.
성능테스트

CREATE OR REPLACE PROCEDURE plsql_number_test(v_loop in number) as
l_start NUMBER;
l_number NUMBER := 0;
l_number_incr NUMBER := 1;
l_pls_integer PLS_INTEGER := 0;
l_pls_integer_incr PLS_INTEGER := 1;
l_simple_integer SIMPLE_INTEGER := 0;
l_simple_integer_incr SIMPLE_INTEGER := 1;

BEGIN

-- number 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_number := l_number + l_number_incr;
END LOOP;

DBMS_OUTPUT.put_line('NUMBER : ' (DBMS_UTILITY.get_time - l_start) ' hsecs');

-- pls_integer 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_pls_integer := l_pls_integer + l_pls_integer_incr;
END LOOP;
DBMS_OUTPUT.put_line('PLS_INTEGER: ' (DBMS_UTILITY.get_time - l_start) ' hsecs');

--simple_integer 형 test
l_start := DBMS_UTILITY.get_time;
FOR i IN 1 .. v_loop LOOP
l_simple_integer := l_simple_integer + l_simple_integer_incr;
END LOOP;
DBMS_OUTPUT.put_line('SIMPLE_INTEGER: ' (DBMS_UTILITY.get_time - l_start) ' hsecs');

END plsql_number_test;
/

실행1
SQL> plsql_number_test(100000000 );

결과1
NUMBER : 1217 hsecs
PLS_INTEGER: 546 hsecs
SIMPLE_INTEGER: 512hsecs --> 가장빠르나 PLS_INTEGER 와는 거의 차이가 나지않는다.

NATIVE 모드로 컴파일 한다.
ALTER SESSION SET PLSQL_CODE_TYPE=NATIVE;
ALTER PROCEDURE plsql_number_test COMPILE;

실행2
SQL> plsql_number_test(100000000 );

결과2
NUMBER : 386 hsecs
PLS_INTEGER: 129 hsecs
SIMPLE_INTEGER: 32 hsecs -->PLS_INTEGER 보다 무려 4배정도가 빠르다.


결론 :
native 로 컴파일 했을때 모든 숫자형식이 다빨라졌지만 특히 SIMPLE_INTEGER의 성능향상이 눈에 뛴다.
2,147,483,648 ~ 2,147,483,647 범위내에서 소수점이 없는 숫자연산을 할경우에는
SIMPLE_INTEGER 를 써야 한다.
물론 10g 이하버젼 이라면 number 형보다 2~3 배 이상 빠른 PLS_INTEGER 를 사용해야 할것이다.
아쉬운점은 PL/SQL 뿐만 아니라 테이블의 데이터 타입으로도 사용할수 있으면 하는것이다.
12버젼에서 컬럼타입으로 사용할수 있게 되기를 기대해보면서 이글을 마친다.

'Oracle > PL/SQL Pattern' 카테고리의 다른 글

PL/SQL-면접문제  (808) 2010.05.07
오라클에서 is_number, is_date 함수 사용하기  (457) 2009.11.11
묵시적인 형변환을 피하라  (414) 2008.05.09
Posted by extremedb
,