반응형
이전글에서 이어서 작성합니다.
https://bae9086.tistory.com/525
Materialized View (MView)
Mview, (이전에는 snapshot으로 불린)는 로컬 또는 원격 테이블에 대해 쿼리를 기반으로 내용이 주기적으로 고쳐지는 테이블 세그먼트입니다. 쿼리형태로 저장된 뷰와는 달리 데이터가 직접 저장되
bae9086.tistory.com
Realtime MView를 한그림으로 표현하면 아래와 같습니다.
출처 : https://oracle-base.com/articles/12c/real-time-materialized-views-12cr2
원본 테이블의 변경 후 refresh가 안된 상태 등. Stale 상태인 MVIEW를 기존에는 계속 사용하지 못했다면, 12c R2부터는 MVIEW Log와 함께 사용할 수 있게 Realtime MView 기능을 제공합니다.
이기능으로써 stale상태인 MView에 대해서도 MView log를 이용하여 변경사항을 적용하느 fast refresh와 마찬가지로 MView log 기반으로 사용됩니다. 하지만 해당 기능은 오직 현재문장에만 영향을 미치고, 변경사항이 영구적으로 다시 적용되지는 않습니다. 적용하려면 refresh가 여전히 필요합니다.
이 기능을 사용하려면 세가지가 설정이 되어 있어야 합니다.
1) oracle parameter QUERY_REWRITE_INTEGRITY = enforced (default) or Trusted로 설정되어 있을 것.
- STALE_TOLLERATED 로 설정된 경우 사용할 수 없음.
2) MView 생성 시 ENABLE ON QUERY COMPUTATION 사용
3 REFRESH .. ON COMMIT 옵션을 사용하면 사용할 수 없습니다.
테스트를 통해 실제로 어떻게 보여지고 확인되는지 보겠습니다. DB버전은 12.2.0,1에서 진행하였습니다.
두가지로 MVIEW를 생성( ENABLE ON QUERY COMPUTATION 옵션 사용 유/무)하여
파라미터 변경, 힌트 등을 사용하여 MVIEW의 query rewrite 후 실행계획에 적용여부를 확인해보겠습니다.
0) 사전준비
테스트 테이블은 EMP와 EMP를 통해서 만든 MVIEW_EMP를 이용합니다.
SQL> select version from v$instance;
VERSION
-----------------
12.2.0.1.0
SQL> select * from emp;
EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- -------------------- --------- --------- ------
7369 SMITH CLERK 7902 1980/12/17 00:00:00 800 20
7499 ALLEN SALESMAN 7698 1981/02/20 00:00:00 1600 300 30
7521 WARD SALESMAN 7698 1981/02/22 00:00:00 1250 500 30
7566 JONES MANAGER 7839 1981/04/02 00:00:00 2975 20
7654 MARTIN SALESMAN 7698 1981/09/28 00:00:00 1250 1400 30
7698 BLAKE MANAGER 7839 1981/05/01 00:00:00 2850 30
7782 CLARK MANAGER 7839 1981/06/09 00:00:00 2450 10
7839 KING PRESIDENT 1981/11/17 00:00:00 5000 10
7844 TURNER SALESMAN 7698 1981/09/08 00:00:00 1500 0 30
7900 JAMES CLERK 7698 1981/12/03 00:00:00 950 30
7902 FORD ANALYST 7566 1981/12/03 00:00:00 3000 20
7934 MILLER CLERK 7782 1982/01/23 00:00:00 1300 10
7788 SCOTT T,T 7566 1987/07/13 00:00:00 3000 20
7876 ADAMS CLERK 7788 1987/07/13 00:00:00 1100 20
14 rows selected.
--MVIEW_EMP 생성
create table mview_emp
as select * from emp
where 1=2;
--데이터 삽입
begin
for i in 1..100000
loop
insert into mview_emp
select * from emp;
commit;
end loop;
end;
/
--건수 확인
SQL> select count(*) from mview_emp;
COUNT(*)
---------
1400000
1 rows selected.
--인덱스 생성
create index idx_mview_emp_01 on mview_emp (deptno, sal);
SQL> SELECT a.index_name, a.column_name, b.visibility
FROM user_ind_columns a, user_indexes b
WHERE a.table_name = 'MVIEW_EMP'
AND a.index_name = b.index_name
ORDER BY index_name;
INDEX_NAME COLUMN_NAME VISIBILITY
------------------ ------------- ----------
IDX_MVIEW_EMP_01 SAL VISIBLE
IDX_MVIEW_EMP_01 DEPTNO VISIBLE
1) ENABLE ON QUERY COMPUTATION 사용
MVIEW 생성 전에 부서별 최소 급여와 사원번호를 조회해보기
SQL> SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
10 7934 1300
20 7369 800
30 7900 950
-----------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
-----------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 73 (100)| |
| 1 | SORT ORDER BY | | 1 | 37 | 73 (94)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 37 | 72 (94)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 4 | VIEW | VW_NSO_1 | 1 | 26 | 69 (98)| 00:00:01 |
| 5 | HASH GROUP BY | | 1 | 26 | 69 (98)| 00:00:01 |
| 6 | TABLE ACCESS FULL| MVIEW_EMP | 1400K| 34M| 2 (0)| 00:00:01 |
-----------------------------------------------------------------------------------
Predicate Information (identified by operation id):
---------------------------------------------------
2 - access("DEPTNO"="DEPTNO" AND "SAL"="MIN(SAL)")
MVIEW 와 MVIEW LOG 생성
옵션 중 REFRESH FAST ON COMMIT 은 아래 경우에 사용가능합니다. 이번 테스트에는 사용안함.
-- 원본데이터가 COMMIT 될때 갱신,
--1개의 테이블에 COUNT(*), SUM, MAX, MIN과 같은 집합 함수를 사용하거나, MView에 조인만이 있는 경우,
-- Group By 절에 사용된 컬럼에 대해 COUNT(col) 함수가 기술된 경우만 사용이 가능 합니다.
--MVIEW LOG 생성
DROP MATERIALIZED VIEW LOG ON mview_emp;
CREATE MATERIALIZED VIEW LOG ON mview_emp
WITH ROWID, SEQUENCE(deptno, sal) INCLUDING NEW VALUES;
--MVIEW 생성
DROP MATERIALIZED VIEW MVIEW_MVIEW_EMP ;
CREATE MATERIALIZED VIEW MVIEW_MVIEW_EMP
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
ENABLE ON QUERY COMPUTATION
AS
SELECT deptno, min(sal) FROM MVIEW_EMP GROUP BY deptno;
SQL> select LOG_OWNER,MASTER,LOG_TABLE,SEQUENCE,LAST_PURGE_DATE from user_mview_logs;
LOG_OWNER MASTER LOG_TABLE SEQUENCE LAST_PURGE_DATE
------------ ----------- ------------------ -------- --------------------
YSBAE MVIEW_EMP MLOG$_MVIEW_EMP YES 2024/01/30 10:35:59
1 rows selected.
SQL> select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST FRESH Y
통계정보 갱신하기.
통계정보를 갱신하지 않으면 MVIEW를 생성하더라도 실행계획 상에서 사용하지 않습니다. MVIEW에 대한 통계정보를 생성 후에 진행하겠습니다.
EXEC DBMS_STATS.gather_table_stats(USER, 'MVIEW_MVIEW_EMP');
SQL> SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
10 7934 1300
20 7369 800
30 7900 950
3 rows selected.
SQL> SELECT * FROM dbms_xplan.display_cursor();
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT ORDER BY | | 1 | 18 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 18 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 4 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 3 | 21 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------MVIEW를 실행계획에서 잘 사용하는것을 확인할 수 있습니다.
원본 테이블에 데이터 삽입하여 MVIEW상태 깨뜨리기
insert into mview_emp (empno,ename,sal,deptno) values (9999,'배익순',9999,99);
insert into emp (empno,ename,sal,deptno) values (9999,'배익순',9999,99);
commit;
select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST NEEDS_COMPILE YSTALENESS가 NEEDS_COMPILE로 변하였습니다.
STALENESS
FRESH : Mview가 일기일관성이 유지되고 현재 정상 상태(사용가능)
STALE : 원본테이블의 데이터가 변경 되었는데 뷰가 갱신되지 않은 상태. 직전상태가 FRESH에서 STALE로 변경되었다면 원본테이블의 이전 상태에 대한 뷰입니다.
NEEDS_COMPILE : MVIEW에 의존하는 일부 개체가 변경되었습니다.컴파일이 필요한 상태 ALTER MATERIALIZED VIEW...COMPILE
UNUSABLE : 뷰를 읽을 수 없음.
IMPORT : Mview가 다른 DB로부터 import 된 상태.(UNKNOWN_IMPORT ='Y'). 어느시점에 fresh된 MVIEW인지 알 수 없음. 따라서 complete refresh가 될때 fresh로 변경 됨.
UNDEFINED : remote의 원본 테이블이 있습니다. 이 상태의 MVIEW는 UNDEFINED로 표시됩니다.
UNKNOWN : MVIEW가 원본테이블과 일기일관성이 보전된 상태인지 알 수 없습니다.(prebuilt table로 생성된 경우에만 해당)
SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
30 7900 950
20 7369 800
99 9999 9999
10 7934 1300
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1343 (100)| |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY | | 85 | 2550 | 1343 (31)| 00:00:01 |
|* 3 | HASH JOIN | | 7000K| 200M| 974 (5)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 15 | 345 | 4 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| IDX_MVIEW_EMP_01 | 1400K| 9570K| 934 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
MVIEW가 정상적이지 않을 때 MVIEW를 활용하지 않고 원본 테이블의 INDEX를 FULL SCAN하며 풀립니다.
refresh 후 다시 확인
SQL> EXEC dbms_mview.refresh('MVIEW_MVIEW_EMP', method=>'C');
select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST FRESH Y
SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
30 7900 950
20 7369 800
99 9999 9999
10 7934 1300
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | SORT ORDER BY | | 1 | 18 | 8 (13)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 18 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 15 | 165 | 4 (0)| 00:00:01 |
| 4 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 4 | 28 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------refresh가 되서 정상인 MVIEW를 잘 활용합니다.
realtime MVIEW 기능을 확인하기 위해 다시 데이터를 insert하고 MVIEW의 상태를 깨드립니다.
insert into mview_emp (empno,ename,sal,deptno) values (8080,'배익순',8080,88);
insert into emp (empno,ename,sal,deptno) values (8080,'배익순',8080,88);
commit;
SQL> select name,value from v$parameter
where name=lower('QUERY_REWRITE_INTEGRITY');
NAME VALUE
------------------------- ---------
query_rewrite_integrity enforced
select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST NEEDS_COMPILE Y파라미터도 enforced인것을 확인하고 STALENESS가 NEEDS_COMPILE인것을 확인합니다.
SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno);
ORDER BY deptno;
ORA-03113: end-of-file on communication channel에러가 발생하면서 세션이 종료됩니다.
MVIEW에 직접 사용하는 쿼리를 직접 조회해보겠습니다.
SQL> SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno;
DEPTNO MIN(SAL)
------ ---------
30 950
99 9999
20 800
10 1300
88 8080
5 rows selected.
----------------------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
----------------------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 24 (100)| |
| 1 | VIEW | | 300 | 7800 | 24 (30)| 00:00:01 |
| 2 | UNION-ALL | | | | | |
|* 3 | VIEW | VW_FOJ_0 | 100 | 2900 | 8 (25)| 00:00:01 |
|* 4 | HASH JOIN FULL OUTER | | 100 | 2400 | 8 (25)| 00:00:01 |
| 5 | VIEW | | 4 | 68 | 3 (0)| 00:00:01 |
| 6 | MAT_VIEW ACCESS FULL | MVIEW_MVIEW_EMP | 4 | 28 | 3 (0)| 00:00:01 |
| 7 | VIEW | | 100 | 700 | 5 (40)| 00:00:01 |
| 8 | HASH GROUP BY | | | | 5 (40)| 00:00:01 |
| 9 | VIEW | | 1 | 26 | 4 (25)| 00:00:01 |
| 10 | RESULT CACHE | 9y35vk0ztp5k6b27jwbpa3f5ht | | | | |
|* 11 | VIEW | | 1 | 90 | 4 (25)| 00:00:01 |
| 12 | WINDOW SORT | | 1 | 181 | 4 (25)| 00:00:01 |
|* 13 | TABLE ACCESS FULL | MLOG$_MVIEW_EMP | 1 | 181 | 3 (0)| 00:00:01 |
|* 14 | VIEW | VW_FOJ_1 | 100 | 2900 | 8 (25)| 00:00:01 |
|* 15 | HASH JOIN FULL OUTER | | 100 | 2400 | 8 (25)| 00:00:01 |
| 16 | VIEW | | 4 | 28 | 3 (0)| 00:00:01 |
| 17 | MAT_VIEW ACCESS FULL | MVIEW_MVIEW_EMP | 4 | 28 | 3 (0)| 00:00:01 |
| 18 | VIEW | | 100 | 1700 | 5 (40)| 00:00:01 |
| 19 | HASH GROUP BY | | | | 5 (40)| 00:00:01 |
| 20 | VIEW | | 1 | 26 | 4 (25)| 00:00:01 |
| 21 | RESULT CACHE | 9y35vk0ztp5k6b27jwbpa3f5ht | | | | |
|* 22 | VIEW | | 1 | 90 | 4 (25)| 00:00:01 |
| 23 | WINDOW SORT | | 1 | 181 | 4 (25)| 00:00:01 |
|* 24 | TABLE ACCESS FULL | MLOG$_MVIEW_EMP | 1 | 181 | 3 (0)| 00:00:01 |
| 25 | MERGE JOIN | | 100 | 4000 | 8 (38)| 00:00:01 |
| 26 | MAT_VIEW ACCESS BY INDEX ROWID| MVIEW_MVIEW_EMP | 4 | 44 | 2 (0)| 00:00:01 |
| 27 | INDEX FULL SCAN | I_SNAP$_MVIEW_MVIEW_EMP | 4 | | 1 (0)| 00:00:01 |
|* 28 | SORT JOIN | | 100 | 2900 | 6 (50)| 00:00:01 |
| 29 | VIEW | | 100 | 2900 | 5 (40)| 00:00:01 |
| 30 | HASH GROUP BY | | | | 5 (40)| 00:00:01 |
| 31 | VIEW | | 1 | 26 | 4 (25)| 00:00:01 |
| 32 | RESULT CACHE | 9y35vk0ztp5k6b27jwbpa3f5ht | | | | |
|* 33 | VIEW | | 1 | 90 | 4 (25)| 00:00:01 |
| 34 | WINDOW SORT | | 1 | 181 | 4 (25)| 00:00:01 |
|* 35 | TABLE ACCESS FULL | MLOG$_MVIEW_EMP | 1 | 181 | 3 (0)| 00:00:01 |
----------------------------------------------------------------------------------------------------------------
직접 쿼리를 조회해보니 MVIEW LOG를 조회하여 result cache에 저장후 변경사항까지 보여주고 있습니다.
SQL> SELECT /*+ FRESH_MV */ deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
ORA-04023: 객체 SELECT /*+ FRESH_MV */ deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno을(를) 검증하거나 승인할 수 없습니다.ORA-04023에러를 발생하며 수행되지 않습니다.
FRESH_MV는 realtime Mview를 쿼리로 사용할때 적용됩니다. 사용시 옵티마이저가 MView가 fresh한 상태가 아니더라도 on query conputation을 사용하여 최신데이터를 가져오도록 지시합니다. 옵티마이저는 realtime Mview가 아닌 개체를 쿼리하는 SELECT문과 모든 DML(UPDATE,INSERT,MERGE,DELETE)에서 힌트를 무시합니다.
MVIEW를 쿼리하는게 아닌 emp도 포함되어 있어서 realtime Mview가 적용되지않고 에러로 빠졌던거같네요.
MVIEW의 상태가 fresh하지 않고, 파라미터가 enforced가 아닌 STALE_TOLERATED일때는 어떤지 확인해봅니다.
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = STALE_TOLERATED ;
SQL> select name,value from v$parameter
where name=lower('QUERY_REWRITE_INTEGRITY');
NAME VALUE
------------------------- ---------
query_rewrite_integrity STALE_TOLERATED
select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST STALE Y
SQL> SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
10 7934 1300
20 7369 800
30 7900 950
99 9999 9999
5 rows selected.
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 8 (100)| |
| 1 | SORT ORDER BY | | 1 | 18 | 8 (13)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 18 | 7 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 15 | 165 | 4 (0)| 00:00:01 |
| 4 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 4 | 28 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
MVIEW는 활용하지만 8080인 데이터는 보이지 않습니다. refresh되지 않은 데이터를 그냥 조회하여 결과를 낸것입니다.
SQL> SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno;
DEPTNO MIN(SAL)
------ ---------
30 950
20 800
99 9999
10 1300
4 rows selected.
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 4 | 28 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
SQL> SELECT /*+ FRESH_MV */ deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
ORA-32364: 이 질의에 대해 질의 시 계산을 수행할 수 없습니다.FRESH_MV 힌트를 사용 시 ORA-32364가 발생하고, MVIEW의 쿼리는 활용하지만 8080의 데이터는 refresh되지 않았습니다.
마찬가지로 refresh 후에 쿼리를 조회하면 MVIEW를 잘 활용합니다. 8080의 데이터도 잘 보입니다.
EXEC dbms_mview.refresh('MVIEW_MVIEW_EMP', method=>'C');
SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno;
DEPTNO MIN(SAL)
------ ---------
30 950
20 800
88 8080
99 9999
10 1300
5 rows selected.
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 4 | 28 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
기본적인 realtime Mview 사용 여부는 1)의 테스트만 보면됩니다. 아래는 미사용하면 어떤 결과가 나오는지 궁금해서 추가로 진행한 테스트입니다.
2) ENABLE ON QUERY COMPUTATION 미사용
기존에 추가로 INSERT한 데이터들과 MVIEW ,MVIEW log를 삭제후 재생성합니다.
MVIEW 재생성시 해당 옵션을 제외하고 생성합니다.
초기세팅 다시 진행
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = enforced ;
SQL> delete from mview_emp where empno in (8080,9999);
SQL> delete from emp where empno in (8080,9999);
commit;
DROP MATERIALIZED VIEW LOG ON mview_emp;
CREATE MATERIALIZED VIEW LOG ON mview_emp
WITH ROWID, SEQUENCE(deptno, sal) INCLUDING NEW VALUES;
DROP MATERIALIZED VIEW MVIEW_MVIEW_EMP;
CREATE MATERIALIZED VIEW MVIEW_MVIEW_EMP
REFRESH FAST ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT deptno, min(sal) FROM MVIEW_EMP GROUP BY deptno;
select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST FRESH N
ON_QUERY_COMPUTATION이 N인것을 확인합니다.
마찬가지로 MVIEW에 대한 통계정보 생성 후 조회해봅니다.
EXEC DBMS_STATS.gather_table_stats(USER, 'MVIEW_MVIEW_EMP');SQL> SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
10 7934 1300
20 7369 800
30 7900 950
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT ORDER BY | | 1 | 18 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 18 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 4 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 3 | 21 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
MVIEW를 잘 활용합니다.
데이터를 넣어 MVIEW를 깨드리겠습니다.
###데이터를 INSERT해서 FRSH상태 깨지게 하기.
insert into mview_emp (empno,ename,sal,deptno) values (9999,'배익순',9999,99);
insert into emp (empno,ename,sal,deptno) values (9999,'배익순',9999,99);
commit;
##
select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST NEEDS_COMPILE N
SQL> SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
10 7934 1300
20 7369 800
30 7900 950
99 9999 9999
--------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 1343 (100)| |
|* 1 | FILTER | | | | | |
| 2 | SORT GROUP BY | | 85 | 2550 | 1343 (31)| 00:00:01 |
|* 3 | HASH JOIN | | 7000K| 200M| 974 (5)| 00:00:01 |
| 4 | TABLE ACCESS FULL | EMP | 15 | 345 | 4 (0)| 00:00:01 |
| 5 | INDEX FAST FULL SCAN| IDX_MVIEW_EMP_01 | 1400K| 9570K| 934 (2)| 00:00:01 |
--------------------------------------------------------------------------------------------
MVIEW를 활용하지 않고 쿼리가 수행됩니다.
refresh 후에 확인합니다
EXEC dbms_mview.refresh('MVIEW_MVIEW_EMP', method=>'C');
select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST FRESH N
SQL> SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
10 7934 1300
20 7369 800
30 7900 950
99 9999 9999
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT ORDER BY | | 1 | 18 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 18 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 4 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 3 | 21 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
refresh 된 후에는 MVIEW를 잘 활용합니다.
MVIEW의 상태가 fresh하지 않고, 파라미터가 enforced가 아닌 STALE_TOLERATED일때는 어떤지 확인해봅니다.
ALTER SESSION SET QUERY_REWRITE_INTEGRITY = STALE_TOLERATED ;
SQL> select name,value
from v$parameter
where name=lower('QUERY_REWRITE_INTEGRITY');
NAME VALUE
-------------------------- ----------------
query_rewrite_integrity STALE_TOLERATED
1 rows selected.
insert into mview_emp (empno,ename,sal,deptno) values (8080,'배익순',8080,88);
insert into emp (empno,ename,sal,deptno) values (8080,'배익순',8080,88);
commit;
데이터 삽입
select owner,mview_name,container_name,REWRITE_ENABLED,REFRESH_MODE,REFRESH_METHOD,STALENESS,on_query_computation From user_mviews;
OWNER MVIEW_NAME CONTAINER_NAME REWRITE_ENABLED REFRESH_MODE REFRESH_METHOD STALENESS ON_QUERY_COMPUTATION
--------- ---------------------- ------------------ --------------- ------------ -------------- ------------------- --------------------
YSBAE MVIEW_MVIEW_EMP MVIEW_MVIEW_EMP Y DEMAND FAST NEEDS_COMPILE N
SQL> SELECT deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
DEPTNO EMPNO SAL
------ ----- ---------
10 7934 1300
20 7369 800
30 7900 950
99 9999 9999
4 rows selected.
--------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
--------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 7 (100)| |
| 1 | SORT ORDER BY | | 1 | 18 | 7 (15)| 00:00:01 |
|* 2 | HASH JOIN SEMI | | 1 | 18 | 6 (0)| 00:00:01 |
| 3 | TABLE ACCESS FULL | EMP | 14 | 154 | 3 (0)| 00:00:01 |
| 4 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 3 | 21 | 3 (0)| 00:00:01 |
--------------------------------------------------------------------------------------------------
MVIEW는 사용하지만 제일 최근에 삽입한 8080의 데이터는 조회되지 않습니다.
SQL> SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno;
DEPTNO MIN(SAL)
------ ---------
30 950
20 800
99 9999
10 1300
4 rows selected.
------------------------------------------------------------------------------------------------
| Id | Operation | Name | Rows | Bytes | Cost (%CPU)| Time |
------------------------------------------------------------------------------------------------
| 0 | SELECT STATEMENT | | | | 3 (100)| |
| 1 | MAT_VIEW REWRITE ACCESS FULL| MVIEW_MVIEW_EMP | 3 | 18 | 3 (0)| 00:00:01 |
------------------------------------------------------------------------------------------------
MVIEW 쿼리 자체를 조회해보면 MVIEW는 타는데 실시간으로 아닌 데이터가 보여집니다.
SQL> SELECT /*+ FRESH_MV */deptno, empno, sal
FROM emp
WHERE (deptno, sal) in (SELECT deptno, MIN(sal)
FROM mview_emp
GROUP BY deptno)
ORDER BY deptno;
ORA-32364: 이 질의에 대해 질의 시 계산을 수행할 수 없습니다.
FRESH_MV 힌트는 당연히 안됩니다. ORA-32364에러가 발생합니다.
테스트 결과, realtime Mview는 경우에 따라 쓸모가 있는 기능일 수는 있겠으나, DW 환경에서 제한적으로 사용 가능한 점. 제가 운영하는 사이트는 MView 오브젝트 자체를 안쓰는점을 보았을때 많이사용하는 기능은 아닌거 같습니다.
반응형
'Oracle > 운영' 카테고리의 다른 글
| Materialized View (MView) (0) | 2024.01.26 |
|---|---|
| [통계정보 export/import]다른 테이블에 통계정보 복사 (0) | 2023.12.14 |
| 통계정보 restore 하기 (0) | 2023.10.05 |
| ddl_lock_timeout 과 ORA-00054 : resource busy (0) | 2023.07.24 |
| 테이블 컬럼삭제 Drop Column과 Unused (0) | 2023.07.19 |
댓글