꿈꾸는 개발자, DBA 커뮤니티 구루비

소속 회사는 많은 업무에서 MVIEW Object를 사용하고 있어 MVIEW의 원활한 동 작 및 성능이 매우 중요한 전환 포인트 중 한 가지였다. 따라서 MVIEW 관련 다양 한 테스트를 진행하였는데 이 과정에서 MVIEW의 처리 속도가 외산 DBMS에 비해 현저히 지연되는 이슈가 발견되었다.

이러한 성능저하의 원인은 티베로의 MVIEW 가 외산 DBMS와 달리 Trigger 방식으로 동작하여 로깅이 갱신되는 과정에서 발생 한 것이었는데 이 수준으로는 정상적인 업무 처리가 불가능하여 혁신적인 성능개 선이 필요하였다.

다행히도 소속 회사에 투입된 엔진의 상위 버전에는 MVIEW가 개선된 아키텍처로 반영이 되어있었고 해당 구조만 간단한 패치로 적용하여 사용 이 가능한 것으로 확인되어 짧은 시간 안에 이슈를 처리할 수 있었다.

가. TEST TABLE & MVIEW 생성

CREATE TABLE MVIEW_TEST
(
 COL1 VARCHAR(10),
 COL2 VARCHAR(10),
 COL3 VARCHAR(10),
 COL4 VARCHAR(10),
 CONSTRAINT PK_MVIEW_TEST PRIMARY
KEY(COL1)
)
TABLESPACE TESTDATA;

CREATE TABLE MVIEW_TEST_M
(
 COL1 VARCHAR(10),
 COL2 VARCHAR(10),
 COL3 VARCHAR(10),
 COL4 VARCHAR(10),
 CONSTRAINT PK_MVIEW_TEST_M
PRIMARY KEY(COL1)
)
TABLESPACE TESTDATA;

CREATE MATERIALIZED VIEW LOG ON MVIEW_TEST_M
TABLESPACE TESTDATA
WITH PRIMARY KEY
EXCLUDING NEW VALUES;

CREATE MATERIALIZED VIEW MVIEW_TEST (COL1,COL2,COL3,COL4)
ON PREBUILT TABLE WITH REDUCED PRECISION
REFRESH FAST ON DEMAND
WITH PRIMARY KEY
ENABLE QUERY REWRITE
AS
SELECT COL1, COL2, COL3, COL4 FROM MVIEW_TEST_M;

나. 99,999 ROWS 성능 비교

1. 성능 개선 전

2. 성능 개선 후


SQL> set timing on;

SQL> INSERT INTO MVIEW_TEST_M
 SELECT LEVEL AS COL1
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL2
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL3
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL4
 FROM DUAL CONNECT BY LEVEL <100000;

99999 rows inserted.
Total elapsed time 00:00:17.532112

SQL> COMMIT;
Commit completed.
Total elapsed time 00:00:00.006475

SQL> EXEC DBMS_MVIEW.REFRESH('MVIEW_TEST', 'F');
PSM completed.
Total elapsed time 00:00:10.377328

SQL> set timing on;

SQL> INSERT INTO MVIEW_TEST_M
 SELECT LEVEL AS COL1
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL2
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL3
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL4
 FROM DUAL CONNECT BY LEVEL <100000;

99999 rows inserted.
Total elapsed time 00:00:03.827569

SQL> COMMIT;
Commit completed.
Total elapsed time 00:00:00.016289

SQL> EXEC DBMS_MVIEW.REFRESH('MVIEW_TEST', 'F');
PSM completed.
Total elapsed time 00:00:15.657918

다. 999,999 ROWS 성능 비교

1. 성능 개선 전

2. 성능 개선 후

SQL> set timing on;

SQL> INSERT INTO MVIEW_TEST_M
 SELECT LEVEL AS COL1
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL2
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL3
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL4
 FROM DUAL CONNECT BY LEVEL <1000000;

999999 rows inserted.
Total elapsed time 00:02:59.010955

SQL> COMMIT;
Commit completed.
Total elapsed time 00:00:00.023046

SQL> EXEC DBMS_MVIEW.REFRESH('MVIEW_TEST','F');
PSM completed.
Total elapsed time 00:01:52.257876

SQL> set timing on;

SQL> INSERT INTO MVIEW_TEST_M
 SELECT LEVEL AS COL1
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL2
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL3
 ,ROUND(DBMS_RANDOM.VALUE(1,1000),0)AS COL4
 FROM DUAL CONNECT BY LEVEL <1000000;
 
999999 rows inserted.
Total elapsed time 00:00:39.982500

SQL> COMMIT;
Commit completed.
Total elapsed time 00:00:00.022666

SQL> EXEC DBMS_MVIEW.REFRESH('MVIEW_TEST','F');
PSM completed.
Total elapsed time 00:02:37.267385

- MLOG를 생성한 테이블에 대량 데이터 INSERT시 성능이 2분 59초 에서 39초로 개선된 것을 확인할 수 있다.

- MVIEW REFRESH의 성능이 1:52초 에서 2:37초로 증가한 것은 새로운 구조 적용과정에서 발생한 부작용으로 생각되나 소속 회사가 보유한 버전의 외산 DBMS 보다는 좋은 성능이어 이슈화 하지 않고 수용하였다.