2018년 1월 29일 네이버 카페에 작성한 "_suppress_identifiers_on_dupkey 파라미터" 글을 보완한 글입니다.
에러가 대량으로 발생하면 에러 메시지를 생성하기 위한 부가적인 부하로 인해 성능 저하가 발생할 수 있습니다.
테스트를 위해 아래와 같이 테이블을 생성하겠습니다.
-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 NUMBER);
CREATE UNIQUE INDEX t1_u1 ON t1 (c1);
아래 예제는 INSERT 문을 수행하고 DUP_VAL_ON_INDEX 예외가 발생하면 UPDATE 문을 수행하는 방식을 보여줍니다. 이런 방식을 INSERT & UPDATE 방식이라고 합니다.
-- 2
SET SERVEROUT ON
DECLARE
v_sqlerrm VARCHAR2(4000);
BEGIN
FOR i IN 1..1e5 LOOP
BEGIN
INSERT INTO t1 (c1) VALUES (i);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
v_sqlerrm := SQLERRM;
UPDATE t1 SET c2 = i WHERE c1 = i;
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE (v_sqlerrm);
END;
/
경 과: 00:00:01.46
동일한 예제를 다시 수행하면 ORA-00001 에러가 발생하고 12.62초가 소요됩니다.
-- 3
DECLARE
v_sqlerrm VARCHAR2(4000);
BEGIN
FOR i IN 1..1e5 LOOP
BEGIN
INSERT INTO t1 (c1) VALUES (i);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
v_sqlerrm := SQLERRM;
UPDATE t1 SET c2 = i WHERE c1 = i;
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE (v_sqlerrm);
END;
/
ORA-00001: 무결성 제약 조건(TUNA.T1_U1)에 위배됩니다
경 과: 00:00:12.62
ORA-00001 에러가 발생하면 에러 메시지에 소유자와 인덱스 명을 표시하기 위해 아래의 쿼리가 재귀적으로 수행됩니다.
-- 4-1
SQL ID: 2jfqzrxhrm93b Plan Hash: 1027684349
select /*+ rule */ c.name, u.name
from
con$ c, cdef$ cd, user$ u where c.con# = cd.con# and cd.enabled = :1 and
c.owner# = u.user#
-- 4-2
SQL ID: 2skwhauh2cwky Plan Hash: 2422281469
select o.name, u.name
from
obj$ o, user$ u where o.obj# = :1 and o.owner# = u.user#
_suppress_identifiers_on_dupkey 파라미터를 TRUE로 설정하면 ORA-00001 에러 메시지에 소유자와 인덱스 명이 표시되지 않습니다.
NAME VALUE DEFAULT_VALUE DESCRIPTION
------------------------------- ----- ------------- --------------------------------
_suppress_identifiers_on_dupkey FALSE FALSE supress owner index name err msg
_suppress_identifiers_on_dupkey 파라미터를 TRUE로 설정하고 동일한 예제를 다시 수행하면 6.38초가 소요됩니다. 결국 에러 메시지에 소유자와 인덱스 명을 표시하기 위해 재귀적으로 수행되는 쿼리에 의해 6.24초가 소요된 것입니다.
-- 5
ALTER SESSION SET "_suppress_identifiers_on_dupkey" = TRUE;
DECLARE
v_sqlerrm VARCHAR2(4000);
BEGIN
FOR i IN 1..1e5 LOOP
BEGIN
INSERT INTO t1 (c1) VALUES (i);
EXCEPTION
WHEN DUP_VAL_ON_INDEX THEN
v_sqlerrm := SQLERRM;
UPDATE t1 SET c2 = i WHERE c1 = i;
END;
END LOOP;
COMMIT;
DBMS_OUTPUT.PUT_LINE (v_sqlerrm);
END;
/
ORA-00001: 무결성 제약 조건(UNKNOWN.obj#=225082)에 위배됩니다
경 과: 00:00:06.38
참고로 INSERT & UPDATE 방식 대신 아래와 같이 UPDATE & INSERT 방식을 사용하면 성능을 개선할 수 있습니다.
-- 6
BEGIN
FOR i IN 1 .. 1e5 LOOP
UPDATE t1 SET c2 = i WHERE c1 = i;
IF SQL%ROWCOUNT = 0 THEN
INSERT INTO t1 (c1) VALUES (i);
END IF;
END LOOP;
COMMIT;
END;
/
경 과: 00:00:01.73
하지만 특별한 경우가 아니라면 절차적인 방식보다 MERGE 문을 사용하는 편이 바람직합니다.
-- 7
MERGE
INTO t1 t
USING (SELECT ROWNUM AS c1 FROM XMLTABLE ('1 to 100000')) s
ON (t.c1 = s.c1)
WHEN NOT MATCHED THEN
INSERT (t.c1) VALUES (s.c1)
WHEN MATCHED THEN
UPDATE SET t.c2 = s.c1;
100000 행이 병합되었습니다.
경 과: 00:00:00.78