数据库教程FGMT15‑Oracle性能优化之物化视图与任务管理

前言

在Oracle数据仓库、OLAP统计分析业务场景，复杂多表关联、聚合分组SQL反复执行会消耗大量CPU与IO资源，物化视图可以预计算并物理保存聚合结果集，配合查询重写特性，业务SQL无需修改即可自动使用预计算结果，大幅降低查询开销。同时数据库各类统计收集、数据同步、数据归档、报表生成等周期性工作，依赖数据库内部定时任务完成，Oracle提供传统DBMS_JOB与新一代DBMS_SCHEDULER调度组件，实现各类任务自动化运维。风哥教程本文围绕物化视图底层原理、各类刷新模式、物化视图日志、查询重写、跨库高级复制场景，以及定时任务完整管理、Scheduler高级对象（program、job class、window窗口）、系统自动维护任务展开完整讲解。风哥 itpux‑com

本套风哥教程面向DBA、数据仓库开发、运维工程师，全部实验标准化环境配置：主机名称fgedu‑net‑cn，硬件规格64G物理内存、8颗CPU；数据库实例名fgedudb，数据库名fgedudb，测试业务用户名fgedu，文件根目录统一为/fgedudb，整套实验基于Oracle19c企业版完成。风哥教程本文分为前言大纲介绍、核心理论知识、实战操作演练、总结四大模块；实战章节包含大量可直接复制运行SQL脚本，读者可以在测试环境完整复现全部实验现象，掌握物化视图设计创建、刷新调优，以及数据库定时任务全套运维手段。网上搜索风哥教程可以学习全套数据库教程

内容大纲

1. 物化视图基础概念，普通逻辑视图与物化视图的本质区别
2. 物化视图各类刷新模式：COMPLETE、FAST、FORCE、NEVER，物化视图日志原理与约束
3. 查询重写QUERY_REWRITE工作原理、初始化参数、启用与限制条件
4. 物化视图两大业务场景：查询性能优化、高级复制快照复制
5. 物化视图日常运维：修改、刷新、重建、删除，相关数据字典视图
6. DBMS_JOB传统定时任务包原理、参数、创建、修改、删除、故障排查
7. DBMS_SCHEDULER新一代调度器整体架构，Program、Schedule、Job基础对象
8. Scheduler高级对象：Job Class任务类、Window时间窗口、任务链Job Chain
9. Scheduler任务监控视图、任务日志、失败任务处理
10. Oracle系统内置自动维护任务，统计信息收集、AWR快照、优化器顾问任务
11. 生产环境物化视图+定时任务综合案例，风险点、运维规范、故障排查流程

一、核心理论知识

本章节为本套风哥教程理论基础，充分理解物化视图刷新机制、查询重写约束、新旧调度组件差异，才能够避免出现刷新失败、查询重写不生效、定时任务异常不执行等生产故障。风哥教程 113257174

1.1 物化视图基础概念

普通逻辑视图（VIEW）只保存SQL定义语句，访问视图时实时解析执行底层SQL，不存储任何数据。物化视图（MATERIALIZED VIEW）是物理实体段，会把定义SQL的查询结果物理存储在磁盘上，拥有自己的数据段、索引，可以建立普通索引、分区，支持DML以外的完整表属性。
物化视图两大典型应用场景：

1. 查询优化场景：数据仓库复杂聚合、多表关联，预计算结果，依靠查询重写，业务SQL不需要修改，优化器自动改写SQL走物化视图，减少大表扫描与join开销。
2. 高级快照复制场景：跨库数据同步，通过数据库链路dblink，把远端库基表数据同步到本地物化视图，实现简单的数据复制同步。

关键提醒：物化视图的数据不会自动跟随基表变化，必须执行刷新操作，才能够和基表保持数据一致性。网上搜索风哥教程可以学习全套数据库教程

1.2 物化视图刷新模式与物化视图日志

1. COMPLETE（完全刷新）：清空物化视图全部数据，重新完整执行定义SQL，全量重新生成结果集。适合数据变化量很大、无法快速增量刷新场景，消耗IO、CPU资源较高。
2. FAST（快速增量刷新）：只同步基表发生变更的数据，不做全量重计算。FAST刷新强制依赖物化视图日志（MATERIALIZED VIEW LOG），基表发生insert/update/delete变更，变更记录写入mlog$_日志表，刷新的时候只读取日志变更记录同步到物化视图。

FAST刷新存在大量约束：聚合类物化视图日志需要指定ROWID、PRIMARY KEY、SEQUENCE INCLUDING NEW VALUES；部分复杂SQL语法不支持fast刷新。

