mview 갱신 시간 설정 / mview 갱신 설정

1. 한시간 마다 Refresh

 

CREATE MATERIALIZED VIEW test_mv1
TABLESPACE PMOFD
PARALLEL 4
refresh fast
start with sysdate
next sysdate + (1/24) /* 1시간 마다 refresh */
AS
SELECT * FROM XONTS_SHIP_EDI_V@PMOF_TO_PROD ;

 

2.  하루에 한번 씩 Refresh 하게 조정

 

alter materialized view test_mv1 refresh next sysdate + 1;

 

3. Mview refresh 하는 중에 마스터 테이블에 대한 lock 가능성

 

기본적으로 MASTER table 에대해서는 LOCK 은 없음.

 

The Oracle read consistent mechanism is used to select a consistent set of 
rows from the MASTER table, therefore it is NOT necessary to take out any 
locks against the MASTER table. 

Note 258252.1 - MATERIALIZED VIEW REFRESH: Locking, Performance, Monitoring
Note.258258.1 - Monitoring Locks During Materialized View Refreshes 

 

4. 데이터 Refresh 방법

 

'refresh fast' 이면 새로운 데이터만, 'refresh complete' 면 모든 데이터 refresh 합니다.
Note 226408.1 - Snapshot or Materialized View Refresh in Oracle8

 

5. refresh 중에 데이터 삭제 하고 다시 가져오면 부하는 없는지 ?

 

TRUNCATE 하므로 부하는 없음.

 

 

 

-------------------------------------------------------------------------------------------------------------------

 

MVIEW 생성시 ORA-12014 에러 발생

 

 

 

 

CREATE MATERIALIZED VIEW test_mv1
TABLESPACE PMOFD
PARALLEL 4
refresh complete 
start with sysdate 
next trunc( sysdate ) + 1 + 1/24 /* 매일 새벽 1 시에 refresh */
AS
SELECT * FROM XONTS_SHIP_EDI_V@PMOF_TO_PROD ;

 

위와 같이 생성시 다음과 같은 에러 발생

 

ORA-12014 : 테이블 XONTS_SHIP_EDI_V는 기본키 제약조건을 포함하고 있지 않습니다.

 

조치 사항

 

Note 254593.1 - Differences Between Rowid & Primary Key Materialized Views

1. Master table 에 PK 를 추가하시던지
2. create mview 시 with rowid 사용하세요. 

 

 

CREATE MATERIALIZED VIEW test_mv1
TABLESPACE PMOFD
PARALLEL 4
refresh complete

with rowid                         /* 순서주의 */
start with sysdate 
next trunc( sysdate ) + 1 + 1/24 /* 매일 새벽 1 시에 refresh */
AS
SELECT * FROM XONTS_SHIP_EDI_V@PMOF_TO_PROD ;

 

 

해당 View가 complete refresh 하는경우는 mview log 생성이 불필요함.

또 start와 next를 주고서 Mview 생성시에는 dba_jobs에 등록된것 확인해 보면 다음 refresh 시간을 확인 가능

 

 

--------------------------------------------------------------------------------------------------------------------

 

다음과 같은 에러가 가끔 발생을 하는경우가 있음

 

/oracle1/admin/PMOF/bdump/pmof_j001_5088.trc
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
With the Partitioning, OLAP and Data Mining options
ORACLE_HOME = /oracle1/product/10.2.0
System name: HP-UX
Node name: PMODB1A
Release: B.11.23
Version: U
Machine: 9000/800
Instance name: PMOF
Redo thread mounted by this instance: 1
Oracle process number: 25
Unix process pid: 5088, image: oracle@PMODB1A (J001)

