Science of Database

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 )

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버젼에서 컬럼타입으로 사용할수 있게 되기를 기대해보면서 이글을 마친다.