3. FORCE（强制刷新）：优先尝试FAST增量刷新，如果条件不满足自动降级执行COMPLETE完全刷新，生产环境最常用的刷新模式。
4. NEVER：从不执行刷新，物化视图数据冻结，只用作静态数据集。

物化视图日志：建立在基表之上的特殊日志表，记录基表DML变更，供fast刷新消费；如果日志堆积没有被物化视图消费，日志表会持续膨胀占用表空间。风哥数据库教程 itpux‑com

1.3 查询重写QUERY_REWRITE原理

查询重写是物化视图提升性能的核心能力：用户提交针对原始基表的SQL，优化器在CBO成本计算阶段自动识别，将SQL改写为直接访问物化视图，业务代码不需要做任何修改。
生效必须同时满足条件：

1. 实例参数QUERY_REWRITE_ENABLED=TRUE；
2. 物化视图对象层面开启ENABLE QUERY REWRITE；
3. CBO优化器模式，不能使用RBO；
4. 重写完整性参数，ENFORCED模式下，物化视图必须是最新刷新完成状态，脏数据不会被用于重写；
5. SQL语句语法、约束、维度对象满足重写语法限制，部分子查询、分析函数语法不支持查询重写。

1.4 DBMS_JOB传统定时任务组件

DBMS_JOB是Oracle早期的定时任务包，19c依然向下兼容，但是已经标记为逐步废弃。
特点：

1. 仅支持执行PL/SQL代码块，不能直接调用操作系统shell脚本；
2. 调度逻辑依靠SYSDATE算术表达式；
3. 任务执行日志记录简单，失败排查信息少；
4. 后台进程CJQ0负责调度，初始化参数job_queue_processes控制任务最大并发数量；

注意：DBMS_JOB提交的时候必须commit，否则任务不会注册到系统；job_queue_processes=0会直接全部禁用job。

1.5 DBMS_SCHEDULER调度器新一代组件

DBMS_SCHEDULER是Oracle10g之后主推的现代化调度框架，完全替代DBMS_JOB，19c内部DBMS_JOB调用底层实际会转换为Scheduler任务。
核心模块化对象：

1. Program（程序）：定义要执行的动作，可以是存储过程、PL/SQL块、操作系统外部可执行脚本，动作定义可以复用给多个job。
2. Schedule（调度计划）：定义执行时间日历表达式FREQ=DAILY;BYHOUR=2，时间调度规则独立，可以被多个job复用。
3. Job（任务）：绑定program与schedule，形成完整可执行定时任务。
4. Job Class（任务类）：把job归入消费组，可以对接前面DBRM资源管理器，限制任务CPU、并行资源，控制任务优先级。
5. Window（时间窗口）：定义时间区间，窗口激活自动切换资源计划，适合夜间维护任务。
6. Job Chain任务链：实现多任务串行、条件分支执行，上一步成功/失败执行不同子任务。

Scheduler自带完整任务运行历史日志视图，dba_scheduler_job_run_details可以查看每一次执行开始、结束时间、报错错误码，故障排查能力远强于DBMS_JOB。

1.6 Oracle内置自动维护任务

Oracle19c数据库自带三套系统自动维护任务，运行在维护窗口内：

1. auto optimizer stats collection：自动统计信息收集任务；
2. auto space advisor：段空间顾问；
3. sql tuning advisor：SQL调优顾问。
   这些任务全部基于DBMS_SCHEDULER+Window窗口实现，可以查询、启用、禁用、修改维护窗口时间。

1.7 生产环境风险总结

1. FAST刷新不要忘记维护基表物化视图日志，日志长时间未消费，mlog$_表持续膨胀占满表空间；
2. 查询重写不生效，优先核对实例参数、对象enable query rewrite开关、物化视图是否为最新状态；
3. DBMS_JOB任务忘记commit，任务不会生效；job_queue_processes等于0，所有定时任务停止调度；
4. Scheduler执行操作系统脚本，需要配置外部凭证credential对象，否则执行操作系统脚本报错；
5. 物化视图完全刷新会产生大量redo/undo，业务高峰禁止执行COMPLETE刷新，尽量避开业务峰值窗口。

二、实战操作演练

本套风哥教程全部实战操作，操作主机fgedu‑net‑cn，数据库fgedudb，业务用户fgedu，目录/fgedudb，硬件规格64G内存8CPU。

环境说明：操作系统登录oracle用户，设置环境变量指向fgedudb实例；物化视图、调度任务操作需要对应权限，部分管理视图需要sysdba查看。

