oracle 物化视图日常维护

一、参数

• optimizer_mode

优化器模式需要ALL_ROWS、FIRST_ROWS、CHOOSE之一

• query_rewrite_enabled

需要启用查询重写，TRUE

• query_rewrite_integrity

1. STALE_TOLERATED

   • 当设置为 STALE_TOLERATED 时,优化器会使用包含既有新鲜数据又有陈旧数据的物化视图进行查询重写。
   • 这意味着查询结果可能包含一些过期的数据,但这种情况是可以接受的。

2. TRUSTED

   • 当设置为 TRUSTED 时,优化器会假设物化视图中的数据是正确的,并会使用这些数据进行查询重写。
   • 这意味着优化器会完全信任物化视图中的数据,不会对其进行验证。

3. ENFORCED

   • 当设置为 ENFORCED 时,这是默认值,优化器只会使用它确定包含新鲜数据的物化视图进行查询重写。
   • 这意味着如果物化视图包含陈旧数据,优化器不会使用它进行查询重写。在这种情况下,用户必须确保验证数据仓库模式中的约束。

这个参数控制了优化器在使用物化视图进行查询重写时对数据准确性的要求。

二、物化视图是如何通过查询重写过程被重写

1. 完整 SQL 文本匹配:

   • 优化器会比较查询的 SELECT 子句 SQL 文本与物化视图定义查询的 SELECT 子句 SQL 文本。
   • 如果完全匹配,则可以使用该物化视图进行查询重写。

2. 部分 SQL 文本匹配:

   • 如果完整 SQL 文本匹配失败,优化器会比较查询剩余部分(从 FROM 子句开始)的 SQL 文本与物化视图定义查询的剩余 SQL 文本。
   • 如果部分匹配成功,也可以使用该物化视图进行查询重写。

3. 一般查询重写方法:

   • 如果完整和部分 SQL 文本匹配都失败,优化器会使用一般的查询重写方法。
   • 优化器会测试物化视图的数据充分性、连接兼容性、分组兼容性和聚合兼容性,即使物化视图只包含部分所需数据或包含超出所需的数据。
   • 只要满足这些兼容性要求,优化器就可以使用该物化视图进行查询重写。

总之,优化器会先尝试完整和部分 SQL 文本匹配,如果失败则使用更复杂的一般查询重写方法来判断是否可以使用物化视图。

三、创建和维护

• 创建

SQL> create table t1 as select * from dba_objects;

Materialized view created.


SQL> insert into t1 select * from t1;

Commit complete.


SQL> select count(*) from t1;

COUNT(*)
----------
145108


SQL> select count(*) from mv_orcl_t1;

COUNT(*)
----------
72554


创建物化视图时可以指定刷新方式的两种选择:ON COMMIT 和 ON DEMAND。

1. ON COMMIT 模式下:

   • 每次事务提交时,物化视图都会被更新,确保物化视图始终包含最新数据。

2. ON DEMAND 模式下:

   • 需要通过调用 DBMS_MVIEW 包中的过程来手动刷新物化视图。

DBMS_MVIEW 包提供了三种不同类型的刷新操作:

• DBMS_MVIEW.REFRESH: 刷新一个或多个物化视图
• DBMS_MVIEW.REFRESH_ALL_MVIEWS: 刷新所有物化视图
• DBMS_MVIEW.REFRESH_DEPENDENT: 刷新所有依赖于指定主表或物化视图的物化视图

有时候不希望同时刷新所有物化视图。当主表中的基础数据更新后,使用该数据的所有物化视图都会变为陈旧。因此,如果推迟刷新物化视图,可以依赖所选的重写完整性级别来确定是否可以使用陈旧的物化视图进行查询重写。或者可以暂时使用 ALTER SYSTEM SET QUERY_REWRITE_ENABLED = FALSE 语句禁用查询重写。刷新物化视图后,可以使用 ALTER SYSTEM SET QUERY_REWRITE_ENABLED = TRUE 语句重新启用查询重写。

四、 刷新方式

1. 完全刷新:

   • 当物化视图初始定义为 BUILD IMMEDIATE 时会执行完全刷新,除非引用了预构建的表。
   • 对于使用 BUILD DEFERRED 的物化视图,必须先请求完全刷新才能首次使用。
   • 在物化视图的生命周期中,可以随时请求完全刷新。
   • 完全刷新需要读取详细表以计算物化视图的结果,这可能是一个非常耗时的过程,尤其是在有大量数据需要读取和处理的情况下。
   • 在请求完全刷新之前,应该考虑处理完全刷新所需的时间。
   • 当物化视图不满足快速刷新的条件时,完全刷新可能是唯一可用的刷新方法。

