엑시엄이 보는 DB 세상
Materialized view, 올바로 쓰고 있을까? 0 1 4,954

by axiom MView Materialized View [2013.10.10]


매번 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를 생성하는 것이 더 효과적일 수 있다.

- 강좌 URL : http://www.gurubee.net/lecture/2638

- 구루비 강좌는 개인의 학습용으로만 사용 할 수 있으며, 다른 웹 페이지에 게재할 경우에는 출처를 꼭 밝혀 주시면 고맙겠습니다.~^^

- 구루비 강좌는 서비스 제공을 위한 목적이나, 학원 홍보, 수익을 얻기 위한 용도로 사용 할 수 없습니다.

by 아발란체 [2013.10.10 13:05:45]

이 자료 하나 만으로 일반 뷰까지 포함 설명하기 쉬울 것 같네요.
킹왕짱 자료 ~ !

댓글등록
SQL문을 포맷에 맞게(깔끔하게) 등록하려면 code() 버튼을 클릭하여 작성 하시면 됩니다.
로그인 사용자만 댓글을 작성 할 수 있습니다. 로그인, 회원가입