2.1 操作系统与数据库环境校验

2.1.1操作系统层面检查（主机fgedu‑net‑cn）

#确认主机名称
hostname
#确认ORACLE_HOME路径全部指向/fgedudb
echo $ORACLE_HOME
#查看内存硬件，确认64G内存
free -h
#查看CPU，确认8CPU
lscpu
#确认dump、diag目录存在
ls -ld /fgedudb/diag /fgedudb/dump

校验输出：hostname输出fgedu‑net‑cn，总内存64G，逻辑CPU数量8。

2.1.2 数据库关键初始化参数确认（64G内存8CPU规格）

登录sqlplus / as sysdba查看关键参数

show parameter memory_target;
show parameter sga_target;
show parameter pga_aggregate_target;
show parameter query_rewrite_enabled;
show parameter job_queue_processes;
show parameter statistics_level;

适配64G内存主机spfile标准配置

alter system set memory_max_target=48G scope=spfile;
alter system set memory_target=48G scope=spfile;
alter system set statistics_level=TYPICAL scope=spfile;
alter system set query_rewrite_enabled=TRUE scope=both;
alter system set job_queue_processes=40 scope=spfile;

job_queue_processes控制调度后台进程数量，0代表禁用全部定时任务；重启实例spfile参数生效。

2.1.3 用户权限准备

create user fgedu identified by fgedudb default tablespace users temporary tablespace temp;
grant connect,resource to fgedu;
grant select_catalog_role to fgedu;
grant create materialized view to fgedu;
grant create job to fgedu;
grant execute on dbms_mview to fgedu;
grant execute on dbms_job to fgedu;
grant execute on dbms_scheduler to fgedu;

2.2 物化视图基础实战（查询优化场景）

切换fgedu用户，准备基表测试数据。

conn fgedu/fgedudb@fgedudb
--创建业务销售基表
CREATE TABLE t_sales_mv(
    sale_id NUMBER,
    sale_date DATE,
    cust_id NUMBER,
    prod_id NUMBER,
    amount NUMBER(12,2)
);
--插入测试数据
INSERT INTO t_sales_mv
SELECT rownum,SYSDATE‑mod(rownum,120),mod(rownum,500),mod(rownum,200),DBMS_RANDOM.VALUE(10,5000)
FROM dual CONNECT BY rownum<=120000;
COMMIT;
CREATE INDEX idx_sales_mv_dt ON t_sales_mv(sale_date);

2.2.1 创建物化视图日志（支持FAST快速刷新）

FAST增量刷新必须在基表建立物化视图日志

CREATE MATERIALIZED VIEW LOG ON t_sales_mv
WITH PRIMARY KEY,ROWID,SEQUENCE
INCLUDING NEW VALUES;

2.2.2 创建聚合物化视图，开启查询重写，FORCE刷新模式

CREATE MATERIALIZED VIEW mv_sales_month_stat
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
ENABLE QUERY REWRITE
AS
SELECT TRUNC(sale_date,'MONTH') sale_month,prod_id,
SUM(amount) total_amt,COUNT(*) total_cnt
FROM t_sales_mv
GROUP BY TRUNC(sale_date,'MONTH'),prod_id;

参数说明：

• BUILD IMMEDIATE：创建的时候立刻填充数据；BUILD DEFERRED创建空物化视图，后续手动第一次刷新；
• REFRESH FORCE ON DEMAND：手动按需刷新，优先fast，失败自动complete；
• ENABLE QUERY REWRITE：开启查询重写特性。

2.2.3 验证查询重写是否生效

explain plan for
SELECT TRUNC(sale_date,'MONTH') sale_month,prod_id,SUM(amount) total_amt
FROM t_sales_mv
GROUP BY TRUNC(sale_date,'MONTH'),prod_id;
select * from table(dbms_xplan.display());

执行计划出现MAT_VIEW REWRITE ACCESS FULL代表查询重写成功，SQL访问mv_sales_month_stat物化视图，不再扫描基表t_sales_mv。网上搜索风哥教程可以学习全套数据库教程

2.2.4 物化视图手动刷新 DBMS_MVIEW包

--完全刷新 COMPLETE
exec dbms_mview.refresh('mv_sales_month_stat','C');
--快速增量刷新 FAST
exec dbms_mview.refresh('mv_sales_month_stat','F');
--FORCE模式，优先fast失败降级complete
exec dbms_mview.refresh('mv_sales_month_stat','?');

2.2.5 修改物化视图属性，关闭/开启查询重写