2. 快速刷新:

   • 快速刷新通常更高效,因为它只需应用变更到现有数据,而不是重新计算整个物化视图。
   • 只处理变更可以使刷新时间大大缩短。

3. PCT 刷新:

   • 当详细表上发生了分区维护操作时,这是唯一可用的快速刷新方法。
   • 如果没有分区维护操作,在通过 DBMS_MVIEW 包的过程请求"快速"刷新时,Oracle 会先尝试基于日志的快速刷新,然后才选择 PCT 刷新。
   • 当请求"强制"刷新时,Oracle 会按照以下顺序选择刷新方法:基于日志的快速刷新、PCT 刷新、完全刷新。
   • 也可以直接请求 PCT 刷新方法,前提是满足 PCT 要求。
   • 如果物化视图满足条件,Oracle 可以使用 TRUNCATE PARTITION 来提高 PCT 刷新的效率。

4. 提交时刷新(ON COMMIT):

   • 物化视图可以使用 ON COMMIT 方式自动刷新。
   • 每当更新物化视图定义的表时,提交事务后这些变更会自动反映在物化视图中。
   • 优点是不需要手动刷新物化视图。
   • 缺点是提交事务的时间会略有延长,因为需要额外的处理。但在数据仓库中这通常不是问题。

5. 手动刷新(DBMS_MVIEW 包):

   • 对 ON DEMAND 刷新的物化视图,可以指定以下四种刷新方法之一。

   • 可以在创建物化视图时定义默认刷新方法。

   • COMPLETE C 通过重新计算物化视图的定义查询来刷新。

   • FAST F 通过增量地将更改应用于物化视图来刷新。对于本地物化视图，它选择优化器估计的最有效的刷新方法。考虑的刷新方法是基于日志的 FAST 和 FAST_PCT。

   • FAST_PCT P 通过重新计算物化视图中受详细表中更改的分区影响的行来刷新。

   • FORCE ? 尝试快速刷新。如果不可能，它会进行完全刷新。对于本地物化视图，它会选择优化器估计的最有效的刷新方法。刷新方法是基于日志的 FAST、FAST_PCT 和 COMPLETE。

五、分类

1、只读物化视图

• 不允许对物化视图执行 DML 操作。
• 应用程序可以查询只读物化视图中的数据,以避免访问主站点,无论网络是否可用。
• 但是需要访问主表进行 DML 操作,这些变更会在下次刷新时传输到物化视图。

2、可更新物化视图

• 可以对可更新物化视图执行插入、更新和删除操作。
• 这些变更会通过高级复制的 PUSH 机制传播到主表。
• 主表上的变更也会通过 REFRESH 机制拉取到物化视图。
• 同时在主表和物化视图上执行 DML 操作可能会产生冲突。

可更新物化视图的限制:

• 必须基于单个表,尽管可以在子查询中引用多个表。
• 必须支持快速刷新。
• 主表必须注册到复制组。
• 物化视图必须属于与主站点复制组同名的物化视图组。
• 物化视图必须位于与主复制组不同的数据库中。
• 物化视图的名称必须与其主表相同。
• 要使物化视图可更新,创建语句必须包含 “FOR UPDATE” 子句。

六、监控物化视图

1. 确定特定MVIEW是否正在刷新

column owner format a15
column username format a15
column mview format a15
select o.owner, o.object_name mview, username, s.sid
from v$lock l, dba_objects o, v$session s
where o.object_id=l.id1 and
l.type='JI' and
l.lmode=6 and
s.sid=l.sid and
o.object_type='TABLE';


2. 判断刷新组是否正在刷新

刷新刷新组有两种可能的方法：

• 刷新正在后台由作业队列进程运行。
• 正在 Sql*Plus 或其他工具中手动运行刷新。

select s.sid, s.username
from dba_jobs_running jr, v$session s, dba_jobs j
where jr.sid=s.sid and
j.job=jr.job and
upper(j.what) like '%REFRESH%%';


column rowner format a15
column rname format a15
column sid format 9999
select username, sid, rowner, rname
from ( select username, s.sid, rc.rowner, rc.rname, count(*)
from v$lock l, dba_objects o, v$session s,
dba_refresh_children rc
where o.object_id=l.id1 and
l.type='JI' and
l.lmode=6 and
s.sid=l.sid and
o.object_type='TABLE' and
o.object_name=rc.name and
o.owner=rc.owner and
rc.type='SNAPSHOT'
group by username, s.sid, rc.rowner, rc.rname
having count(*)=( select count(*) from dba_refresh_children
where rowner= rc.rowner and rname=rc.rname and
type='SNAPSHOT') );


