POWER 함수의 CPU 연산 #1

POWER 함수의 두 번째 매개변수에 소수를 사용하면 과도한 CPU 연산이 발생할 수 있다. 관련 내용을 살펴보자.

테스트를 위해 아래와 같이 테이블을 생성하자.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 AS SELECT 3 AS c1 FROM XMLTABLE ('1 to 1000000');

아래 쿼리는 POWER 함수의 두 번째 매개변수로 정수, 유한소수, 무한소수를 사용했다. 소수를 사용한 2-2, 2-3번 쿼리는 정수를 사용한 2-1번 쿼리보다 26초 이상의 시간이 소요되었고, 무한소수를 사용한 2-3번 쿼리는 유한소수를 사용한 2-2번 쿼리보다 2초 이상의 시간이 소요되었다.

-- 2-1
SELECT COUNT (POWER (c1, 2.0)) FROM t1;

경   과: 00:00:00.20

-- 2-2
SELECT COUNT (POWER (c1, 0.1)) FROM t1;

경   과: 00:00:26.21

-- 2-3
SELECT COUNT (POWER (c1, 1/3)) FROM t1;

경   과: 00:00:28.30

아래 쿼리는 POWER 함수의 첫 번째 매개변수로 정수, 유한소수, 무한소수를 사용했다. 두 번째 매개변수와 달리 수행 시간의 차이가 크지 않은 것을 확인할 수 있다.

-- 3-1
SELECT COUNT (POWER (2.0, c1)) FROM t1;

경   과: 00:00:00.22

-- 3-2
SELECT COUNT (POWER (0.1, c1)) FROM t1;

경   과: 00:00:00.23

-- 3-3
SELECT COUNT (POWER (1/3, c1)) FROM t1;

경   과: 00:00:00.53

아래는 두 매개변수에 모두 무한소수를 사용했다. 33.93초가 소요되었다. 이는 순수한 CPU 연산이므로 병렬 쿼리 외에는 별다른 해법이 없다.

-- 4
SELECT COUNT (POWER (1/c1, 1/c1)) FROM t1;

경   과: 00:00:33.93

다행히 POWER 함수의 입력값의 종류가 적다면 아래와 같이 스칼라 서브 쿼리 캐싱으로 쿼리의 성능을 개선할 수 있다. 참고로 12.1 버전부터 EXPRESSION EVALUATION 오퍼레이션에 의해 병렬 서버에서도 스칼라 서브 쿼리를 수행할 수 있다.

-- 5
SELECT COUNT ((SELECT POWER (1/c1, 1/c1) FROM DUAL)) FROM t1;

경   과: 00:00:00.12

스칼라 서브 쿼리 캐싱으로 해결이 어려운 경우라면 DETERMINISTIC 함수나 FUNCTION RESULT CACHE를 고려해볼 수 있다.

-- 6-1
CREATE OR REPLACE FUNCTION f1 (i_n2 IN NUMBER, i_n1 IN NUMBER)
RETURN NUMBER DETERMINISTIC
IS
BEGIN
    RETURN POWER (i_n2, i_n1);
END;
/

-- 6-2
SELECT COUNT (f1 (1/c1, 1/c1)) FROM t1;

경   과: 00:00:00.70

-- 6-3
CREATE OR REPLACE FUNCTION f2 (i_n2 IN NUMBER, i_n1 IN NUMBER)
RETURN NUMBER RESULT_CACHE
IS
BEGIN
    RETURN POWER (i_n2, i_n1);
END;
/

-- 6-4
SELECT COUNT (f2 (1/c1, 1/c1)) FROM t1;

경   과: 00:00:02.28