*** SERVICE NAME:(SYS$USERS) 2006-08-11 01:05:24.642
*** SESSION ID:(959.30126) 2006-08-11 01:05:24.642
*** 2006-08-11 01:05:24.642
ORA-12012: 작업 69의 자동 실행중 오류 발생
ORA-12008: 구체화된 뷰 새로 고침 경로 오류
ORA-01555: 너무 이전 스냅샷:롤백 세그먼트 수에 "" 이름으로 된 것이 너무 작습니다
ORA-02063: line가 선행됨 (PMOF_TO_PROD로 부터)
ORA-06512: "SYS.DBMS_SNAPSHOT", 줄 2255에서
ORA-06512: "SYS.DBMS_SNAPSHOT", 줄 2461에서
ORA-06512: "SYS.DBMS_IREFRESH", 줄 683에서
ORA-06512: "SYS.DBMS_REFRESH", 줄 195에서
ORA-06512: 줄 1에서

 

해결방안은

 

Note.269814.1 - ORA-01555 Using Automatic Undo Management - Causes and Solutions

 

Auto managed undo 일경우 TBS를 늘려주고 undo_retention 값을 수정해 주라고 나와 있음

 

Solution
--------

1. The UNDO tablespace is too small. Increase the size of the UNDO tablespace.
The UNDO tablespace should be large enough to store the undo data generated by 
active transactions as well as those preserved to honor the undo retention setting.

2. Tune the value of the UNDO_RETENTION parameter. This is important for systems
running long queries. The parameter's value should at least be equal to the 
length of longest running query on a given database instance. This can be 
determined by querying V$UNDOSTAT view once the database has been running 
for a while.

SQL> select max(maxquerylen) from v$undostat; 

The V$UNDOSTAT view holds undo statistics for 10 minute intervals. This view 
represents statistics across instances, thus each begin time, end time, and 
statistics value will be a unique interval per instance.
This view contains the following columns:

BEGIN_TIME     - The beginning time for this interval check
END_TIME       - The ending time for this interval check
UNDOTSN        - The undo tablespace number
UNDOBLKS       - The total number undo blocks consumed during the time interval
TXNCOUNT       - The total number of transactions during the interval
MAXQUERYLEN    - The maximum duration of a query within the interval
MAXCONCURRENCY - The highest number of transactions during the interval
UNXPSTEALCNT   - The number of attempts when unexpired blocks were stolen from 
                 other undo segments to satisfy space requests
UNXPBLKRELCNT  - The number of unexpired blocks removed from undo segments to be
                 used by other transactions
UNXPBLKREUCNT  - The number of unexpired undo blocks reused by transactions
EXPSTEALCNT    - The number of attempts when expired extents were stolen from 
                 other undo segments to satisfy a space requests
EXPBLKRELCNT   - The number of expired extents stolen from other undo segments 
                 to satisfy a space request
EXPBLKREUCNT   - The number of expired undo blocks reused within the same undo 
                 segments
SSOLDERRCNT    - The number of ORA-1555 errors that occurred during the interval
NOSPACEERRCNT  - The number of Out-of-Space errors

When the columns UNXPSTEALCNT through EXPBLKREUCNT hold non-zero values, it is 
an indication of space pressure. If the column SSOLDERRCNT is non-zero, then 
UNDO_RETENTION is not properly set. If the column NOSPACEERRCNT is non-zero, 
then there is a serious space problem.

3. Retention Guarantee

With Server release 10g version we have an option to guarantee undo retention. 
When this option is enabled the database never overwrites unexpired undo data 
that is, undo data whose age is less than the undo retention period. Make sure
to define the UNDO tablespace large enough to meet the guarantee requirement. 

You can enable the guarantee option by specifying the RETENTION GUARANTEE clause 
for the UNDO tablespace when it is created by the CREATE UNDO TABLESPACE statement
or at a later period using the ALTER TABLESPACE statement.

SQL> alter tablespace undotbs1 retention guarantee; 

4. How to calculate and properly size the UNDO tablespace
    
The required space depends on the amount of undo blocks that is needed during a
specific period of time, and it is lineary related to the UNDO_RETENTION setting.
Use the formula presented in Note 262066.1 to calculate the size of the UNDO 
tablespace.