3. 确定上次和下次刷新日期

如果刷新是由作业队列进程自动完成或通过手动执行dbms_job.run() 完成，则可以通过查询 dba_jobs来查找刷新的下一个和最后一个刷新时间，

alter session set nls_date_format='yyyy-mm-dd hh24:mi:ss';

column what format a36
select what, last_date, next_date
from dba_jobs
where upper(what) like 'ÛMS_REFRESH.REFRESH(%%.%%';


select rt.owner, rt.name, rt.last_refresh
from dba_refresh_children rc, dba_snapshot_refresh_times rt
where rc.owner=rt.owner and
rc.name =rt.name and
rc.rname='' and
rc.owner='';


4. 确定刷新组中的哪个 MVIEW 正在刷新

select currmvowner, currmvname
from v$mvrefresh
where sid=;


5. 确定刷新当前阶段

column sid format 9999
column state format a26
select l.sid,
decode( count(*), 0, 'No propagation in progress',
'Propagation in progress' ) State
from v$lock l, dbms_lock_allocated la
where l.type='UL' and
l.lmode=4 and
l.id1=la.lockid and
la.name='ORA$DEF$EXE$PushCommonLock'
group by l.sid;


column sid format 9999

select l.sid, 'Currently propagating to ' || substr(la.name, 13)
from v$lock l, dbms_lock_allocated la
where l.type='UL' and
l.lmode=6 and
l.id1=la.lockid and
la.name like 'ORA$DEF$EXE$%';


column sid format 9999
select l.sid,
decode( count(*), 0, 'No purge in progress',
'Purge is in progress' ) State
from v$lock l, dbms_lock_allocated la
where l.type='UL' and
l.lmode=6 and
l.id1=la.lockid and
la.name='ORA$DEF$EXE$PurgeCommonLock'
group by l.sid;


connect / as sysdba
column "MVIEW BEING REFRESHED" format a30
column INSERTS format 9999999
column UPDATES format 9999999
column DELETES format 9999999
select CURRMVOWNER_KNSTMVR || '.' || CURRMVNAME_KNSTMVR
"MVIEW BEING REFRESHED",
decode( REFTYPE_KNSTMVR, 1, 'FAST', 2, 'COMPLETE', 'UNKNOWN' ) REFTYPE,
decode(GROUPSTATE_KNSTMVR, 1, 'SETUP', 2, 'INSTANTIATE',
3, 'WRAPUP', 'UNKNOWN' ) STATE,
TOTAL_INSERTS_KNSTMVR INSERTS,
TOTAL_UPDATES_KNSTMVR UPDATES,
TOTAL_DELETES_KNSTMVR DELETES
from X$KNSTMVR X
WHERE type_knst=6 and
exists (select 1 from v$session s
where s.sid=x.sid_knst and
s.serial#=x.serial_knst);


七、取消刷新

COLUMN JOB HEADING 'Job ID' FORMAT 999999
COLUMN PRIV_USER HEADING 'Privilege|Schema' FORMAT A10
COLUMN RNAME HEADING 'Refresh|Group|Name' FORMAT A10
COLUMN ROWNER HEADING 'Refresh|Group|Owner' FORMAT A10
COLUMN BROKEN HEADING 'Broken?' FORMAT A7

SELECT J.JOB,
J.PRIV_USER,
R.ROWNER,
R.RNAME,
J.BROKEN
FROM DBA_REFRESH R, DBA_JOBS J
WHERE R.JOB = J.JOB
ORDER BY 1;


col last_date format a10
col last_sec format a10
col next_sec format a10
col interval format a20
col what format a30
col what broken a10

select job,last_date, last_sec,
total_time,next_date,next_sec,what,interval,broken
from DBA_JOBS
where job=n;


SELECT * FROM V$MVREFRESH;


ALTER SYSTEM KILL SESSION 19,233;


BEGIN
DBMS_JOB.BROKEN ( n, true);
commit;
END;


八、终止挂起的刷新

exec dbms_job.broken (, TRUE);


select a.spid, b.sid, b.username
from v$process a, v$session b
where a.addr = b.paddr and sid =xxx;


ps -ef | grep spid

kill -9 spid


exec dbms_job.run(job);