# 数据库教程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）

```bash

#确认主机名称

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`查看关键参数

```sql

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标准配置

```sql

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 用户权限准备

```sql

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用户，准备基表测试数据。

```sql

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增量刷新必须在基表建立物化视图日志

```sql

CREATE MATERIALIZED VIEW LOG ON t_sales_mv

WITH PRIMARY KEY,ROWID,SEQUENCE

INCLUDING NEW VALUES;

```

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

```sql

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 验证查询重写是否生效

```sql

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包

```sql

--完全刷新 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 修改物化视图属性，关闭/开启查询重写

```sql

alter materialized view mv_sales_month_stat disable query rewrite;

alter materialized view mv_sales_month_stat enable query rewrite;

```

#### 2.2.6 查询物化视图相关字典

```sql

--查看物化视图基础信息，刷新状态、重写开关

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，此处仅演示语法。

```sql

--创建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，任务才会注册到系统。

```sql

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查询、修改、停止、删除

```sql

--查询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程序对象，绑定存储过程

```sql

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点执行

```sql

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

```sql

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任务常用管理操作

```sql

--手动运行任务

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查询监控视图

```sql

--查看任务定义

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资源。

```sql

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 查看系统内置自动维护任务

```sql

--查看维护窗口

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定时任务回退手段；尽量将刷新调度放在业务低峰期执行。