alter materialized view mv_sales_month_stat disable query rewrite;
alter materialized view mv_sales_month_stat enable query rewrite;

2.2.6 查询物化视图相关字典

--查看物化视图基础信息，刷新状态、重写开关
select mview_name,refresh_method,refresh_mode,rewrite_enabled,rewrite_capable
from user_mviews;

--查看物化视图日志信息
select master_log,log_table from user_mview_logs;

--查看物化视图日志表数据（观察DML变更记录）
select * from mlog$_t_sales_mv fetch first 10 rows only;

2.3 跨库快照复制物化视图实战（dblink高级复制场景）

本示例演示通过database link拉取远端库数据，生产环境需要提前创建dblink，此处仅演示语法。

--创建database link（示例，替换真实远端库信息）
create database link fgedu_remote connect to fgedu identified by fgedudb using 'fgedudb_remote';

--创建远端表快照物化视图
CREATE MATERIALIZED VIEW mv_remote_sales
BUILD IMMEDIATE
REFRESH FORCE ON DEMAND
AS
SELECT * FROM t_sales_mv@fgedu_remote;

2.4 DBMS_JOB传统定时任务实战

注意：DBMS_JOB提交必须执行commit，任务才会注册到系统。

conn fgedu/fgedudb@fgedudb
--创建测试存储过程，用于job调用
create or replace procedure p_mv_refresh_proc as
begin
  dbms_mview.refresh('mv_sales_month_stat','?');
end;
/

--提交dbms_job定时任务，每天凌晨2点执行物化视图刷新
declare
v_jobno number;
begin
dbms_job.submit(
job=>v_jobno,
what=>'p_mv_refresh_proc;',
next_date=>to_date('2026‑09‑11 02:00:00','yyyy‑mm‑dd hh24:mi:ss'),
interval=>'sysdate+1'
);
dbms_output.put_line('job编号:'||v_jobno);
end;
/
commit; --必须commit！job才生效

DBMS_JOB查询、修改、停止、删除

--查询job信息
select job,what,next_date,broken,failures from user_jobs;

--修改job，修改下次执行时间
exec dbms_job.change(job=>41,next_date=>sysdate+1/(24*60));

--手动立刻执行job
exec dbms_job.run(41);

--标记job broken，不再调度
exec dbms_job.broken(41,true);

--删除job
exec dbms_job.remove(41);
commit;

2.5 DBMS_SCHEDULER新一代调度器完整实操

2.5.1 创建Program程序对象，绑定存储过程

conn fgedu/fgedudb@fgedudb
BEGIN
dbms_scheduler.create_program(
program_name=>'PROG_MV_REFRESH',
program_type=>'STORED_PROCEDURE',
program_action=>'P_MV_REFRESH_PROC',
enabled=>TRUE,
comments=>'物化视图刷新程序'
);
END;
/

2.5.2 创建Schedule调度日历，每天凌晨02点执行

BEGIN
dbms_scheduler.create_schedule(
schedule_name=>'SCH_DAILY_02AM',
repeat_interval=>'FREQ=DAILY;BYHOUR=2;BYMINUTE=0;BYSECOND=0',
comments=>'每天凌晨2点执行'
);
END;
/

2.5.3 创建Job任务，绑定program与schedule

BEGIN
dbms_scheduler.create_job(
job_name=>'JOB_MV_DAILY_REFRESH',
program_name=>'PROG_MV_REFRESH',
schedule_name=>'SCH_DAILY_02AM',
enabled=>TRUE,
comments=>'每日凌晨物化视图刷新定时任务'
);
END;
/

2.5.4 Scheduler任务常用管理操作

--手动运行任务
exec dbms_scheduler.run_job('JOB_MV_DAILY_REFRESH');

--禁用任务
exec dbms_scheduler.disable('JOB_MV_DAILY_REFRESH');

--启用任务
exec dbms_scheduler.enable('JOB_MV_DAILY_REFRESH');

--停止正在运行的任务
exec dbms_scheduler.stop_job('JOB_MV_DAILY_REFRESH',force=>false);

--删除job
exec dbms_scheduler.drop_job('JOB_MV_DAILY_REFRESH');

--删除program、schedule
exec dbms_scheduler.drop_program('PROG_MV_REFRESH');
exec dbms_scheduler.drop_schedule('SCH_DAILY_02AM');

2.5.5 Scheduler查询监控视图

--查看任务定义
select job_name,enabled,program_name,schedule_name from user_scheduler_jobs;

