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

매번 View에 정의된 쿼리를 실행하는 일반 뷰(View)와는 달리 스냅샷(Snapshot)이라고 불리는 Materialized View(이하 M-view)는 쿼리 결과를 별도의 공간에 저장하고, 쿼리가 실행될때 미리 저장된 결과를 보여줌으로써 성능을 향상시켜 준다.

또한 일반 View와 마찬가지로 'Refresh Time'을 지정함으로써 실시간 데이터도 확인할 수 있다. 하지만 성능 향상을 위해 사용한 M-view가 오히려 성능저하의 원인이 될 수도 있다.

잘못된 M-View 사용

앞에서 언급한 것처럼 원본 테이블에 쿼리를 수행해 결과를 출력하는 방식이 아니라 M-view는 별도의 저장 공간을 가지고 쿼리의 결과를 미리 저장하는 방식이므로 실시간으로 데이터를 보여줄 필요가 적은 통계성 쿼리나 자주 update되지 않는 테이블에 M-view를 생성하는 것이 일반적이다.

만약 원본 테이블에 대량의 DML이 발생한다면, 이 DML이 원본 테이블 이외에도 모두 M-view에 순차적으로 반영되어야 해서 많은 시간이 소요되므로 M-View로 인해 성능저하가 발생할 수 있기 때문이다.

또한 업무 특성을 고려하지 않고 원본 테이블에 이미 쿼리의 결과가 저장되어 있으므로 빠른 성능을 기대해 M-view를 생성 했다면 오히려 성능을 더 악화시킬 수 있다.

잘못된 M-view 예제

-- 테이블 생성
CREATE TABLE TEST1  AS
SELECT LEVEL COL1, MOD(LEVEL, 5) COL2
  FROM DUAL
CONNECT BY LEVEL <= 1000000
/

-- COL2에 인덱스 생성
CREATE INDEX TEST1_IDX ON TEST1(COL2, COL1)
/


-- M-VIEW LOG 생성
CREATE MATERIALIZED VIEW LOG ON TEST1 WITH ROWID
INCLUDING NEW VALUES
/


-- M-VIEW 생성
CREATE MATERIALIZED VIEW MVIEW_TEST1
 BUILD IMMEDIATE
REFRESH FAST ON COMMIT WITH ROWID
ENABLE QUERY REWRITE AS (
SELECT COL1, COL2
  FROM TEST1
 WHERE COL2 in ( '1','2','3') ) ;


-- 개발자의 의도는 100만 건의 데이터 중 30%에 해당되는 데이터만을 SELECT 할 
경우를위해서 M-view를 생성해 속도를 향상시키는 것이다.


-- M-view를 이용한 Data 추출
SELECT * 
  FROM MVIEW_TEST1
 WHERE COL1 IN ('1', '2', '3');
 
 
 Call Count  CPU Time Elapsed Time Disk Query Current Rows
----- ------ -------- ------------ ---- ----- ------ -----
Parse      1    0.031        0.059   0     70    0      0
Execute    1    0.000        0.000   0      0    0      0
Fetch      2    0.063        0.074   0   2219    0      3
Total      4    0.094        0.133   0   2289    0      3

Misses in library cache during parse: 1
Optimizer goal: ALL_ROWS
Parsing user: SYSTEM (ID=5)
Rows Row Source Operation
---------------------------------------------------
0 STATEMENT
3 MAT_VIEW ACCESS FULL MVIEW_TEST1 (cr=2219 pr=0pw=0 time=50 us)

실제 쿼리가 들어오는 Access pattern은 MVIEW_TEST1 데이터 중 col1의 값을 추출하는 쿼리가 대부분이었다.

M-View에 Index가 생성되어 있지 않으므로 M-View의 Full Scan이 발생해 총 '2289'블록을 Access 했다.

일반 View 예제

-- 일반 View 생성
CREATE OR REPLACE VIEW VIEW_TEST1
    AS
SELECT COL1, COL2
  FROM TEST1
 WHERE COL2 in ('1','2','3')
/


-- 일반 View를 이용한 Data 추출
SELECT * 
  FROM VIEW_TEST1
 WHERE COL1 IN ('1','2','3')
/


 Call Count  CPU Time Elapsed Time Disk Query Current Rows
----- ------ -------- ------------ ---- ----- ------ -----
Parse      1    0.000        0.031    0     0      0     0
Execute    1    0.000        0.000    0     0      0     0
Fetch      2    0.016        0.000    0    28      0     3
Total      4    0.016        0.032    0    28      0     3
Misses in library cache during parse: 0

Optimizer goal: ALL_ROWS
Parsing user: SYSTEM (ID=5)
Rows Row Source Operation
---------------------------------------------------
0 STATEMENT
3 INLIST ITERATOR (cr=28 pr=0 pw=0 time=31 us)
3 INDEX RANGE SCAN TEST1_IDX (cr=28 pr=0 pw=0time=77 us)(Object ID 52555)

일반 View는 실제 원본 Table에 Query를 실행하므로 COL2+COL1으로 구성된 TEST1_IDX를 이용해 '28'블록만 Access했다.

이처럼 업무의 특성은 고려하지 않고 원본 테이블보다 적은 양의 Data Block을 Access하므로 (100만 블록의 30%만 M-view에 저장되므로) 성능 향상을 이유로 M-view를 생성했다면 성능을 더 악화시킬 뿐이다.

또한 이 Table이 실시간으로 많은 트랜잭션을 발생시키는 테이블이라면 원본 테이블과 더불어 M-view 테이블도 실시간으로 트랜잭션을 반영해야 하므로 더 안 좋은 성능을 초래할 뿐이다.

위와 같이 변경된 데이터를 실시간으로 보여줘야 되고, DML이 많이 발생하는 테이블이라면 차라리 일반 View를 생성하는 것이 더 효과적일 수 있다.