--查看任务运行历史记录，报错信息
select job_name,log_id,actual_start_date,status,error#,additional_info
from user_scheduler_job_run_details order by actual_start_date desc;

status字段值：SUCCEEDED成功，FAILED执行失败，STOPPED人为停止；error#记录ORA错误编号，排查定时任务故障优先查询该视图。风哥数据库教程 itpux‑com

2.5.6 Job Class任务类对接资源管理器（高级）

可以把scheduler job归入job class，映射DBRM消费组，限制任务CPU资源。

conn / as sysdba
BEGIN
dbms_scheduler.create_job_class(
job_class_name=>'JOB_CLASS_MV_GROUP',
resource_consumer_group=>'FGEDU_BATCH',
comments=>'物化视图批量任务组，映射批量消费组'
);
END;
/
--修改job归属job_class
BEGIN
dbms_scheduler.set_attribute(
name=>'FGEDU.JOB_MV_DAILY_REFRESH',
attribute=>'JOB_CLASS',
value=>'JOB_CLASS_MV_GROUP'
);
END;
/

2.6 查看系统内置自动维护任务

--查看维护窗口
select window_name,repeat_interval,enabled from dba_scheduler_windows;

--查看内置自动任务
select client_name,status from dba_autotask_client;

2.7 综合故障排查流程

1. 物化视图故障排查

   1. 刷新FAST报错：检查基表物化视图日志是否存在；确认物化视图定义语法是否支持fast刷新；
   2. 查询重写不生效：核对query_rewrite_enabled=TRUE；确认mv对象enable query rewrite；确认mv数据是最新刷新状态；
   3. mlog$_日志持续暴涨：物化视图长期没有执行刷新，日志无法被消费，执行一次刷新释放日志。

2. 定时任务故障排查

   1. DBMS_JOB不执行：确认job_queue_processes>0；提交job之后是否执行commit；broken标记是否为Y；查看user_jobs.failures失败计数；
   2. Scheduler任务不执行：查询user_scheduler_job_run_details看status、error#报错；确认job是enabled状态；日历repeat_interval语法是否合法；
   3. 操作系统类型program执行失败：必须配置credential凭证对象。

三、风哥针对本文总结

本套风哥教程完整覆盖Oracle物化视图与任务管理整套知识，包含物化视图与普通视图本质区别、COMPLETE/FAST/FORCE/NEVER四类刷新模式、物化视图日志约束、查询重写原理，物化视图查询优化、快照复制两套业务场景；同时讲解DBMS_JOB传统定时任务，新一代DBMS_SCHEDULER调度器Program/Schedule/Job/Job Class/Window高级对象，系统内置自动维护任务，全套实战脚本与故障排查流程。

1. 物化视图是物理存储段，不会自动跟随基表同步，必须调用dbms_mview刷新；FAST增量刷新依赖基表物化视图日志；FORCE模式优先增量刷新，失败自动降级完全刷新，生产场景优先选用FORCE。
2. 查询重写不需要修改业务SQL，但必须满足三层条件：实例参数query_rewrite_enabled=TRUE、物化视图对象开启enable query rewrite、物化视图数据处于最新状态，同时SQL语法满足重写约束。
3. 注意物化视图日志（mlog$_xxx）表空间膨胀风险，如果物化视图长期不刷新，DML变更会持续堆积在日志表，占用大量存储，需要定期执行刷新消费日志数据。完全刷新COMPLETE会产生大量redo/undo，业务高峰窗口禁止执行。
4. DBMS_JOB属于传统兼容组件，19c虽然支持，但Oracle官方推荐新项目全部使用DBMS_SCHEDULER；DBMS_JOB提交任务必须commit，否则任务不会注册生效。
5. DBMS_SCHEDULER采用模块化对象设计，Program、Schedule可以复用；Job Class可以对接数据库资源管理器DBRM，限制定时任务CPU、并行资源，避免批量任务打满实例资源；任务运行历史保存在*_scheduler_job_run_details，故障排查优先查看该视图错误编号。
6. job_queue_processes初始化参数控制后台调度进程数量，设置为0会直接禁用全部DBMS_JOB任务，Scheduler不受该参数完全限制。
7. Oracle系统内置三套自动维护任务，运行在Scheduler维护Window窗口，不要随意禁用；业务自建定时任务尽量避开系统维护窗口，防止资源争抢。
8. 生产环境物化视图+定时任务上线前，需要完整验证刷新耗时、资源消耗；准备应急脚本：手动刷新mv、临时disable定时任务回退手段；尽量将刷新调度放在业务低峰期执行。